99.1% Of all customers recommend us, we're so confident about our results we publish all reviews and statsView Live Stats View Reviews
Creating And Importing Custom Lists In AutoFill
Fri 23rd September 2011
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.
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.
Original article appears here:
London's widest choice in
dates, venues, and prices
On-site / Closed company:
Back Office Executive
Jens was brilliant and explained everything very well, was more than happy with this course.
Pine (UK) Holdings Ltd
PowerPoint Intermediate Advanced
Max was a great trainer. Really patience, helpful and insightful.
STL provide the best training service available I have ever been to. I highly recommend their Excel Courses for people looking to improve their skills. You wont be left disappointed.