30 responses

  1. PG
    January 21, 2010

    It’s got to be bold, though!

    Reply

  2. Loranga
    January 21, 2010

    Nice one, thanks!

    Reply

  3. Geoff
    January 21, 2010

    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.

    Reply

  4. Finnur
    January 21, 2010

    That’s a great trick. I’m pretty sure I’ll use it in the future.

    Thanks.

    Reply

  5. Chris
    January 21, 2010

    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!

    Reply

  6. ikkeman
    January 21, 2010

    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.

    Reply

  7. mikii
    January 21, 2010

    great trick, tnx!

    Reply

  8. zak
    January 21, 2010

    Hi Chandoo,

    How are you doing the negative values?

    Reply

  9. Zacho
    January 21, 2010

    I’m also wondering how to do the negatives…

    Reply

  10. Chandoo
    January 21, 2010

    @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 🙂

    Reply

  11. Jeff Weir
    January 21, 2010

    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.

    Reply

  12. Nimesh
    January 22, 2010

    nice tip chandoo.
    thanks

    Reply

  13. Neil Allison
    January 22, 2010

    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.

    Reply

  14. ikkeman
    January 22, 2010

    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?

    Reply

  15. Charley
    January 25, 2010

    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.

    Reply

  16. Charley
    January 25, 2010

    Hmmm… instead of the character called Full Block (code 2588), a question mark was inserted in my previous post.

    Reply

  17. Nirvana
    February 16, 2010

    Too good a tip. Thanks a lot.

    Reply

  18. cesar
    February 20, 2010

    I prefer Stencil. It looks good when I print or when I copy to a word document

    Reply

  19. Ben
    September 11, 2010

    Really like the second chart, is there a tutorial anywhere on how to do this? I’ve noticed that the Rept function is not fond of negative numbers.

    Thanks

    Reply

  20. Chandoo
    September 12, 2010
  21. Ben
    January 26, 2012

    Is there a way to do something like this with the built in Data Bars, so specifically across the 3 cells? When i convert the script to pdf it comes out looking like the arial example above which isn’t smart enough i’m afraid :/

    Reply

  22. Anov
    October 30, 2013

    hi Chandoo,

    the script font doesn’t work with me. I think i got a different script font in my PC. So, I tried impact instead but there are still spaces in between the lines. are there further adjustments needed?

    Reply

  23. Gaurang M
    February 18, 2014

    Awsome trick…was looking for such type of bar for making reports…

    Thanks, it really helps nw!

    Regards,
    Gaurang M

    Reply

  24. Xun
    July 16, 2014

    the excel 2013 can not use the script font while 2010 can. Do you know why?

    Reply

  25. Misty
    April 26, 2015

    Thanks for finally writing about > Use ?Playbill? font to make your incell charts realistic
    [quick-tips] | Chandoo.org – Learn Microsoft Excel Online < Loved it!

    Reply

  26. Uttam bengani
    October 7, 2015

    How these incell charts are different from Sparklines function available ?

    Reply

  27. Shiv
    July 31, 2017

    How is this different from using Data Bars under Conditonal Formatting for Excel 2013?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top
mobile desktop