Concept
Excel's structure works like a building:
Workbook (the whole file — Sales2026.xlsx)
└── Worksheet (one tab — "Jan", "Feb", "Summary")
└── Cell (one box — B5)
└── Range (a group of cells — B5:D20)
A cell address is always column first, row second: B5 means column B, row 5. Never the other way round — 5B is invalid.
Range notation:
| You write | It means |
|---|---|
B5 |
One cell |
B5:D20 |
The rectangle from B5 to D20 (3 columns × 16 rows = 48 cells) |
B:B |
The entire column B |
5:5 |
The entire row 5 |
B:D |
Columns B through D, entirely |
B5:B5000 |
Column B, rows 5 to 5000 |
A colon : means "from … to". A comma , means "and also" — B5,D8,F12 is three separate cells (a non-contiguous range, selected by holding Ctrl).
Referencing another sheet — sheet name, then !:
=Jan!B5
If the sheet name contains a space, it needs single quotes:
='Jan Sales'!B5
Referencing another file — the filename goes in square brackets:
=[Sales2026.xlsx]Jan!B5
This works, but avoid it. Rename or move the file and the link breaks, giving you #REF! errors. It's far better to bring both datasets into one file using Power Query (covered in Track 2).
[SS-3: formula bar showing =Jan!B5 with the Jan tab highlighted below]
Excel's limits (worth knowing, because people do hit them):
- Rows: 10,48,576
- Columns: 16,384 (A to XFD)
- Sheets per workbook: limited only by memory
If your data exceeds 10 lakh rows, Excel is the wrong tool — you need Power Pivot or a database.
Naming convention: Don't put spaces in sheet names. Use
Jan_Salesinstead ofJan Sales. Formulas get shorter, you skip the quotes, and you avoid headaches later in Power Query and VBA.