Ranges, arrays, and their dynamic versions
Both look like "a bunch of values," but they live in different places, and each comes in a fixed-size and a self-resizing form. Knowing which of the four you're holding explains why some formulas work, why others throw an error, and why some reports quietly miss new data.
The core idea
A range is a reference to cells on the worksheet, such as A2:A6. It has an address, and every cell in it can hold a value, a formula, and formatting.
An array is a set of values held in memory while Excel calculates. It can be typed directly as a constant, like {42,38,55}, or produced by an operation, like A2:A6*1.2. An array has no address. If you enter one as a formula on its own, modern Excel spills it into the cells below or beside.
A range can always be used as an array, because Excel can read its values. An array can never be used as a range, because there's nothing on the sheet to point at.
Column A holds Charpy impact results in joules. Pick a formula to see whether Excel is pointing at cells or building values in memory.
Why the difference matters
Some functions insist on a real range. The *IF and *IFS family (SUMIF, COUNTIF, AVERAGEIFS and friends) need a range for their range arguments, and so does OFFSET. That's why =SUMIF(A2:A6*2,">80") is rejected while =SUMIF(A2:A6,">40") works. When you need to test a calculated array, switch to SUMPRODUCT, FILTER, or SUM with conditions multiplied together.
| Range | Array | |
|---|---|---|
| Lives | On the worksheet grid | In memory during calculation |
| Has address | Yes, e.g. A2:A6 | No (unless spilled; then use D2#) |
| Good for | Source data, visible checks, formatting, charts | Transformations without helper columns |
| Watch out | Fixed addresses miss new rows | Hidden steps are harder to audit; #SPILL! when blocked |
Adding the time dimension: static vs dynamic
Ranges and arrays each come in two flavours. A static one has a size fixed when you write the formula. A dynamic one works out its size every time Excel recalculates, so it grows and shrinks with the data. That gives four combinations:
a reference to cells
values in memory
The word "dynamic" shows up in both columns of Excel's vocabulary, which is where most of the confusion comes from. A dynamic range answers "which cells should I look at?" A dynamic array answers "how many values did my calculation produce?" The first is about addresses; the second is about results.
How the four are linked
They aren't separate tools. In a modern workbook they form a chain, and the spill range is the hinge that connects arrays back to ranges.
tblCharpy[Joules] grows as rows are added=FILTER(…,…>40) returns however many pass=COUNTIF(C2#,">50") uses it as a real rangeThree links are worth remembering. First, any range can become an array: the moment a formula does arithmetic on it or passes it to most functions, Excel reads its values. Second, an array can only become a range by spilling: once a dynamic array lands on the sheet, its footprint is a real range with an address. Third, the # operator turns that footprint into a dynamic range: C2# always means "whatever C2 spilled, at its current size." That's how you feed a FILTER result into SUMIF or COUNTIF, which refuse raw arrays but accept C2#.
Column A is a Table column named tblCharpy[Joules]. Column C holds =SORT(FILTER(tblCharpy[Joules], tblCharpy[Joules]>40)), which spills (dashed outline). Add or remove results and compare the formulas below.
Reference or values? A quick test
Some functions hand back a reference (so the result is a range, and works anywhere a range is required), and others hand back values (an array). INDEX, OFFSET, INDIRECT, XLOOKUP pointed at a range, and TRIMRANGE return references, which is why the classic dynamic named range =OFFSET($A$2,0,0,COUNTA($A:$A)-1) works inside SUMIF. FILTER, SORT, UNIQUE, SEQUENCE, TRANSPOSE, and any arithmetic return values.
When you're unsure, ask Excel: =ISREF(INDEX(A2:A6,2)) returns TRUE, while =ISREF(FILTER(A2:A6,A2:A6>40)) returns FALSE. If ISREF says FALSE and you need a range, spill the result into a cell and reference it with #.
A2:.A5000) are a light option in Microsoft 365 when you don't want a Table. OFFSET-based names still work everywhere but are volatile, recalculating on every change, so avoid them in large workbooks. INDEX-based names (=$A$2:INDEX($A:$A,COUNTA($A:$A))) do the same job without the volatility.Drill: range or array?
Quiz
The Excel object model
Everything in Excel is an object sitting inside another object: a cell inside a sheet, a sheet inside a workbook, a workbook inside the application. VBA, Office Scripts, and Python libraries all use this same family tree.
How objects work
Each object has properties (things it is, like a cell's Value or a font's Bold) and methods (things it can do, like ClearContents or Refresh). Objects of the same kind are grouped into collections, which are named in the plural: Worksheets holds every Worksheet.
You reach an object by walking down the tree with dots: Application.Workbooks("QC.xlsx").Worksheets("Charpy").Range("B2"). In practice VBA lets you start lower, so Worksheets("Charpy").Range("B2") assumes the active workbook.
Select any object to see what it is, where you meet it in the normal Excel window, and a line of VBA that uses it.
The ones you'll use most
Almost all practical automation uses a short chain: Application → Workbook → Worksheet → Range, plus ListObject for Tables, PivotTable, Chart, and Shape. Two shortcuts are worth knowing early. Cells(row, column) returns a Range using numbers, which is handy inside loops. Sheets includes chart sheets, while Worksheets includes only grid sheets.
workbook.getWorksheet("Charpy").getRange("B2"), and Python's openpyxl uses wb["Charpy"]["B2"]. The names change slightly; the hierarchy doesn't.Quiz
Excel symbols
Excel reuses a small set of characters for very different jobs. The same # can mean a spill range, a digit placeholder, or an error. Context tells you which.
One symbol, many jobs
Symbol finder
Search by symbol or meaning, or filter by where the symbol is used.
Order of operations
When a formula mixes operators, Excel works through them in this order. Operators on the same level are evaluated left to right.
| Order | Operator | Meaning |
|---|---|---|
| 1 | : (space) , | Reference operators |
| 2 | - | Negation, as in -A1 |
| 3 | % | Percent |
| 4 | ^ | Exponent |
| 5 | * / | Multiply, divide |
| 6 | + - | Add, subtract |
| 7 | & | Join text |
| 8 | = < > <= >= <> | Comparisons |
Type what Excel would return, then check. Several of these surprise experienced users.
Quiz
Relative vs absolute references
This is the idea that makes a single formula reusable across hundreds of rows. Get it right and you write a formula once; get it wrong and every copy quietly points at the wrong cell.
What Excel actually stores
When cell C2 holds =A2*B2, Excel doesn't remember "A2." It remembers "two cells to my left, same row." Copy the formula to C3 and it becomes =A3*B3. That's a relative reference.
A $ locks the part it sits in front of. $F$1 is absolute: it always means F1, wherever you copy it. $F1 and F$1 are mixed: one part locked, one part free.
| Reference | Column | Row | When copied |
|---|---|---|---|
| A1 | moves | moves | Both shift |
| $A$1 | locked | locked | Never changes |
| $A1 | locked | moves | Stays in column A |
| A$1 | moves | locked | Stays in row 1 |
In the formula bar, put your cursor in a reference and press F4 (Mac: ⌘T or Fn F4). Each press moves to the next form.
Each order's total is Qty × Price × the markup in F1. Write the formula for C2 so it still works when filled down to C8.
Each cell in B2:F6 should equal its row header in column A times its column header in row 1. Write one formula for B2 that can be filled both right and down. Only arithmetic and cell references are supported here.
Habits worth building
Copying and moving behave differently. Copying or filling shifts relative references. Cutting and pasting, or inserting rows, keeps every formula pointing at the same cells.
Names and Tables read better than dollar signs. Name F1 Markup and the formula becomes =A2*B2*Markup, which is absolute and self-explaining. In an Excel Table, =[@Qty]*[@Price] always means "this row."
Conditional formatting uses the same rules. References are relative to the top-left cell of the range the rule applies to. A rule meant to check each row should use $A2, not $A$2, or every row checks row 2.
Quiz
Final check
Eleven questions that mix all four lessons. Score 80% or more to complete the module.