Cells, ranges, and formatting

Why it matters

A spreadsheet is often the first place data lands: a technician logs sample results, an engineer tracks material costs, a project manager lists tasks. If that first table is messy, every formula, filter and chart built on it is harder and more likely to be wrong. Ten minutes spent laying out a clean table saves hours later.

This course works in Google Sheets (free, in the browser) or LibreOffice Calc (free, desktop). Microsoft Excel works too; where menus differ, the Excel path is given.

The core idea: cells, rows, columns, ranges

• A cell is one box, named by its column letter and row number: B3 is column B, row 3.
• A range is a rectangle of cells, written with a colon: A1:E11 is everything from A1 to E11. B:B is all of column B.
• A sheet (or tab) is one grid. A file (a workbook in Excel, a spreadsheet in Sheets) can hold many sheets.
• Every cell holds one of: a number, text, a date, a boolean (TRUE/FALSE), or a formula that starts with =.

What you see in a cell is not always what is stored. A cell can store 0.0825 and display 8.25%, or store a date and show Sep 2, 2026. Formatting changes the display, not the value. Check the real value in the formula bar above the grid.

A tidy table: one row per record, one column per field

Data analysts call this layout tidy data:

1. One header row at the top, with short, unique names (Date, Item, Qty, UnitCost).
2. One row per record (one order, one sample, one inspection).
3. One column per field, and each column holds one type of value: all dates, or all numbers.
4. No blank rows or columns inside the table, and no merged cells. Blank rows break sorting and filtering; merged cells break almost everything.
5. Units in the header, not in the cells: write Mass_g and 12.5, not 12.5 g. Text like 12.5 g cannot be added up.

Worked example

Bad layout:

Problems: a title in the header row, units mixed into values, a blank row, inconsistent names. The tidy version:

Put a title or notes on a separate sheet or above the table with a blank row between, never inside it.

Formatting that helps (and does not lie)

• Number formats. Select the column, then Format > Number (Sheets), Format > Cells (LibreOffice), or Home > Number Format (Excel). Use Currency or Number with 2 decimals for money, Percent for rates, a date format for dates.
• Dates. Type dates as 2026-09-02 (ISO format). All three tools recognise it as a real date, and it sorts correctly in any country.
• Freeze the header row so it stays visible while scrolling: View > Freeze > 1 row (Sheets), View > Freeze Cells > Freeze First Row (LibreOffice), View > Freeze Panes > Freeze Top Row (Excel).
• Bold the header and widen columns (double-click the border between column letters to auto-fit).
• Align numbers right (the default). If a number sits on the left, it is probably stored as text: a warning sign you will fix in the cleaning lesson.

Excel and newer Google Sheets can also turn a range into a Table (Insert > Table or Ctrl+T in Excel, Format > Convert to table in Sheets). Tables add filter buttons and banded rows and grow automatically. They are useful but optional for this course.

Common mistakes

• Typing 1,000 or $8.50 as text by accident (a leading apostrophe or a space). Check alignment.
• Using colour as data ("red rows are urgent"). Colour cannot be filtered reliably or counted by formulas; add a Priority column instead.
• Several tables side by side on one sheet. Give each table its own sheet.
• Over-formatting: rainbow fills and borders on every cell make data harder to read, not easier.

Quick recap

• Cells are named like B3; ranges like A1:E11.
• Formatting changes how a value looks, not the stored value.
• Tidy tables: one header row, one row per record, one type per column, no blank rows, no merged cells, units in headers.
• Use ISO dates, freeze the header, and set number formats per column.

Try it

Open the Do tab to build the practice table used throughout this course.