How to Increment Numbers in Excel
To increment numbers in Excel, type the first two values of your pattern (such as 1 and 2) into adjacent cells, select both, then drag the fill handle down or across to continue the series. For more control you can use Home > Fill > Series, a ROW()-based formula, or the SEQUENCE function in Excel 365.
If you work with data in Excel, there may come a time when you need to increment numbers – adding a fixed value to a series of cells. Thankfully, Excel has several built-in features that make incrementing numbers simple and fast – whether you need to increase the value of each cell by a set amount or drag the fill handle to continue a series. In this blog post, we’ll show you how to increment numbers in Excel quickly and easily.
Why Increment Numbers in Excel?
If you work with Excel regularly, there is a high chance at some point, you will need to increase or decrease a series of cells that contain numerical data. These numbers could be changing inventory, a sales report, data analysis, or any other situation where you need to update a series of numbers.
Using Excel’s Fill Handle
Excel’s fill handle is an easy way to increase or decrease numbers sequentially. Start by typing your first number in the cell where you want your series to start. Select and hover your mouse over the bottom right corner of the cell until the cursor changes to a plus sign.
Click and drag the fill handle in the direction you want to fill. If you’re incrementing by one, drag the fill handle down for consecutive cells. Or, drag it to the right if you are numbering across columns.
Excel will automatically fill any missing numbers and adjust the values you want to increment by based on your pattern. If you want to overwrite data, select ‘Fill Without Formatting’ when dragging the fill handle.
To increment by an amount other than one, use a two-value pattern: type your first two numbers in adjacent cells (for example, 5 in the first cell and 10 in the second), select both cells, then drag the fill handle. Excel reads the step between the two values and continues the series (15, 20, 25, and so on). This works for descending series too – enter 100 then 90 and Excel will count down. For more on this tool, see our guides to the fill handle in Excel and how to use the fill handle, or learn to autofill numbers in Excel more generally.
How to Use the Fill Handle with Keyboard Shortcuts
Keyboard shortcuts are a great way to increase efficiency and productivity. For incrementing numbers using the fill handle, there is a useful trick:
1. Type the first number in the series into the cell.
2. Use the Ctrl key and drag the fill handle in the desired direction. For example, drag down for a vertical series, or to the right for a horizontal series.
3. Release the mouse and Ctrl key. Excel will adjust the following numbers based on the initial value entered.
Using Excel’s Fill Command
If you don’t want to use the fill handle, the fill command is another way to increment numbers in Excel. Start by selecting the cells you want to fill, including the initial value you want to start with.
Go to the ‘Home’ tab, click on ‘Fill’ (in the Editing group, on the right of the ribbon) and choose ‘Series’. Choose the type of progression you want – linear, growth, or date – and then choose the step value and stop value. On a Mac, the same command lives under Home > Fill > Series as well. The Fill Series dialog is the most precise way to increment, because you set the exact step value (the amount added each time) and a stop value where the series ends. For related techniques, see how to autofill in Excel and how to fill cells in Excel.
Final Thoughts
Incrementing numbers in Excel shouldn’t be complicated or time-consuming. By utilizing the fill handle or fill command, you can quickly and easily make changes to cells without having to manually type each number. Give these methods a try and see how much more efficient your workflow becomes.
Using Formulas to Increment Numbers
In addition to using the fill handle and fill command, you can also use formulas to increment numbers in Excel. The two most common functions used are the ‘=ROW()’ function and the ‘=INDEX()’ function.
The ‘=ROW()’ function returns the row number of the cell it sits in, and when dragged down, Excel automatically increments the row number because the cell reference moves. For example, if you enter ‘=ROW()’ in cell A1 and drag it down to A3, the formula stays the same in each cell but each one returns a different value:
- Cell A1: =ROW() returns 1
- Cell A2: =ROW() returns 2
- Cell A3: =ROW() returns 3
If your list does not start in row 1, subtract an offset so it begins at 1. For example, if your first number is in A5, enter ‘=ROW()-4’ and drag down. The ROW() approach is self-correcting: if you insert or delete rows in the middle, the numbering renumbers itself automatically. For a deeper look at the function, see our guides to the ROW function in Excel and how to number rows in Excel.
The ‘=INDEX()’ function can also be used to create a sequential list of numbers. Start by entering the initial value you want to start with. For example, in A1, enter ‘1’. In cell A2, enter the following formula:
=INDEX( A:A , ROW()-1 )+1
Drag the formula down, and Excel will automatically increment the numbers.
Using the SEQUENCE Function (Excel 365)
If you have Microsoft 365 or Excel 2021, the SEQUENCE function is the fastest way to generate an incrementing list in a single step. It spills the whole series automatically, so there is nothing to drag. The syntax is ‘=SEQUENCE(rows, [columns], [start], [step])’.
- ‘=SEQUENCE(10)’ returns the numbers 1 through 10 down a column.
- ‘=SEQUENCE(10, 1, 5, 5)’ starts at 5 and increments by 5 (5, 10, 15, …).
- ‘=SEQUENCE(1, 10)’ spills 1 through 10 across a row.
Because the result is dynamic, editing the formula updates every number at once. SEQUENCE works identically on Windows and Mac as long as you are running Excel 365; it is not available in Excel 2019 or earlier, where the fill handle, Fill Series, or a ROW() formula are the right tools instead. SEQUENCE pairs well with other dynamic-array functions when you need to generate random numbers in Excel or build numbered lists on the fly.
Incrementing Numbers with Custom Lists
If you have a specific sequence of numbers you need to increment in Excel, you can create a custom list. For example, suppose you need to create a sequence of numbers for an order form, and you want to use a specific sequence of numbers, such as ‘Order #001, Order #002, Order #003,’ and so on.
To do this, go to ‘File’ > ‘Options’ > ‘Advanced’ > ‘Edit Custom Lists’. In the ‘Custom Lists’ dialog box, enter the series of values you want to use, and then click ‘Add.’ Click ‘OK’ to close the dialog box.
Now, when you start typing the first number in the sequence, Excel will automatically recognize the custom list and increment the numbers accordingly. For a full walkthrough, see how to create a custom autofill list in Excel.
As you can see, there are several ways to increment numbers in Excel, and depending on your situation, some methods may be more suitable than others. Whether you prefer using the fill handle, fill command, formulas, or custom lists, Excel has you covered.
By taking advantage of these features, you can save time and minimize the risk of errors that come with manually typing out each number. With a little bit of practice, you’ll be able to increment numbers in Excel quickly, efficiently, and with ease.
FAQ
Here are some common questions related to incrementing numbers in Excel:
Q: Is it possible to increment numbers by a specific value?
A: Yes. You can use the fill handle, fill command, or formulas to increase or decrease values by a specific amount. For example, if you want to increment numbers by 5, select the initial value and use the fill handle to drag down, pressing the Ctrl key while dragging. Alternatively, you can use the fill command and enter the step value you want.
Q: What should I do if I need to increment non-numerical values?
A: If you need to increment non-numerical values, such as days of the week or months of the year, you can use a custom list. To create a custom list, go to ‘File’ > ‘Options’ > ‘Advanced’ > ‘Edit Custom Lists’ and add the list of values you want to use. Excel will recognize the list and increment the values accordingly.
Q: Can I increment numbers between sheets in Excel?
A: Yes. You can use a formula to reference a cell in another sheet and increment the value as needed. For example, you can use the formula ‘Sheet2!A1+1’ to increment the value in cell A1 on Sheet 2 by one.
Q: What is the fastest way to increment numbers in Excel?
A: The fastest way to increment numbers in Excel is to use keyboard shortcuts. For example, select the initial value and use the fill handle while holding the Ctrl key. Alternatively, you can use the fill command and enter the series values you want.
Q: Can I undo an increment in Excel?
A: Yes. If you accidentally increment the wrong values, you can use the ‘Undo’ feature to reverse the changes. Alternatively, you can use the ‘Fill -> Series’ command again and reverse the order or delete the values.
Frequently Asked Questions
Why does the fill handle copy the same number instead of incrementing?
If you drag the fill handle from a single cell, Excel copies that value instead of counting up. To increment, either enter two values first (such as 1 and 2), select both, and then drag – or drag from one cell while holding the Ctrl key (Option on a Mac), which tells Excel to increment by one instead of copying.
How do I increment by a specific step like 5 or 10?
Type the first two numbers of your pattern in adjacent cells so the gap equals your step (for example, 5 and 10 for a step of 5), select both, and drag the fill handle. For exact control, use Home > Fill > Series and type the step value directly into the dialog box. In Excel 365 you can also use ‘=SEQUENCE(rows, 1, start, step)’.
What is the difference between the fill handle and Fill Series?
The fill handle is the fastest drag-to-fill method and is ideal for quick, visible ranges. Fill Series (Home > Fill > Series) is a dialog box where you type an exact step value, stop value, and progression type (linear, growth, or date) – better when you want a precise series or do not want to drag across many cells.
How do I auto-increment numbers that update when rows are added or deleted?
Use a formula instead of static numbers. ‘=ROW()’ (or ‘=ROW()-offset’ if your list does not start in row 1) renumbers itself automatically when you insert or delete rows. See how to automatically number rows in Excel for a step-by-step guide.
Is SEQUENCE available on Mac and older Excel versions?
SEQUENCE works the same on Windows and Mac, but only in Excel for Microsoft 365 and Excel 2021. In Excel 2019 or earlier, it is not available – use the fill handle, Fill Series, or a ROW() formula to increment numbers instead.
Can I increment dates the same way as numbers?
Yes. The same fill handle and Fill Series methods work for dates, and Excel can increment by day, weekday, month, or year. See how to autofill dates in Excel and how to automatically fill dates in Excel for the details.
