Type a day of the week or a month of the year into an Excel cell and you can drag the Fill handle (the little black cross) at the lower right corner to complete the sequence.
Very handy when you need it but an inconvenience when you don't. How do you copy the word "March" as a constant when you have a list of events all happening in March? The answer is to hold down the CTRL key as you drag. A small plus sign appears on the Fill handle and the Autofill sequence is not generated.
Both the days of the week and months of the year are standard Excel Custom Lists but I think that these lists become really invaluable when you start to create your own sequences. Your Custom lists are stored with your copy of Excel so that once you have set them up you can use them on any workbook. For many of us so much spreadsheet work consists of the sheer drudgery of data entry and creating a few common lists like lists of department names, product names etc. makes a world of difference and cuts down the eternal copying and pasting of data.
Creating Custom Lists
You need the the Custom Lists dialog to create your list but opening it can be a bit of a struggle and depends on which version of Excel you have. If you've got the good old-fashioned classic version of Excel with the drop down menus then it's Tools, Options and look for the Custom Lists tab.
For Excel 2007 click the Office button then Excel Options and the Edit Custom Lists command button is in the first section.
From Excel 2010 onwards Custom Lists has been wrapped up in a bag, the bag left in a drawer and the chest of drawers moved to the spare room. Where have they put it?
Easy! It's File tab, Options, Advanced then scroll way down the list until you find the General section and it's the Edit Custom Lists command button over to the right. Usually quite invisible at this stage as your eyes are drawn to the left after scrolling down that huge list of sections. Often you can't see the Edit Custom Lists button for looking—only the other day I spent a good ten seconds staring at it before my brain cell said "Yeah!"
 |
| There it is! |
After this treasure hunt actually creating the list is a breeze, type the entries into the List entries box on the right hand side separating each entry either with a comma or by pressing the Enter key and when you have finished click the Add button. Your new list appears in the listing on the left. You can delete the custom lists you have created yourself but you can’t delete the preset day and month lists.
Importing Custom Lists
But it is usually much easier to import your list from some cells, after all that's why we're doing this in the first place; we don't want to have to keep typing in our list. There's a very good chance that you already have your list entered somewhere so select the cells first and then click Import when you open the dialog or click the red arrow to collapse the Import list from cells box.
 |
| Importing a Custom List |
Using your Custom List
Once your list has been created you use it exactly as you do the days and months lists—type in one of the entries from the list, then grab the Fill handle and drag to generate the rest of the sequence.
Don't forget that you can also use your Custom list for sorting, Excel will treat the items in the list as the most important things in that order when sorting, with any items not in the list automatically going in alphabetical order at the end of the data. This way, you could create a list of the top 10 clients who would always appear in the order you would like at the top of the data and all others would appear in normal alphabetical order below the top 10.
 |
| Sort by Custom List |
No comments:
Post a Comment