AutoFill can be used to enter text or numbers such as days of the week, dates, months, text or a series of numbers.
Excel has some predefined lists like days and months but you can also create custom lists which we will demonstrate in this article.
When a cell or range is selected you will notice a small box on the bottom right hand corner. This is called the Fill Handle
When you hover your mouse over the fill handle you should see the white cross icon change to a black cross. If the icon does not change then the AutoFill feature has been disabled. To enable or disable the AutoFill feature follow these steps.
Click the File tab and select Options (this will enable the Excel Options dialog box) Click the Advanced tab on the left.
In the Editing Options section locate enable fill handle and cell drag and drop.
Check the box to enable AutoFill. Uncheck the box to disable AutoFill.
You can use AutoFill to fill text down a column or across a row.
Here are a few examples of how this is done. Column A copies the text from cell A1 to A10 by dragging down the fill handle.
Column B copies the text from cell B1 to B10 but this time because we have data in column A Excel recognises this so we can simply double-click the fill handle and the text will fill down to cell B10.
Column C fills down a series. We have added a number at the end of the text. Excel will automatically fill the number series down. Again because we have data in the previous column we can simply double click the fill hand to fill down.
Column D copies text down the same as column A only this time we use the keyboard shortcut Ctrl + D
You can AutoFill numbers in much the same way the main difference being that if you only have one number Excel assumes you want to keep this number and fills down the same number. Column A we have started with the number 1 and filled down to row 10. Because we do not have a sequence of numbers Excel fills down the number 1 in all rows.
Column B we repeat this process only before we click the fill handle we press and hold the Ctrl key and Excel now increments the number by 1 in each row.
Column C we have a sequence in the number pattern. 2,4,6 Excel recognises this sequence and continues the pattern as we fill down.
There are many different options available when using AutoFill to fill dates. In this example we will demonstrate a few of them. Column A fills down the date increasing the day by one in each row.
Column B fills down the date only this time we Right-Click the Fill Handle and drag down. Once you release the click a small menu will appear. Select Fill Weekdays and Excel fills down the date increasing the date by one but only including weekdays, Saturday and Sunday are omitted.
Column C Right-Click the fill handle and drag down. From the menu select Fill Months. This will keep the same date but add one month in each row. Excel keeps the same date unless that date We have changed the date in cell C1 to 31st January to show you what happens when the next month does not contain the given date. e.g. We started the date 31st January the next month will be February and as you know February does not contain 31 days. If this is the case Excel changes the date to the last day of the month as shown in the example.
Column D fills down the same only increases the year by one in each row.
Column E fills down the date by recognising the sequence in the dates. In the example extend the date by seven days. This can be useful if you want to add weeks or fortnights etc. down a column. Simple click the fill handle and drag down. In the last example we select rows 11 and 12 and drag down to row 15. In the example you can see that all the dates are filled down in the sequence each one was previously added.
AutoFill can also be used to fill Time values down a column or across a row. Here we will look at a few examples if using AutoFill for times. Column A In column A we have a 30 minute gap between the first two times. Excel recognises this gap and continues the sequence as we fill down the column.
Column B We start with just one time. When we fill down Excel increases this time by 1 hour in each row.
Column C We have a 3 hour gap between the first two times. Excel recognises this gap and continues the sequence as we fill down the column.
Click in cell A1 and type the month "January" when finished typing press Ctrl + Enter. Click and hold the Fill Handle and drag it across to cell F1
Notice that as you drag across each cell Excel displays a small marker indicating what value or text will be displayed in the cell.
In this example we will fill the days of the week down column A. You do not have to start with the first day of the week, type in any day of the week and fill down. In this example we will start on Wednesday and fill down 10 rows. Notice that after the seventh day Excel returns to the day we started with and continues the loop. Excel will also recognise patterns in days for example of you were to remove Thursday from the list when you fill down Excel will always jump from Wednesday to Friday.
As well as filling down text and numbers you can also use AutoFill to fill formulas to other cells. One very important point when using AutoFill to fill formulas is that you understand the absolute cell reference and relative cell reference within the formula. These will be explained in more detail in another article. In this example we a using a simple relative cell reference. Add the formula A1+B1 in cell D1 Fill down cell D1 to cell D10 Each cell from D1 to D10 now has a unique formula counting the values in column A & B in the relating row numbers. e.g. The formula in cell D5 has the formula A5+B5
Clicking and dragging the fill handle is the standard way of using the AutoFill feature but to speed the process there are keyboard shortcuts you can use.
Keyboard Shortcut : Ctrl + D Example Select cell A1 and enter text or a number value hold the Shift key and click cell A10 press Ctrl + D
Keyboard Shortcut : Ctrl + R Example Select cell A1 and enter text or a number value hold the Shift key and click cell G1 press Ctrl + R
Fill Left or Fill Up
The keyboard shortcut keys for fill Left and fill Up and harder to remember Fill Left: Alt - H - FI - L Fill Up: Alt - H - FI - U When you are using fill Left or Up it is best to click the Fill option located in the Editing section of the Home tab.
When you want to fill down a series of text that Excel does not recognise you can create a custom list.
For example lets say you have ten staff members in the office and you find yourself regularly typing all their names down a column. You can create a custom list then after typing the first name you can use AutoFill to fill in the rest of the names.
In this example we will go through the steps how how to do this.
Column A has a list of ten names that we want to add as a custom list.
Click the File tab and select Options from the menu to open the Excel Options dialog box.
Scroll down to the General section
Click Edit Custom Lists
You can add names manually in the List Entries box. Create your list then click Add.
Since we already have our list in the worksheet we can import it.
Click the Icon next to the Import button
Select the range that contains the names you want to add. In this example we select $A$1:$A$10
Click the Import button
The list has now been added to the Custom lists box.
If we now type any name on the list and use AutoFill Excel will add each name as we drag down the rows or across the columns.
Excel 2013 has a new feature called FlashFill which is similar to AutoFill but with a lot more power.
Look out for our complete guide to FlashFill in another tutorial.
- EXCEL TOPICS
- EXCEL VBA