Excel Dashboard Course

Lesson 1 • Data Entry and Cell Formatting

Apply consistent data types, number formats, and cell styles to raw data. Proper formatting is the foundation of readable, accurate dashboards.

Lesson 2 • Workbook Organization Best Practices

Separate raw data, calculations, and dashboard output across dedicated sheets. This structure prevents errors and simplifies future updates.

Lesson 3 • Navigating the Excel Interface

Master the Ribbon, Quick Access Toolbar, and worksheet navigation shortcuts. These skills eliminate friction in all subsequent dashboard-building tasks.

Lesson 4 • Structuring Data as Tables

Convert raw ranges into structured Excel Tables with headers and auto-expansion. Tables make formulas and charts dynamic and easier to maintain.

Lesson 1 • Data Validation and Input Controls

Create dropdown lists, range rules, and custom validation messages to enforce data integrity at entry. Controlled inputs prevent downstream dashboard errors.

Lesson 2 • Lookup and Reference Functions for Data Prep

Apply INDEX-MATCH and XLOOKUP to merge and enrich datasets from multiple sources. Mastering lookups eliminates manual copy-paste errors in data assembly.

Lesson 3 • Power Query for Automated Data Cleaning

Use Power Query to import, transform, and refresh data without manual steps. Automated pipelines keep dashboards current with minimal maintenance effort.

Lesson 4 • Text and Date Manipulation Functions

Use LEFT, RIGHT, MID, TEXT, and date functions to parse and reformat values. These functions convert raw strings into structured, usable fields.

Lesson 1 • Calculated Fields and Items

Add custom metrics like profit margin or growth rate directly inside a PivotTable. Calculated fields extend analysis without altering the source data.

Lesson 2 • Slicers and Timelines for PivotTables

Insert slicers and timelines to filter PivotTables interactively without using dropdown menus. These controls are the primary interactivity layer in Excel dashboards.

Lesson 3 • Grouping, Sorting, and Filtering Data

Group dates by month or quarter, sort values, and apply label and value filters. These controls let analysts focus on the exact data segments dashboards need.

Lesson 4 • Building Your First PivotTable

Create a PivotTable from a structured Table and arrange fields in rows, columns, and values. This section establishes the core workflow used throughout the chapter.

Lesson 1 • Choosing the Right Chart Type

Match chart types to data relationships: comparison, trend, composition, and distribution. Correct chart selection prevents misleading or confusing visualizations.

Lesson 2 • Formatting Charts for Clarity

Apply consistent colors, remove chart junk, and label data points effectively. Clean formatting directs viewer attention to the insight, not the decoration.

Lesson 3 • Combination and Secondary Axis Charts

Plot two different metrics on one chart using a secondary axis and mixed chart types. Combination charts reveal relationships between metrics with different scales.

Lesson 4 • Building Charts from PivotTables

Create PivotCharts linked to PivotTables so charts update automatically when data changes. PivotCharts are the standard chart source for dynamic dashboards.

Lesson 1 • Navigation and Dashboard Usability

Add hyperlink buttons, sheet tabs, and instructional labels to make dashboards self-explanatory. Good navigation reduces user errors and support requests.

Lesson 2 • Planning the Dashboard Before Building

Define the audience, key questions, and required metrics before touching Excel. A clear plan prevents redesign and ensures the dashboard answers real business needs.

Lesson 3 • Grid-Based Layout and Alignment

Use row height, column width, and alignment guides to create a precise visual grid. Consistent alignment signals professionalism and reduces cognitive load for users.

Lesson 4 • Color, Typography, and Visual Hierarchy

Apply a limited color palette, readable fonts, and size contrast to establish hierarchy. Visual hierarchy guides the viewer's eye from the most to least important metric.

Lesson 1 • Formula Auditing and Performance

Trace precedents, evaluate formulas step by step, and reduce volatile functions to improve speed. Efficient formulas prevent slow dashboards in large workbooks.

Lesson 2 • Logical and Lookup Formula Combinations

Combine IF, AND, OR with XLOOKUP and INDEX-MATCH to build intelligent metric calculations. Layered logic handles exceptions and edge cases that simple formulas miss.

Lesson 3 • Named Ranges and Dynamic References

Define named ranges and use OFFSET or INDEX to create ranges that expand with new data. Dynamic references ensure charts and formulas always capture the full dataset.

Lesson 4 • Dynamic Array Functions

Use FILTER, SORT, UNIQUE, and SEQUENCE to generate auto-updating output ranges. Dynamic arrays replace complex helper columns and manual list maintenance.

Lesson 1 • Dynamic Chart Titles and Labels

Link chart titles, axis labels, and text boxes to cell values so they update automatically. Dynamic labels eliminate the need to manually edit charts after data changes.

Lesson 2 • Dependent Dropdown Lists

Build cascading dropdowns where the second list updates based on the first selection. Dependent dropdowns guide users to valid combinations and reduce input errors.

Lesson 3 • Scenario and What-If Controls

Use Scenario Manager, Data Tables, and Goal Seek to let users model assumptions interactively. What-if controls transform dashboards into decision-support tools.

Lesson 4 • Protecting Interactivity While Locking Structure

Lock formula cells and chart objects while leaving controls and input cells editable. Selective protection lets users interact freely without breaking the dashboard.

Lesson 1 • Conditional Formatting as a Visual Layer

Apply icon sets, data bars, color scales, and custom rules to highlight patterns in data. Conditional formatting adds a visual intelligence layer without adding extra charts.

Lesson 2 • Dashboard Maintenance and Documentation

Write a data dictionary, update log, and user guide so dashboards remain maintainable over time. Documentation is the professional standard that separates one-off files from assets.

Lesson 3 • Automating Refresh with Macros

Record and edit VBA macros to refresh data, reset filters, and navigate sheets with one click. Automation reduces manual steps and ensures dashboards are always current.

Lesson 4 • Advanced Chart Types for Dashboards

Build waterfall, bullet, gauge, and heat map charts to visualize complex metrics. These chart types communicate performance and variance more precisely than standard charts.