Excel intermediate Course

Lesson 1 • Essential Keyboard Shortcuts

High-impact shortcuts for navigation, selection, and formatting are introduced. Consistent shortcut use accelerates every task covered in later chapters.

Lesson 2 • Ribbon and Quick Access Toolbar

Explore ribbon tabs, groups, and commands relevant to intermediate tasks. Customising the toolbar reduces repetitive clicks throughout the course.

Lesson 3 • View Options and Window Controls

Freeze panes, split views, and custom views are configured for large datasets. These settings improve readability when working with complex spreadsheets.

Lesson 1 • Data Validation Rules

Whole number, decimal, list, and date constraints restrict invalid input. Validation rules enforce data integrity at the point of entry.

Lesson 2 • Efficient Data Entry Techniques

AutoFill, Flash Fill, and custom lists speed up repetitive entry tasks. These tools reduce manual errors before validation rules are applied.

Lesson 3 • Input Messages and Error Alerts

Custom prompts guide users before entry; error alerts block or warn on invalid input. These features make validated cells self-documenting.

Lesson 1 • Lookup and Reference Functions

VLOOKUP, HLOOKUP, INDEX, and MATCH retrieve data from tables dynamically. These functions replace manual searching and link datasets across sheets.

Lesson 2 • Statistical and Math Functions

SUMIF, COUNTIF, AVERAGEIF, and ROUND perform conditional aggregation and rounding. These functions extend basic arithmetic into criteria-driven analysis.

Lesson 3 • Logical Functions for Decision Making

IF, AND, OR, and nested IF statements evaluate conditions and return dynamic results. Logical functions form the backbone of automated decision formulas.

Lesson 4 • Date and Time Functions

TODAY, NOW, DATEDIF, EDATE, and NETWORKDAYS calculate durations and deadlines. Date functions automate time-sensitive reporting and scheduling tasks.

Lesson 1 • Creating and Formatting Tables

Insert tables from ranges, apply table styles, and configure header rows. Proper table setup is the foundation for all structured reference formulas.

Lesson 2 • Structured References in Formulas

Table column names replace cell addresses in formulas, making them readable and auto-expanding. Structured references reduce formula maintenance as data grows.

Lesson 3 • Sorting and Filtering Table Data

Multi-level sort and AutoFilter options organise and isolate relevant records. Filtering skills apply directly to PivotTable and dashboard work later.

Lesson 1 • Finding and Removing Duplicates

Remove Duplicates and COUNTIF-based detection identify redundant records. Deduplication is essential before aggregation or reporting.

Lesson 2 • Sorting, Filtering, and Advanced Filter

Advanced Filter extracts records matching complex criteria to a separate location. This extends basic table filtering for multi-condition data extraction.

Lesson 3 • Text to Columns and Flash Fill

Text to Columns splits concatenated fields; Flash Fill infers and applies patterns. Both tools rapidly restructure data without formulas.

Lesson 4 • Importing External Data

Text files, CSV imports, and basic web queries bring external data into Excel. Understanding import settings prevents formatting and delimiter errors.

Lesson 1 • PivotTable Design and Value Display

Show values as percentages, running totals, or rank to reframe the same data. Layout and style options make PivotTables presentation-ready.

Lesson 2 • PivotCharts for Visual Summaries

PivotCharts link directly to PivotTables and update dynamically with filter changes. They bridge data analysis and visual reporting covered in the next chapter.

Lesson 3 • Calculated Fields and Items

Custom calculations are added inside the PivotTable without altering source data. Calculated fields extend analysis beyond standard aggregation functions.

Lesson 4 • Grouping and Filtering Pivot Data

Date grouping, manual grouping, and report filters segment data for focused analysis. These techniques answer specific business questions from a single PivotTable.

Lesson 1 • Choosing the Right Chart Type

Matching data relationships to chart types prevents misleading visualisations. Correct chart selection is the first decision in every visualisation workflow.

Lesson 2 • Sparklines and In-Cell Visuals

Sparklines embed miniature trend charts inside cells for compact dashboards. They complement full charts by showing row-level trends without extra space.

Lesson 3 • Conditional Formatting as Visualisation

Colour scales, data bars, and icon sets turn numeric ranges into visual signals. Rule-based formatting highlights exceptions and patterns without charts.

Lesson 4 • Advanced Chart Techniques

Combination charts, secondary axes, and dynamic chart ranges handle complex data. These techniques solve visualisation problems that basic charts cannot address.

Lesson 1 • Workbook-Level Security Settings

Workbook passwords, structure protection, and read-only settings control file access. These settings are applied before distributing files to external stakeholders.

Lesson 2 • Formula Auditing Tools

Trace precedents, dependents, and error indicators locate formula problems visually. Auditing skills prevent silent errors from propagating through linked calculations.

Lesson 3 • Printing and Export Settings

Page layout, print areas, headers, and PDF export produce professional hard copies. Correct print settings ensure reports render accurately on paper and screen.

Lesson 4 • Cell and Sheet Protection

Locking cells and protecting sheets prevents accidental edits to formulas and structure. Selective unlocking allows data entry while preserving formula integrity.