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
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
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
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 2: Formulas and functions
Writing formulas, the order of operations, error values, relative and absolute references, and the core functions with conditional totals.
- 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
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
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 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
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
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
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 4: Working with data
Lookups with VLOOKUP, XLOOKUP and INDEX with MATCH, sorting, filtering and Excel Tables, and conditional formatting and data validation.
- 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
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
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 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
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
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
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 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
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
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
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.
Projects
- A personal budget workbook Build a monthly budget in your own Excel that records income and spending, shows the balance by category and month, and warns when a category is over its limit.
- A monthly sales dashboard Turn a raw sales export into a one-page dashboard that refreshes each month: totals by region and product, the trend, the top sellers and targets.
What to learn next
- Database Management Systems Tables and SQL: queries, joins, grouping, keys, subqueries, ER design, normalization, relational algebra, indexes and transactions, on a real database in your browser.
- Python Programming From your first print() to classes, recursion and data structures.