In this post, I’ll demonstrate how to create a McKinsey-style bar chart using just symbols and formulas, without using any Excel’s built-in chart components.
BENEFITS OF THIS TYPE OF CHART:
▪ It’s basically a bar chart with end points of our choice and much more vertically space-efficient and flexible.
▪ It’s based on formulas, we can even apply conditional formatting and such.
And because the graphics (lines and endpoints) are all unicode symbols, we can scale them, color them, style them as text.
▪ We can easily highlight areas of the “chart” simply by choosing the desired cells (no need to format each data series like a real chart).
▪ We can add comments in-between the labels (y-axis) and the “chart” graphics by simply adding the strings with CONCAT or & in the formulas from a column in source data.
▪ We can easily change the endpoint and line characters (e.g. to stars or blocks, etc.).
WHEN TO USE:
▪ This type of “chart” works best with whole numbers as the number of characters shown cannot be fractions (e.g. the line length).
▪ This is suited for one series of numeric values only (like waterfall charts).
▪ If the values are very large, we obviously don’t want to repeat the line symbols that many times. So, just scale the values either in the table or in the REPT() formula cells
by dividing all the values by the same number to scale down/up. E.g. If we set a max value for line char in a cell, we can divide all original values by that number (e.g. /100).
WHEN NOT TO USE:
▪ When accuracy is paramount (e.g. fractional values).
▪ When the values are vastly different with a very small value as minimum and the largest value is very large.
▪ To account for that, divide all the values with the minimum value in dataset (non-zero). This ensures that at least one line character will be drawn for the smallest, while keeping the large values relatively compressed.
Examples
Suppose we have a survey results on pet types people own. The results are tabulated as percentages of respondents who indicated which pet(s) they have. Multiple choices were allowed, so total adds up to over 100%.
First, we need to shape the data that makes it easy to write the formula once and just drag it down to all the rows. Then the formulas just bring them over to the right for the right layout for the “chart”, and uses the unicode symbols to draw out the lines and endpoints, etc. As you can see I specified the symbols for the line character and the endpoint in different cells so I can easily change them as needed and the formula with draw out the new visual. The arrangement looks like this:
The formulas are shown in dark red. Basically, we’re using REPT() and concatenation features to draw the entire visual. Then it’s a matter of formatting and customization as needed. My final visual looks like this:
This is a straight-forward example, but as you can imagine, using unicode and formulas, you can get more creative with the visuals. In the next example, let me demonstrate a couple of variations that are easy to create. I remember saying earlier that it’s best not used with fractional values but I have used it here anyway…why? Because the difference between 1 and 0.5 are shown correctly and we don’t have that many fractions as it’s dominated by whole numbers, so in some cases, it might be perfectly fine as long as high precision is not critical to show on the visual.
Suppose we have data on how many days in a year it rains, or is sunny, etc. in Seattle. We can tabulate the data, but can also add some weather symbols to make it really easy for the audience. My table looks like this:
Next, we can jump right into the formulas and create a visual.
One variation is shown below. As you can see number of days range from 154 (max) to 4 (min). Obviously, we don’t want to draw the rain-and-umbrella 154 times, it’d look really, really wide! So, you can divide the numbers with a common denominator in the formulas until it makes sense visually. That’s how it’s done here. With the data shaping, we are able to assign each data series its own symbols very easily.
Let’s look at another variation below. In this example, we will use the unique symbol for each data series at the beginning right after its label, then draw the line symbols, and then append the numeric value. The formula looks like this: CONCAT(C108,REPT($C$101,D108/$C$114)& $C$102 & ” ” & D108) using the table shown above and C114 contains the formula: MIN(D:D) to find the lowest numeric value to ensure we guarantee at least one symbol will be shown for even the smallest data series value while also ensuring that the largest value will also fit on your visual aesthetically.
I hope it was educational as well as interesting for you. Have fun!
Related:
