Excel Advanced
Beginner 12 min read Excel Advanced

Excel Essential Skills for Analysts

The 20% of Excel that does 80% of analyst work

Excel remains the fastest tool for quick analysis: absolute references, named ranges, tables and date/number formatting turn a sheet into a controllable calculation model.

Excelabsolute referencesnamed rangesExcel tablesformattingmodeling
Before we start

What you will learn

  • Use absolute and relative references and named ranges safely
  • Convert ranges to structured Excel Tables
  • Apply date/number/conditional formats without breaking calculations
Introduction

What is this?

Before formulas get fancy, an analyst must make cells predictable. That means mastering the difference between A1 and $A$1, naming ranges for readability, converting data to structured Tables that auto-expand, and formatting values and dates so they display truthfully without corrupting the underlying numbers.

Motivation

Why do you need it?

  • Absolute references and named ranges prevent formula breakage when you copy or filter.
  • Tables make ranges self-extending: formulas recalc over new rows automatically.
  • Clean number/date formatting is what separates a deliverable from a data dump.
  • Nearly every advanced feature (LOOKUPs, pivot tables, Power Query) builds on these basics.
Think about it

Mental model

Imagine

Labeling the aisles in a warehouse

Think of

Named ranges

You can memorize slot B-14, but labeling it 'tax_rate' makes every forklift driver (formula) correct without ceremony, and the label survives re-arrangement.

Mechanics

How it works

  • Relative references (A1) adjust when copied; absolute ($A$1) lock the cell; mixed ($A1 or A$1) lock one axis.
  • A name (Sales) replaces a range (Sheet1!$B$2:$B$1000) and travels with the workbook.
  • A Table (Ctrl+T) assigns structured names like [@Revenue] and expands formulas down new rows automatically.
  • Number formats only change display; date formats depend on the cell holding a true date serial, not text.
  • Conditional formatting applies rules (color scales, icons) that re-evaluate when data changes.

Anatomy of a reusable spreadsheet model.

Input block
Calculation block
Table
Output block
Hands-on

Examples

References and names in action

References and names in action
// Named range: BaseRate = $F$3
=D2 * BaseRate          // reliable calc, readable formula
=$B$2 * C2              // mixed: lock column B, let row move
=SUM(SalesTable[Revenue])  // structured reference over a Table

Named ranges read like English; absolute references survive copying; Table structured references expand automatically as rows are added.

Real world

In production

  • A finance analyst keeps a 'model inputs' sheet with named rates, prices and assumptions referenced everywhere.
  • An ops lead turns a weekly export into an Excel Table so the pivot and charts refresh as data lands.
  • A pricing analyst uses conditional formatting to flag margins below target across 200 SKUs in one glance.
Guidelines

Best practices

  • Put all assumptions in one named, colored input area; formulas never bury constants.
  • Convert any range you will grow to a Table before building formulas on it.
  • Store dates as real dates (Ctrl+Shift+#), numbers as numbers, text as text — one type per column.
  • Lock input cells you don't want edited and protect the sheet at delivery.
Watch out

Common mistakes

  • Copying formulas with relative references that silently reference the wrong row.
  • Formatting text dates that break sorting, month grouping and Lookups.
  • Hard-coding $F$3 everywhere, then changing the model and leaving stale references.
  • Building on a raw range, then adding rows and forgetting to extend every formula.
When things break

Debugging tips

  • Use Trace Precedents/Dependents (Ctrl+[ / Ctrl+]) to see what a formula touches.
  • F2 on a cell shows referenced cells highlighted — a fast sanity check.
  • Watch for #VALUE! vs #REF!: value comes from type mismatch, ref from a deleted range.
  • Name a range once and any formula that needed the old reference becomes an easy find.
Interviews

Interview checkpoint

Practice

Hands-on lab

Build a Reusable Revenue Model in Excel
25 min · beginner
In progress
Objectives
  • Structure inputs, calculations and output blocks
  • Use named ranges and mixed references in formulas
  • Convert the data to a Table and verify auto-expansion

Build a Reusable Revenue Model in Excel

25 min · beginner

0/5 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

Which reference stays literally fixed when copied to other cells?

2

A cell formatted as 'Jan 2026' with the general format still sorts correctly as a date.

Go deeper

Official documentation

Microsoft — relative and absolute references
docs
Microsoft — Excel table overview
docs