SubscribeUse “Script” font to make your incell charts realistic [quick-tips]
Most of you already know that using the REPT formula along with pipe (”|”) symbol, we can make simple in-cell charts in excel. For eg. =REPT("|",10) looks like a bar chart of width 10.
Despite the simplicity, most people don’t use in-cell charts because these charts don’t look anything like their counterparts. But you can overcome this drawback with a secret I am share now.
Just change the font to “Script”, size 7. See this to understand the difference.
With a simple font change, you can make your incell charts magical. What more, combining incell charts with conditional formatting and some awesome alignment, you can make charts like this with ease.
PS: Script is one of the default fonts of Windows operating system, so you dont need to worry about the availability.
Related: Tutorials on in-cell charts | REPT formula help & syntax | Conditional Formatting Basics | Quick tips
Comments
RSS feed for comments on this post. TrackBack URI
Leave a comment
If you have a question, please ask in the forums
At Pointy Haired Dilbert, I have one goal, "to make you awesome in excel and charting". PHD is started in 2007 and today has 300+ articles and tutorials on using excel, making better charts. 
Adjust width of Column Shortcut Alt,...
It’s got to be bold, though!
Nice one, thanks!
I tend to use the font ‘playbill’ for in cell graphs.
It also works well, and is more flexible when it comes to font size than script.
I’m on a Mac, but I’m pretty sure Playbill is a default font on Windows too.
That’s a great trick. I’m pretty sure I’ll use it in the future.
Thanks.
That was exactly what i needed.
I dislike Arial, ASCII 219 is to big, and the sparkline vba add- in just WAY too slow.
Thx again!
Chandoo
try this one in cell I7
=CONCATENATE(IF($C7>0,$K7&” “&ABS(C7),”"),CHAR(10),IF($D7>0,$L7&” “&ABS(D7),”"),CHAR(10),IF($E7>0,$M7&” “&ABS(E7),”"),CHAR(10),IF($F7>0,$N7&” “&ABS(F7),”"),CHAR(10),IF($G7>0,$O7&” “&ABS(G7),”"),CHAR(10),IF($H7>0,$P7&” “&ABS(H7),”"))
It’ll help with the read error due to the round(x/3,1) in your bar set-up cells. (15 and 17 produce equally high bars)
While using Arial, simply use insert-symbol for a wide range of options full block or smily’s or anything else.
great trick, tnx!
Hi Chandoo,
How are you doing the negative values?
I’m also wondering how to do the negatives…
@Zak & Zacho… I have used ABS() to make the negatives positive and then fed that to REPT(). Btw, negative bars are right aligned in a separate column.
@PG: depends on the font size. At size 7 it looks ok without bold. But I guess size 9 or 11 would need bolding.
@Geoff: Good tip about playball. Yes, that font is available in windows as well.
@Ikkeman: Are you sure this is the post where you wanted to comment?
@Mikii, Finnur, Loranga, Chris: Thanks
If you work for a bank, for negative values I’d suggest you use the skull and crossbones character in Arial Unicode, character code 2620.
To use it in Excel 2007, select a black cell, hit the INSERT tab, then select SYMBOL, make sure the FONT box on the pop-up window at the left is set to Arial Unicode MS, make sure the SUBSET box on the right is set to Miscellaneous Symbols, then click on the skull and crossboned (or the hammer and sickle in the case that the losses from your bank have already been socialized by the Government) and click INSERT.
Then select the cell, copy the character, and paste it into your REPT function so it looks like this:
=REPT(”Symbol”,”Number of times to be repeated”)
There’s more on this at http://chandoo.org/wp/2008/08/21/display-symbols-excel-chart/ in the comments.
nice tip chandoo.
thanks
Hi Chandoo – very cool, thank you.
I found the font size and zoom to be the important factors to get it looking correct. However, in a quick test, printing to a PDF revealed the “striations” that you showed us how to hide. After some more playing I found that changing to “g” with Webdings font creates a barchart without the “striations” of using “|”. In my quick test it survived zoom and printing. However, as the “g”/Webdings character is much wider than the “|”/Script, I divided the “number_times” in the REPT function by about 10 to get the same cell width when using Webdings.
Yes, I’m sure – it adds the value to the bar. As I metioned, 15 and 17 will result in exactly equally high columns.
When you want to keep the cell with the columns small, you can add the values to the cell above/below. (value&char(10)&value).
Ofcourse, if you don’t mind not seeing the difference between values, than why make the bars?
I like to have the value at the end of the bars.
With the script font, the value isn’t very readable.
For a status report I used Courier 7 points and the formula =REPT(”?”;a1/2,5)&” “&TEXT(a1/100;”#%”) , with in A1 the percentage ranging from 0 to 100.
I used the “/2,5″ to make the bar smaller.
Hmmm… instead of the character called Full Block (code 2588), a question mark was inserted in my previous post.
Too good a tip. Thanks a lot.
I prefer Stencil. It looks good when I print or when I copy to a word document