March 2, 2017
Hi fellow Excel Addict,
In today's 'Excel in Minutes' tip, I'm going to show you how to 'Create Your Own Custom AutoFill Lists'. This 'Excel magic' can also save you much time and frustration in certain circumstances that we Excel users often find ourselves in.
Did you read my 'Excel in Seconds' newsletter on Tuesday? I showed how to 'Make Your Spreadsheet Fit Your Screen'. Many readers we surprised how simple and effective this tip was. You can read it here.
I hope you'll have a great weekend and keep on Excelling,
Keep on Excelling,
Francis Hayes (The Excel Addict)
If you missed my last newsletter, you can click here to view it online.
If you have a favourite quote, send it to me and I may post it in my newsletter.
Create Your Own Custom AutoFill Lists
Most of us work with custom lists in Excel that need to be entered in a specific order that is not alphabetic such as region names, department names, job titles, etc...
Manually typing these lists over and over isn't very efficient. You may think that simply copying them from an existing workbook is pretty easy...and it is ...compared to retyping them each time.
But what if I showed you how you could instantly create these lists 'out of thin air'?
Excel's Custom List feature makes creating your own custom lists simple.
You've probably already used custom cists in Excel without even realizing it. When you need to enter the twelve months of the year and you type January in the first cell, then drag the Fill Handle to AutoFill the remaining months, that's a 'built-in' custom list. Or if you type Sunday in once cell and drag the Fill Handle to fill in the days of the week, that's also a 'built-in' custom list.
I'll bet when you first discovered those tricks you were amazed, right?
Well it can be just as easy to autofill your own custom lists whenever and wherever you need them. Just one time you'll need to create your list. After that, you can autofill it at any time and in any workbook. There's no need for copying or sorting.
Creating your Custom List
1) First you need to tell Excel what you want in your custom list. So, just enter the values for your list in a worksheet in the order that you always want them to appear. If you already have this list in a worksheet, you can even skip this step;
2) Next select the cells containing your list (only one column wide);
3) From the File tab choose Options, Advanced tab, scroll down to the General section, then click the Edit Custom Lists button;
4) The cells you selected for your list will be shown in the 'Import list from cells' field. Click the Import button and your list will be added to the Custom Lists area. (Alternatively, instead of importing your list from your worksheet, you could click NEW LIST andtype your list of values directly into the 'List entries' field then click the Add button to create the list.)
5) Click OK and your Custom List will be saved in Excel, not just the current workbook, and will be available for use in any workbook.
AutoFilling your Custom List
Whenever you want to add your custom list to any worksheet, simply enter the first value from your list into a cell and drag the fill handle. The list will be autofilled into those cells. If you drag over more cells than there are items in the list, the list will repeat over. Actually, you can start by typing any value in your custom list and when you AutoFill, the list will continue from that entry.
Sorting Based on a Custom List
Now that you have a custom list, you can sort existing data into the order of your custom list. From the Sort dialog (Data, Sort), choose Custom List from the Order dropdown in the Sort dialog, click OK, select your custom list and click OK.
In this example, my list happens to be in alphabetical order, however, sorting using a custom list will sort data in whatever order your list is in.
|"Spreadsheets Tips From An
Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630