The Autofill button is one of the simplest, yet greatest tools in Excel. There are a variety of different ways to use the autofill feature depending on how you are using your data. Here we’ll look at the most common uses.
Increase Each Cell by One Number
Here we’re filling out the day of the month in March, starting with one. Let’s drag the autofill down and add a second column to note the department we’re in:
We’re eventually going to make a pivot table with data from every department, including engineering, R & D, and others, but for now we’re compiling data from Sales. Dragging the autofill cross hairs down column B worked to fill in “Sales”, but its giving us the number “1” for every row in column A. That’s a simply fix:
Excel can pick up a variety of different patterns when you want to use autofill to your advantage. For number sequencing, all you have to do is type in a pattern that will increase by one number in each cell, as shown above.
Next, select both B1 and B2 and drag the autofill handle down.
Now that it recognized our sequence, it will continue adding by 1.
…Ok, I know you’re not that impressed by Autofill just yet, but we’re just getting started.
Here we have almost the same example. We are adding in dates from March along with the department. Same rules, right? Nope.
Here we drag the autofill button down and by default, it increases by one day in each cell because Excel knows this is a date.
Let’s make it even easier:
By double clicking the autofill button on B1, it will autofill all the way down to where it still finds a value in Column C
This feature is really fun to use across thousands of rows when you have formulas, QA checks, or other types of data that need to be auto filled. The caveat, however, is that if you have a very large data set and there is a GAP in the column next to your autofill column, the formula will stop. Keep this in mind when using large data sets.
(cont. in part 2)