How to Apply AutoFill Shortcut in Excel (7 Methods)

This tutorial will help you to use Autofill shortcut in Excel of various types to conveniently autofill or populate the cells in Excel without any hassle. We will learn 7 different types of Excel autofill shortcut to let Excel automatically fill the rows for us.  We will use Keyboard Shortcuts, Fill handle, Flash Fill, SHIFT, and Alphanumeric Keys together, Repeat Shortcut Keys, Own Autofill List, and VBA Macro Code.


Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.


7 Suitable Methods to Use AutoFill Shortcut in Excel

Let’s assume a scenario where we have information about employees of a company. We have the first and last names of the employees, their monthly salaries, and the bonus they got last month for the sales they had made. Along with these columns, we also have empty columns named Serial, Full Name, and Net Income. We will autofill the cells in these columns using different Excel autofill shortcut.

Excel Autofill Shortcut


1. Fill Formulas to Adjacent Cells Using Keyboard Shortcut in Excel

You can use the Fill command to fill a formula into an adjacent range of cells. Simply do the following:

Step 1:

  • Select the cell that has the formula in it. In this example, it is cell H5 under the Net Income In the Net Income column, we will sum up the salary and bonus of an employee with the SUM formula.
=SUM(F5,G5)

Fill Formulas to Adjacent Cells Using Keyboard Shortcut

Step 2:

  • With cell H5 being the active cell, press Shift + DOWN ARROW key (if you are filling a column) or Shift + RIGHT ARROW key if you are filling a row) up to where you want to fill the content to.
  • Drag the fill handle across the cells that you want to fill.

● Drag the fill handle across the cells that you want to fill

  • You can also press Ctrl+D to fill the formula down in a column, or Ctrl+R to fill the formula to the right in a row. In this example, we are filing the formula down in a column. So we will press CTRL+D. Upon pressing CTRL+D, we will find that all the cell has been filled with the SUM formula and has Net Income for every corresponding employee.

Press Ctrl+D to Fill the Formula Down in a Column

Read more: How to Use Autofill Formula in Excel


2. Fill Down a Series Using the Fill Handle

We have a Serial column where all the employees will be serialized in ascending order. We can autofill the column using the Fill Handle(+) tool in Excel.

Step 1:

  • First, enter values in some of the cells of the Serial You can fill in only one cell although. But AutoFill works accurately when it has some data to work with.
  • Select the cells you filled, and then click and hold the fill handle. The pointer changes to a plus symbol (+) when the mouse is in the correct place.

Fill Down a Series Using the Fill Handle

Step 2:

  • Now drag the mouse down (if you’re filling a column) or to the right (if you’re filling a row). As you drag, a tooltip appears, displaying the text that is being generated for each cell.

Excel Autofill Shortcut

  • On releasing the mouse button, Excel will fill the series with the serial numbers for the rest of the employees.

 Excel Filled the Series with the Serial Numbers

  • You can also double-click the Fill Handle icon to autofill the cells. This will only work if you have values to the left or right of the column you need to fill as Excel looks at the adjacent column to define the last cell in the range to fill.

Read more: How to AutoFill Numbers in Excel


3. Use the Flash Fill Feature in Excel

Flash Fill automatically fills your data when it senses a pattern. For example, you can use the Flash Fill command to separate first and last names from a single column or combine them from two different columns.

Step 1:

  • We have a First Name column and a Last Name column for the employees. We will combine the two names to get a full name in the Full Name.
  •  Write the First Name and then the Last Name for the first employee in the Full Name.  Now when we are going to do the same for the second employee, Excel will show us suggestions of the Full Names for the second and rest of the employees.

Use the Flash Fill Feature in Excel

Step 2:

  • Upon pressing the ENTER,  we will get the full name of the rest of the employees. Alternatively, we can also press CTRL + E if Excel does not show us a suggestion.

Ful Names of the Employees

Read more: Flash Fill Not Recognizing Pattern in Excel


4. AutoFill Series Using Modifier and Alphanumeric Keys in Excel

There is another shortcut to autofill the series using only the keyboard. You have to do the following:

Step 1:

  • Use SHIFT+DOWN ARROW to highlight where you want the series to go – must include the filled-in cells.
  • Then press ALT + H + F + I + S. A pop-up window named Series will appear.

