Autofill in Excel

Autofill is an interesting and valuable feature of Excel. Use the autofill feature to fill the Excel cells with data that follow a particular pattern stored in other cells. It automatically fills a series of cell.

For example, you want to create a monthly report in Excel where one column contains dates. You can either manually enter the date of each day one by one or use the autofill feature to fill the date in cells that follow a pattern. This feature is a very helpful feature for it.

Using autofill feature, you can auto-populate the data in a specific pattern horizontally and vertically.

What is autofill?

Autofill is a feature/function of Excel that recognize the series and fill the data in the corresponding row or column accordingly. When you drag the data in a direction (down, left, or right), it identifies the pattern of cell data and fills in other cells automatically.

To use this feature, we initially need data in one or two cells to recognize the pattern. Based on which the data will fill in other cells. It can generate all types of values (Number, text, date, etc.).

Excel fills the data in cells either by repeating the same values of the first cell or insert a sequence by recognizing the pattern of first cell and second cell.

Autofill Examples

We will show you the different examples for autofill feature to automatically generate and fill the cells that follow a particular pattern.

Example 1: Autofill data horizontally

Take an example in which we will try to automatically fill the data in cells horizontally by recognizing the data pattern.

Step 1: We have initially taken the month names as an abbreviation in A1 and B1 cells.

Autofill in Excel

Step 2: Now, select both the cell and hover the mouse at the bottom right corner of the selected cell. A small bold + sign will appear. (This is called Excel Fill Handler.)

Autofill in Excel

Step 3: Drag the + sign down in the Excel sheet. It will recognize the pattern as month and automatically fill the remaining month name in other cells.

Autofill in Excel

See that the data is generated and placed in cells horizontally. Here, it has inserted a sequence by recognizing the pattern of first cell and second cell.

Step 4: At the end of the generated values, an Autofill Options box will display.

Autofill in Excel

Step 5: Click this Autofill Options box that will show many autofill options.

Autofill in Excel

Example 2: Autofill data vertically

Take an example in which we will try to automatically fill the data in cells vertically by recognizing the data pattern. You have to select at least cells to recognize the pattern.

Step 1: We have initially taken the month names in A1 and B1 cells.

Autofill in Excel

Step 2: Now, select more than one cell and hover the mouse at the bottom right corner of the selected cell. A small bold + sign will appear. (This is called Excel Fill Handler.)

Autofill in Excel

Step 3: Drag down the + sign vertically in the Excel sheet. It will recognize the pattern as month and automatically fill the remaining month name in other cells.

Autofill in Excel

See that the data is generated and placed in cells vertically. Here, it has inserted a sequence by recognizing the pattern of first cell and second cell.

Step 4: If you drag this to more than the month in a year, it will repeat the value from the first one.

Autofill in Excel

Example 3: Autofill the data in multiple rows/columns

Till now, we have used the autofill feature to generate the data either in a single row or single column. Excel allows to use the autofill feature to fill the data in multiple rows and columns at once.

You do not need to do anything complex. Steps are almost the same as the above two examples. This example will help you to autofill the cells for a range of cells.

Following are the steps for this:

Step 1: We have an Excel sheet in which we have initially taken the date in column A and day in B column cells.

Autofill in Excel

Step 2: Now, select both columns data and hover the mouse at the bottom right corner of the selected cell. A small bold + sign will appear.

Autofill in Excel

Step 3: Drag down the + sign below in the Excel sheet. It will recognize the pattern of column A as date and column B as day, then automatically fill the remaining month name in other cells.

Autofill in Excel

See that the autofill feature has generated the data for both columns and placed it in the below cells.

Example 4: Autofill repeated value

If you want the same value to be filled in other cells using the Excel autofill feature, you have to select only that cell whose value you want to repeat. This is like copying the same data in contiguous cells.

Step 1: For this example, we have taken this data which we want to fill in contiguous cells.

Autofill in Excel

Step 2: Select it and hover the mouse bottom right corner of the selected cell, then drag the + sign (autofill) below.

Autofill in Excel

Step 3: See that - this time, it has generated and filled the same values in the below cells.

Autofill in Excel

If you need the same values in continuous cells, this method is better rather than using the copy and paste option of Excel. This will save and effort of yours.

Example 5: Autofill the repeated date/days

Excel allows to autofill the same date in other cells using the Excel autofill feature. If you need the same date or day in other cells, you can use autofill, but the method we have used above is not useful.

If you try to follow the same steps as example 4 with day or date, this will not work. The above method is not complete enough; you have to follow some more steps.

Step 1: We have entered the current date using Ctrl+; key in A1 cell.

Autofill in Excel

Now, repeat the same steps as example 4 (from step 2 to step 3).

Step 2: You will get the auto-filled values in a specific pattern (increasing order) like this -

Autofill in Excel

Step 3: Generated values are still selected. Press the Ctrl+D command to change the generated dates to repeated dates (current date) on selection.

Autofill in Excel

See that all auto-filled cells are containing the repeated date now.

Autofill Options

Use Autofill Options to fill the specific data in Excel cells generated automatically using the Excel autofill feature. For example, weekdays, days, months, years, series, etc. These options are visible when you use the autofill feature; an Autofill Options box displays at the end of the auto-generated values.

Let us take an example to see that how these autofill options work to autofill the weekdays dates. Weekdays refers to the working days from Monday to Friday. When you select the Weekdays options, it fills the cells with weekdays dates.

Steps to fill the weekdays date in cell using Excel autofill

Step 1: Enter a date in a cell and select it to autofill the dates in other fields.

Autofill in Excel

Step 2: Drag the autofill (+) sign down to other cells and see it has auto-populated the dates in a series of the recognized pattern.

Autofill in Excel

Step 3: At the end of the generated values, an Autofill Options box will display. Click this Autofill Options box that will show many autofill options.

Autofill in Excel

Step 4: Here, click the Fill Weekdays option in the list.

Autofill in Excel

Step 5: You will see that all the auto-filled dates are changed to weekdays dates.

Autofill in Excel

Tip: Weekdays refers to the working days from Monday to Friday. When you select the Weekdays options, it fills the cells with weekdays dates.

Hence, you can work with autofill options like this. It also has several other options in the list. Use them whenever needs accordingly.






Latest Courses