Can Excel Kind by Colour? Learn how to Fill Colour and use Kind & Filter’s Customized Kind

Can Excel kind by colour? Completely. Along with sorting by values, Excel can kind by cell colour, font colour, and cell icon.

First, you have to you have to outline the colours on your kind columns/fields. For this instance, we’ll choose colours primarily based on the SALES column.

1. Spotlight all of the rows with gross sales from 100 to 500 million. To decide on a background colour, click on Residence on the primary menu row and go to the Font group. Discover the paint-bucket icon that stands for Fill Colour, and click on the down arrow subsequent to it. A colour palette seems, from which you will select a colour by clicking its thumbnail. For our instance, we have chosen Yellow from the Customary Colours group.

2. Spotlight the subsequent group with gross sales from 77 to 85 million, then select Blue.

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

JD Sartain / IDG Worldwide

Select colours for every particular vary

Now that the colours are outlined by SALES, you should utilize them to kind by BOOK, AUTHOR, LANGUAGE, or PUB DATE, and, for instance, view authors with gross sales of Cyan, which denotes the group with gross sales between 30 and 39 million.

1. Transfer the cursor anyplace inside the info vary, press Ctrl+A to spotlight every thing.

2. From the Residence tab on the primary menu, choose the Enhancing group. Click on Kind & Filter > Customized Kind and the Kind dialog field opens.

3. In case you have headers, you’ll want to verify the field that claims: My information has headers.

4. Select a form key from the Kind By area field underneath Column, reminiscent of Writer.

5. Underneath Kind On, select Cell Colour from the drop-down listing.

6. Underneath Order, select the thumbnail for the colour by which you want to kind—in our instance, it is cyan. 

7. And, within the final area field, select On Prime, which types the chosen rows to the highest of the spreadsheet (selecting On Backside types the chosen rows to the underside).

8. Subsequent, click on the Add Degree button, which provides a second kind key to the question.

9. From left to proper, select AUTHOR, Cell Values, A to Z, and click on 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.

To comment on this article and other Haveaheartsavealife content, visit our Facebook page or our Twitter feed.

Leave a Reply

Your email address will not be published. Required fields are marked *