Microsoft Excel has a variety of features designed to make data entry quick and easy; and AutoFill is no exception. This fantastic tool is used to enter repetitive lists automatically for you. While the days of the week, months of the year and yearly seasons are set to default; you can customize your own lists so that they include the information necessary for your spreadsheet. So if you needed to enter all your employees’ names on a regular basis, then it would be wise to set up a custom list for them instead. This applies to any information you frequently enter; such as product lists, customer details and more. This Excel course will help you learn Excel; and teach you how to create a custom list for AutoFill:
Click on the round File button in the top left corner of your Excel toolbar. A window will open up that offers several choices. Click on the Excel Options button at the bottom. This is what will come up:
Now you should click on Advanced (note that if you are working with Excel 2007, you will need to click on Popular which is right at the top). In Excel versions 2010 and 2013, you need to scroll down to the sub-heading called General where you need to click on Edit Custom Lists. If working in Excel 2007, you will find Edit Custom Lists in the first category on the page once you have clicked on Popular (which is Top options for working with Excel). This is what it will look like once you have clicked on Edit Custom Lists:
On the left-hand side, you will see all the existing lists. If you have never created a custom list, then it will show the default lists that Excel offers. The right-hand column is where you will enter the information that you require. For the purposes of this Excel tutorial, I have listed all my animals for you. Once you have typed in the data you wish to include in the list, click the far right-hand button that says Add. Once you have added it, you can click on the OK button at the bottom. Remember to also click OK on the previous window named Excel Options.
Now that you have created a new custom list, let us use it in a practice spreadsheet.
- Type the first name on the list into a cell and press Enter. You can type any name in the list, but if it needs to be in that specific order then it is faster to enter the first one.
- Go back to the cell by clicking on it once.
- Your first word will now have a black border surrounding it, with a small box at the bottom right-hand corner. Hover your mouse over the little box and a black cross will appear (known as the AutoFill Handle).
- Click on the AutoFill Handle and hold it down. This will allow you to drag information up, down, left or right.
- Drag in the direction you require while continually pressing the AutoFill Handle. You will see the words in your list appearing as you drag.
- When you have dragged as far as you need to, simply let go of the AutoFill Handle.
It should now look like this:
The little orange box with squares contains your AutoFill options. If you require the use of them, then this is what they will do:
Copy Cells: This option will automatically copy the original word that was typed into all the cells.
Fill Series: This is the default setting and will use the order of the custom list that you created.
Fill Formatting Only: This will include the formatting within the cells.
Fill Without Formatting: This option will fill the list without copying any of the formatting.
Now that you know how to create a custom list for AutoFill in Excel, you will be able to use it to automatically fill lists without having to type each entry. There is no limit to the number of lists that you can create, so you can use customized lists for every repetitive pattern within your various spreadsheets.
A number of students in our South Florida Excel training courses have suggested we write an article on AutoFill custom lists, simply because it saves so much time on manual entry. We encourage you to watch our live Excel tutorials for a deeper understanding of how Excel works.
