Excel: From Beginner to Advanced

A reading course from the Excel window to PivotTables, dynamic arrays and Power Query, with worked examples and the keyboard shortcuts for every topic.

Level
Beginner
Time
About 18 hours
Modules
6
Lessons
18
Exercises
0

Excel is the most widely used tool for working with numbers, lists and reports. This course takes you from your first workbook to the tools analysts use every day: formulas and references, lookups, Tables, charts, PivotTables, what-if analysis, dynamic arrays and Power Query.

It is a reading course: there is nothing to run on these pages. Each lesson explains a topic, walks through a worked example you can repeat in your own copy of Excel, lists the common mistakes and gives the keyboard shortcuts for Windows and Mac. Quizzes check your understanding as you go.

What you will learn

  • Enter, edit and format data, and move around large sheets quickly
  • Write formulas with relative, absolute and mixed references
  • Use the core functions for totals, logic, text, dates and lookups
  • Organise and analyse lists with Tables, filters, conditional formatting and PivotTables
  • Present data with charts and answer what-if questions
  • Use dynamic arrays, LET, Power Query and protection in real workbooks

Skills you will gain

  • Spreadsheet formulas and functions
  • Data cleaning and organisation
  • Data analysis with PivotTables
  • Charts and reporting
  • Keyboard-driven productivity

Before you start

  • A computer with Microsoft Excel (Microsoft 365 or Excel 2021 recommended; Excel for the web covers most lessons)
  • Basic computer use: opening and saving files, using a mouse and keyboard
  • No previous Excel or programming knowledge

Where it is used

  • Budgets and personal finance
  • Sales and inventory reports
  • Cleaning exported data
  • Business dashboards and what-if models

Modules

Module 1: Getting started

The Excel window, cells and ranges, entering and editing data, and formatting cells and numbers.

  1. 1

    The Excel window, workbooks and navigation

    What a workbook, worksheet, cell and range are, how to read cell addresses, and how to move around a sheet quickly with the mouse, the Name Box and the keyboard.

  2. 2

    Entering and editing data

    How Excel decides whether you typed a number, text, a date or a formula, how to edit cells safely, and how AutoFill and Flash Fill save typing.

  3. 3

    Formatting cells and number formats

    How formatting changes what a cell looks like without changing its value: fonts, borders and alignment, number formats for money, percentages and dates, and custom formats.

Module practice and quiz →

Module 2: Formulas and functions

Writing formulas, the order of operations, error values, relative and absolute references, and the core functions with conditional totals.

  1. 1

    Formulas and operators

    How to write formulas that calculate from other cells, the arithmetic and comparison operators, the order Excel works them out in, and what the common error values mean.

  2. 2

    Relative, absolute and mixed references

    What happens to cell references when a formula is copied, how dollar signs lock a column or row, how F4 switches between the kinds, and how named ranges make formulas readable.

  3. 3

    SUM, AVERAGE, COUNT and conditional totals

    Excel's most used functions: SUM, AVERAGE, MIN, MAX, COUNT, COUNTA and COUNTBLANK, ROUND, and the conditional versions SUMIF, COUNTIF, AVERAGEIF, SUMIFS and COUNTIFS.

Module practice and quiz →

Module 3: Logic, text and dates

Decisions with IF, AND, OR and IFS, handling errors, cleaning and splitting text, and calculating with dates and times.

  1. 1

    IF, AND, OR, IFS and handling errors Intermediate

    Making decisions in formulas: IF with one or more conditions, AND, OR and NOT, nested IFs versus IFS, and catching errors with IFERROR and IFNA.

  2. 2

    Text functions Intermediate

    Cleaning and reshaping text: LEFT, RIGHT and MID, LEN, FIND and SEARCH, TRIM and CLEAN, changing case, joining with & , CONCAT and TEXTJOIN, SUBSTITUTE, TEXT and VALUE.

  3. 3

    Dates and times Intermediate

    How Excel stores dates as serial numbers and times as fractions of a day, and the functions that build, split and calculate with them: TODAY, DATE, EOMONTH, EDATE, WEEKDAY, NETWORKDAYS and more.

Module practice and quiz →

Module 4: Working with data

Lookups with VLOOKUP, XLOOKUP and INDEX with MATCH, sorting, filtering and Excel Tables, and conditional formatting and data validation.

  1. 1

    Lookups: VLOOKUP, XLOOKUP and INDEX with MATCH Intermediate

    Finding values in a table: VLOOKUP and its pitfalls, XLOOKUP in Excel 2021 and Microsoft 365, the flexible INDEX and MATCH pair, exact and approximate matches, and handling values that are not found.

  2. 2

    Sorting, filtering and Excel Tables Intermediate

    Organising lists: sorting by one or more columns, filtering with AutoFilter, removing duplicates, and turning a range into an Excel Table with automatic formatting, structured references and a total row.

  3. 3

    Conditional formatting and data validation Intermediate

    Making a sheet react to its data and protecting what users can type: conditional formatting rules, colour scales and data bars, formula-based rules, drop-down lists and validation messages.

Module practice and quiz →

Module 5: Analysis and charts

Choosing and formatting charts, summarising lists with PivotTables, and answering what-if questions with Goal Seek, data tables and scenarios.

  1. 1

    Charts and sparklines Intermediate

    Choosing the right chart for a question, creating and formatting column, bar, line, pie, scatter and combo charts, chart elements and axes, and tiny in-cell sparklines.

  2. 2

    PivotTables Intermediate

    Summarising thousands of rows in seconds: building a PivotTable from a Table, rows, columns, values and filters, value field settings, grouping dates, slicers, PivotCharts and refreshing.

  3. 3

    What-if analysis: Goal Seek, data tables and scenarios Advanced

    Asking "what if" of a model: Goal Seek to find the input that gives a target result, one- and two-variable data tables, Scenario Manager for named sets of inputs, and an introduction to Solver.

Module practice and quiz →

Module 6: Advanced Excel

Dynamic arrays with FILTER, SORT, UNIQUE and SEQUENCE, LET and LAMBDA, Power Query for repeatable cleaning, and protecting, printing and sharing workbooks.

  1. 1

    Dynamic arrays, LET and LAMBDA Advanced

    Formulas that return many values: spilling, the # spill reference, FILTER, SORT, SORTBY, UNIQUE and SEQUENCE, the #SPILL! error, and naming parts of formulas with LET and writing your own functions with LAMBDA.

  2. 2

    Power Query: getting and cleaning data Advanced

    Importing and cleaning data with Power Query: connecting to files and folders, recorded applied steps, removing columns and rows, splitting, changing types, unpivoting, merging and appending queries, and refreshing.

  3. 3

    Protecting, printing and sharing workbooks Advanced

    Finishing a workbook for others: locking cells and protecting sheets and structure, passwords to open, printing with print areas, titles and scaling, sharing and co-authoring in OneDrive, and an honest look at macros and Office Scripts.

Module practice and quiz →

Projects

Final assessment →

What to learn next