Excel: use the colon operator for expanding ranges

Key insights

• Lock the first cell in a range with F4, then copy the formula down to expand its endpoint.
• SUM and MAX can use an expanding range to calculate a running total or the highest value so far.
• COUNTIF can count each customer's orders to date when its range expands with each row.
• XLOOKUP can search an expanding range from last to first to return a customer's previous order.
• A 3D reference between Start and End worksheets can include sheets added between them.

Excel’s colon operator can define an expanding range, so a copied formula can include each new row without manually changing its starting point. This approach is useful for spreadsheet users tracking cumulative results, customer history or summaries across worksheet tabs. Kenji Farré (Kenji Explains) [MVP] demonstrates these formula patterns using sales and order examples.

What it does

A colon between two cell references defines the continuous range between them. In a formula such as , the dollar signs keep the starting cell fixed while the ending cell changes when the formula is copied down. Each row therefore refers to a longer range, from the same first cell through the current row.

This isn’t a new Excel feature or a separate command to turn on. It combines the range operator with absolute and relative references. Excel’s F4 key can add dollar signs to a reference while you edit a formula; in this example, the first reference remains fixed and the second remains relative.

The same structure can support more than totals. The demonstration applies it to the largest value so far, counts of each customer's orders, and lookup formulas that search previous entries. The practical benefit is that the formula can adjust as it is filled down a list.

Who it is for and what you need

This technique is for people who create or maintain Excel worksheets with ordered records, such as dated sales or customer orders. It does not require a separate add-in or a named subscription in the available material. The examples use functions including SUM, MAX, COUNTIF, UNIQUE, COUNTA, XLOOKUP and IFERROR.

The source does not state which Excel versions, plans, or platforms support every function shown, and it does not give rollout or preview information. It also does not describe an admin setting. Before using a formula in a shared workbook, check that the Excel version used by its readers supports the functions in that formula.

If you work with Excel regularly, you can find more coverage in our Excel topic hub.

How it works

1. Build a running total. In the first result row, use SUM with the first amount cell as both ends of the range. In the next row, make the starting reference absolute with F4, leaving the ending reference relative. For example, with amounts in column D starting at row 3, enter and fill down. The ending row advances as the formula is copied.
2. Track the maximum to date. Use the same range pattern inside MAX, such as . The result at each row is the largest amount from the fixed starting cell through that row.
3. Count orders for each customer so far. Use COUNTIF with an expanding customer-name range and the current row's customer as the criterion. Lock the first cell of the range, but leave its endpoint relative. As the formula moves down, it counts matching names from the beginning of the list to the current record.
4. Look up a previous order. Use XLOOKUP with the current customer as the lookup value and expanding ranges for the customer names and order amounts. Set the search mode to -1 to search from last to first, and provide a not-found result such as “First Order.” This can return the customer's most recent earlier match within the range.
5. Calculate time since the previous order. Subtract the date returned by a last-to-first XLOOKUP from the current order date. Wrap the calculation in IFERROR if first-time customers have no earlier match and should show a blank instead of an error.
6. Summarize a sequence of worksheets. A reference such as refers to cell E4 across the sheets from Jan through Mar; use it within SUM or MAX. To include future sheets inserted into the sequence, place empty Start and End tabs around the monthly tabs and use in the formula.
7. Sum between chosen dates. The demonstration uses one XLOOKUP to find the range's start position and another to find its end position, joins those references with a colon, then wraps the resulting range in SUM. Changing the date inputs changes the selected range and the sum.

Limitations and open questions

Expanding ranges depend on the data being arranged so the formula’s position corresponds to the records it should include. The examples do not cover how to handle blank rows, sorted or unsorted dates, duplicate dates, or data inserted above the fixed starting cell. Test formulas against representative records before relying on them for reporting.

The worksheet method also has a specific boundary: the Start and End sheets need to remain at either end of the sheets that should be included. A sheet outside those boundary tabs will not be part of the 3D reference. The demonstration does not compare this approach with Excel Tables or structured references, so it does not establish which approach is preferable for a particular workbook.

Finally, the available source does not verify compatibility for every Excel version or explain how the formulas behave in all edge cases. In particular, confirm the exact XLOOKUP behavior and result you need with your own data before deploying a formula across a large workbook.

Sources

• Kenji Farré (Kenji Explains) [MVP] Excel formula demonstration

Frequently asked questions

How do I make an expanding range in Excel?

Use a colon to define the range, lock its starting reference with dollar signs, and leave the endpoint relative. When you copy the formula down, the endpoint moves to the current row.

How can I calculate a running total in Excel?

Use SUM with a fixed starting cell and a relative ending cell, such as =SUM($D$3:D3), then fill the formula down the column.

Can an Excel formula include new worksheets automatically?

A 3D reference between Start and End sheets can include worksheets added between those two boundary tabs. The source does not describe other worksheet insertion scenarios.

Which Excel versions support these formulas?

The available material does not specify supported Excel versions, plans, or platforms. Check that the version used in your workbook supports the functions in your formula, especially XLOOKUP and UNIQUE.