Display symbols in excel chart axis

Posted on August 21st, 2008 in Charts and Graphs , hacks , ideas , Learn Excel - 9 comments

show symbols in chart axis labelsHere is a quick excel tip to make your charts look professional by showing symbols in the axis labels instead of text.

Just follow these 4 simple steps:

  • First identify the symbols you want to show on the axis or as data label from symbol fonts like webdings, wingdings or a custom dingbat font like company logos

    dingbat-fonts-symbols-excel-howto

  • Now use this symbol characters as axis labels
  • Finally change the font to the selected symbol font
  • Really there is no 4th step, so go ahead add a little jazz to the chart. Happy Friday!

Some more charts with symbols used as axis labels / data labels.

axis-symbols-excel-chart-howto-images

Also try: Min-max charts to tell the story better, Incell graphs with colors, hot looking thermometer chart

Your email address is safe with us. Our policies

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

9 Responses to “Display symbols in excel chart axis”

  1. Robert says:

    Nice idea. Thanks for posting.

    But you might be confronted with a problem – especially with the company logo fonts – if you pass on your spreadsheet to somebody else and the font is not installed on his computer. Results: unpredictable.

    As far as I know there is no way to embed fonts into the worksheet itself (unlike PowerPoint).

    Or is there a way to embed fonts into excel-worksheets?

  2. Chandoo says:

    @Robert..

    agree, excel doesnt have a good way to share fonts along with workbook. But, this could be more of a copy the chart and paste it as image use. Of course the webdings/wingdings has good set of symbols to start.

    let us see if some reader has a good way to spread fonts through spreadsheets :)

  3. Sam Krysiak says:

    You can embed an Excel workbook in a Word document, which itself has the font embedded. This is a little cumbersome, but it’s the only way I’ve found as yet of embedding non-system corporate dingbat fonts (or, for that matter, any fancy in-cell graph fonts you use – see http://chandoo.org/wp/2008/05/07/create-in-cell-pie-charts-in-excel-how-to/) into something that is readable, editable and printable to users that don’t have the font installed.

    In your Word document, choose Options from the Tools menu. From the Options dialog, select the “Embed TrueType Fonts” check box. Embed your Excel workbook; when you save, the font is embedded in the document, and accessible in the workbook.

    This only works with TrueType fonts, and only those where the font designer has designated them fully embeddable; Word honours the font designer’s security designation, even if you choose to embed them.

  4. Robert says:

    I found a solution using vba:

    The vba code ‘embeds’ the font into a very hidden worksheet in a first step. Whenever the workbook is opened, vba “creates” the font and installs it on the computer. When closing the workbook, vba uninstalls the font again (though: the font file itself is not deleted).

    I am not quite sure whether this is really the way it is working. I couldn’t find a website that explains the concept and the vba code. I only found the link to the download site:

    http://home.fuse.net/tstom/EmbedFontIntoExcel.zip

    It seems to work at least for the example (harvey balls) the developer has been using. I haven’t tested it with other fonts.

  5. Chandoo says:

    @Sam Krysiak: Thanks for that excellent tip, even though I post few ideas that involve installing fonts, I rarely use them when spreading the sheet as it involves considerable extra work. But using word to share it can be very handy.

    @Robert, that is a sneaky little way to get around the font issue. Thanks for sharing it with us :)

  6. Bryce Digdug says:

    That is a great idea to past the Excel file into a word document. Word supports embedded TrueType fonts though you have to go to Preferences (?) and check this option for the document. I don’t believe Excel allows embedding of TrueType fonts so if you use a non-company-standard font people will read it in their default font. To get around this I convert to PDF.

  7. Jeff weir says:

    I never noticed this before, but there’s an ‘easter egg’ in Excel 2007. Apparently, Jon Peltier managed to sneak his company logo into Webdings (character 143). You can see how close it is to his ‘web 2.0′ version at http://peltiertech.com/

    He must know some people…

    Here’s some code from the web to help dump the entire Webding set into a spreadsheet. Once it’s run (and it takes quite a while) then just copy the range and paste somewhere adjacent, and select the copied range and change font to Webdings using the font toolbar. Then do the same for Webdings 2 and Webdings 3. MUCH faster than running this macro 3 more times for different sets.

    Sub ShowFont()
    ‘from http://www.eggheadcafe.com/forumarchives/Excelprogramming/Jan2006/post25848963.asp Ed Ferrero
    Dim i As Integer
    Dim rngStart As Range
    Set rngStart = Range(“A1″)
    With rngStart
    For i = 1 To 2000
    .Offset(i – 1, 0) = i
    .Offset(i – 1, 1) = Chr(i)
    .Offset(i – 1, 2) = Chr(i)
    .Offset(i – 1, 2).Font.Name = “Webdings”
    Next
    End With
    End Sub

    John Walkenbach makes the point in ‘Favourite Excel 2007 Tips and Tricks’ that Excel’s native CHAR() function only returns the first 255 characters in a font, whereas Unicode fonts contain many more. He suggests a simple User Defined Function, as follows to display more than just the first 255:

    Function Char2(code)
    Char2 = ChrW(code)
    End Function

    I used this in a worksheet, and copied it down 65535 rows (at which point I got a #VALUE! error).

    Looking at the fonts it returns, there’s a lot that are (or seem) empty, there’s a lot that just return a box, and there are A LOT of asian characters. I’m glad we’ve just got 26 characters in which to do SUMPRODUCT functions in English!

    Some of these are really cool, and I can see how some might be interesting for the right project. Perhaps I’ll write it up and post it when Chandoo’s busy changing nappies. But for now, here’s a few favourites below.

    Plug these numbers into the Walkenbach function, or look up their bracketed Arial Unicode reference (where most of the fonts seem to reside )
    -A very happy snowman at 9731 (or Arial Unicode MS no. 2603)
    – The skull and crossbones at 9760 (or Arial Unicode MS no. 2620)
    – The old soviet hammer and sickle at 9773 (Arial Unicode MS no. 262D, ironically placed right next to the Peace symbol at 262E)
    – Great cleavage at 5729
    A man in what appears to be a French Foreign Legion hat at 12320 (or Arial Unicode MS no. 3020, with the name ‘postal mark face’??)
    – a funny little beatle at 3424

    Those kids at school were right…I am such a nerd…

    Later

  8. Chandoo says:

    @Jeff… wow.. very cool. I didnt know this exists.. I will test it when I get sometime…

  9. Alexandre Cardoso says:

    It didn’t work on charts from Pivot Table. Isn’t it possible?

    Thanks.

Leave a Reply