+ Reply to Thread
Results 1 to 7 of 7

How to remove 0.0s from excel bar chart displays

  1. #1
    Registered User
    Join Date
    03-04-2017
    Location
    NJ
    MS-Off Ver
    Excel for Mac Version 15.31
    Posts
    75

    How to remove 0.0s from excel bar chart displays

    Dear Excel Community,

    I have an excel bar chart (please see attached) that has 4 categories (boy, girl, different word, prefer not to answer), listed in Column B. Column C indicates their sample size, and column D indicates their scores. However, I wrote a formula in Column D (not shown in the datasheet) to only display the scores if N size in Column C is greater than 10. The bar chart then displays the scores of the categories accordingly, i.e., display the scores for those categories of N size greater than 10, with score values placed right next to the bars.

    My head scratching problem is that for the bars that are not displayed, there's a "0.0" that seem to serve as a placeholder for them instead. How do I get rid of these 0.0s? I tried to manually delete these 0.0s but what happens is that the next time I enter a dataset where the N sizes are greater than 10 for the last 2 categories, the values for the scores next to the bars are not displayed. How do get rid of the 0.0s so that when new datasets are selected, the bars would just show up with scores for N sizes greater than 10, and not show up at all (and without the 0.0s) ? I went into Format Data Labels and Format Data Series and messed with the settings, but nothing seemed to work.

    I hope someone can provide me some assistance to this problem. I have lots of charts like this, but if you show me how to do this with one chart, I can replicate the process with the rest of the bar charts. I may have to edit each chart individually so any advice on the shortcuts would be immensely appreciated.

    Thank you so much for your time and valuable feedback, as always!

    Anita

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,886

    Re: How to remove 0.0s from excel bar chart displays

    You can apply a custom number format to the data labels:

    0.0;-0.0;;

    which won't display anything when they are 0.
    Rory

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: How to remove 0.0s from excel bar chart displays

    I notice that the "boy" data labels are formatted with the custom number format code "0.0;0.0;" -- which should hide 0 values. My solution would be to apply this same number format to each of the other data labels. Because you have added each category as a separate data series, you must apply the number format to each data label individually (you cannot select all data labels and apply the number format to all in one step). With only 4 categories/series, it should not be too terribly tedious to do it one at a time.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    03-04-2017
    Location
    NJ
    MS-Off Ver
    Excel for Mac Version 15.31
    Posts
    75

    Re: How to remove 0.0s from excel bar chart displays

    Dear rorya and MrShorty,

    You guys are awesome! Thank you for your prompt feedback. I tested your suggestion and it works.

    However, I have many many more charts like that (like around 60) with even more categories than just the 4 I demonstrated in my worksheet. Is there a way to apply the custom number format code to all the data labels at once? It sounds like from MrShorty's comments that perhaps going through one by one is the way to go, but if there's some other suggestions to minimize the labor intensive part of this process and thereby making my edits less error prone, that would be amazing!

    Thank you again for everything!

    Anita

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: How to remove 0.0s from excel bar chart displays

    My first thought is to add the data to the chart as a single data series rather than a separate data series for each category. Of course, the applicability of this suggestion depends on why you chose to create the chart with each point added as a separate data series rather than to add all points as a single data series.

  6. #6
    Registered User
    Join Date
    03-04-2017
    Location
    NJ
    MS-Off Ver
    Excel for Mac Version 15.31
    Posts
    75

    Re: How to remove 0.0s from excel bar chart displays

    Absolutely, MrShorty! The excel was designed by someone else, where the many bar charts were supposed to represent many different categories of interest.

    Thank you again for your kind attention on my inquiry,

    Anita

  7. #7
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,886

    Re: How to remove 0.0s from excel bar chart displays

    You can certainly do it with code, if that’s of interest? If so, should they all use the same number format, and what would that be?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. bar on a column Chart Click Displays another Chart
    By art_a_game in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-18-2017, 05:55 AM
  2. Excel 2016 - Chart displays on screen but prints blank page
    By ahartman in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 10-15-2016, 06:10 AM
  3. Bar chart displays the same number with a gap
    By Linorl in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 10-26-2015, 04:50 AM
  4. Chart coded through VBA sometimes displays itself sometimes not.
    By seigna in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 05-07-2013, 11:29 AM
  5. Replies: 2
    Last Post: 11-02-2011, 10:55 AM
  6. Replies: 3
    Last Post: 03-23-2011, 09:45 AM
  7. [SOLVED] Scatter chart displays as line chart
    By Hannu in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 07-11-2006, 01:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1