This advanced Excel course is designed for participants who need to analyse and summarise complex data..Coverage includes advanced tools such as formulas, macros, and advanced charting techniques. Participants will work through practical examples that demonstrate the power of Excel tools and features.
Learning Outcomes
Create advanced formulas including array and lookup functions
Use PivotTables to summarise and analyse large data sets
Automate tasks using macros and recording tools
Troubleshoot and audit complex formulas
Key Topics:
Advanced functions
PivotTables and PivotCharts
Macro recording
Formula auditing and error tracing
Module 1. Using Auditing Tools and Range Names
- Using Range Names
- Using Range Names in Formulas
- Creating Range Names from Headings
- Deleting Range Names
- Using Range Names in 3-D Formulas
- Displaying/Removing Dependent Arrows
- Displaying/Removing Precedent Arrows
- Showing Formulas
- Labs
Module 2. Using Advanced Functions
- Using the VLOOKUP Function
- Using the HLOOKUP Function
- Using the XLOOKUP Function
- Using the IF Function
- Using the IFS Function
- Using the IFERROR Function
- Using an AND Condition with IF
- Using an OR Condition with IF
- Using COUNTIF Function
- Using SUMIF Functions
- Using SUMIFs Functions
- Using MAXIFS Functions
- Using MINIFS Functions
- Using Text Functions (UPPER,LOWER, PROPER, TRIM, TEXTJOIN)
- Labs
Module 3. Creating/Revising PivotTables
- Use Recommended PivotTable
- Adding PivotTable Report Fields
- Selecting a Page Field Item
- Refreshing a PivotTable Report
- Changing the Summary Function
- Adding New Fields to a PivotTable Report
- Moving PivotTable Report Fields
- Hiding/Unhiding PivotTable Report Items
- Deleting PivotTable Report Fields
- Creating a Page Field Report
- Formatting a PivotTable Report
- Creating a PivotChart Report
- Labs
Module 4. PivotTable Enhancement
- Introduction to Data model
- Setting relationship for Table
- Create PivotTable from Data Model
- Insert Slicer and Timeline for PivotTable
Module 5. Recording Macros
- Recording a Macro
- Saving a Macro-Enabled Workbook
- Running a Macro
- Assigning a Shortcut Key
- Using a Shortcut Key
- Deleting a Macro
- Adding a Macro to Quick Access Toolbar
- Deleting a Macro Button from QAT
- Labs
