We all know about the extensive charting capabilities in Excel. In this blog, I show a quick and easy way to create charts within-cells (not on a separate canvas!), next to data. Much like sparklines but without using that feature. It’s simple, fun, and effective.
Let’s try 2 examples. First, we’ll use some movie ratings data snapshot I got from IMDB database. The table is straightforward. What we want to do is create a visual “star” charts based on the ratings, and highlight certain scores/ratings.
The data snapshot is: 
We notice that the reviews are on a scale of 10, but for brevity, we want to rescale it to 5 points. So, using a simple logic, I re-scored the reviews bringing them all to a scale of 5 max, and then rounding the results. I don’t need the fractions for this exercise.
Now my table looks like this:
Now I insert a new column, say Stars where I’ll insert the number of character symbols (yes, characters, not images) based on the re-scaled review numbers. This is where REPT() function comes in handy. So, put 5 asterisks (*) side-by-side in a cell, I can simply call REPT(“*”,5). Therefore, we can substitute the boring “*” with any Unicode character such as a ★ and repeat it any number of times…in this case, the value in Reviews (Rescaled) column for all the rows!
We also find that ★ character code is 9733. So, my formula for a specific cell is: =REPT(UNICHAR(9733),D4) where D4 has the re-scaled rating.
Great! Now we apply the formula to the whole range, and because it’s text, you can easily apply Font color and other attributes to it. For example, we want to call attention to movies that did poorly (1 to 2- star reviews), we can add Conditional Formatting or Font attributes as we wish.
The finished chart looks like this:
Pretty simple, and effective! Let’s do another example…
Say, you have week’s data on a coffee consumption at a cafe for each day. As we have seen in my other blogs, you can do all sorts of prediction and analysis using such data but here we’ll just focus on this charting aspect.
Using the same logic as before, we rescale the total daily consumption to a 10-scale point values (this is so the chart doesn’t unnecessarily look long and wide). Also, we want to call attention to the slowest day (in this case below, Thursday) by using conditional formatting or simply applying color attributes to the text. The text used here is a coffee cup ☕ (how appropriate) and its code is 9749.
The finished table with in-cell chart looks like this:
If we wanted to have vertical bars instead of horizontal, we can simply transpose the data and it’d look like this.
So, there you go. Very simple, character-based charts that are extremely fast and very effective for quick communication.
