Working efficiently in Excel

Last updated November 202613 sectionsabout 26 min read

Business Data Analytics is a practical paper sat on a computer. Section I is twenty multiple choice questions, and every sitting since the pilot has set several of them on Excel itself: which shortcut shows formulas, which function counts cells meeting a condition, which tool builds a drop-down list. Sections II and III are 20-mark practicals answered in an Excel workbook that the examiner opens and marks cell by cell. Speed and a clean, traceable layout are therefore worth marks in their own right.

This chapter covers the environment: the parts of the window, the file formats, the keyboard shortcuts the examiner tests, references, names, Tables, validation, formatting, error values, auditing and protection. It closes with the workbook layout that a marker expects to see. The functions themselves follow in the chapters on core Excel functions and on lookup and dynamic array functions.

The workbook and the Excel window

A workbook is one file. It holds one or more worksheets (grids of cells) and may hold chart sheets, which carry a single chart. A cell is identified by its column letter and row number, so the cell in column AB, row 11 is AB11. A block of cells is a range, written with a colon between its corners: A2:A16 is fifteen cells in one column, while A2:J11 is ten columns by ten rows.

File formats and when each matters

Keyboard shortcuts

The examiner has tested shortcuts directly: Ctrl+V to paste (December 2022), Ctrl+` to show formulas (December 2023), the shortcut for function arguments (August 2024) and Go To Special (April 2025 and August 2026). Learn the tables below by purpose rather than as one long list; each shortcut is the one an analyst actually reaches for while building a model.

Navigation

Selection

Editing and entry

Formatting

Formulas and calculation

Data, charts and tools

Ctrl+Shift+Enter and dynamic arrays

Before Excel 2021, a formula that worked on a whole array at once, such as =SUM(F2:F13*G2:G13), had to be confirmed with Ctrl+Shift+Enter, and Excel wrapped it in braces. A formula that returned several values had to be typed into a pre-selected block of exactly the right size. Excel 2021 and 365 have a dynamic array engine: the same formula is entered with plain Enter, and a formula that returns several values spills them into the neighbouring cells automatically. Braces you see in an older file are legacy array formulas; they still work, but edit them with Ctrl+Shift+Enter or rewrite them.

Relative, absolute and mixed references

When a formula is copied, Excel adjusts each reference by the distance moved unless a dollar sign fixes it. Getting the dollar signs right is what lets one formula be written once and filled across a whole grid, which is how a marker can see that a model was built rather than typed.

The illustration below is Tausi Distributors Ltd, a fictional Nairobi wholesaler used throughout this part. Its pricing analyst wants each product's current price increased by three alternative growth rates, in one grid built from one formula.

C3: =$B3*(1+C$2)     copied right to D3: =$B3*(1+D$2)
                     copied down to C4:  =$B4*(1+C$2)
                     copied to E5:       =$B5*(1+E$2)

Named ranges and the Name Manager

A name replaces a cell address with a word, so =Loan*Rate/12 reads like the question. Names are absolute by default, appear in every formula that uses them, and make a model easier for a marker to follow.

• Create one select the cell or range, type the name in the Name Box and press Enter; or Formulas > Define Name.
• Create many at once select labels plus values (e.g. A2:B5) and press Ctrl+Shift+F3, choosing Left column, so each value is named after its label.
• Manage them Ctrl+F3 opens the Name Manager, where names are edited, re-pointed or deleted and their scope (workbook or one sheet) is shown.
• Rules a name starts with a letter or underscore, has no spaces (use Tax_Rate), cannot look like a cell address (TR2026 is a cell, so it is not allowed) and is not case-sensitive.

B2 named Loan        B3 named AnnualRate        B4 named Months
=PMT(AnnualRate/12, Months, -Loan)

Excel Tables and structured references

Ctrl+T converts a range with headers into an Excel Table (Insert > Table). A Table is the right container for any list of records the exam gives you: it expands automatically when rows are added, copies a formula down the whole column the moment you type it once, keeps headers visible while scrolling, and gives every column a name that formulas can use. A PivotTable or chart built on a Table picks up new rows when refreshed.

Calculated column inside the Table:   =[@Units]*[@Price]
Outside the Table:                    =SUMIFS(Sales[Amount], Sales[Region], "Nairobi")
Total row (SUBTOTAL ignores filtered-out rows): =SUBTOTAL(109, [Amount])

Data validation

Data validation (Data > Data Tools > Data Validation) restricts what may be typed into a cell. It is the feature that creates a drop-down list, which the August 2024 paper asked about directly. In a model it protects the input cells: a scenario selector can only hold Base, Optimistic or Downside, and a rate cell can only hold a decimal between 0 and 1.

1. Select the input cells.
2. Data > Data Validation > Settings: choose Allow (Whole number, Decimal, List, Date, Text length or Custom).
3. For a list, type the items separated by commas (Base,Optimistic,Downside) or point the Source at a range.
4. Input Message tab: a prompt shown when the cell is selected.
5. Error Alert tab: Stop (refuses the entry), Warning or Information.

K2: =SORT(UNIQUE(C2:C13))        spills the distinct regions (Excel 2021 and later)
Validation for M2, Allow: List, Source: =$K$2#
Custom rule for a rate cell B3, Allow: Custom, Formula: =AND(B3>0, B3<1)

The # after $K$2 is a spill reference: it means the whole array that K2 returns, however long it becomes. When a new region appears in column C, the list in K2 grows and the drop-down offers it without anyone editing the validation rule. Circle Invalid Data (Data > Data Validation drop-down) highlights cells that broke a rule before it was applied.

Conditional formatting

Conditional formatting (Home > Conditional Formatting) changes a cell's appearance when its value meets a rule. It turns a schedule into an exception report: overdue debtors in red, the top ten customers in green, variances above tolerance flagged.

• Highlight Cells Rules greater than, less than, between, equal to, text that contains, a date occurring, duplicate values.
• Top/Bottom Rules top 10 items or percent, above or below average.
• Data Bars an in-cell bar proportional to the value.
• Colour Scales a two- or three-colour gradient, useful for a heat map of a pivot grid.
• Icon Sets arrows or traffic lights by threshold, set by value or percentile in Manage Rules.
• New Rule > Use a formula any TRUE/FALSE formula written for the top-left cell of the selection.

Select A2:H13 (the whole register), New Rule > Use a formula:
=$H2>100000        highlights the entire row of any sale above Sh.100,000
=$C2=$M$2          highlights rows whose region matches the drop-down in M2

Number formats and custom formats

A number format changes how a value looks, never the value itself. Ctrl+1 > Number > Custom accepts a format code of up to four sections separated by semicolons: positive; negative; zero; text.

Error values

=IFERROR(A2/B2, 0)                     any error becomes 0
=IFNA(VLOOKUP(K2, A2:E6, 4, FALSE), "Not listed")   only #N/A is trapped

Auditing a model

The examiner marks the working, so formulas must be traceable, and the August 2026 paper set a question on exactly how a reviewer finds hard-coded figures in a calculation block. The Formulas tab's Formula Auditing group and Go To Special do this work.

Protecting the model

Every cell is Locked by default, but locking has no effect until the sheet is protected. So the method is to unlock the input cells first and then protect the sheet, leaving users free to change inputs but unable to overwrite a formula.

1. Select the input cells, Ctrl+1 > Protection, untick Locked.
2. Optionally tick Hidden on formula cells so their formulas do not show in the formula bar.
3. Review > Protect Sheet, choose what users may still do (select unlocked cells, sort, use AutoFilter) and set an optional password.
4. Review > Protect Workbook protects the structure: sheets cannot be added, deleted, renamed or unhidden.
5. File > Info > Protect Workbook > Encrypt with Password prevents the file being opened at all without the password.

Laying out the exam workbook

Each Section II and III question instructs you to create a worksheet with the name of the entity and to put your workings and solutions in it. Right-click the sheet tab, Rename, and type the entity's name (for example Tausi Distributors), one sheet per question. Then lay the sheet out in three visibly separate blocks.

• Never type a number into a formula that also appears as an input: =B5*16% should be =B5*$B$3 with 16% in B3. Markers award method marks for linked formulas.
• Keep time running across columns (years or months in C to G) and line items down rows, so one formula fills across.
• Label every row and state the unit in the header; format money with thousands separators and percentages as percentages.
• Put a check figure where one exists: assets minus equity and liabilities equals zero, total of the pivot equals the total of the data.
• Save regularly with Ctrl+S; an exam workstation can freeze.

Practice