Celebrate 'The VLOOKUP Book' birthday with us. Last day to get 50% discount on the e-book (31 October only).

Click here for details

Use “Script” font to make your incell charts realistic [quick-tips]

Posted on January 21st, 2010 in Charts and Graphs - 27 comments

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.

Use Script font to make your incell charts realistic

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.

Incell variance chart

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

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

27 Responses to “Use “Script” font to make your incell charts realistic [quick-tips]”

  1. PG says:

    It’s got to be bold, though!

  2. Loranga says:

    Nice one, thanks!

  3. Geoff says:

    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.

  4. Finnur says:

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

    Thanks.

  5. Chris says:

    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!

  6. ikkeman says:

    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.

  7. mikii says:

    great trick, tnx!

  8. zak says:

    Hi Chandoo,

    How are you doing the negative values?

  9. Zacho says:

    I’m also wondering how to do the negatives…

  10. Chandoo says:

    @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 :)

  11. Jeff Weir says:

    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.

  12. Nimesh says:

    nice tip chandoo.
    thanks

  13. Neil Allison says:

    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.

  14. ikkeman says:

    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?

  15. Charley says:

    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.

  16. Charley says:

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

  17. Nirvana says:

    Too good a tip. Thanks a lot.

  18. cesar says:

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

  19. [...] Incell chart secret – use “Script” font to make better incell charts | More [...]

  20. [...] You also lose font ┬átype information in 2007, so if you’re using any special fonts to create in-cell charts they will end up looking odd. Excel 2010 preserves font information (as long as the font is [...]

  21. Ben says:

    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

  22. Ben says:

    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 :/

  23. Anov says:

    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?

  24. Gaurang M says:

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

    Thanks, it really helps nw!

    Regards,
    Gaurang M

  25. […] for some reason you cannot use databars, then rely on in-cell bar charts. These are simple to setup and works great in many situations where conditional formatting may not […]

  26. Xun says:

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

Leave a Reply