Have you ever needed to number a list in reverse order, such as from 50 to 1? Well, you’re in luck! In this article, I’m going to show you how to generate backward/reverse sequence numbers in Google Sheets using custom or dynamic base values. Get ready to level up your spreadsheet skills!
How to Generate Backward Sequence Numbers Based on a Base Value in Google Sheets
There are two formulas you can use to populate backward/reverse sequence numbers in Google Sheets. The first formula uses the Row and Sort combo, while the second formula utilizes the Sequence function.
Let’s say our base value is 10, which is located in cell C1. To get sequence numbers in reverse order from 10 to 1, you can use either of the following formulas:
Formula 1:
=sort(row(A1:A10),1,0)The row part of the formula generates sequential numbers from 1 to 10, but the sort function reverses the output.
Here’s the second formula:
Formula 2:
=sequence(10,1,C1,-1)This formula generates 10 numbers starting from the base value 10 in cell C1. The step value is -1, so the sequential numbering is automatically in reverse order.
As you can see, we didn’t use any reference to the base value in the first formula. That was just to show you the normal way to reverse sequential numbers in Google Sheets. If you want to take the base value from a cell, like C1, you can use the indirect function in the first formula like this:
=sort(row(indirect("A1:A"&C1)),1,0)Reverse Serial Numbering Against a List in Google Sheets
In the previous examples, we used a base value of 10 (which can be any number). The numbering backward happened from this number to 1. But what if you want to generate backward/reverse serial/sequence numbers without a base value, or in other words, using a dynamic base value?
There are different scenarios based on whether the list contains blanks or not. If the list has no blanks, you can replace the earlier base value 10 (cell reference C1) with the Counta formula in the following formulas:
Formula 3:
=sequence(counta(A2:A),1,counta(A2:A),-1)Formula 4:
=sort(row(indirect("A1:A"&counta(A2:A))),1,0)If there are blanks in the list (A2:A14), the formulas won’t fill numbers in the last few rows depending on the number of blank cells. To handle this situation, you can use the following formulas:
“
Here, instead of depending on the Counta function, I’m using a different formula to find the last row number that has values in the list:
=match(1,ArrayFormula(1/(A2:A<>"")),1)Replace Counta with this formula in the earlier two formulas to generate backward sequential numbers in Google Sheets. The updated formulas will be:
Formula 5:
=sequence(match(1,ArrayFormula(1/(A2:A<>"")),1),1,match(1,ArrayFormula(1/(A2:A<>"")),1),-1)Formula 6:
=sort(row(indirect("A1:A"&match(1,ArrayFormula(1/(A2:A<>"")),1))),1,0)I hope you were able to generate backward sequential numbers using the above formulas in Google Sheets. Now let’s take it to the next level!
Backward Sequence Numbers Only Against Values (Leaving Blanks)
Here, too, you can use either the Sequence or Row functions based formulas. To avoid confusion, I’ll stick with the Sequence function. But first, let’s see what we’re going to do.
Column B contains reverse serial numbers that only appear in rows that have a value in column A. So how do we achieve that?
Let’s go through the step-by-step instructions:
Step # 1:
Filter row numbers for the rows containing values in column A by inserting this Filter formula in cell D2:
=filter(row(A2:A),A2:A<>"")Step # 2:
Fill down reverse sequential numbers against the values in D2:D (the output of the previous formula) by using this formula in cell E2:
=sequence(counta(A2:A),1,counta(A2:A),-1)Step # 3:
Vlookup the row numbers A2:A in D2:E. That means Vlookup the row numbers A2:A in the first column in D2:D and return the value from E2:E. Enter the following formula in cell B2:
=ArrayFormula(ifna(vlookup(row(A2:A),D2:E,2,0)))Step # 4 (Final Step):
Replace D2:E in the Vlookup formula in cell B2 with the corresponding formulas, i.e., {filter(row(A2:A),A2:A<>""),sequence(counta(A2:A),1,counta(A2:A),-1)}.
Here is the final formula to generate backward sequential numbers, leaving blank cells in Google Sheets:
=ArrayFormula(ifna(vlookup(row(A2:A),{filter(row(A2:A),A2:A<>""),sequence(counta(A2:A),1,counta(A2:A),-1)},2,0)))Infinite Reverse Row/Sequence Numbering in Google Sheets
Let’s say you have a sheet with 100 rows, and you want reverse sequential numbering in all those rows and any future rows. In this case, you need to consider a base value to start the numbering in backward order. For example, if the numbering starts from row # 1, the base value would be 100. If the numbering starts from row # 2, the base value would be 99.
However, it’s not practical to manually input the base value in each formula. Instead, you can use the Rows function as an alternative:
=sequence(rows(A1:A),1,rows(A1:A),-1)Change rows(A1:A) to rows(A2:A) if the formula is in row #2, rows(A3:A) if the formula is in row #3, and so on.
That’s it! Now you know how to generate backward sequence numbers in Google Sheets. Harness the power of these formulas and level up your spreadsheet game. Happy numbering!
Check out Crawlan for more cool tips and tricks!
Resources
- Skip Blank Rows in Sequential Numbering in Google Sheets.
- Insert Sequential Numbers Skipping Hidden | Filtered Rows in Google Sheets.
- Number Rows Like 1, 1.1, 1.2, 1.3 in Google Sheets.
- How to Generate Odd or Even Random Numbers in Google Sheets.
- How to Filter Decimal Numbers in Google Sheets.
- Group Wise Serial Numbering in Google Sheets.
- How to Randomly Select N Numbers from a Column in Google Sheets.
- How to Copy Only Numbers from Multiple Columns in Google Sheets.
- Assign the Same Sequential Numbers to Duplicates in a List in Google Sheets.
- How to Use Roman Numbers in Google Sheets.
- How To Increment Numbers in Grouping in Query in Google Sheets.
- Convert Numbers to Month Name in Google Sheets.
