Build and Safeguard a Reliable Excel Workbook
Build and Safeguard a Reliable Excel Workbook is an intermediate-level course of 5 lessons (about 1 h 30 min), with 15 exercises and 15 quiz questions. Prerequisites: Excel: PivotTables. The course needs a Windows virtual machine with Power BI, planned for a later phase of the platform.
- Excel
- Intermediate
- 5 lessons
- 1 h 30 min
- 15 exercises
- Windows virtual machine (phase 2)
The course in brief
This course teaches you to design workbooks that stay correct when the data changes. You secure data entry, structure your data in named tables, build dynamic reports with dynamic array functions, simulate assumptions with scenarios and Goal Seek, then audit and protect your model before sharing it.
What you will be able to do
- Control data entry with drop-down lists, limits and custom rules
- Structure data in named tables and write structured references
- Extract, deduplicate and sort with FILTER, UNIQUE, SORT and SORTBY
- Organize a model into assumptions, calculations and results, and simulate it with scenarios, data tables and Goal Seek
- Audit formulas, add consistency checks and protect calculation areas
Syllabus: 5 lessons
- Create validation rules (list, whole number, date, custom)
- Write a suitable input message and error alert
- Find existing data that does not comply with the rules
- 02
Names and Structured Tables
- Convert a range into a structured table and name it
- Write formulas with structured references
- Define and manage names for constants and ranges
- 03
Dynamic Array Functions: FILTER, UNIQUE, SORT
- Understand how dynamic results spill
- Extract, deduplicate and sort data with FILTER, UNIQUE, SORT and SORTBY
- Combine these functions and reuse them with the # operator
Dynamic arrays and spillingFILTER, UNIQUE, SORT, SORTBYLET function
- 04
Assumptions, Scenarios and Goal Seek
- Organize a model into assumptions, calculations and results
- Compare sets of assumptions with the Scenario Manager and a data table
- Find an input value with Goal Seek
- 05
Best Practices and Formula Auditing
- Apply the design rules of a reliable workbook
- Use auditing tools to understand and fix a formula
- Set up checks and protect calculation areas
Final project
A Reliable Sales Budget Model
A fictional small business entrusts you with tracking its sales and preparing its budget. Build a workbook in three parts: a sales input sheet secured by validation, a structured table feeding a dynamic report by region and by product, and a profitability model with scenarios and a break-even point. The workbook must include a checks area, a Read me sheet and formula protection.
Deliverables
- An .xlsx file with the sheets Lisez-moi, Listes, Ventes, Rapport, Modele, Controles
- A dynamic report based on UNIQUE, TRIER, FILTRE and SOMME.SI.ENS with no hand-copied formulas
- A summary of three scenarios and the break-even point calculated with Goal Seek
- A checks area displaying OK for at least three verifications
Assessment criteria
- Protected input: named lists, limits, stop alert
- No hard-coded constants in calculation formulas, named assumptions
- The report updates when a sale or a region is added
- Correct results and working checks
- Protected sheets with only the input cells editable, clear documentation
Frequently asked questions
Do I need Microsoft 365 to follow this course?
Validation, tables, names, scenarios, Goal Seek and auditing exist in all recent versions. The FILTER, UNIQUE, SORT and LET functions require Microsoft 365, Excel for the web or a recent standalone version.
How is this different from the PivotTables course?
A PivotTable summarizes data. This course is about designing the workbook itself: controlled data entry, structure, formulas that follow the data, simulation and verification.
Does sheet protection secure my data?
No. It prevents you from overwriting formulas by mistake. For confidentiality, you need to encrypt the file or manage access rights where it is stored.
When should I use Solver instead of Goal Seek?
Goal Seek adjusts a single cell to reach a result. Solver varies several cells under constraints to optimize an objective.
This course, told for your job
Say what you want to be able to do: “Build and Safeguard a Reliable Excel Workbook” takes its place in your path, with examples from your field. The concepts stay those of the course.