A Simple Excel Tip to Increase Usability of Data
When preparing reports using Excel, you’ll usually find yourself staring at a big table of numbers that might look something like this:
I would say that looks…. not fun.
Highlight What’s Important
I help prepare and QA some of the reports we prepare for clients at my company. When creating or reviewing charts and tables, I think a very simple and universally accepted rule to live by is that we should do our best to highlight what’s important. If, once you’ve finished preparing your report, it’s not clear in a few seconds what the important information is, then you may have some work to do.
Thinking it back to the stage where you just have a bunch of numbers you’re looking at, it can also be helpful to quickly highlight basic trends that help you (as the “analyst” in this scenario) wrap your head around what’s happening.
Excel’s Conditional Formating tool is what you need.
After selecting the data in column B (make sure you do not select any “Total” values that will skew your highlights), click the Conditional Formatting button in the Home ribbon and scroll to “Color Scales.” You’ll see this menu, which allows you to instantly highlight the lows and highs in your data set:
I chose the Green ↔︎ White ↔︎ Red option. Here’s the result:
Similar to using Excel’s “Quick Charts” to quickly visualize a data set as a graph, Color Scales (and the other Conditional Formatting options in that menu) can can help you quickly identify different trends within the table itself. This is also really valuable if you’re presenting a table to someone you work with (or for).
Keep in mind that Excel tables can also be copied and pasted directly into Word docs, or exported as images and placed within other presentation documents. Since Excel can accomplish these visual tricks so quickly, it can be worth creating tables there first before placing into your other programs.
Final Thoughts
I tend to get bogged down in some of the little style details when I prepare reports, because my eyes will hone in on things I see as problems that will distract from the overall picture.
However, the most important thing to do with your data is answer the question “Why?” Why should anyone care about the information being presented? If there is no clear answer, then the graph or chart isn’t worth much.
For 11 amazing tips on answering the Why and communicating more effectively through data check out Avinash Kaushik’s latest blog post: “Closing Data’s Last-Mile Gap: Visualizing For Impact!” I think every paragraph is worth your time, but if nothing else, read the blue headings for his 11 tips on making sure decision makers actually understand your data.
