false
OasisLMS
Login
Catalog
Excel 2021 Advanced
Excel 2021 Advanced - Quick Reference Guide
Excel 2021 Advanced - Quick Reference Guide
Back to course
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
×
Please select your language
1
English