Yipes! Stripes! (Color-coded Rows in Excel)

Fruit Stripe Gum adI occasionally receive spreadsheets where rows of data are colored yellow, teal or pink to indicate some special status. Essentially, the fill color feature is used like a highlighter marker, something the paint bucket icon on the toolbar or ribbon makes easy to do. In and of itself, there’s nothing wrong with highlighting rows, but problems can arise if color-coded rows are the only way that some important piece of information is communicated.


Let’s say that someone sends you a bug-tracking list in Excel with rows colored to indicate that high priority issues are magenta and medium priority issues are yellow. If you want to filter that spreadsheet to show just high priority issues or sort the spreadsheet by priority, it’s not a simple thing to do–at least if you are still using Excel 2003. Because formatting is not actually data, you can’t sort or filter on color. Amazingly enough, Microsoft added support  for filtering and sorting by color in Excel 2007. That’s wonderful but my employer has not yet upgraded from Excel 2003, so I don’t have that feature.

Sample Excel bug tracking spreadsheet

Sample Excel bug tracking file based on template from Chandoo.org

But even with the ability to sort and filter by color in Excel, you’re still limited if you want to create calculated columns based on priority or if you need to export the data to a database or another program. For these situations, the spreadsheet should have had a separate column in the data table labeled “Priority” with values like “High” or “Medium” rather than using background color alone to indicate priority. This would also be useful for color-blind users.

If you add a Priority column, it doesn’t mean that you have to do extra work to maintain highlighting separately. You can get the best of both worlds–real data columns plus highlighting that automatically changes when priority is updated–using the Conditional Formatting feature. Here’s how to do it using the bug tracking spreadsheet example. Assume that headings are in the first row, data records start in the second row and column C is Priority. First, select all the cells in the table except the heading row and click on Format, Conditional Formatting. Then, for Condition 1 pick “Formula Is”, type ‘=$C2=”High”‘ in the box and select magenta formatting. Repeat for “Medium” using yellow formatting.  The image below shows what the Conditional Formatting should look like in Excel 2003. The main limitation is that you only get three conditions, so if you want to make it more garish, you’re out of luck.

Conditional formatting dialog

By the way, if you want to vary the color of the TEXT of numbers in a cell based on whether the cell is positive, negative or zero, you can use the old custom number format feature.

So what do you do if you get a spreadsheet where there’s only formatting to convey some piece of information and you need to use it as data? You can add a custom function that returns the color of a specific cell and then create a column in your data table using that function.

To do this, add the following code to a VBA module in your spreadsheet:

Public Function CellColor(rng As Range) As Long
    CellColor = rng.Interior.Color
End Function

Then, add a new column to the data table with a heading like “Cell Color,” and copy a formula like “=CellColor(A2)” to every row in that column. The function will return the background color as an integer in RGB format (e.g. white is 16777215, pale yellow is 10092543, and magenta is 16711935) so you might want to use a lookup table to map the color to a more meaningful value.

It’s great that Microsoft implemented a feature to allow sorting and filtering by cell color, but it’s something that would be unnecessary if spreadsheets surfaced important information as data, not formatting. Consider your audience. If you know that the recipient is only going to print your file or paste it into a PowerPoint slide, there might not be any reason to add another column to a table. If, on the other hand, you share your file with people that may want to do more analysis on your spreadsheet, give them some data to work with.

Advertisements

One Response to Yipes! Stripes! (Color-coded Rows in Excel)

  1. Pingback: Excel Conditional Formatting by Formula Gotchas | Computers ala Mike

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: