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, Alphanumeric Keys, Repeat Shortcut Keys, Own Autofill List, and VBA Macro Code.


Watch Video – Apply AutoFill Shortcut in Excel


How to Apply AutoFill Shortcut in Excel: 7 Suitable Methods 

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 autofill the content.
  • 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


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

 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 Sequential Letters in Excel


3. Use the Flash Fill Feature in Excel

Flash Fill automatically fills your data when it senses a pattern in cells. 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. It helps to repeat formula and fill the cells simultaneously.

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 of 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


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


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 in their respective office room. Both the ID No and PABX No increase linearly just like the Serial No of the employees. So, if we autofill the Serial and ID No columns then we can use the repeat shortcut key to autofill the PABX
  • So, we will first autofill the Serial and ID No columns 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


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 names of the materials every time, we can use our own custom list to autofill the names of the materials.

Step 1:

  • First, we will go to the Options 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

Excel Custom List to Autofill


7. AutoFill Using the VBA Macro

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

Step 1:

  • First,  select range B4:H14 → go to View tab Macro Record Macro

Recording 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

Read More: [Fixed!] AutoFill Formula Is Not Working in Excel Table


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.

Download Practice Workbook

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


Conclusion

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


Related Articles


<< Go Back to Excel Autofill | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
ASM Arman
ASM Arman

Abu Saleh Arman is a Marine engineer and Excel & VBA expert. He loves programming with VBA. He finds VBA programming a time-saving tool to manipulate data, handle files, and interact with the internet. He is very interested in Python, MATLAB, PHP, Deep Neural Networks, and Machine Learning, showcasing his diverse skill set. Arman holds a B.Sc in Naval Architecture & Marine Engineering from BUET, Bangladesh. However, he switched to a content developer, where he writes technical content... Read Full Bio

1 Comment
  1. I’m here to learn about automatic student report card using excel

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo