Can Excel Sort by Color? How to Fill Color and use Sort & Filter's Custom Sort

Excel can sort by cell color, font color, cell icon, and values.

Credit: Rob Schultz

Can Excel sort by color? Absolutely. In addition to sorting by values, Excel can sort by cell color, font color, and cell icon.

First, you must you must define the colors for your sort columns/fields. For this example, we’ll select colors based on the SALES column.

1. Highlight all the rows with sales from 100 to 500 million. To choose a background color, click Home on the main menu row and go to the Font group. Find the paint-bucket icon that stands for Fill Color, and click the down arrow next to it. A color palette appears, from which you may choose a color by clicking its thumbnail. For our example, we've chosen Yellow from the Standard Colors group.

2. Highlight the next group with sales from 77 to 85 million, then choose Blue.

3. From 60 to 65 million, choose green; for 50 million, choose Orange; choose lavender for 40 to 45 million, and cyan (turquoise) from 30 to 39 million

01 choose colors for each specific range JD Sartain / IDG Worldwide

Choose colors for each specific range

Now that the colors are defined by SALES, you can use them to sort by BOOK, AUTHOR, LANGUAGE, or PUB DATE, and, for example, view authors with sales of Cyan, which denotes the group with sales between 30 and 39 million.

1. Move the cursor anywhere inside the data range, press Ctrl+A to highlight everything.

2. From the Home tab on the main menu, select the Editing group. Click Sort & Filter > Custom Sort and the Sort dialog box opens.

3. If you have headers, be sure to check the box that says: My data has headers.

4. Choose a sort key from the Sort By field box under Column, such as Author.

5. Under Sort On, choose Cell Color from the drop-down list.

6. Under Order, choose the thumbnail for the color by which you wish to sort—in our example, it's cyan. 

7. And, in the last field box, choose On Top, which sorts the selected rows to the top of the spreadsheet (choosing On Bottom sorts the selected rows to the bottom).

8. Next, click the Add Level button, which adds a second sort key to the query.

9. From left to right, choose AUTHOR, Cell Values, A to Z, and click OK.

02 sort spreadsheet range by color first then by author JD Sartain / IDG Worldwide

Sort spreadsheet range by color first, then by AUTHOR

Notice that all the Cyan cells (30 to 39 million in SALES) are sorted to the top of the spreadsheet and then alphabetized by AUTHOR within the Cyan colored cells. The rest of the spreadsheet is sorted by AUTHOR only with all the remaining SALES totals (except 30 to 39 million).

03 cyan cells sorted to the top alphabetized by authors name JD Sartain / IDG Worldwide

Cyan cells sorted to the top alphabetized by AUTHOR’s name

For more samples and detailed instructions regarding sorting by colors, values, and icons, and using conditional formatting, check out these two past articles: How to use Microsoft Excel's conditional formatting, and How to customize Excel conditional formatting.

Join the newsletter!

Or

Sign up to gain exclusive access to email subscriptions, event invitations, competitions, giveaways, and much more.

Membership is free, and your security and privacy remain protected. View our privacy policy before signing up.

Error: Please check your email address.

Tags Office 2013Office 2016

Keep up with the latest tech news, reviews and previews by subscribing to the Good Gear Guide newsletter.

JD Sartain

PC World (US online)
Show Comments

Essentials

Mobile

Exec

Budget

Back To Business Guide

Click for more ›

Brand Post

Most Popular Reviews

Latest Articles

Resources

PCW Evaluation Team

Christopher Low

Brother RJ-4230B

This small mobile printer is exactly what I need for invoicing and other jobs such as sending fellow tradesman details or step-by-step instructions that I can easily print off from my phone or the Web.

Aysha Strobbe

Microsoft Office 365/HP Spectre x360

Microsoft Office continues to make a student’s life that little bit easier by offering reliable, easy to use, time-saving functionality, while continuing to develop new features that further enhance what is already a formidable collection of applications

Michael Hargreaves

Microsoft Office 365/Dell XPS 15 2-in-1

I’d recommend a Dell XPS 15 2-in-1 and the new Windows 10 to anyone who needs to get serious work done (before you kick back on your couch with your favourite Netflix show.)

Maryellen Rose George

Brother PT-P750W

It’s useful for office tasks as well as pragmatic labelling of equipment and storage – just don’t get too excited and label everything in sight!

Cathy Giles

Brother MFC-L8900CDW

The Brother MFC-L8900CDW is an absolute stand out. I struggle to fault it.

Luke Hill

MSI GT75 TITAN

I need power and lots of it. As a Front End Web developer anything less just won’t cut it which is why the MSI GT75 is an outstanding laptop for me. It’s a sleek and futuristic looking, high quality, beast that has a touch of sci-fi flare about it.

Featured Content

Product Launch Showcase

Don’t have an account? Sign up here

Don't have an account? Sign up now

Forgot password?