AutoFill Series Using Modifier and Alphanumeric Keys

Step 2:

  • Now, Press SHIFT + TAB + F. The series type will change to Autofill.
  • Press OK or ENTER.

Excel Autofill Shortcut

  • The rest of the cells will be auto-filled.

Rest of the Cells are Autofilled

Read more: How to Autofill Numbers in Excel without Dragging


Similar Readings


5. Repeat Shortcut Key to AutoFill in Excel

Although there isn’t a shortcut key or Ribbon command that does the same thing as double-clicking the AutoFill handle, Excel still recognizes it as a command. This means that you can use Excel’s Repeat feature to AutoFill as many times as you need after you have done the first AutoFill manually.

Steps:

  • Let’s assume that we are working with employee ID No and PABX to their respective office room. Both the ID No and PABX No increase linearly just like the Serial No of the employees. So, if we just autofill the Serial and ID No column then we can use the repeat shortcut key to autofill the PABX
  • So, we will first autofill the Serial and ID No column using one of the above-mentioned methods.

Repeat Shortcut Key to AutoFill

  • Then we will fill the first two rows of the PABX column and select both of them using the fill handle.
  • Now we will press CTRL+Y together to fill up the PABX.

Press CTRL+Y Together to Fill Up the PABX Column

Read more: Excel Formulas to Fill Down Sequence Numbers Skip Hidden Rows


6. Create Own AutoFill List

There is a collection of AutoFill lists that Excel recognizes and refers to every time you use the feature. However, you can add your own lists to the collection, which extends the series Excel recognizes.

For example, the company we are working on may have to write down the name of some office materials every day. So, instead of writing the name of the materials every time, we can use our own list to autofill the name of the materials.

Step 1:

  • First, we will go to the Option menu from the File.

Create Own AutoFill List

  • Select the Advanced.

● Select the Advanced Section

Step 2:

  • Locate the General heading and at the bottom of that section, click Edit custom lists.

Edit Custom List

  • In the “Custom lists” box select New List.
  • Then in the “List entries” box type in your list separated with a comma as shown in the picture below.
  • After typing your list, click Add to store your list.
  • Click OK to close the Custom list window and OK again to close the Excel Option.

Custom List to Autofill in Excel

Step 3:

  • Now type the first two items of the list in two cells. Select both of the cells and drag the fill handle to the cell where the last item of the list should be. As you drag, a tooltip appears, displaying the item of the list that is being generated for each cell.

Excel Autofill Shortcut

  • Release the fill handle and you will see the cells have been filled with items on the list.

Excel Custom List to Autofill

Read more: How to AutoFill from List in Excel


7. AutoFill Using the VBA Macro

You can also use a macro to autofill the cells. Just follow these steps:

Step 1:

  • First, go to View tab Macro Record Macro

Autofill Using the VBA Macro

  • Choose an optional name, for example, MacroAutoFill (no space in the name!).
  • Then select an optional shortcut, for example, Ctrl+Y.
  • Click OK to start recording the macro.

Add Macro in Excel to Autofill

Step 2:

  • Go to Home TabEditing FillSeries.

Excel Autofill Shortcut

  • For “Series in” choose “Columns“, check the “AutoFill” option, then click OK.

Excel Autofill Shortcut

  • Go to View tabMacro → “Stop Recording.

Stop Recording Macro in Excel

Step 3: 

  • Now type the first two numbers in the first two cells under the Serial column. Select both of the cells.
  • Then press the macro shortcut keys CTRL+Y All the cells of the Serial column will be autofilled.

Excel Autofill Shortcut


Things to Remember

  • If you don’t see the fill handle, it might be hidden. To display it again:

        ⇒ First, click File > Options.

        ⇒ Then click Advanced.

        ⇒ Under Editing Options, check the Enable fill handle and cell drag-and-drop box.

  • To change how you want to fill the selection, click the small Auto Fill Options icon that appears after you finish dragging, and choose the option that you want.

Conclusion

In this article, we have learned to use Excel autofill shortcuts. I hope from now on you would find it very easy to use the Excel autofill shortcut. However, if you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!


Further Readings

ASM Arman

ASM Arman

Hi there! I am ASM Arman. I Completed B.Sc. in Naval Architecture and Marine Engineering. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations. Have a great day!!!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo