Advanced Excel · Beginner level

Beginner level · Module 1 of 5 · 10 guided hours

Reliable Formulas and Excel Tables

Refresh core formula skills and build workbooks on Excel tables, named ranges and validation so formulas stay correct as data grows.

Learning objectives

  • Use relative, absolute and mixed references correctly
  • Store data in Excel tables and use structured references
  • Prevent and handle errors with data validation and IFERROR

Topics

  • relative, absolute and mixed references
  • named ranges
  • Excel tables
  • structured references
  • data validation
  • IFERROR and error types

Lessons

  1. Lesson 1 References and Named Ranges

    Use relative, absolute and mixed references deliberately, and name important cells and ranges.

    relative referenceabsolute reference $A$1mixed referencenamed range

  2. Lesson 2 Excel Tables and Structured References

    Store data in Excel tables so formulas, formatting and summaries grow with the data.

    Ctrl+Ttable namecalculated columntotal row

  3. Lesson 3 Validation and Error Handling

    Stop bad data at entry with data validation, and handle formula errors cleanly.

    data validationdrop-down list#N/A, #DIV/0!, #VALUE!IFERROR

Practical activity

Convert a messy sales sheet into an Excel table with validated inputs and a summary block that keeps working when rows are added.

Expected outcomes

  • Workbooks that stay correct as data changes
  • Fewer data-entry errors

Assessment

Practical: repair a workbook with broken references and unvalidated inputs; marked on correctness after new rows are added.

Free taster lesson Excel Fundamentals Refresher Read a free self-study lesson on this topic in the Free Course Library.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.