Excel for Jobs: Formulas, Pivot Tables and Office Skills

Free Excel course for jobs. 15 lessons: formulas, IF, SUMIF, VLOOKUP, pivot tables, charts, data cleaning, validation, printing and test practice.

Beginner · 15 lessons

Start Here

  1. Excel for Beginners: The Screen, Cells and Your First Sheet - Learn the Excel screen, cells, rows, columns and ranges, enter data, save your file and meet the shortcuts you will use every day. No experience needed.
  2. Enter and Format Data: Numbers, Dates, Text and Tidy Tables - Understand how Excel treats numbers, text and dates, fix the common problems (numbers stored as text, broken dates), and format a table so it is easy to read.

Formulas

  1. Your First Formulas: Cell References and the $ Sign - Write formulas with +, -, *, /, copy them correctly, and understand relative and absolute references ($). Fill the Amount column of the sales table.
  2. SUM, AVERAGE, COUNT, MIN and MAX: The Everyday Functions - Use the functions that every office job needs: SUM, AVERAGE, COUNT, COUNTA, MIN, MAX, ROUND and AutoSum, with ranges and the status bar shortcut.
  3. IF, AND, OR and IFERROR: Making Excel Decide - Make formulas that make decisions with IF, combine tests with AND and OR, nest IFs sensibly and hide errors with IFERROR. Examples for commission and results.
  4. SUMIF, COUNTIF and SUMIFS: Totals for Each Group - Total, count and average only the rows that match a condition: sales per region, per person or per item, with one or several conditions.

Lookups, Text and Dates

  1. VLOOKUP, XLOOKUP and INDEX MATCH: Find Data in Another Table - Look up a price, name or code from another table. Learn VLOOKUP with exact match, XLOOKUP in newer Excel and INDEX MATCH, plus the #N/A error.
  2. Clean Messy Data: TRIM, LEFT, MID, CONCAT, Text to Columns - Fix messy data: extra spaces, wrong capitals, joined or split names, duplicates and blanks, using text functions, Flash Fill and Remove Duplicates.
  3. Dates in Excel: TODAY, EDATE, NETWORKDAYS and Ageing - Calculate due dates, days between dates, working days, ages and month ends with Excel date functions, and avoid the common date pitfalls.

Organising and Reporting

  1. Sort, Filter and Excel Tables - Sort and filter data safely, turn a range into an Excel Table, use structured references and slicers, and keep formulas growing with your data.
  2. Pivot Tables for Beginners: Summaries in Seconds - Build your first pivot table: totals by region, item and staff, with filters, grouping and refreshing. Summarise thousands of rows with a few clicks.
  3. Excel Charts: Show Your Numbers in a Picture - Choose the right chart, build column, line and pie charts, make them readable, and combine a pivot table, charts and slicers into a dashboard.

Work Skills

  1. Data Validation, Conditional Formatting and Protecting Your Sheet - Stop wrong entries with drop-down lists and rules, highlight what matters with conditional formatting, and protect formulas from accidental changes.
  2. Print, Share and Work Faster: Page Setup and Shortcuts - Print a sheet properly on one page, save as PDF, share safely, add comments, and learn the keyboard shortcuts that make you visibly faster at work.
  3. Excel for Office Jobs: Tests, Real Tasks and Next Steps - Practise the tasks that appear in Excel skills tests and real office jobs, describe your skills honestly on a resume, and plan what to learn next.