IMAGE: Excel In Minutes Tips and Tricks from The Excel Addict - Microsoft Excel 2003, 2007, 2010, 2013, 2016, 2019, 365
 TheExcelAddict.com
 
September 16, 2019
 
Hi fellow Excel Addict,
 
As you may know, over the past several months I have been working on a remodeling project at a rental property I own an hour's drive from my home. Unfortunately, the time commitment has impacted my ability to send out my tips on a regular schedule. I apologize for that. I wasn't expecting it to take so long but unfortunately that's what's often happens with remodeling projects — over over schedule and over budget. However, I'm beginning to see the end and hopefully will be finished soon.

Francis Hayes - Learn to work smarter with Excel at TheExcelAddict.com

 I hope you like this week's 'Excel in Minutes' tip and are nice enough to share and recommend it to your colleagues and any other Excel users you know.

Keep on Excelling,
Francis Hayes (The Excel Addict)
Email:  fhayes[AT]TheExcelAddict.com

Send Email. Connect With Customers. Grow Your Business.

With AWeber, you get all the email marketing tools you need to create and send beautiful and engaging emails. For a behind-the-scenes look at how you can use AWeber, sign up to our Test Drive email series:
 
Missed my last newsletter?

Click Here to View it Online


Having a positive attitude can help us in difficult times.
I hope today's quote will help you realize a positive attitude today.

Quote of the Day

"Be yourself; everyone else is already taken."

-- Oscar Wilde --

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

THIS WEEK'S 'EXCEL IN MINUTES' TIP
 
Want To Create Your Own Custom Sort Order?

Sorting is an integral part of data analysis in Excel. Normally you sort text in ascending or descending alphabetical order and you sort numbers from highest to lowest or lowest to highest. This allows you to better understand relationships in your data.

But have you ever wanted to sort your data by some other order that wasn't alphabetical or numeric? Maybe your company wants to present its data by region. For example, in Canada, a company may want to present its data by province and territory from west to east, NOT in alphabetical order. You could sort the data in alphabetical order and then move the rows of data into the your custom order...but there is a much easier way.

In Excel you can create a Custom List and use that list to sort your data in your own custom order.

First you need to create your own custom list:

1) In a blank range, enter the values of your custom list in the order that you want from top to bottom;

Click here for practice file

2) Next, select the cells you just typed;

Create your own custom list for sorting and autofilling

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

Edit Custom List Options in Microsoft Excel 2007 2010 2013 2016 365

 5) In the Custom Lists dialog box, click Import, and then click OK twice.

Import Custom List in Microsoft Excel 2007 2010 2013 2016 365

Note: There are a couple of issues in versions of Excel prior to 2013 which limited the length of a Custom List to 255 characters and items in a list could not begin with a number.

Sorting your data based on a custom list:

1) Select the range of cells you want to sort, or select a single cell if you want to sort the entire range;

2) From the Data tab, in the Sort & Filter group, click Sort;

3) In the Sort dialog, select the Column you want to Sort on from the first dropdown and in the Order dropdown select Custom List...

4) In the Custom Lists dialog, select your custom list;

5) Finally, click OK and OK.

Sort By Custom List in Microsoft Excel 2007 2010 2013 2016 365

Autofilling Using a Custom List

Note that Custom Lists are not just for sorting. These lists can also be used for AutoFilling cells. Type a value from your custom list in to any cell and drag the Fill Handle to fill in all other value from the list. You can even AutoFill your custom list up, down, left or right. How cool is that?

Autofill a Custom List
As you can see in the sorting image above, I have also created a custom list for autofilling the letters of the alphabet which is sometimes need to do. I just type A and drag the AutoFill handle to complete my alphabet. So easy.

Drag To Fill a Custom List

To share this tip with your friends and
colleagues, choose one of these options...
 
Want To Create Your Own Custom Sort Order?
 
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