Flagship Course on Microsoft Excel
This course overview:
- Course duration – 20 Hours
- Updated and well-organized course content.
- Details and practical content.
- 8 years + experienced trainer.
- Personalized learning methods.
- 15+ practical exams and quizzes.
- 30+ case studies.
- QA session.
At the end of the course participants will be able to:
- Analyze data using complex functions and tools.
- Effectively organize data into a spreadsheet.
- Get external data into Excel with power query.
- Manage Excel reports in an advanced pivot and power pivot.
- Apply advanced formatting to the worksheet.
Course description and objectives
The course is aimed to analyze the main advanced features of Excel, including complex functions, what-if analysis, pivot tables, and macros, with specific examples and exercises that can be immediately applied to one’s study and work. The goal is to improve the advanced skills of Excel users, to give them the tools for an effective and qualified approach to both academic and professional activities.
Benefits of this course
- At the end of this advanced Excel course you will have a complete understanding of the higher functions of Excel.
- You will be able to perform the most advanced calculations and functions, manipulate data using pivot tables and ‘advanced’ analysis, record macros and integrate with other office applications.
Course Content
Formula
- What is Formula?
- How to use formula?
- Difference between formula and function?
Basic Function
- SUM
- AVERAGE
- MIN
- MAX
- COUNT
- COUNTA
- COUNTBLANK
Cell References
- Relative Cell References
- Absolute Cell References
- Mixed Cell References
Working with Multiple Sheets
- 3d sum
- Consolidation Static All Same
- Consolidation Linked All Same
Exam 1 & 2
Mathematical Functions
- SumIf
- SumIfs
- CountIf
- CountIfs
- AverageIf
- AverageIfs
Exam 3 & 4
- General is default
- Structure and Reference
- Placeholders
- Number formats for TEXT
Exam – 5
- Today
- Now
- Day
- Month
- Year
- Date
- Edate
- Date Calculations
- Datedif
- YEARFRAC
Exam – 6
- Upper
- Lower
- Proper
- Left
- Mid
- Right
- Trim
- Len
- Exact
- Concatenate
- Textjoin
- Rept
- Find
- Search
- Replace
- Substitute
- Advanced Text Functions
Exam -7
- Filtering on Text, Numbers
- Sorting Options
- Advanced Filters on different criteria(s)
- What are the Array Formulas, Use of the Array Formulas?
- Basic Examples of Arrays (Using ctrl+shift+enter).
- Array with len and mid functions formulas.
- Sumproduct
- Offset function
- Indirect function
- Name Range
- Goal Seek
- Scenario Analysis
- Data Tables (PMT Function)
Exam – 8
- Number, Date & Time Validation
- Text and List Validation
- Custom validations based on formula for a cell
- Dynamic Dropdown List Creation using Data Validation – Dependency List
- If Functions
- How to Fix Errors – iferror
- Nested If
- Complex if and or functions
Exam – 9
- VLOOKUP ( EM)
- VLOOKUP (AM)
- Hlookup
- Advanced Vlookup
- Match
- Vlookup and Match
- Nested VLookup
- Advanced Index and Match
Exam (10, 11 & 12)
- Introduction of Power Query
- Query Editor in Power Query
- Getting Data from Current Workbook Table/Range
- Import Data from Text Files
- Import and Transform Data from CSV
- Append Method | Combine Excel Worksheets
- Combine Excel Worksheets
- Append Data from Multiple Workbooks
- Append Data from Folder
- Creating Simple Pivot Tables
- Basic and Advanced Value Field Setting
- Classic Pivot table
- Grouping based on numbers and Dates
- Calculated Field & Calculated Items
- Adding Slicers Tool in Pivot & Tables & Charts
- Exam 13
- Introduction
- Why use Power Pivot?
- Installing Power Pivot
- Importing Data into Power Pivot
- Modeling the Data
- Creating PivotTables and Slicers from our Model
- Introduction to Excel Macros
- Recording and Running a Macro
- Absolute vs Relative
- Assigning Buttons to Macros
- Assigning Macros to a Shape
- Deleting a Macro