This guide will show you how you can automatically fill increment cells in Excel.
We commonly increment cells by 1, such as creating a sequence of cells from 1 to 10. Excel can also handle arbitrary increments if necessary.
For example, let’s say you want to keep track of daily progress for three months. This could be the number of steps you walk each day or something more like a calorie counter. We start with Day 1, then proceed with Day 2, and so on.
It would be a tedious process to keep doing this for the whole three months. Luckily, Excel allows us to use either the Autofill function or a custom formula to fill everything out.
If we want to track something like weight, you might want to update it weekly or every two weeks. Microsoft Excel also allows you to change the increment to any custom value you want. The AutoFill function can also handle other sequential data types, such as months and dates.
This use case is just one way we can take advantage of incremental cells in our spreadsheets. We can also make the increment a negative number. This effectively allows us to count down or decrement in value.
Now that we know when we might need to fill increment cells, let’s dive into how to use it and work on an actual sample Excel spreadsheet.
A Real Example of Automatically Filling Increment Cells in Excel
Let’s take a look at an actual example of creating increment cells in an Excel spreadsheet.
The example below uses incremented values to label the Day column. This allows you to write down your weight every five days.
To get the values in Column C, we can use the following formula:
Alternatively, we can use the built-in AutoFill function to create the same result. We just have to select two consecutive cells with two different values. The difference between these two values will determine the increment used for the AutoFill function.
You can make your own copy of the spreadsheet above using the link attached below.
If you’re ready to create your own range of incrementing cells in Excel, read the guide in the next section!
How to Automatically Fill Increment Cells in Excel
This section will guide you through each step needed to automatically fill increment cells in Excel. You’ll learn how we can use both Excel formulas and the built-in AutoFill function to accomplish this.
Follow these steps to start using the AutoFill function:
- First, select the range you want to fill in with increment cells. For this example, we’ll fill in the range A2:A25 with labels every five days.
- Since we won’t be starting with Day 0, we can manually write the first few cells in the range. Notice how cells A3, A4, and A5 are equally spaced apart already. This allows us to use the AutoFill function to fill out the rest of the range.
- To start using the AutoFill function, select two consecutive cells with a difference equal to the increment number. We’ve selected cells A4:A5 for this example.
- Next, drag down the selection until cell A25. This should fill out the entire range with cells that increment by 5.
Alternatively, we can use Excel formulas to create the incremented values. A benefit of this is that you can dynamically change the increment if needed. You may also, however, use a constant in the formula.
- To increment a cell, refer to that cell and add the increment using an Excel formula. In this example, we’re adding an increment of five to cell A5.
- Like the AutoFill function, we just need to drag down the formula to fill in the rest of the range.
You now know two easy methods to fill increment cells in Microsoft Excel automatically.
Frequently Asked Questions (FAQ)
- Can I decrement values?
You can decrement values as easily as incrementing them. For the AutoFill method, you just have to make your second cell lower in value than your first cell. For the Excel formula method, simply use the subtract operation rather than an addition operation.
- Does the AutoFill feature only work for numbers?
The AutoFill feature in Excel is flexible enough to handle many data types for incrementing. The feature can increment text values with numbers in them. It can also handle months, and days of the week, among others.
This makes the AutoFill function a great shortcut to fill in a table requiring labels for January to December or Sunday to Saturday.
That’s all you have to remember to start automatically filling increment cells in Excel. This step-by-step tutorial should make it easy for you to create a range of cells that increase or decrease by a constant value.
You can choose between using the AutoFill function or creating a formula to specify the increment yourself.
Automatically filling increment cells is just one example of an easy shortcut you can do in Excel. With so many other Excel functions out there, you can surely find one that suits your use case.
Are you interested in learning more about what Excel can do? Stay notified of new guides like this by subscribing to our newsletter!