Explanation
In this example, the goal is to create automatic row numbers starting in cell B5 that match the data entered in column C. When new data is added to the list, the row numbers should increase as required. If items are deleted, the row numbers should respond accordingly. This has traditionally been a tricky problem in Excel because there is no built-in function to create and maintain row numbers. The article below explains several options. The best option for you will depend on what Excel version you use, and on your particular requirements.
Row numbers with the Fill Handle
The examples below use formulas to automatically generate row numbers. However, if you only need to enter row numbers on a one-time basis, you can use the fill handle to automate most of the process. The trick is to enter the first two numbers to help Excel understand what you want. Then select the two cells, and double click the fill handle to send it down, or simply drag the fill handle to the desired location. The animation below shows the basic process:
The advantage of this approach is you don’t need to use any formulas at all. The disadvantage of this approach is that you will need to repeat the process if you add or remove items from the list, or if you otherwise want to generate a new set of row numbers.
Automatic row numbers with SEQUENCE
In the current version of Excel , the easiest way to create automatic row numbers is to use the SEQUENCE function . The SEQUENCE function generates a list of sequential numbers that spill directly on the worksheet. For example, to create numbers between 1-3 you can use SEQUENCE like this:
=SEQUENCE(3) // returns {1;2;3}
If you change rows to 5, SEQUENCE returns an array with 5 numbers:
=SEQUENCE(5) // returns {1;2;3;4;5}
The output from SEQUENCE is an array of values that will spill into multiple cells. The challenge in this example is how to calculate the number of rows needed for the list or table. We do this with the COUNTA function and a full column reference to column C like this:
=COUNTA(C:C) // returns 12
COUNTA returns 12, because there are 11 items in the list, plus the column header in cell C4. Because we are not assigning a row number to the header row, we need to subtract 1 to get a correct count:
=COUNTA(C:C)-1 // returns 11
Putting it all together, we have this formula in cell B5:
=SEQUENCE(COUNTA(C:C)-1)
Working from the inside out, COUNTA returns 12, from which 1 is subtracted, leaving 11:
=SEQUENCE(11)
SEQUENCE then returns an array that contains 11 numbers as a final result:
={1;2;3;4;5;6;7;8;9;10;11}
This array lands in cell B5 and spills into the range B5:B15. If the number of items in column C changes, COUNTA returns a new count, and SEQUENCE returns a new array of row numbers. Note that if there are other cells in column C that contain content that is not part of the data with row numbers, you have two choices. One option is to subtract a different number from COUNTA. For example, if there are two non-blank cells above the list in column C, you would subtract 2:
=SEQUENCE(COUNTA(C:C)-2)
Another option is to remove the full column range C:C and make the range more specific. For example:
=SEQUENCE(COUNTA(C5:C100))
Here the idea is that there will never be more than 100 items in the list, so we are only counting non-blank cells in the range C5:C100. This also means we will get a correct count from COUNTA with no need to subtract anything from the result.
Legacy Excel
In older versions of Excel that do not include the SEQUENCE function, you can use a more manual formula based on the ROW function . As the name implies, the ROW function returns the row number for a reference:
=ROW(A1) // returns 1
=ROW(E3) // returns 3
When a reference is not provided , ROW returns the row number of the cell the formula lives in. For example, if ROW is entered in cell B5, the result is 5:
=ROW() // returns 5 in B5
This means we can create sequential row numbers beginning with 1 by subtracting an appropriate offset value. For example to start numbering in cell B5, we can subtract 4 like this:
=ROW()-4 // returns 1 in B5
The catch with this formula is that you will need to manually copy it down column B to keep it in sync with the items in the list. This formula will continue to work as long as rows are not added or deleted above the first row of data. If rows are added or deleted above the data, or if you start the list in a different row, the hardcoded offset value 4 will need to be adjusted as needed.
Row numbers in an Excel Table
If you need to add automatic row numbers to an Excel Table , you can’t use the SEQUENCE function, because Excel tables do not yet support dynamic array formulas . However, you can use a special formula based on the ROW function like this:
=ROW()-ROW(Table1[#Headers])
In this formula, the required offset is calculated based on the row number of the table header. In the worksheet shown, the header is in row 4. However, if the table exists at (or is moved to) another location, the formula will continue to work correctly.
See this article for a detailed explanation.
Row numbers for a named range
The approach for creating sequential row numbers in a table can be adapted to work with a named range like this:
=ROW()-ROW(INDEX(data,1,1))+1
Here, we are working with a single named range called data . To calculate the required offset, we use INDEX to get the location of the first cell in the range, then feed that result into the ROW function:
ROW(INDEX(data,1,1))
We pass the named range data into INDEX and request the cell at row 1, column 1. In other words, we are asking INDEX for the first (upper left) cell in the range. INDEX returns that cell as an address, and the ROW function returns the row number of that cell, which is used as the offset value explained above. The advantage of this formula is that it is portable. It won’t break when the formula is moved, and it will work with any named range.
Explanation
In this example, the goal is to join two one-dimensional ranges together horizontally. This can be done with the CHOOSE function and array constant .
The CHOOSE function
The CHOOSE function is used to select arbitrary values by numeric position. CHOOSE is a flexible function and accepts a list of text values, numbers, cell references, in any combination. For example, if we have the colors “red”, “blue”, and “green”, we can use CHOOSE like this:
=CHOOSE(1,"red", "blue", "green") // returns "red"
=CHOOSE(2,"red", "blue", "green") // returns "blue"
=CHOOSE(3,"red", "blue", "green") // returns "green"
If we give CHOOSE an array constant like {1,2}, CHOOSE will return the first and second values in an array at the same time:
=CHOOSE({1,2}},"red", "blue", "green") // returns {"red","blue"}
The result is an array that contains two values and, in the dynamic array version of Excel , these values spill onto the worksheet into the range G5:H16.
Applications
Traditionally, the use of CHOOSE function to combine ranges is used up in tricky array formulas . The formulas below are good examples:
- VLOOKUP case-sensitive
- VLOOKUP multiple criteria
In these formulas, the CHOOSE function is used to create a new table (in memory) that can be used by the VLOOKUP function to workaround a difficult problem.
Note: the forthcoming HSTACK function will make this use of CHOOSE unnecessary.
