When you want to number a list in reverse order like top 50 to 1, you can use a formula that generates backward/reverse sequence numbers in Google Sheets.

In this tutorial, I am going to explain how to generate backward/reverse sequence numbers in Google Sheets by using a custom base value or a dynamic base value.

For example, if the base value is 10, I want to get the numbering (counting pattern) like 10, 9, 8, etc. Also, a few more things you can learn. Here are them.

1. Generate the sequence numbers in reverse order against a list (dynamic base value).
2. Backward/reverse sequence numbering leaving blank cells in the list (dynamic base value).
3. Infinite backward sequence numbering (dynamic base value based on total rows in the sheet).

How to Generate Backward Sequence Numbers Based on a Base Value in Google Sheets

We can use two formulas to populate backward/reverse sequence numbers in Google Sheets. One is using the Row and Sort combo and the other is using Sequence.

Assume the base value is 10 and which is in cell C1. To get sequence numbers backward from 10 to 1 we can use either of the below two formulas.

Formula 1:

=sort(row(A1:A10),1,0)

The ‘row’ part of the formula actually generates sequential numbers from 1 to 10 but the ‘sort’ reverses the output.

Here is the next formula.

Formula 2:

=sequence(10,1,C1,-1)

This second formula generates 10 numbers starting from the base value 10 in cell C1. The step value is -1. So automatically the sequential numbering is in reverse order.

See the screenshot. The base value is, as earlier said, in cell C1. But did we use that in the first formula as a reference?

Nope! We didn’t use any reference to this base value. In that, I was just showing you the normal way to reverse sequential numbers in Google Sheets.

To take the base value from a cell, here from cell C1, use Indirect in the first formula as below.

=sort(row(indirect("A1:A"&C1)),1,0)

Reverse Serial Numbering Against a List in Google Sheets

In the earlier examples, we have used a base value 10 (you can use any other number though). So the numbering backward will happen from this number to 1.

What about generating backward/reverse serial/sequence numbers without a base value or we can say by using a dynamic base value?

There are two different scenarios. If the list contains no blanks, then we can replace the earlier base value 10 (cell reference C1) with a Counta formula as follows.

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), then the formulas won’t fill numbers in the last few rows depending on the number of blank cells. Considering all these aspects here are the more acceptable formulas.

Here instead of depending on the Counta function, I am 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 formulas will be as follows.

Formula 5:

=sequence(match(1,ArrayFormula(1/(A2:A<>"")),1),1,match(1,ArrayFormula(1/(A2:A<>"")),1),-1)

We don’t need to use those multiple Array Formulas in the function above. Here is the most accepted form.

=ArrayFormula(sequence(match(1,1/(A2:A<>""),1),1,match(1,1/(A2:A<>""),1),-1))

Formula 6:

=sort(row(indirect("A1:A"&match(1,ArrayFormula(1/(A2:A<>"")),1))),1,0)

Here in this case as also we can rewrite the formula. Here the ArrayFormula is not even required because of the presence of the SORT function.

=sort(row(indirect("A1:A"&match(1,1/(A2:A<>"")),1)),1,0)

I hope you could manage to generate backward sequential numbers using the above formulas in Google Sheets. Now time to take you to the next level.

Backward Sequence Numbers Only Against Values (Leaving Blanks)

Here also we can use the Sequence or the Row functions based formulas. In order to avoid confusion, I am sticking with the Sequence function. But before that see what we are going to do.

As you can see, column B contains reverse serial numbers that only in the row that has value in column A. How to achieve that?

Here are the step by step instructions.

Please note. First, you will only get individual formulas. We will combine the formulas in each step later. So that we can use a single formula to fill down backward/reverse sequential numbers in rows containing values.

Step # 1:

Filter row numbers for the rows containing values in column A. Insert 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, i.e. against the above formula output. The formula to insert 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 value from E2:E. The formula to key in cell B2.

=ArrayFormula(ifna(vlookup(row(A2:A),D2:E,2,0)))

Step # 4 (Final Step):

Replace D2:E in the Vlookup in cell B2 with 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

Assume you have a sheet with 100 rows and you want reverse sequential numbering in all those rows and future rows (if any).

Here you must consider a base value to start the numbering in backward order and that would be 100 if the numbering is from row # 1 or 99 if the numbering is from row # 2.

It’s not practical to put the base value as above. Here is an alternative using the Rows function.

=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. I hope you have got it right.

That’s all. Enjoy!

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.