Business Data Analytics cheat sheet

Key terms

Ribbon

The band of tabs (Home, Insert, Page Layout, Formulas, Data, Review, View) that replaced menus in Excel 2007. Each tab is split into groups; Data holds Sort & Filter, Data Tools (Data Validation, Text to Columns, Remove Duplicates) and Forecast (What-If Analysis).

Quick Access Toolbar

The small toolbar above or below the ribbon that holds commands you use constantly, such as Save, Undo and Redo. Right-click any ribbon command and choose Add to Quick Access Toolbar to put it there; each item then also gets an Alt plus number shortcut.

Backstage view

What opens when you click File: Info, New, Open, Save As, Print, Share, Export and Options. Excel Options (File > Options) holds the calculation settings, add-ins such as Solver and the ribbon customisation.

Name Box

The box at the left of the formula bar that shows the address or name of the active cell. Typing an address or a defined name into it and pressing Enter jumps straight there; typing a name over a selected range creates that name.

Formula bar

Shows the content of the active cell: the formula, not its result. Ctrl+Shift+U expands it for long formulas.

Status bar

The strip at the bottom of the window. Select a range and it shows the Sum, Average and Count of the selection without a formula, which is a quick check on any total you build.

Fill handle

The small square at the bottom right of the active cell. Dragging it copies a formula or extends a series (months, numbers, dates); double-clicking it fills down as far as the adjacent column has data.

Sheet tabs

The tabs at the bottom that name each worksheet. Double-click a tab to rename it. In this paper every answer sheet must carry the name of the entity in the question.

Relative (A1)

Both column and row move when copied. Copying =B3*C3 one row down gives =B4*C4.

Absolute ($A$1)

Neither moves. Use it for a single input cell such as a tax rate or a discount rate that every row reads.

Mixed, column fixed ($A1)

The column stays A while the row moves. Use it when every column of a grid must read the same input column.

Mixed, row fixed (A$1)

The row stays 1 while the column moves. Use it when every row of a grid must read the same header row.

Trace Precedents

Draws arrows from the cells a formula reads (Formulas > Trace Precedents). A dashed arrow with a sheet icon means a precedent on another sheet.

Trace Dependents

Draws arrows to the cells that use the active cell, showing what would change if it changed.

Remove Arrows

Clears the tracer arrows.

Show Formulas

Ctrl+` displays every formula in place of its value, so a typed number in a row of formulas stands out at once.

Evaluate Formula

Steps through a formula one calculation at a time, showing each intermediate result: the tool for a long nested IF that returns the wrong value.

Error Checking

Walks through cells with errors or inconsistent formulas (the green triangles).

Watch Window

Keeps chosen cells (NPV, profit) in a floating window so their values are visible while inputs elsewhere are changed.

Go To Special

F5 > Special, or Home > Find & Select > Go To Special. Formulas selects every formula cell, Constants every typed value, Blanks every empty cell, plus Current region, Visible cells only, Precedents and Dependents.