Creating And Importing Custom Lists In AutoFill

Fri 23rd September 2011

AutoFill is a useful tool for producing predictive incremental data. Using the Custom Lists function, however, you can AutoFill with just about anything you can type. Here's how to do it.

Excel has some excellent automatic features that save a lot of time and effort for the user. For the data entry user, AutoComplete saves thousands of keystrokes by predicting the current entry. This is extremely useful when entering names and addresses from a certain geographical area, where the same towns and cities are entered repeatedly. To enter Newcastle upon Tyne simply by typing the first two letters is an example of the enormous amount of assistance this feature offers.

AutoFill is another great time saver, as anyone who has to type the days of the week or months of the year regularly will testify. Values with predictable increments can also be entered using AutoFill, but its capabilities are not restricted to straightforward numbering. For example, I performed the simple task of instructing AutoFill to display values on every other row.

To do this, I typed 1 in cell B2, left B3 blank and entered 2 into cell B4. I selected these three cells and dragged the AutoFill handle down to continue the increment. The result was not what I was expecting, but I didn't need to seek advice on where I had gone wrong; the way the cells were filled told me exactly what the problem was.

I had omitted to include B5 as a second blank cell to make the sequence value, blank, value, blank. Missing out this second empty cell meant that the sequence consisted only of value, blank, value, and, although the resulting AutoFill continued the increment, it displayed pairs of numbers broken up by single blank cells. I soon put this right, but the experience showed me that care must be taken when using AutoFill, as it will do exactly what you instruct it to.

While AutoFill is a great time-saver when it comes to predictable text, you can also create your own custom lists to save time in typing out any often used data.

For example, if you were involved with the running of the darts team down at your local pub and you like to print off a summary of the games each week for the pub notice board. To save typing the name of each player every time you make a new sheet, you could create a custom list to enter these names automatically.

Select Options from the Tools menu and click on the Custom Lists tab. In the list entries box, type the names of the players, separating each one with a comma. When you have entered all of the names, click the Add button and they will be transferred to the Custom Lists box. You could save even more time here by entering the shortest surname first. So if you have entered Lee J as your first entry, type this into the first cell and press Enter. This will display the fill handle that you can now drag down until all of the team members' names are visible.

Setting up a custom list for a dozen or so darts players is fairly easy. But what if there was a much longer list to be entered, say the 92 football teams in the English league? The good news is that Excel will allow you to import this data direct from your worksheet to the Customs Lists box, without the need to type each one.

To enter these details automatically, use the mouse to select the range of cells containing the elements you wish to use in your custom list and do the following:

Excel 2007 and on
Click the Office button and select Excel Options. Select the Popular tab in the pane on the left and click the Edit Custom List button to open the Custom List dialog box. You should see the cells you selected in the Import List From Cells box at the bottom. Click Import and your selection will automatically appear in the Custom List box.

Pre 2007
Click Tools and select Options. In the dialog box that appears select the Custom Lists tab. Your selected cells should appear in the Import List From cells box. Click Import and OK, and the job is done.

AutoFill is one of the wonders of Excel that can be customised to make short work of entering data in countless scenarios. As the above shows, AutoFill is not restricted to dealing with predictable increments, the Custom Lists feature make it possible to drag out any set of data you choose.

Author is a freelance copywriter. For more information on microsoft excel training london, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-1970-creating-and-importing-custom-lists-in-autofill.html