Intermediate Excel: Formulas, Cell References & Tables
This lesson bridges beginner Excel and real analyst-level work. You'll master the three types of cell references, learn to sort and filter big lists without corrupting them, convert ranges into true Excel Tables, and set up print layouts that make multi-page reports look professional. These are the habits that separate someone who 'knows Excel' from someone who can be handed a messy 500-row export and turn it into something usable in ten minutes.
Why this skill matters professionally
Almost every office job that touches spreadsheets eventually requires copying a formula down hundreds of rows, and if you don't understand absolute references, that formula silently breaks — pulling the wrong tax rate, the wrong commission percentage, the wrong lookup table — and nobody notices until a report is wrong in front of a manager. Employers list 'advanced Excel' constantly in admin, bookkeeping, analyst, and operations postings, and in practice that phrase usually means exactly these skills: locked references, clean sorting/filtering, and structured Tables that don't fall apart when new rows are added.
Learning objectives
- Write formulas using relative, absolute, and mixed references.
- Sort and filter a data range without breaking the rows.
- Convert a range to an Excel Table and use structured references.
- Set page layout and print titles so multi-page reports look professional.
Background concepts
Three kinds of references
A cell reference tells a formula where to look. Excel has three flavors, and the dollar sign ($) controls which parts move when you copy the formula.
- Relative (B2) — shifts with the formula as you copy it in any direction.
- Absolute ($B$2) — never shifts, no matter where you copy it.
- Mixed ($B2 or B$2) — locks only the column or only the row.
Why ranges misbehave
A raw range of data (just rows and columns with no formatting) has no memory of its own boundaries. When you sort it, filter it, or add a row at the bottom, Excel has to guess where the data starts and ends. Guess wrong and you get scrambled records or a missed row in a SUM.
Excel Tables solve the boundary problem
Turning a range into a Table (Ctrl+T) gives it a name, banded rows, automatic filter arrows, and — most importantly — formulas that use structured references like Table1[Sales] instead of D2:D500. Add a row and every formula, chart, and PivotTable built on that Table instantly includes it.
Print layout is part of the deliverable
A report that's correct but prints across nine unreadable pages with no headers repeated isn't finished. Page Layout view, print titles, and defined print areas are what make a spreadsheet look like a professional document instead of a raw data dump.
Step-by-step
1. 1. Build a formula with a relative reference
In a blank cell type =B2*C2 (quantity times price). Press Enter, then copy it down the column with the fill handle. Notice each row automatically references its own B and C cells.
2. 2. Lock a reference with F4
Suppose column E should multiply every row by a single tax rate sitting in H1. Type =D2*H1, then click on H1 inside the formula bar and press F4 — it becomes $H$1. Copy down; H1 never moves.
3. 3. Understand mixed references
Build a small multiplication table where row 1 holds numbers 1-5 across, and column A holds numbers 1-5 down. In B2 type =$A2*B$1 — the column of A is locked, the row of 1 is locked — then copy it across and down. Every cell fills correctly with one formula.
4. 4. Sort a list safely
Click any single cell inside your data (not a whole column), then Data → Sort. Excel auto-detects the full contiguous block. Choose the column and order, and check 'My data has headers' so the header row isn't sorted into the data.
5. 5. Filter instead of deleting
Data → Filter adds dropdown arrows to every header. Uncheck values you don't want to see. Rows aren't deleted — they're hidden, and row numbers turn blue to remind you a filter is active. Data → Clear removes the filter.
6. 6. Convert a range to a Table
Select your data and press Ctrl+T. Confirm the range and header checkbox. Rename it under Table Design → Table Name (e.g., 'SalesTable') instead of leaving the default 'Table1'.
7. 7. Use structured references
Inside a Table, formulas can reference columns by name: =SalesTable[@Price]*SalesTable[@Qty] refers to the values in the same row automatically — no dollar signs needed, and it auto-fills to every row you add.
8. 8. Set a print area and titles
Page Layout → Print Area → Set Print Area limits printing to the block you select. Page Layout → Print Titles → Rows to repeat at top lets you pick the header row so it appears on every printed page.
9. 9. Preview before printing
File → Print shows the exact page breaks. Adjust scaling under 'Fit Sheet on One Page' or 'Fit All Columns on One Page' so wide reports don't spill an extra thin column onto page 2.
Real-world workplace examples
Commission calculator
A sales rep sheet uses =Revenue*$B$1 where B1 holds a single commission rate; the rate can be updated once and every rep's commission recalculates instantly.
Loan amortization
Interest calculations lock the annual rate cell with $ signs so 60 rows of monthly payments all reference the same rate without drifting to blank cells below it.
Shared inventory table
A warehouse team converts their reorder list to an Excel Table; when a new SKU is typed in the row below the last one, the reorder-point formula and conditional formatting extend automatically.
Filtered expense review
An accounts payable clerk filters a 2,000-row expense list to show only 'Pending' status and one vendor, reviews those thirty rows, then clears the filter — the other 1,970 rows were never touched.
Board-ready printout
A finance assistant sets print titles so the account name column repeats on every page of a 4-page budget variance report, so nobody has to guess which row they're reading on page 3.
Practical scenarios
The broken tax formula
An HR assistant builds a payroll worksheet with =Gross*B1 for tax withholding, where B1 holds the tax rate, then copies it down 40 rows. Because B1 isn't locked, row 10 actually multiplies by B10 (empty), giving $0 withholding. The fix: rebuild with =Gross*$B$1, verify against row 1, then re-copy. The lesson: always spot-check a formula's result on at least two different rows before trusting a fill-down.
Sorting disaster averted
A junior analyst selects only the 'Amount' column and sorts it, planning to also sort the neighboring 'Customer' column separately. Excel's 'Extend the selection?' dialog appears — if she'd clicked 'Continue with the current selection', amounts and customer names would permanently mismatch. She correctly chooses 'Expand the selection', keeping every row intact.
Table saves an audit
A monthly Table of vendor payments grows from 300 to 340 rows over the quarter. Because it's a real Table (not a static range), the SUMIFS formulas on the Dashboard tab and the associated PivotTable both silently pick up the new 40 rows with zero edits — a Table refresh, not a formula rewrite, was all that was needed before the audit deadline.
Common mistakes beginners make
Forgetting to lock a lookup range
Copying a VLOOKUP down a column without locking the table array makes the range creep down and eventually return errors. Fix: always wrap the lookup range in $ signs, e.g. $F$2:$G$50.
Sorting one column in isolation
Highlighting a single column before sorting scrambles that column relative to the rest of the row. Fix: click one cell inside the data, not a column header, before sorting.
Deleting filtered rows instead of hiding them
New users sometimes select visible filtered rows and delete them, not realizing hidden rows underneath also get deleted in older versions or macros. Fix: use Filter to view, not to permanently remove; delete deliberately from the full unfiltered list.
Renaming Table columns without checking formulas
Renaming a Table header breaks structured references elsewhere that spelled out the old name literally instead of using [@ColumnName] syntax. Fix: rename headers before building formulas, or use Find & Replace on formula text carefully.
Printing without setting titles
A report with 60 rows prints with headers only on page 1; page 2 becomes an unlabeled wall of numbers. Fix: always set Print Titles → rows to repeat at top for any report over one page.
Best practices
- Press F4 immediately after typing a reference you know should be locked — don't retype dollar signs manually.
- Convert any list you'll keep adding rows to into a real Excel Table from day one.
- Name your Tables meaningfully (SalesTable, not Table3) so formulas stay readable.
- Always include header rows and check 'My data has headers' when sorting.
- Use Filter to review, not to permanently trim data — clear it before saving if the file will be shared.
- Preview print layout before every report handoff; nobody should have to guess which column a number belongs to.
- Freeze panes (View → Freeze Panes) on large lists so headers stay visible while you scroll.
- Test a copied formula on at least the first and last row of the range before trusting the fill.
Professional tips
- F4 cycles through all four reference states each time you press it — relative, absolute, row-locked, column-locked.
- Ctrl+T instantly formats a range as a Table and turns on filters in one keystroke.
- Structured references in Tables ([@ColumnName]) are self-documenting — they read like plain English months later.
- Use Table slicers (Table Design → Insert Slicer) for a clickable filter UI non-technical coworkers can use.
- Set 'Scale to Fit' to 1 page wide, however many pages tall, for reports that are wide but not long.
- Ctrl+Shift+L toggles AutoFilter on and off instantly from the keyboard.
Practice exercises
Build a locked-rate calculator
Create a list of 10 products with quantity and price, add a single tax rate cell, and write one formula with a mixed reference that correctly fills down for all 10 rows.
Sort and filter a mock dataset
Paste in 50 rows of fake sales data (name, region, amount). Sort by amount descending, then filter to show only one region, and confirm row counts match what you'd expect.
Convert to a Table and add rows
Turn your 50-row dataset into a named Table, add 5 new rows at the bottom, and verify any SUM or AVERAGE formulas outside the Table automatically include the new rows.
Print a clean 3-page report
Take a dataset with more than 60 rows, set print titles so headers repeat, set a print area, and preview to confirm no columns are cut off.
Review questions
What's the difference between $B$2, $B2, and B$2?
$B$2 is fully absolute (never moves); $B2 locks only the column B; B$2 locks only row 2. Which parts move when copied depends on which dollar signs are present.
Why should you click one cell instead of selecting a column before sorting?
Selecting one cell lets Excel auto-detect and sort the entire contiguous range together, keeping rows intact; selecting only one column risks sorting it independently and scrambling the data.
What does converting a range to a Table change functionally?
It gives the range a name, automatic filters, banded formatting, and — critically — auto-expanding formulas and structured references that update automatically when rows are added.
When would you use a mixed reference instead of fully absolute?
When building a grid or matrix formula (like a multiplication table) where you want the row to stay fixed going across but the column to stay fixed going down, or vice versa.
How do you keep header rows visible on every printed page?
Page Layout → Print Titles → set 'Rows to repeat at top' to the header row.
Key takeaways
- F4 cycles reference types — memorize it, it saves constant retyping.
- Absolute references ($B$2) are essential any time a formula points at one shared constant.
- Always sort by clicking inside the data, never by selecting a single column.
- Filtering hides rows; it does not delete them — clear filters before final delivery if needed.
- Excel Tables auto-expand formulas, charts, and PivotTables when new rows are added.
- Structured references (Table[@Column]) are easier to read and audit than raw cell ranges.
- Set print titles and print areas before sharing any multi-page report.
- Spot-check copied formulas on multiple rows, not just the first one.
Frequently asked questions
Does Ctrl+T change my data or delete anything?
No — it only formats the range and adds Table functionality; the values are untouched.
Can I convert a Table back to a normal range?
Yes, Table Design → Convert to Range removes Table features but keeps the formatting and data.
Why did my VLOOKUP break when I copied it down?
The lookup table range probably wasn't locked with $ signs, so it shifted along with the formula.
Is sorting reversible?
Ctrl+Z undoes a sort immediately after doing it, but once you save and close, the original order may be lost unless you had a row-number column to restore it.
What's the shortcut to toggle filters on and off?
Ctrl+Shift+L.
Do Tables work with PivotTables?
Yes — building a PivotTable from a Table means new rows automatically flow into the Pivot's source data after a refresh.
How many decimal places should I lock in print layout?
Print layout doesn't control decimals; that's cell number formatting — set it separately under Format Cells.
Related lessons
12 min • Beginner
15 min • Easy
20 min • Intermediate
20 min • Advanced