Pro Excel and Data Analysts

Introduction:

Excel remains one of the most extensive pieces of software i.e. used in the workplaces on a Regular Basis. While making the Course, I focused on building strong foundations of Excel for the Professionals. This application provides an outstanding combination of functions and tools for working with quantitative data analyzing, reporting, and decision making. Scientist or Business Manager! Wherever you grow your career. You need Excel. It will give you a job, make you essential at your job, and bring you tremendous career success.

If you are a newcomer in Excel or after all these years of random use of Excel you want to start back learning Excel systematically, this course is for you. This is a 16 Hours course that will build you the skills and level of confidence you need to succeed in your career.

Learning Outcomes:

  1. Get Accuracy: Assure and maintain the integrity and accuracy of the Spreadsheets
  2. Gain Utility: Utilize the Power of the various inbuilt Excel Functions that are underutilized
  3. Learn Inquire Add-in: Learn in detail about the Inquire Add-in for Reviewer OR Auditor
  4. Do Analysis: Analyze your entire Workbook formulas, hidden sheets, macros, and a lot more
  5. Do Presentation: Get pictorial presentation of the Relationship between Workbook, worksheets and individual cells, PowerPoint
  6. Make Comparisons: Compare Multiple Versions of Various Files even at Cells Level (Formatting Changes /Changes made to the Macros line-by-line)
  7. Index Sheet Creation: Create an index Sheet within an Excel Sheet using Power Query

Methodology

Learners will have to fix a Real-life five-year transaction data table with multiple types of errors. The programme will be delivered using formal lectures combined with practical and interactive case studies and exercises. There will be a great emphasis on gaining practical experiences.

Contents of Training:

Session:1 (A Complete Guide to get started with Excel)

  • Microsoft Excel 2016 Into & Data entry tools
  • Useful keyboard shortcuts in Microsoft Excel
  • Excel Ribbon – Quick access toolbar
  • Select and Navigate Massive Data in Excel
  • Back Stage file management

Session:2 (Features to speed up your review)

  • Excel Watch Window – For displaying selected results all times
  • View/Compare and Monitor multiple files side by side
  • All items in the Manual explained: Go to Window and Special
  • Find and select cells formatting that meets specific conditions

Session :3 (Analysis of an Excel file using Inquire)

  • Relationship between Workbook/Worksheet and Cells
  • Text Compare – Compare files
  • Clear Formatting in Excel
  • Link Excel sheets to MS Word, PowerPoint

Session: 4 (Data Correction)

  • Remove duplicates
  • Conditional Formatting
  • COUNT-IF for data duplicate check
  • Date Function & Calculate years, months, day
  • Sorting & Filtering
  • Data tools
  • Outline

Session:5 (Data Management)

  • Data Validation
  • Left, Right & Mid Function
  • Flash Fill
  • Text to Column

Session:6 (Data Visualization)

  • Define Names
  • Table
  • Creating Macro
  • Assign Macro
  • Editing Macro Codes

Session:7 (FORMULA AUDITING)

  • Excel formula calculations based on Order of Operations
  • Identify Errors with Formula Auditing
  • Detect/Check errors in the Formula Text in Spreadsheets
  • Using Structured References like Relative & Absolute, Structured, Named Ranges/cells – Part 1
  • Using Structured References like Relative & Absolute, Structured, Named Ranges/cells – Part 2
  • Rounding Precision – Excel’s Precision as displayed

Session:8 (EXCEL PIVOT TABLE)

  • Pivot Tables, Data Models– Introduction
  • Create Pivot Table – Data Source – Single Sheets
  • Multiple Sheets, External Data, and Existing pivot table from Other Data Sources
  • how to create a data model
  • How to Filter the data – Report Level, Slicers, Timelines – Label & Value filters
  • Create a heat map data bars and sparklines in excel using conditional formatting
  • Calculated fields and items- Usage issues
  • Excel pivot table issues Resolved
  • How to Sort the data in Pivot Table – Ascending, Descending, Manual or Custom Sort
  • Group and rename your Data
  • Summarizing Excel Data using various options

Leave a Comment

Your email address will not be published. Required fields are marked *