Module 03: Build dependable worksheets
Turn an order range into a growing Excel table
Build a named table, calculate each order line, and verify that a new record enters the total.
Excel 2016, 2019, 2021, 2024, and Microsoft 365 for Windows desktop. Ribbon names can vary. This lesson uses ordinary table formulas, not dynamic arrays. Time is an estimate.
Useful first: Sort complete records and filter a focused view · Copy formulas while keeping shared inputs fixed · Choose the right total, average, and count
- Create a table with a verified header and range.
- Use structured references for row calculations and totals.
- Reconcile the result after adding one complete record.
Small data. A result you can check.
Synthetic data only. Paste at A1; add the LineTotal heading and formulas as instructed.
View the raw practice data
OrderID Product Quantity UnitPrice O101 Mug 2 12 O102 Tray 3 8 O103 Cup 4 5
01Start with records that belong together
Paste the sample into A1:D4 of a blank worksheet. Each row is one order line, and the headings describe fields rather than a report title. Enter LineTotal in E1. Keep summary formulas to the right of the dataset so they cannot be mistaken for another order when the table grows.
Check that Quantity and UnitPrice contain numbers. The sample uses integers, avoiding decimal-separator ambiguity. The three initial calculations are 2 × 12, 3 × 8, and 4 × 5. Their total is 68; record this control before creating any formulas.
| Range | Contents |
|---|---|
| A1:D1 | OrderID, Product, Quantity, UnitPrice |
| A2:D2 | O101, Mug, 2, 12 |
| A3:D3 | O102, Tray, 3, 8 |
| A4:D4 | O103, Cup, 4, 5 |
| E1 | LineTotal |
02Create and name the table
Select A1:E4, press Ctrl+T, and confirm both the range and My table has headers. Select a cell inside the table and use Table Design > Table Name to name it Orders. A table provides a defined record range and named columns; the banded style is only its appearance.
Do not select an entire worksheet column for this small exercise. Blank records inside an oversized table would complicate later counts. If the proposed range is wrong, cancel the dialog and select the intended rectangle again.
03Calculate a line and then the whole table
Enter the formula below in E2. The @ references mean Quantity and UnitPrice from the current table row. A calculated column normally fills the other rows; verify E2:E4 contains 24, 24, and 20. If automatic filling is disabled, fill the formula through the table data rows yourself.
Enter Total in G1 and =SUM(Orders[LineTotal]) in G2. This references the table's data column rather than a fixed final row. It should return 68. Filtering does not turn this SUM into a visible-rows-only total; use the linked SUBTOTAL guide when that is the question.
=[@Quantity]*[@UnitPrice]Enter in E2, inside the Orders table. Check every calculated row, not just the first result.
Add a fourth order and prove that the table and its calculation include the new record.
- With no Total Row enabled, enter O104, Plate, 5, and 6 in A5:D5 immediately below the table.
- Verify the table border includes row 5. If it did not expand, use Table Design > Resize Table and set A1:E5.
- Check the formula in E5 and the summary in G2. Save the workbook as XLSX so the table and formulas remain available.
Show the worked answer
E5 is 30 and G2 is 98.
The data contains four order records; Orders[LineTotal] includes E2:E5.
Check your work- All four cells E2:E5 are formulas with results 24, 24, 20, 30.
- Changing D5 from 6 to 7 makes E5 equal 35 and G2 equal 103; restore 6 afterward.
One question before you move on.
Ready for the next step?
Mark this lesson when you can explain the idea and reproduce the practice result.
Your checklist stays on this browser.Reference notes
These lessons use original examples. Check Microsoft’s documentation for details and platform-specific options.
Prepared September 13, 2026 · Column Harbor editorial team. Menu locations and available functions can vary by Excel version.