Intermediate Excel · Interactive lesson 12 of 12
Intermediate productivity & auditing
When a worksheet's totals look wrong, these are the tools that find out why — instead of re-typing formulas and hoping. This is also the capstone: a workbook you can build, explain, and defend end to end.
Screens shown: Excel for Microsoft 365 for Windows. Practice works in any browser.
Written tutorialProductivity and Formula Auditing in ExcelSkill check
Try these first:
- Select every blank cell in a range with Go To Special.
- Paste a formula's result as a static value with Paste Special.
- Copy formatting from one cell to another with Format Painter.
- Trace which cells a formula actually depends on.
Comfortable already? You've completed the Intermediate track — see the capstone exercise below.
Targeted teach
What you're learning
- Go To Special
- Ctrl+G → Special — jump straight to every blank cell, every formula, or every constant in a selection, all at once.
- Paste Special
- Paste just the values, just the formatting, or transpose rows into columns — instead of a full copy that drags formulas along with it.
- Format Painter
- Copies a cell's formatting only, leaving its value untouched — a fast way to make dozens of cells match one styled example.
- Trace precedents / dependents
- Draws arrows from a formula to the cells it reads (precedents) or from a cell to the formulas that use it (dependents) — the fastest way to understand a sheet you didn't build.
- Evaluate Formula
- Steps through a formula's calculation one piece at a time, instead of guessing where a wrong answer came from.
- Version-safe saving
- Save As before a risky structural change, and know your Excel version's file format before sharing a workbook that uses newer functions.
Where to find it in Excel
Formulas›Formula Auditing
| A | B | C | D | E | F | G | H | ||
|---|---|---|---|---|---|---|---|---|---|
| 1 | Item | Qty | Price | Total | |||||
| 2 | Pens | 12 | 1.50 | 18.00 | |||||
| 3 | Paper | 3 | 6.00 | 18.00 | |||||
| 4 | Tape | 4 | 2.25 | 9.00 | |||||
| 5 | With tax | 48.60 | |||||||
| 6 | |||||||||
| 7 | Tax rate | 8% | |||||||
| 8 |
Swipe sideways to see the whole window.
- 1Formulas tab.
- 2Trace Precedents draws arrows from the cells a formula uses.
- 3Trace Dependents shows which formulas use the selected cell.
- 4Evaluate Formula steps through the calculation one part at a time.
- 5Go To Special, Paste Special and Format Painter are on the Home tab.
Rendered view of Excel for Microsoft 365 (Windows). Ribbon layout can differ slightly on Mac, Excel for the web or a narrow window.
Guided practice
A small commission sheet. Work through each auditing tool below, on the Total cell.
Real-world exercise — capstone
Intermediate Excel readiness
This exercise touches nearly everything in the track — a good way to check what actually stuck:
- Turn the range into an Excel Table and give it a meaningful name (I1).
- Calculate commission using Sales times Commission Rate, with the rate locked by an absolute reference (I2).
- Use IF to return Met Goal or Below Goal in the Status column (I3).
- Use conditional formatting to highlight below-goal rows (I6).
- Use a PivotTable to summarize Sales by Region (I8).
- Create a simple PivotChart and add a Region slicer (I9).
- Format money, percentages, headers, and the chart cleanly.
- Explain which parts should update automatically when a new rep is added.
Work through it independently, start to finish — that's the real test of the whole track.
Spot a mistake or have an idea for this lesson? Send feedback