Excel 2021 Advanced-Excel 2021 Advanced

Excel 2021 Advanced - Course Outline

Pdf Summary

This Excel 2021 Advanced course teaches learners to work efficiently with complex data analysis, formulas, formatting, and automation tools. It begins with PivotTables: creating and expanding PivotTables, adding multiple row/column/value fields, changing calculations, creating and removing calculated fields, using GETPIVOTDATA (and cube functions), filtering with fields and timelines, adjusting layouts, grouping/ungrouping values, refreshing manually or automatically, and applying PivotTable formatting.<br /><br />Next, the course covers PivotCharts, including creating and inserting PivotCharts, modifying them, refreshing chart data, using and toggling field buttons, changing chart layouts with Quick Layouts, and applying styles and colors. It also introduces drill-down techniques to explore underlying data directly from PivotCharts.<br /><br />Participants then learn how to troubleshoot formulas by identifying common errors (#####, #NAME?, #VALUE!, #DIV/0, and #REF!), using the Watch Window to monitor key cells, and evaluating formulas step-by-step to locate issues.<br /><br />Advanced formatting topics include building custom conditional formatting rules (including formula-based rules), editing and reordering rules, and applying international currency, date, and time formats via Windows regional settings. The course also explores themes and design consistency by applying and creating custom font sets and color schemes, building custom themes, and working with form controls through the Developer tab. Learners also practice saving forms as templates and reopening template-based forms.<br /><br />Advanced formulas include logical and lookup functions such as IF, nested functions, AND/OR/NOT, SUMIF/AVERAGEIF/COUNTIF, VLOOKUP, HLOOKUP, and INDEX/MATCH combinations. Text functions (UPPER, LOWER, PROPER, LEFT, RIGHT, MID) and calculation mode options are also covered.<br /><br />Finally, data analysis tools (Goal Seek, Scenario Manager, and data consolidation) and macros are introduced, including enabling macros securely, recording/running macros, editing and testing macros, and copying macros for reuse.

Keywords

Excel 2021 advanced

PivotTables

PivotCharts

GETPIVOTDATA

cube functions

formula auditing

conditional formatting

lookup functions (VLOOKUP INDEX MATCH)

what-if analysis (Goal Seek Scenario Manager)

macros and VBA automation