Formatting Cell Data as a Table – Charts and Objects in Spreadsheets (Senior 3 ICT) | Ganzaa

Charts and Objects in Spreadsheets

Lessons

Senior 3 · ICT

Charts and Objects in Spreadsheets

5 lessons

Lesson 4 of 5

Formatting Cell Data as a Table

40 min

The school shop's sales list

The student council runs a small shop that sells exercise books, pens and mathematical sets. Its treasurer keeps a sales list in Excel. Every week she adds rows at the bottom, copies the Amount formula down by hand, changes the SUM to include the new rows, and recolours alternate rows so the list is easy to read. One week she forgot to extend the SUM, and the council's report was 27,000 Rwf short.

Excel can turn a plain range into a table, an object that formats itself, grows by itself, copies its formulas by itself and totals itself.

Definition

Table (in Excel): a range of cells turned into one named object, with a header row, filter buttons and a style, that grows when rows or columns are added next to it.

Banded rows: alternate rows shaded in two colours, to guide the eye along a row.

Total row: an extra last row of a table where each column can show a sum, average, count, maximum or minimum.

Calculated column: a table column whose formula, typed once, is filled into every row automatically.

Structured reference: a formula reference that uses the table's and columns' names, such as Sales[Amount], instead of cell addresses such as D2:D6.

Formatting data as a table

Make sure the data has one heading row, no empty rows or columns inside it, and no merged cells.
Click any one cell inside the data.
Home → Format as Table and click a style (Light, Medium or Dark).
The Create Table box shows the range Excel found, such as =$A$1:$D$6. Check it, and keep My table has headers ticked. Click OK.

The shortcut Ctrl + T (or Insert → Table) does the same with the default style. A new Table Design tab appears whenever a cell in the table is selected.

Mock-up drawn by Ganzaa. Prices are in Rwf and the sales are illustrative.

What the table does for you

Feature	What happens
Self-formatting	Banded rows, a bold header and borders, which stay correct after sorting, filtering or adding rows.
Filter buttons	Every heading gets a ▾ button for sorting and filtering (S2 Unit 3).
Growing	Type in the row just below the table, or the column just to its right, and the table takes it in.
Calculated columns	Type a formula once in a column; Excel fills it into every row, including new ones.
Total row	Tick Table Design → Total Row; in each total cell, pick Sum, Average, Count, Max or Min from a list.
Headings that stay	Scroll down a long table and the headings replace the column letters A, B, C at the top.
Charts that keep up	A chart made from a table includes new rows automatically.

Name your table. In Table Design → Table Name, replace “Table1” with a name without spaces, such as Sales. Formulas can then use it: =SUM(Sales[Amount]) adds the whole Amount column, however long it grows.

Worked example 1: a calculated column

The table Sales has the columns Item, Unit price and Qty sold. In D1 the treasurer types the heading Amount; the table grows to include column D. In D2 she types =, clicks B2, types * and clicks C2. Excel writes the formula with structured references:

=[@[Unit price]]*[@[Qty sold]]

“@” means “in this row”. Excel fills it down the whole column at once: exercise books $400 \times 120 = 48\,000$ , pens $200 \times 210 = 42\,000$ , and so on. When she adds “Eraser, 150, 60” in the row below, the table grows and the new Amount, 9,000, appears by itself.

Worked example 2: the total row and a filter

With Total Row ticked, the Amount total shows 141,500 Rwf (before the eraser was added). Now the council filters Unit price to show only items costing 300 Rwf or more: exercise books, rulers and mathematical sets.

The total row now shows $48\,000 + 12\,000 + 30\,000 = 90\,000$ Rwf. It uses the SUBTOTAL function, which ignores rows hidden by a filter. A plain =SUM(D2:D6) typed below the table would still show 141,500. Both are right; they answer different questions.

Warning

Common problems

Empty rows inside the data: Excel stops the table at the gap. Delete the empty rows first.
Two heading rows or merged headings: a table can have only one header row, with one heading per column. Unmerge and combine them.
Format as Table is greyed out: the sheet may be protected or shared, or the selection is already part of a table.
Only colours, no table: to keep the look but return to a plain range (for example, for merged cells), use Table Design → Convert to Range.

Aldo James: how to use tables in Excel, a beginner's guide covering two ways to turn a range into a table, styles, the total row and how tables grow.

✦

Hands-on: a shop table

In S3_Charts.xlsx, add a sheet Shop and type the Item, Unit price and Qty sold columns from the mock-up above (A1:C6).
Click in the data, press Ctrl + T, check the range and the headers box, and click OK. Pick a different style from Table Design → Table Styles.
Rename the table Sales.
Add the calculated column Amount as in Worked example 1. Check that every row filled in.
Tick Total Row. Set the Qty sold total to Sum and the Unit price total to Average.
Add two new items below the table. Did the formatting, the Amount and the totals follow?
Make a pie chart of Item and Amount. Add one more item: does the pie gain a slice by itself?
In a cell outside the table, type =MAX(Sales[Amount]). Which item does it point to?

Practice

Define an Excel table, banded rows and a total row.
Write the steps to format the range A1:E40 as a table with a Medium style.
Give four advantages of a table over a plain range that has been coloured by hand.
What three things should be checked in the data before formatting it as a table?
Explain what =SUM(Results[Mark]) means, and why it is better than =SUM(C2:C41) in a list that grows every term.
A table of 6 items has Amounts 48,000, 42,000, 9,500, 12,000, 30,000 and 9,000 Rwf. A filter hides the items under 10,000 Rwf. What does the total row show?
Gisa formatted his data as a table, but the table stopped at row 15, although the data goes to row 40. Diagnose the problem.
Teta says: “Format as Table is just a quick way to colour cells.” Evaluate her statement.

Show answersHide answers

Table: a range turned into one named object with a header row, filter buttons and a style, which grows when data is added. Banded rows: alternate rows shaded differently. Total row: a last row showing a sum, average, count, maximum or minimum for each column.
Click any cell in A1:E40 → Home → Format as Table → choose a Medium style → check =$A$1:$E$40 and tick My table has headers → OK.
Any four: it keeps its formatting after sorting and adding rows; it grows by itself; formulas fill whole columns; a total row in one click; filter buttons; headings stay visible when scrolling; charts update with new rows; formulas can use names.
One heading row; no empty rows or columns inside; no merged cells.
Add every value in the Mark column of the table named Results. The structured reference grows with the table, so new rows are included automatically; C2:C41 would miss row 42 onwards.
Hidden: 9,500 and 9,000. Shown: $48\,000 + 42\,000 + 12\,000 + 30\,000 = 132\,000$ Rwf.
Row 16 is probably empty, so Excel saw the data as ending at row 15. Delete the empty row, then use Table Design → Resize Table to set the range to row 40.
Partly true but mostly wrong. It does colour the cells, but it also creates an object that grows, fills formulas into calculated columns, totals with a total row that respects filters, keeps headings visible and lets formulas and charts use names. Colouring cells by hand gives none of these.

Sources and credits

Steps checked against Create and format tables, Microsoft Support (facts only).
Video: How To Use Tables in Excel: 2026 Beginner Guide, Aldo James, embedded from YouTube.
The mock-up, feature table, worked examples, hands-on task and practice items were written by Ganzaa. The shop data are illustrative.

1The school shop's sales list

2Formatting data as a table

3What the table does for you

4Hands-on: a shop table

The school shop's sales list

Formatting data as a table

What the table does for you

Hands-on: a shop table