Transform raw data into actionable insights with automated Excel dashboards. This course guides participants in creating a dynamic dashboard that updates automatically when data changes, using Excel features like tables, PivotTables, charts, slicers, and formulas.
Learning Outcomes:
Apply statistical and logical functions to summarise data
Create dynamic dashboards using PivotTables and charts
Use macros to automate data updates and interactions
Build user-friendly controls to filter and display insights
Design dashboards tailored to business requirements
Key Topics:
Range names, formula auditing, and logical functions
VLOOKUP, HLOOKUP, and MATCH/INDEX usage
PivotTable design and dynamic chart creation
Macro recording and assignment to dashboard controls
Interactive dashboard elements like checkboxes and combo boxes
Module 1. Auditing Tools and Range Name
- Jumping to a Named Range
- Assigning Names
- Using Range Names in Formulas
- Creating Range Names from Headings
- Applying Range Names
- Deleting Range Names
- Using Range Names in 3-D Formulas
- Displaying/Removing Dependent Arrows
- Displaying/Removing Precedent Arrows
- Showing Formulas
- Labs
Module 2. Statistical and Logical Functions
- Using the COUNTIF Function
- Using SUMIF Function
- Using AverageA Function
- Using IF function
- Using IsError function to avoid error display
- Creating multiple conditions using nested IF
- Using logical function OR, And within IF
- Labs
Module 3. Lookup and Reference Formulas
- Using Vlookup to find specific data
- Using Hlookup to find values in rows
- Using Match and Index to retrieve data
- Practice
Module 4. Conditional Formatting
- Creating Conditional Formatting
- Editing Conditional Formatting
- Adding Conditional Formatting
- Custom Number Formats
- Labs
Module 5. Creating PivotTables
- Creating a PivotTable Report
- Adding PivotTable Report Fields
- Selecting a Report Filter Field Item
- Refreshing a PivotTable Report
- Changing the Summary Function
- Adding New Fields to a PivotTable
- Moving PivotTable Report Fields
- Using Expand and Collapse Buttons
- Deleting PivotTable Report Fields
- Creating Report Filter Pages
- Formatting a PivotTable Report
- Creating a PivotChart Report
- Labs
Module 6. Recording Macros
- Recording a Macro
- Assigning a Shortcut Key
- Deleting a Macro
- Adding a Macro to Quick Access Toolbar
- Deleting a Macro Button from QAT
- Labs
Module 7. Using Macros Button
- Using Macro Button
- Create Macro Button
- Formatting a Macro Button
- Rename a Macro Button
- Copy and Re-assign Macro Button
- Moving/Sizing a Macro Button
- Deleting a Macro Button
- Labs
Module 8. Working with Dashboard
- Creating Excel Dashboard
- Applying Range Names
- Create PivotChart
- Create Combo Box to Filter Chart
- Create Dynamic PivotChart Title
- Create Macro to update PivotChart
- Create Dynamic Chart with CheckBox
- Create Links
- Labs
