Microsoft Excel Course Online January 8, 2025 Advanced

The Advanced Microsoft Excel training course covers the following topics:

Working Across Sheets and Files

• Data consolidation
• Advanced Lookups and Returns with DGET and FILTER

Pivot table calculations and charts

• Creating a PivotTable from a list or a table
• Customizing a PivotTable
• Refreshing a PivotTable
• Creating a calculation in a PivotTable
• Calculated Items & Fields
• Summarize techniques
• New GROUPBY and PIVOTBY functions
• Creating Pivot charts
• Creating a table from a list
• Using what-if analysis tools

Working with custom formats

• Locking cells
• Learn to lock rows or columns with mixed cell referencing
• Lock rows
• Lock columns
• Mixed cell referencing
• Custom number formats

Advanced conditional formatting

• Automatically apply conditional formatting

Nested IF statements:

• Beyond the standard IF statements
• Use nested IF statements to include multiple options
• Create complex logic statements
• Creating conditional or multiple conditionals

Goal seek

• Find values to create an output across an Excel sheet/workbook.
• Use a goal seek tool to find values to meet a specific result

Data Tables

• Impacting a targeted output
• Building sensitivity analysis

Match vlookup

• Create a VLOOKUP by using the MATCH function

Match index

• Performing a reverse lookup
• Discovering how to combine two MATCH’s within the INDEX Data extraction

Auditing Cells

• Discovering errors within formulas
• Tracing for dependent cells and precedent cells
• Go to special

Hot Keys

• Automate functions and commands
• Text formatting to calculations using your keyboard

Automation techniques