Workbooks, cells and references

• Workbook — the spreadsheet file, saved with the extension .xlsx. One workbook contains one or more worksheets.

• Worksheet — a single grid of rows and columns within the workbook, reached by its tab at the foot of the window. Sheets can be renamed, reordered, copied, coloured, hidden and deleted.

• Cell — the intersection of one row and one column; the smallest unit that holds data.

• Cell address (reference) — the column letter followed by the row number, such as C5 or AB120. A cell on another sheet is written Sheet2!C5.

• Range — a rectangular block of cells, written with a colon. A1:C10 is every cell from A1 to C10 — $3$ columns $\times$ $10$ rows $=30$ cells.

Size of a worksheet.

$$\text{Rows: }1,048,576(=2^{20})\text{Columns: }16,384(=2^{14})$$

Columns are lettered A to Z, then AA to AZ, and so on up to XFD.

Also worth knowing: the active cell is the currently selected one, shown in the Name Box; and the formula bar shows what a cell actually contains, which may differ from what it displays.

What it means. The column is simply too narrow to display the number in its current format. It is not an error of any kind: the value stored in the cell is complete and correct, and any formula referring to the cell gets the right answer. Excel shows hashes rather than a truncated number, because a number cut short would be worse than no number — 1234567 displayed as 123 would be actively misleading.

How to correct it. Any of:

1. Widen the column — drag the boundary in the column heading, or double-click it to autofit to the widest entry.
2. Reduce the decimal places shown, or choose a more compact number format.
3. Reduce the font size, or switch on Wrap Text / Shrink to Fit in Format Cells.

How it differs from an error value. Hashes filling the cell are a display problem. An error value is a computation problem, and the cell genuinely holds no usable result:

The practical difference: widening the column fixes hashes and nothing else; an error value needs the formula or the data corrected, and it propagates — every formula depending on an error cell shows an error too.

The three kinds differ in what happens to a reference when the formula is copied, and they are distinguished by the dollar signs. The dollar locks whatever follows it.

1. Relative reference — A1

No dollar signs. Both the column and the row adjust by the displacement of the copy. Moved one column right, A1 becomes B1; moved one row down, A2.

Needed when: every row must use its own data. Calculating =B2*C2 for the amount of an order in row 2, then filling down so that row 3 computes =B3*C3. This is the default, and correct most of the time.

2. Absolute reference — $A$1

Both parts locked. The reference is identical in every copy of the formula.

Needed when: one value stored in one cell must be consulted by every formula. A GST rate in E1, used as =B2*$E$1 and filled down; a currency conversion factor; a grand total used to compute each row's percentage share, =B2/$B$20.

An extra benefit: the constant exists in exactly one place, so changing it updates every dependent formula at once. Typing the number into each formula instead would mean editing every one.

3. Mixed reference — $A1 or A$1

One part locked, the other free.

• $A1 — column fixed, row free. Copying across keeps pointing at column A; copying down moves through its rows.
• A$1 — row fixed, column free. Copying down keeps pointing at row 1; copying across moves through its columns.

Needed when: a formula must be filled both across and down, taking one factor from a header row and the other from a header column. In a multiplication table with the multipliers in row 1 and the multiplicands in column A, the single formula

$$\text{=B\$1*\$A2}$$

fills the entire grid correctly. Neither a fully relative nor a fully absolute version can do this — the first drifts diagonally into nonsense, the second repeats one product in every cell.

Shortcut. Pressing F4 while the cursor is on a reference cycles A1 → $A$1 → A$1 → $A1 → A1.

First fix the rule for each reference:

• $B3 — column locked at B, row free
• C$2 — column free, row locked at 2
• A1 — both free

(a) C3 → E3. Displacement: 2 columns right, 0 rows.

$$\text{=\$B3+E\$2-C1}$$

(b) C3 → C6. Displacement: 0 columns, 3 rows down.

$$\text{=\$B6+C\$2-A4}$$

(c) C3 → F8. Displacement: 3 columns right, 5 rows down.

$$\text{=\$B8+F\$2-D6}$$

Method to use in the exam: write the displacement as (columns, rows) first, then treat each reference independently — add the column displacement unless a dollar precedes the letter, and the row displacement unless a dollar precedes the number.

The cause: formatting changes the display, not the value.

Reducing the decimal places shown is a format. The cell continues to hold its full value, and every formula uses that full value. So the sheet is doing arithmetic on numbers that are not the ones on the screen.

A concrete illustration. Suppose four cells hold

$$10.4,10.4,10.4,10.4$$

Displayed with no decimals, each rounds to $10$, so the column reads

$$10,10,10,10$$

Adding what is displayed gives $40$. But SUM works on the stored values:

$$10.4+10.4+10.4+10.4=41.6$$

which is then displayed as $42$. The reader sees four tens totalling forty-two, and quite reasonably concludes the spreadsheet is wrong. It is not — the displayed figures are each rounded down by $0.4$, and four such losses come to $1.6$, enough to move the total by two.

$$\text{apparent error}=\sum (\text{rounding of each cell})$$

The discrepancy therefore grows with the number of rows, which is why it is invisible in a short test and obvious in a real report.

How it should be handled. Which remedy is right depends on what is actually required.

1. If the underlying precision matters — show it. Display enough decimal places that the figures shown do add up. Honest and simplest, and correct for engineering or scientific data.

2. If the values themselves should be whole — round them, do not merely format them. Use the ROUND function to change the stored value:

=ROUND(A2,0)

Now the cell genuinely holds $10$, and SUM over the rounded column gives exactly $40$. Use ROUND(x,2) for two decimals. This is the correct approach for currency, where the paisa either exists or it does not, and a printed invoice must add up.

3. Round the total rather than the parts, if the parts are estimates and only the total is reported. Perfectly valid, but it must be documented, because the total will not equal the sum of the printed parts.

What not to do. Excel offers a setting called Set precision as displayed, which permanently discards the hidden decimals across the whole workbook. It appears to solve the problem, and it does — by destroying data irreversibly. There is no undo and no way to recover the original values.

The general lesson. Formatting is for the reader; functions are for the data. Any requirement expressed as "the values must be whole" or "amounts must be to two decimals" is a requirement about values, and must be met with ROUND — never with a display format.