How to Autofill in Microsoft Excel

Autofill in Microsoft Excel is a wonderful tool that takes much of the work out of data entry. By using the Autofill button or simply dragging your cursor, you can auto-populate cells in seconds. However, there’s more to this feature than meets the eye.

You can use Autofill for simple tasks like filling in the days of the week. But you can take this tool further and fill dates, patterns, and formatting. What’s more, you can fill cells with values that increase by a certain amount and even combine text from different rows.

Even if you’ve used Autofill before, take a look at these various ways to use the feature that you may not realize exist. That’s right, it’s time to make the most out of the Autofill feature in Excel.

Autofill a List

Starting with the simplest task you can perform with Excel Autofill, you can easily fill a list of items. This includes built-in lists or custom lists you create.

Excel offers months and days as full words or three-letter abbreviations built right in. Let’s use the list of days as an example.

1. Enter the first list item in a cell and then select the cell.

2. Drag the Fill Handle (square) in the bottom right corner of the cell in the direction you want to fill the cells. As you drag, you’ll see each list item as you pass through each cell.

3. Release when you reach the end and you’ll see your filled cells.

If you have custom lists set up in Excel, the application recognizes this as you use Autofill too. Here, we have a custom list of colors that we can enter using Autofill.

Tip: To view or create lists, go to File > Options. Select Advanced on the left and the Edit Custom Lists button on the right.

Autofill a Pattern

Another great way to use Autofill is when you have a pattern in your data. Maybe you have ranges of numbers or letters in your dataset. By selecting two or more cells, you can drag to fill the remaining cells.

For a basic example, we have letters A, C, and E. We want to fill the remaining cells with the same letters in that order.

1. Select at least two cells containing the pattern you want to replicate. Here, we select all three cells because that’s the entire pattern.

2. Drag the Fill Handle in the lower right corner of the cell in the direction you want to fill.

3. Release when you fill the number of cells you want.

Autofill Dates

When you enter dates in Excel, the application is smart enough to recognize this which allows you to use Autofill to complete a sequential series of dates. Additionally, you can fill in the dates by adjusting the month, day, or year, giving you more than one option.

Basic Fill Adjusting Dates

By default, when you use Autofill for the Short Date format (MM/DD/YYYY), you’ll see the date increase automatically.

1. Enter a date in a cell using the above format and select the cell.

2. Drag the Fill Handle in the direction you want to fill.

3. Release when you fill the number of cells you want.

If you choose the Long Date format instead (Day, Month, Date, and Year) it works the same way. You’ll see the date increase by one.

Other Date Autofill Options

Along with the above two simple fill options for dates, you have additional options to increase the weekdays, months, or years rather than the dates.

1. Enter a date in a cell using your preferred format and select the cell.
2. Hold your right mouse button as you drag the Fill Handle in the direction you want to fill.

3. When you reach the number of cells you want to fill, release and you’ll see a pop-up window.
4. Choose the type of fill you want to use from days, weekdays, months, or years.

You’ll then see your dates filled with the option you selected. Here, we picked months.

Autofill With or Without Formatting

Another super handy way to use Autofill in Excel is for formatting. You can fill cells with only the formatting from your selected cell or with the data not including the formatting. Let’s look at a couple of examples.

Fill Formatting

Here, we have a cell formatted with red, italic font and a yellow fill color. We plan to enter other data in the cells unrelated but want to copy that formatting to save some time.

1. Select the cell containing the formatting you want to fill.

2. Hold your right mouse button as you drag the Fill Handle in the direction you want to fill.
3. When you reach the number of cells you want to fill, release and you’ll see that pop-up window.
4. Choose Fill Formatting Only.

Then, when you enter text into one of the cells you filled, you’ll see it’s formatted the same as your selected cell.

Fill Without Formatting

Maybe you want to do the opposite of the above. You can fill the data but remove the formatting. Here, we’ll use a cell with the same formatting as above; however, we only want to fill the dates.

1. Select the cell containing the data you want to fill.

2. Hold your right mouse button as you drag the Fill Handle in the direction you want to fill.
3. When you reach the number of cells you want to fill, release and you’ll see that pop-up window again.
4. Choose Fill Without Formatting.

Then, you’ll see the data fill the cells but without the formatting.

Autofill Linear Amounts

If you have numbers you want to fill that increase in each cell, you can use Autofill as well. With the Linear Trend option, each amount is increased by the amount in the cells you select. This is best explained with an example.

Here, we have numbers 20 and 30. With the Linear Trend Autofill, we can fill the remaining cells with each increasing by 10 because this is in the increase between 20 and 30.

1. Select both cells containing the numbers.

2. Hold your right mouse button as you drag the Fill Handle in the direction you want to fill.
3. When you reach the number of cells you want to fill, release and you’ll see that pop-up window but with additional options available.
4. Select Linear Trend.

You’ll then see your increased numbers.

Exact Linear Amounts

If you prefer to select the exact value for the increase as well as the stopping value, you can do this using the Autofill button and a single cell instead.

1. Select the cell containing the number.
2. Go to the Home tab and open the Autofill drop-down menu in the Editing group. Choose Series.

3. In the Series dialog box, mark the options for Rows or Columns for the adjacent cells you want to fill and Linear as the Type. Note: The Trend checkbox performs the same action as the Linear Trend option above when using the Fill Handle.

4. Enter the Step value which is the number you want to increase each cell. Optionally, enter a Stop value which is the number you want to end the series by. Select OK.

You’ll then see your cells filled starting with the number you selected, adding the step value to each, and ending by the stop value.

Autofill Growth Amounts

Similar to the Linear Trend option above is the Growth Trend. The difference here is that the numbers are multiplied by a step value rather than added.

Here, we have numbers 2 and 6. With the Growth Trend Autofill, we can fill the remaining cells with all numbers increasing by multiples of 3 because this is the increase between 2 and 6.

1. Select both cells containing the numbers.

2. Hold your right mouse button as you drag the Fill Handle in the direction you want to fill.
3. When you reach the number of cells you want to fill, release and you’ll see the pop-up window.
4. Select Growth Trend.

You’ll then see your multiplied numbers.

Exact Growth Amounts

If you prefer to select the exact value for the Growth increase as well as the stopping value, you can do this using the Autofill button just like with the Linear Autofill.

1. Select the cell containing the number.
2. Go to the Home tab, open the Autofill drop-down menu, and choose Series.

3. In the box, mark the options for Rows or Columns for where you want to fill and Growth as the Type. Note: The Trend checkbox performs the same action as the Growth Trend option above when using the Fill Handle.

4. Enter the Step value which is the number you want to multiply each cell by. Optionally, enter a Stop value which is the number you want to end the series by. Select OK.

You’ll then see your cells filled starting with the number you selected, multiplying the step value, and ending by the stop value.

Autofill to Combine Text

One more time-saving task you can perform with Autofill is to combine text in different rows into a single cell. This is called Fill Justify and is a super cool feature.

1. Start by increasing the width of the column that will contain the combined text. You can simply select the column header and drag the arrow that appears on the right to increase the width. If you skip this step, the result won’t be accurate.

2. Select the cells in the rows containing the text.
3. Go to the Home tab, open the Autofill drop-down menu, and choose Justify.

You’ll then see the text from all cells combined into a single cell.

Use Autofill’s Full Potential

Autofill in Excel can be used for more than simply filling in days of the week. With these more advanced uses, you can not only save time, but reduce the risk of errors from typos or miscalculations in your worksheet at the same time.

For more, look at how to use Flash Fill in Excel too.