How to return the index of rows of a table in Excel as an array on separate cells?

Understanding Dynamic Arrays in Modern Excel

The ability to return an array of values from a single cell is a hallmark of modern Excel versions (Microsoft 365 and Excel 2021+). Previously, users had to press Ctrl+Shift+Enter to create legacy array formulas. Today, functions like SEQUENCE, RANDARRAY, and SORT are "dynamic arrays" that spill results into neighboring cells automatically. When working with structured references like Table1, this behavior allows for highly responsive dashboards where row indices update instantly when rows are added or deleted.

The SEQUENCE Function Explained

The core tool for this task is the SEQUENCE function. Its syntax is straightforward: SEQUENCE([rows], [columns], [start], [step]). For generating row indices, we primarily rely on the first two arguments.

• rows: The number of rows in the generated array. By using ROWS(Table1), we ensure this number changes dynamically as the table grows or shrinks.
• columns: The number of columns in the generated array. Since we want a vertical list of row numbers, we leave this at its default value of 1.
• start: The starting number. Defaults to 1, which is ideal for standard row indexing.
• step: The increment between numbers. Defaults to 1.

By nesting ROWS() inside SEQUENCE(), you create a self-adjusting formula that never requires manual updating of ranges.

Step-by-Step Implementation Guide

Follow these steps to implement the solution in your workbook:

1. Select the Source Table: Identify the Excel Table from which you need the row indices. Ensure it has a defined name (e.g., Table1) by clicking anywhere inside the table and checking the Table Design tab in the ribbon.
2. Choose the Output Cell: Click on the top-left cell where you want the list of indices to begin. If you have existing data below this cell, ensure there is enough empty space for the array to spill down without causing a #SPILL! error.
3. Enter the Formula: Type the following formula exactly:

   =SEQUENCE(ROWS(Table1))

   Replace Table1 with the actual name of your table.
4. Press Enter: The formula will calculate immediately. You should see a column of numbers starting from 1, extending down to match the height of your table.

Handling Older Versions of Excel

If you are not using Microsoft 365 or Excel 2021, dynamic array functionality is unavailable. In these older versions, you must use a different approach involving the ROW and INDIRECT functions, or simply drag the fill handle.

Method 1: Fill Handle (Static) Type 1 in the first cell and 2 in the second cell. Select both cells and drag the fill handle down to the last row of your table. Note that this method is static; if you delete a row in the middle, the sequence will break unless you manually correct it.

Method 2: Legacy Array Formula (Dynamic-ish) If you need a formula-based solution that updates when rows are added/deleted, use:

=ROW(INDIRECT("A1:A"&ROWS(Table1)))

Advanced Customization

You can modify the output of the SEQUENCE function to suit specific needs. For example, if you want the indices to start from 0 instead of 1, use =SEQUENCE(ROWS(Table1),,0). If you need horizontal indices across columns, add a second argument: =SEQUENCE(1, COLUMNS(Table1)).

Additionally, you can combine this with other functions to perform lookups based on these generated indices. For instance, pairing it with XLOOKUP or INDEX/MATCH allows you to extract entire rows of data based on their sequential position.

For more information on managing tables and rows programmatically, see Using VBA to Add Rows and Shift Tables. If you need to sort or filter these indexed results, refer to Excel 2021: How to "fill down" an array?.

Frequently Asked Questions

Why do I get a #SPILL! error?

This error occurs when the cells below or beside your formula are occupied by other data. Excel needs empty space to display the dynamic array. Clear the obstructing cells or move your formula to a location with sufficient blank rows/columns.

Can I use this method with filtered tables?

No, ROWS(Table1) counts all rows in the table, including hidden ones. If you need indices only for visible rows after filtering, you must use a helper column with SUBTOTAL or a VBA macro, as standard array functions ignore filter states.

Does this work in Excel Online?

Yes, provided your Excel Online account is part of a Microsoft 365 subscription that supports dynamic arrays. Older web-based versions may not support the SEQUENCE function.

How do I make the indices reset after deleting rows?

The SEQUENCE formula automatically recalculates whenever the table structure changes. Deleting a row reduces the count returned by ROWS(), so the array will shrink accordingly, maintaining a continuous sequence from 1.

Is there a way to generate unique IDs instead of simple indices?

Simple indices repeat if rows are moved. For unique persistent IDs, consider using a combination of TEXT and NOW() or a GUID generator via VBA, as formula-generated numbers are volatile and change upon recalculation.