The Excel Addict - Help with Excel 2013, 2010,
2007, 2003


March 2, 2017
 
Greetings from The Excel Addict

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)
Email:  fhayes[AT]TheExcelAddict.com


Francis Hayes (TheExcelAddict.com)
 


If you missed my last newsletter, you can click here to view it online.


 
Quote of the Day

" It’s not what you say out of your mouth that determines your life;
it’s what you whisper to yourself that has the most power."

-- Robert T. Kiyosaki --
 
If you have a favourite quote, send it to me and I may post it in my newsletter.

Excel in Minutes with TheExcelAddict.com

Create Your Own Custom AutoFill Lists

Custom List Used In Spreadsheet in Microsoft Excel 2007 2010 2013 2016 365Most 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

<DOWNLOAD PRACTICE WORKBOOK>

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);

Add A Custom List in Microsoft Excel 2007 2010 2013 2016 365

3) From the File tab choose Options, Advanced tab, scroll down to the General section, then click the Edit Custom Lists button;

Excel Options Custom Lists in Microsoft Excel 2007 2010 2013 2016 365

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.)

Import Custom List in Microsoft Excel 2007 2010 2013 2016 365
Notice the 4 built-in custom lists: (Sun..., Sunday..., Jan..., January...). The A, B, C list is a custom list I created for myself for quickly autofilling the alphabet either across columns or down rows.

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.

Autofill Your Custom List in Microsoft Excel 2007 2010 2013 2016 365

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.

Sorting A Custom List in Microsoft Excel 2007 2010 2013 2016 365

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.



Click here to get more tips like this every week



If you've found this tip helpful, please share it.




Thanks for supporting this newsletter and website



Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose.
"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