Excel Basics for Analysts: Cells, References and Tables
Excel is where most people first work with data, and the habits you build here carry into Power BI, SQL and Python. This course uses one small sales dataset throughout. Type it into a blank workbook, starting in cell A1, so you can follow every lesson.
| OrderID | Date | Region | Rep | Product | Category | Units | UnitPrice |
|---|---|---|---|---|---|---|---|
| 1001 | 2025-01-06 | North | Asha | Laptop | Computers | 2 | 900 |
| 1002 | 2025-01-09 | South | Ben | Mouse | Accessories | 10 | 20 |
| 1003 | 2025-01-15 | North | Asha | Monitor | Computers | 3 | 250 |
| 1004 | 2025-02-02 | East | Chen | Keyboard | Accessories | 6 | 45 |
| 1005 | 2025-02-11 | South | Ben | Laptop | Computers | 1 | 900 |
| 1006 | 2025-02-20 | East | Chen | Webcam | Accessories | 4 | 60 |
| 1007 | 2025-03-03 | North | Diana | Monitor | Computers | 2 | 250 |
| 1008 | 2025-03-14 | South | Ben | Keyboard | Accessories | 8 | 45 |
Rules for a dataset you can analyse
- One row per record. Here, one row is one order line.
- One column per attribute, with a single header row.
- No blank rows or columns inside the data, no merged cells, no totals mixed in with the data.
- One type of value per column: all dates, all numbers or all text.
Your first formula
A formula starts with =. In I1 type the header Revenue, and in I2 type:
=G2*H2Excel multiplies Units by UnitPrice for that row. Double-click the small square at the bottom-right corner of the cell (the fill handle) to copy the formula down the column.
Relative and absolute references
When you copy =G2*H2 down one row it becomes =G3*H3. That is a relative reference: it moves with the formula.
Sometimes one part must stay fixed. Put a tax rate of 0.18 in cell L1, then in J2 type:
=I2*$L$1The dollar signs make $L$1 an absolute reference, so every copied row still points at L1. Press F4 while editing a reference to cycle through $L$1, L$1, $L1 and L1.
Turn the range into a Table
Click any cell in the data and press Ctrl+T. An Excel Table gives you:
- Automatic expansion when you add rows, so formulas, charts and PivotTables stay up to date.
- Filter buttons on every header.
- Structured references that use column names in place of cell addresses.
Name the table Sales on the Table Design tab. The revenue formula can now be written as:
=[@Units]*[@UnitPrice]and a total anywhere in the workbook as:
=SUM(Sales[Revenue])The rest of this course assumes your data is in a Table called Sales.
Practice
Add a ninth order of your own at the bottom of the table and confirm that the Revenue column fills in by itself.