How to fill series in backward, descending or decreasing order in Excel?

Normally, the sequence series we create in Excel are in ascending order, increasing as we move down a column or across a row. However, there are many practical cases where you might need to fill a sequence in a backward, descending, or decreasing order. For example, you may want to create a countdown, allocate tasks in reverse order, or format data for reporting purposes. In this article, we will demonstrate several effective methods for filling a series in backward or descending order in Excel. These methods cover manual, formula-based, and automated approaches, allowing you to choose the most suitable way according to your specific needs.

Fill series in backward or descending order with fill handle feature
Fill series in backward or descending order with Kutools for Excel
Fill series in backward or descending order using Excel formula
Fill series in backward or descending order using VBA code

Fill series in backward or descending order with fill handle feature

To quickly generate a numeric series in backward or descending order, such as 100, 99, 98, ..., you can make use of Excel’s built-in Fill Handle feature. This approach is suitable when you need to create a consecutive series that decreases by a set step and want a quick solution without complex settings.

1. Enter the first value of your descending series into the desired cell—for example, type 100 into cell A1.

2. Select cell A1. Hover your mouse pointer over the bottom-right corner of the cell so the cursor changes to a small black cross. Hold down the right mouse button and drag the Fill Handle down the column as far as you need. Once you reach your target cell, release the mouse button. In the shortcut menu that appears, select Series to specify the fill options. See below:

3. In the Series dialog box that appears, enter a negative value in the Step value field—such as -1 to decrease by 1—and make sure to check the options for filling by column or row based on your selection. Click the OK button to generate the series. Below is an example:

After confirming, Excel will automatically fill the range you selected with the numbers in descending order based on your specified decrement.

Fill series in backward or descending order with Kutools for Excel

For users who frequently work with custom sequences or need to combine numbers with text, Kutools for Excel provides an easy-to-use Insert Sequence Number utility. This feature allows you to create and fill a descending series with advanced options, such as fixed lengths, prefixes, suffixes, and customized step values. It offers significant convenience and flexibility, especially for batch numbering tasks.

1. Navigate to Kutools > Insert > Insert Sequence Number. 

2. In the Insert Sequence Number dialog box, click New to set up a new series. Configure the following fields as needed:

3. Once your sequence is configured and added, select the range you want to populate. In the Insert Sequence Number dialog box, click your created series, then tap the Fill Range button. The selected cells will instantly be filled with your descending series, maintaining any text formatting or digit length.

4. After filling the series, close the dialog box.

Fill series in backward or descending order using Excel formula

Sometimes, you may want to fill a descending series dynamically using formulas—especially when working with large or variable-size ranges, or when you wish to avoid manual operations with the fill handle. With a proper formula, you can automatically generate a backward series that adjusts when new rows are added or removed, or even reference starting values in other cells for more flexibility.

1. Select the top cell of your intended series (for example, A1). If you want the series to start at 100, enter the following formula in cell A1:

=100-ROW(A1)+1

This formula works as follows: "100" is the starting number. "ROW(A1)" returns the row number of the current cell (1 in this case). As you copy this formula down the column, the row number increases by1 each row, thus decreasing the result by 1 each time and creating a descending series.

2. After entering the formula in A1, press Enter to confirm. To continue the series, select cell A1, drag the fill handle down to copy the formula to additional rows. Adjust the starting number (100 in the example) to fit your desired starting value.

Fill series in backward or descending order using VBA code

For more advanced scenarios, such as filling series with custom step sizes, or more complex patterns (e.g., including a prefix/suffix, alphanumeric combinations, or filling a large variable range), you can use a VBA macro to automate the process. This method is recommended for experienced users or when you have repetitive tasks or require maximum customization.

1. Open the VBA editor by clicking Developer Tools on the ribbon, then select Visual Basic. In the VBA window, click Insert > Module to create a new module, and paste the following code into the code window:

Sub FillDescendingSeries()
    Dim rng As Range
    Dim StartNum As Long
    Dim StepValue As Long
    Dim cell As Range
    Dim xTitleId As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.Selection
    Set rng = Application.InputBox("Select the range to fill", xTitleId, rng.Address, Type:=8)
    
    StartNum = Application.InputBox("Enter the starting number", xTitleId, "100", Type:=1)
    StepValue = Application.InputBox("Enter the decrement per row (e.g.,1)", xTitleId, "1", Type:=1)
    
    For Each cell In rng
        cell.Value = StartNum
        StartNum = StartNum - StepValue
    Next
End Sub

2. To run this macro, click the “Run” button. You will first be asked to select the range you want to fill (e.g., A1:A20), then to enter the starting number (such as 100), followed by the decrement step (such as 1 for each next value to be1 less than the previous). The macro will then automatically fill your chosen range with the descending sequence.

Related articles:

• How to create increment number with or inside text in Excel?
• How to fill column with series repeating pattern numbers in Excel?
• How to fill series of numbers in a filtered list column in Excel?