Advanced Excel skills are vital-but with Copilot, they become exponentially more powerful. This course helps participants harness advanced Excel features alongside with AI assistance to streamline analysis, model scenarios, and improve business decision-making. Through hands-on exercises, they will master data modelling, automation, and prompt engineering within Excel 365.
Learning Outcomes:
Analyse large datasets using advanced Excel formulas and tools
Build scenario models and use what-if analysis features
Automate workflows with macros and Excel Copilot prompts
Key Topics:
Data modelling with named ranges and advanced formulas
Scenario management and goal seek
Macros and automation tools
Copilot for data analysis and prompt-based tasks
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
Lesson 11: Introduction to Copilot
- Overview of Copilot
- How Copilot can assist with Office tasks
Lesson 12: Copilot and Excel 365
- Using Copilot for complex formulas
- Using Copilot for Data Analysis and visualization
.png?width=50&name=Artificial%20Intelligence%20(AI).png)
-1.png?width=360&name=Server%20(4)-1.png)
/AI%20(6)-1-1.png?width=360&name=AI%20(6)-1-1.png)
/AI%20(8)-1.png?width=360&name=AI%20(8)-1.png)
-1.png?width=360&name=Automation%20RPA%20(2)-1.png)
/AI%20(5)-1.png?width=360&name=AI%20(5)-1.png)
