Data-savvy professionals need tools that go beyond basic spreadsheets. This course provides participants with the advanced Excel skills needed to handle complex datasets and automate workflows. Through practical modules, they will use functions, pivot charts, macros, to streamline reporting and decision-making.
Learning Outcomes:
Analyse with PivotTables and charts
Use advanced Excel functions for dynamic analysis
Design macros to automate repeatable tasks
Implement data validation and conditional logic
Key Topics:
Advanced charting and data visualisation
Lookup functions and dynamic ranges
Macro creation and execution
Lesson 1: Conditional Formatting
- Applying Conditional Formats
- Changing a Conditional Format
- Adding a Conditional Format
- Creating a Custom Conditional Format
- Using Data Bars
- Deleting a Conditional Format
- Creating a Custom Number Format
Lesson 2: Advanced Chart
- Formatting an Axis
- Changing the Axis Scaling
- Formatting the Data Series
- Adding Data from Different Worksheets
- Using a Secondary Axis
- Changing Data Series Chart Types
- Changing Source Data Range
Lesson 3: Naming Cells and Ranges
- 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
Lesson 4: Validation and Auditing
- Sorting
- Multiple Levels Sort
- Using Custom Sort
- Creating Subtotals in a List
- Removing Subtotals from a List
- Using Data Validation
- Validating Data using a List
- Creating a Custom Error Message
- Removing Data Validation
- Displaying/Removing Dependent Arrows
- Displaying/Removing Precedent Arrows
- Showing Formulas
- Protecting a Worksheet
- Unprotect a worksheet
- Assigning a Password to a File
- Removing a Password from a File
Lesson 5: Advanced Formulas and functions
- Using the VLOOKUP Function
- Using the HLOOKUP Function
- Using XLOOKUP Function
- Using the IF Function
- Using the IFS Functions
- Using an AND Condition with IF
- Using an OR Condition with IF
- Using Date Function
- Using COUNTIF / SUMIF Function
- Using COUNTIFS / SUMIFS Function
- Using Text Functions
Lesson 6: Advanced Filter
- Creating a Criteria Range
- Using a Criteria Range
- Showing All Records
- Using an Advanced And Condition
- Using an Advanced Or Condition
- Copying Filtered Records
- Using Database Functions
- Removing Duplicates from a Table
Lesson 7: Importing and Linking
- Importing Data from Text Files
- Use Text to Column
Lesson 8: PivotTables
- Creating a PivotTable Report
- 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
- Adding Slicer
- Deleting PivotTable Report Fields
- Creating a Page Field Report
- Formatting a PivotTable Report
- Creating a PivotChart Report
Lesson 9: Scenario Analysis
- Creating a Scenario
- Displaying a Scenario
- Editing a Scenario
- Creating a Scenario Summary Report
Lesson 10: 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
