This week I was at a conference where the presenter strongly urged participants not to use color in an Excel. She claimed you couldn’t sort columns by color. She went on to say one of her clients had done this and she spent hours editing the sheet. Perhaps, at one time this functionality wasn’t available, but you can do it now. The tutorial below can be used for font colors or cell color. I’ll be using Excel 2010.
It’s probably my laziness, but I find it very easy and useful to change a cell’s background color. I often use this when I’m doing a prioritization of a list. I just need to highlight a cell and choose a color. I tend to keep things simple and use the default set.
To start, I’ve already built a sample spreadsheet with some values. Please don’t put any stock in the words and numbers as they’re fictional. I used a keyword generator and Excel’s randbetween function.
To sort your spreadsheet by color,
- Click the Data tab.
- Highlight your data range. You can also click CTRL+A
- Click the Sort button. A pop-up dialog will appear like the outlined one below.
- Click the drop-down arrow next to Sort by and select the column with your color. I’m using the Effort column.
- Move to the right and click the drop-down arrow next for Sort On values.
- Select Cell Color. Excel will grab the colors from the spreadsheet.
- Select your first color and keep the Order value as On Top.
- Click the Add Level button in the top left of the panel.
- Add in your other levels and colors. Excel will process them in order so you can keep them all as On Top.
- Click OK.
You should now see your sorted list by color. You’re not limited to just 3 colors and can keep adding. You also don’t need to enter the last color as Excel will put it on the bottom.
The same process works if you wish to sort by font color.
Note: Did you notice anything about the before and after pictures? The Volumes don’t match up because of my error. I did this post in two sessions and closed down the Excel file. When I reopened, it generated new random numbers. That should teach me to use Paste Special next time.