Excel 2021 Advanced-Excel 2021 Advanced

Excel 2021 Advanced - Quick Reference Guide

Pdf Summary

This Microsoft Excel 2021 Advanced Quick Reference Guide summarizes key tasks for PivotTables, PivotCharts, macros, formula troubleshooting, advanced formatting, data analysis, and advanced formulas.<br /><br />For PivotTables, it explains how to create one from a selected data range (Insert > PivotTable), then build it by dragging fields into four areas in the PivotTable Fields pane: Filters (to filter results via a dropdown above the table), Columns (creates column headings), Rows (creates row headings), and Values (the calculated numeric results). It also covers adding multiple fields, filtering, grouping values (Analyze > Group Field), refreshing data (Analyze > Refresh), and formatting using the Design tab options, including subtotals, grand totals, report layout (compact/outline/tabular), and blank rows.<br /><br />For PivotCharts, it describes creating a chart from an existing PivotTable (Analyze > PivotChart), changing what’s displayed by moving fields in the task pane, refreshing the chart, modifying chart elements (Design > Add Chart Element), applying chart styles, changing chart type, and enabling drill-down expand/collapse field buttons.<br /><br />The macros section shows how to enable the Developer tab, record a macro (Developer > Record Macro), stop recording, run macros (Developer > Macros > Run), edit macros in Visual Basic, delete macros, and adjust macro security settings.<br /><br />Formula troubleshooting includes common errors (#######, #NAME?, #VALUE!, #DIV/0, #REF!) and auditing tools: Trace Precedents, Error Checking, Watch Window, and Evaluate Formula.<br /><br />Advanced formatting focuses on creating, editing, and reordering conditional formatting rules (Home > Conditional Formatting).<br /><br />Data analysis highlights Goal Seek (Data > What-If Analysis > Goal Seek).<br /><br />Advanced formulas covered include nested functions; IF with AND/OR/NOT; conditional calculations (SUMIF, AVERAGEIF); lookups (VLOOKUP, HLOOKUP, MATCH, INDEX); text functions (UPPER, LOWER, PROPER, LEFT, RIGHT, MID).

Keywords

Excel 2021 advanced

PivotTables

PivotTable Fields pane

PivotCharts

Macro recording

VBA editor

Formula error troubleshooting

Conditional formatting rules

Goal Seek what-if analysis

Advanced Excel formulas