Learning Excel: Data Analysis Course

Lesson 1 • Customizing Excel for Analysis

Configures display options, AutoSave, and the ribbon for an analytical workflow. A tailored environment reduces friction during complex data tasks.

Lesson 2 • Orienting to the Excel Workspace

Covers the ribbon, Quick Access Toolbar, and sheet tabs as entry points to Excel. Establishes spatial awareness needed for all subsequent tasks.

Lesson 3 • Cell Navigation and Selection Techniques

Introduces keyboard shortcuts and mouse methods for moving through large datasets. Efficient navigation is foundational to speed in data analysis.

Lesson 1 • Number and Date Formatting

Applies built-in and custom number formats to display values clearly without altering underlying data. Proper formatting aids interpretation of analytical outputs.

Lesson 2 • Conditional Formatting for Data Signals

Applies rules, color scales, and icon sets to highlight patterns automatically. Visual cues surface insights without requiring additional calculations.

Lesson 3 • Efficient Data Entry Methods

Covers AutoFill, Flash Fill, and data validation to speed up and standardize entry. Consistent input reduces cleaning effort before analysis.

Lesson 4 • Understanding Excel Data Types

Distinguishes numbers, text, dates, and logical values and explains how Excel stores each. Correct data types prevent formula errors downstream.

Lesson 1 • Logical Functions and Conditional Logic

Builds decision-making formulas using IF, AND, OR, and NOT. Conditional logic enables dynamic outputs that respond to changing data conditions.

Lesson 2 • Mathematical and Statistical Functions

Covers SUM, AVERAGE, MIN, MAX, COUNT, and ROUND for basic quantitative analysis. These functions underpin nearly every analytical calculation in Excel.

Lesson 3 • Text Functions for Data Cleaning

Uses LEFT, RIGHT, MID, TRIM, CONCATENATE, and TEXT to manipulate string data. Text functions are essential for standardizing imported or inconsistent data.

Lesson 4 • Date and Time Functions

Calculates durations, extracts date parts, and builds dynamic date logic with TODAY and NOW. Date functions are critical for time-series and deadline-driven analysis.

Lesson 1 • VLOOKUP and HLOOKUP Essentials

Teaches vertical and horizontal lookup syntax, match types, and common pitfalls. Understanding these legacy functions provides context for modern alternatives.

Lesson 2 • Lookup Across Multiple Sheets and Files

Extends lookup functions to reference data on other sheets and in external workbooks. Cross-workbook lookups enable consolidated reporting from distributed data sources.

Lesson 3 • INDEX and MATCH for Flexible Lookups

Combines INDEX and MATCH to retrieve values from any column or row direction. This pair overcomes VLOOKUP limitations and handles dynamic column positions.

Lesson 1 • Data Type Conversion and Validation

Converts text-stored numbers and dates to proper types and validates ranges with rules. Correct types ensure functions calculate accurately on every record.

Lesson 2 • Structuring Data as Excel Tables

Converts ranges to structured tables to enable auto-expansion, structured references, and filtering. Table format is the recommended foundation for all analytical workflows.

Lesson 3 • Handling Missing and Inconsistent Data

Addresses blank cells, placeholder values, and inconsistent categories using formulas and Find & Replace. Consistent data enables reliable grouping and filtering.

Lesson 4 • Identifying and Handling Duplicates

Uses conditional formatting and Remove Duplicates to detect and eliminate repeated records. Clean unique records are a prerequisite for accurate aggregation.

Lesson 1 • AutoFilter and Advanced Filter

Filters rows by value, condition, and criteria ranges to isolate relevant subsets. Filtering is the fastest way to focus analysis on a specific segment.

Lesson 2 • Subtotals and Grouping

Uses the Subtotal tool and row grouping to create collapsible summary sections. Grouped subtotals support hierarchical reporting without pivot tables.

Lesson 3 • SUMIF, COUNTIF, and AVERAGEIF Functions

Aggregates data conditionally based on one or multiple criteria without filtering. These functions produce segment-level summaries directly within the dataset.

Lesson 1 • Grouping, Filtering, and Slicers

Groups date fields, applies report filters, and adds slicers for interactive exploration. These controls transform a static summary into a self-service analytical tool.

Lesson 2 • Calculated Fields and Items

Creates custom metrics inside a PivotTable using calculated fields and items. Custom calculations extend the PivotTable beyond simple aggregation.

Lesson 3 • PivotCharts for Visual Summaries

Generates PivotCharts linked to PivotTables and customizes chart type and layout. PivotCharts provide interactive visual summaries that update with filter changes.

Lesson 4 • Building Your First PivotTable

Creates a PivotTable from a structured table and arranges fields in rows, columns, and values. The PivotTable is Excel's most powerful tool for rapid data summarization.

Lesson 1 • Building an Interactive Dashboard

Combines charts, slicers, KPI cells, and dynamic formulas into a single dashboard sheet. A well-designed dashboard delivers at-a-glance insight to stakeholders.

Lesson 2 • Auditing and Optimizing Workbooks

Uses trace precedents, error checking, and calculation settings to validate and speed up complex workbooks. Auditing ensures analytical outputs are accurate and trustworthy.

Lesson 3 • What-If Analysis Tools

Applies Goal Seek, Scenario Manager, and Data Tables to model outcomes under varying assumptions. What-if tools support evidence-based decision-making by quantifying uncertainty.

Lesson 4 • Dynamic Arrays and Spill Functions

Uses FILTER, SORT, UNIQUE, and SEQUENCE to generate dynamic output ranges automatically. Spill functions eliminate manual updates and enable fully automated reporting.