Professional workbooks: structure, auditing, protection and Copilot

In this lesson you will learn

Build a workbook on the “inputs → calculations → outputs” principle
Create and manage named ranges
Find and fix errors with the formula auditing tools
Protect a sheet and verify Copilot's results

Elvin has “inherited” a budget file from a colleague: 15 sheets called Sheet1, Sheet2…, hidden numbers inside formulas, merged cells that block sorting — and a total that is somehow ₼3000 short. Spreadsheet errors are costly in real life: in 2013 it emerged that an averaging formula in a famous economics paper had left out several countries. In this lesson you will learn to build files where errors are rare and quick to find.

A reliable structure

Inputs → calculations → outputs. Inputs (rates, targets) on their own sheet or block, formulas elsewhere, the report last. Mark inputs with one colour (for example, blue font).
No numbers inside formulas. Instead of =B2*1.18 write =B2*(1+VAT_Rate): when the rate changes you edit one cell, not a hundred formulas.
The same formula along a row or column. If one formula in a column differs from the rest, it is almost always a mistake.
“Center Across Selection” instead of merging: Format Cells › Alignment › Horizontal › Center Across Selection looks the same but doesn't break sorting or copying.
Meaningful names and a “README” sheet: sheets called Inputs, Data, Calc, Report; the first sheet explains the purpose, sources and change history.

Named ranges

1
Name a single cell
Select F1, type VAT_Rate in the Name Box left of the formula bar and press Enter. Or use Formulas › Defined Names › Define Name. Names cannot contain spaces; use _.
2
Create names from headers
Select A1:C11 › Formulas › Defined Names › Create from Selection (Ctrl+Shift+F3) › Top row. Each column gets its header as a name: Net, Gross.
3
Manage the names
Formulas › Defined Names › Name Manager (Ctrl+F3): change the address, delete old names that show #REF!, and check with Scope whether a name belongs to the whole workbook or one sheet.

Gross = Net · (1 + VAT_Rate)

where:

Netthe amount without VAT, ₼
VAT_Ratea named input cell, e.g. 18% = 0.18

In Excel: =Net*(1+VAT_Rate) — the formula explains itself. For ₼250: 250 · 1.18 = ₼295. Total VAT: =SUM(Net)*VAT_Rate.

Auditing formulas

Tool (`Formulas › Formula Auditing`)	What it does
`Trace Precedents`	draws blue arrows from the cells a formula uses
`Trace Dependents`	shows the formulas that depend on this cell — check before deleting
`Show Formulas`	shows every formula instead of its result
`Error Checking`	walks through the green triangles: “Inconsistent Formula”, “Formula Omits Adjacent Cells”, “Number Stored as Text”
`Evaluate Formula`	calculates a formula step by step — you see where a nested formula goes wrong
`Watch Window`	keeps an eye on key cells from other sheets in one window

Interactive

Loading simulation…

An expense budget (in manat) with two hidden errors and a check cell.

Where is the ₼3000?

Elvin's file shows a net total of ₼5620 and a gross total of ₼10,184.40. Check: 5620 · 1.18 = 6631.60 — the gap is large. Find the errors step by step.

Show solution

1) Trace Precedents on B12: the blue box covers B2:B10, and B11 (₼3000) is outside. The correct total is 5620 + 3000 = ₼8620.
2) Check again: 8620 · 1.18 = 10,171.60, but C12 = 10,184.40 — still a ₼12.80 gap.
3) Show the formulas with Show Formulas: C7 = =B7*1.2 differs from the rest. 640 · 1.2 = 768, whereas 640 · 1.18 = 755.20; the difference is 12.80.
4) After the fix: 8620 · 1.18 = ₼10,171.60 = C12. The check cell is 0.

Protection

1
Unlock the input cells
By default every cell is Locked, but that only takes effect once protection is on. Select the inputs › Ctrl+1 › Protection › untick Locked.
2
Protect the sheet
Review › Protect › Protect Sheet › choose what is allowed (Select unlocked cells, Use AutoFilter…) › optional password. Now formulas can't be deleted by accident.
3
Protect the workbook structure
Review › Protect › Protect Workbook prevents adding, deleting and renaming sheets. To truly encrypt the file: File › Info › Protect Workbook › Encrypt with Password.

Copilot in Excel

In Microsoft 365 the Home › Copilot button on the ribbon opens the assistant pane (what it can do depends on your subscription and your organisation's settings). From a plain-language request Copilot can suggest a formula column, sort and filter data, highlight key values, build PivotTables and charts, and explain trends. For best results the file should be saved in OneDrive or SharePoint and the data should sit in an Excel Table with headers.

Weak prompt	Strong prompt
“Analyse the table”	“Show the 3 largest expense items by the Net column and their share of the total in %”
“Write a formula”	“Add a Gross column: Net × (1 + VAT_Rate), and explain the formula”

Select the cells a formula refers toCtrl+[

Select the formulas that depend on the active cellCtrl+]

Show/hide formulasCtrl+`

Name ManagerCtrl+F3

Create names from selectionCtrl+Shift+F3

Go To (then Special… › Constants)Ctrl+G

Interactive

Loading simulation…

Drill the shortcuts a professional uses every day.

Key points

Structure: inputs → calculations → outputs; no hidden numbers in formulas, the same formula down a column.
Named ranges (VAT_Rate) make formulas readable; manage them with Ctrl+F3.
Auditing: Trace Precedents/Dependents, Evaluate Formula, Error Checking and a check cell.
Protect Sheet guards against accidents; for confidentiality use Encrypt with Password.
Copilot speeds you up, but always verify its results with the auditing tools.

Check yourself

10 questions. Every correct answer earns XP.

1 / 10

Which formula follows best practice?