Cole Nussbaumer, at the Storytelling with Data blog, has introduced a useful term for data visualization in business: the Grable. It’s a combination of a table and a graph.
Tables are good for some things, like looking up specific values and especially when each row has several columns of numbers and text data. Graphs are good for other things, like showing patterns in the data instantly.
Sometimes you need both – a combination table and graph – or “grable”.
Now, the idea of a combination table/graph is not new; they’ve been around forever. But the term “grable” is new. And it’s a useful addition to the data viz vocabulary, especially for business data.
How do you create a grable? There are several ways to do it. In Cole’s grable above, she creates a table for the text part of the grable and then adds a graph, which she resizes to align with each row. That’s option one.
Option 2 is to use conditional formatting (Excel 2007 or later). Enter the data in one column, then select all the cells and choose Home > Conditional Formatting > Data Bars > Solid Fill. Now a bar chart appears over top of the data values.
One problem with conditional formatting is sometimes the number half on the graph and half off, so it’s hard to read. Or, you may not have the right Excel version. In that case, there’s a poor man’s version which can work just as well. It looks like this
Here’s how to build the poor man’s conditional formatting. List the data in one column. Then in the column next to it where you want the bar to appear, enter this Excel formula:
Let me explain this formula. REPT means to repeat the symbol in quotations a certain number of times. The symbol in quotations is the “|” pipe symbol. And when you place them side by side enough times they look like a bar. Use the Playbill font for best results.
The formula in red is the number of times you want to repeat it. “A4″ is an example where you would point to a cell that contains your data. But if the number is too large (eg. 400,000) that’s too many pipe symbols. It would stretch from here to the elevators. Instead, divide that number by some reasonable amount so the bar is the length you want. Similarly, if the number is too small (eg. 0.5) then multiply that by some reasonable number to get a bar length you like.
Try a grable the next time you present data to executives. Thanks to the charting gurus at Chandoo.org for this handy Excel tip!
About the author: Bruce Gabrielle is author of Speaking PowerPoint: the New Language of Business, showing a 12-step method for creating clearer and more persuasive PowerPoint slides for boardroom presentations. Subscribe to this blog or join my LinkedIn group to get new posts sent to your inbox.