Excel 2021 Advanced-Excel 2021 Advanced

Excel 2021 Advanced - Student Guide

Pdf Summary

This document is an Excel 2021 Advanced training guide from CustomGuide that teaches power-user skills for analyzing, formatting, automating, and troubleshooting workbooks. It is organized into modules covering PivotTables, PivotCharts, formula troubleshooting, advanced formatting, advanced formulas, what‑if analysis, data consolidation, and macros.<br /><br />The PivotTables section explains how to build PivotTables from well-structured data, add and rearrange fields (Rows, Columns, Values, Filters), expand/collapse levels, add multiple value fields, change summary calculations (Sum, Average, Count, etc.), and create/remove calculated fields. It also introduces GETPIVOTDATA for reliably extracting PivotTable results, filtering with report filters, and using interactive Timelines for date filtering. Additional lessons cover layout options (subtotals, grand totals, report layout forms), grouping/ungrouping values (especially dates), refreshing PivotTables manually or automatically on open, and applying built-in styles and style options.<br /><br />The PivotCharts module shows how to create charts linked to PivotTables, modify chart fields, refresh charts, filter using field buttons, adjust chart elements and quick layouts, apply styles and color schemes, and drill down to view summarized levels or the underlying detail records.<br /><br />Formula troubleshooting lessons describe common errors (####, #NAME?, #VALUE!, #DIV/0!, #REF!) and tools such as Error Checking, Trace Precedents, the Watch Window, and Evaluate Formula.<br /><br />Advanced formatting covers custom conditional formatting rules (including formula-based rules), managing and ordering rules, international date/time/number and currency formats, theme fonts and colors (including custom themes), and using Developer-tab form controls (e.g., check boxes, combo boxes) plus saving and reopening forms as templates.<br /><br />Advanced formulas include IF and nested functions; AND/OR/NOT; SUMIF/AVERAGEIF/COUNTIF (and multi-criteria variants); lookup functions (VLOOKUP, HLOOKUP, INDEX+MATCH); text functions (UPPER/LOWER/PROPER, LEFT/RIGHT/MID); and calculation mode settings (automatic vs. manual). The Analyze Data module explains Goal Seek, Scenario Manager (including scenario summaries), and consolidating data across worksheets by position or category. Finally, the Macros module covers enabling macro security, recording/running macros, editing VBA code (including Personal Macro Workbook), and copying/testing macros between workbooks.

Keywords

Excel 2021 advanced training

PivotTables

PivotCharts

GETPIVOTDATA

PivotTable calculated fields

Formula troubleshooting

Conditional formatting rules

Lookup functions (VLOOKUP INDEX MATCH)

What-if analysis (Goal Seek Scenario Manager)

Macros and VBA