• Foundation
  • About 14 min
  • 5 practice questions

Spreadsheets from zero: workbooks, sheets, rows, columns and cells

Name the parts of a spreadsheet window, read and write cell addresses and ranges, and tell LibreOffice Calc and Microsoft Excel files apart.

If something looks wrong, please tell us.

A spreadsheet is a grid for numbers and text that can do the arithmetic for you. Shopkeepers use it for stock and bills, offices for salaries and attendance, schools for marksheets, and families for monthly budgets. Once you understand the grid, every other spreadsheet skill - formulas, charts, printing - builds on it. The two programs you will meet most are LibreOffice Calc (free and open source) and Microsoft Excel (part of Microsoft Office). Their screens look different, but the ideas are the same, and these lessons teach the shared ideas first and name the differences where they matter.

Workbook and worksheet

The file you open is called a workbook. Inside it are one or more worksheets (usually just called sheets), shown as tabs near the bottom of the window with names like Sheet1. You can add a sheet, rename it (right-click the tab and choose Rename), move it or delete it. Keeping different months or departments on different sheets of one workbook keeps related work together.

File extensions to recognise
ProgramIts own file formatTemplate format
LibreOffice Calc.ods (OpenDocument Spreadsheet).ots
Microsoft Excel (2007 and later).xlsx.xltx
Older Microsoft Excel.xls.xlt

Both programs can open and save each other's formats, so a sheet made in Calc can be sent as .xlsx to someone who uses Excel. Both can also save a plain .csv file, which keeps only the values of one sheet - no formatting, no formulas and no extra sheets.

Rows, columns and cells

The grid has columns running up and down, labelled with letters A, B, C and so on, and rows running across, numbered 1, 2, 3 and so on. After column Z the letters continue as AA, AB ... AZ, then BA, BB, and so on. In Excel 2007 and later a sheet has 1,048,576 rows and 16,384 columns, the last column being XFD. Where a column and a row meet is a cell, the box that holds one value.

Inside a workbook

  • Worksheets
    • Tabs such as Sheet1
    • Add, rename, move or delete
  • Columns
    • Run up and down
    • Labelled A, B, C ... then AA, AB
  • Rows
    • Run across
    • Numbered 1, 2, 3 ...
  • Cells
    • Where a column and a row meet
    • Address: column letter + row number, such as C5

Cell addresses and ranges

Every cell has an address (also called a cell reference): the column letter followed by the row number. The cell in column C and row 5 is C5 - never 5C. The cell you have clicked is the active cell; it has a thick border, and its address appears in the Name Box at the left end of the formula bar. The formula bar (Calc also calls it the input line) shows what is really stored in the active cell, which matters later when a cell shows a result but holds a formula.

A range is a rectangular block of cells written as the first (top-left) cell, a colon and the last (bottom-right) cell. A1:A10 is ten cells down column A; A1:C1 is three cells across row 1; B2:D5 is a block. To count the cells in a range, multiply the number of columns by the number of rows.

Worked example 1: counting cells in a range

How many cells are in B2:D5? Columns B, C, D make 3 columns. Rows 2, 3, 4, 5 make 4 rows. Cells = 3 × 4 = 12. In the same way, A1:C3 has 3 × 3 = 9 cells, and C4:F10 has 4 columns (C, D, E, F) × 7 rows (4 to 10) = 28 cells.

Worked example 2: which column is the 28th?

Columns 1 to 26 are A to Z. Column 27 starts the two-letter names: 27 is AA, so 28 is AB. Column 52 is AZ, and column 53 is BA. Going the other way, AD is 26 + 4 = column 30, because D is the fourth letter.

Moving around the sheet

  • Arrow keys move one cell at a time.
  • Enter confirms what you typed and moves down; Tab confirms and moves right.
  • Ctrl+Home jumps to cell A1 in both programs.
  • Typing an address such as K200 in the Name Box and pressing Enter jumps straight to that cell.
  • Sheets in other tabs can be reached with a click, or with Ctrl+Page Down and Ctrl+Page Up.

One visible difference: when a formula refers to a cell on another sheet, Excel writes Sheet2!A1 (with an exclamation mark) while Calc writes Sheet2.A1 (with a dot). The idea - sheet name, then cell - is the same.

Address = column letter + row number (C5). Range = first cell : last cell (B2:D5). Cells in a range = columns × rows.

Key takeaways: a workbook holds sheets; a sheet is a grid of columns (letters) and rows (numbers); each cell has an address such as C5; a range such as B2:D5 is a block whose size is columns times rows. Calc saves as .ods and Excel as .xlsx, and each can read the other.

Check your understanding

  1. 1. Which address names the cell in column D and row 7?

    1. 7D
    2. D:7
    3. R7D
    4. D7
    Show answer

    Answer: D. A cell address is always the column letter first and then the row number, so column D, row 7 is D7. The other forms are not cell addresses.

  2. 2. How many cells are in the range B2:D5?

    1. 12
    2. 8
    3. 9
    4. 15
    Show answer

    Answer: A. Columns B, C and D are 3 columns; rows 2 to 5 are 4 rows. Cells = 3 × 4 = 12.

  3. 3. Which column comes immediately after column Z?

    1. ZA
    2. AA
    3. Z1
    4. BA
    Show answer

    Answer: B. After the 26 single letters, column names continue with two letters starting from AA, then AB, AC and so on. BA comes only after AZ.

  4. 4. A file saved in LibreOffice Calc's own format has which extension?

    1. .xlsx
    2. .odt
    3. .ods
    4. .csv
    Show answer

    Answer: C. Calc's native format is OpenDocument Spreadsheet, .ods. The .xlsx extension belongs to Excel, .odt is a Writer text document, and .csv is plain values only.

  5. 5. What does the Name Box at the left of the formula bar show?

    1. The name of the workbook file
    2. The total of the selected cells
    3. The address of the active cell
    4. The name of the current sheet tab
    Show answer

    Answer: C. The Name Box shows the address (such as C5) of the active cell or the selected range, and you can type an address there to jump to it. The file name is in the title bar and the sheet name is on its tab.

Privacy · Terms · Refunds · Delivery