+ Reply to Thread
Results 1 to 4 of 4

How to apply custom number format to all data labels across many bar charts

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

    How to apply custom number format to all data labels across many bar charts

    Dear VBA experts,

    I'm writing to request your help with my excel chart bar issues. I itinially had posted it here,
    http://https://www.excelforum.com/excel-charting-and-pivots/1298414-how-to-remove-0-0s-from-excel-bar-chart-displays.html#post5239481

    Two of the excel experts recommended that I apply a custom number format to all the data labels, like this: 0.0;-0.0;; to make sure that the bar charts do not display 0.0 when they are not supposed to be displayed, which was very helpful.

    My problem is, I have many more charts like this that occur within a single worksheet, across worksheets and workbooks, and I wonder if there's a way to apply some kind of vba programming so that it could do this at once, rather than for me to go into each data label, change each custom number format, go to the next bar chart, and do this like hundreds of times. Not only this is time consuming but error prone as well (I just know that I will miss some of the data labels if I have to go through so many!)

    Would someone be able to help me with the vba code for this? I don't know anything about the vba and would not be able to do this on my own.

    I'm reattaching the same data sheet I had attached in the earlier post for your convenience.


    Thank you for your attention on this.

    Sincerely,

    Anita

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2016 primarily
    Posts
    4,860

    Re: How to apply custom number format to all data labels across many bar charts

    I guess that answers my question in your other thread. This macro should do the trick:

    Please Login or Register  to view this content.
    Rory
    I drink, and I know things

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

    Re: How to apply custom number format to all data labels across many bar charts

    Good morning, Rory,

    Why, this is amazing!!! I tested these and they worked. I don't understand most of it, but one step at a time. Thank you so much for your VBA expertise because it will save me tons of hours from painstakingly going through each data label and changing one by one.

    And I now saw your question from my other thread . Sorry about that. I think I panicked for not figuring out a solution right away so I spent a bunch of time on the internet searching for a solution and missed your question.

    Have a wonderful day,

    Anita

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2016 primarily
    Posts
    4,860

    Re: How to apply custom number format to all data labels across many bar charts

    Glad to help, and thanks for the rep.

    The code itself is just a loop through each sheet, then within that loop through each chart on each sheet, and through each series on each chart to format the datalabels.

+ 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. Formating number type in charts data labels (removing zeros)
    By campelliann in forum Excel General
    Replies: 1
    Last Post: 07-11-2015, 08:44 AM
  2. Paste values in a column, then apply custom number format
    By Excel_vba in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-23-2013, 05:17 AM
  3. Automatically Apply Labels to Excel Charts
    By mbuckley56 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-01-2012, 11:52 AM
  4. Replies: 2
    Last Post: 02-17-2011, 02:13 AM
  5. How do I put custom data labels on charts in Excel?
    By mattgoof2005 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 07-26-2006, 08:35 PM
  6. Replies: 0
    Last Post: 05-09-2006, 10:45 AM
  7. Custom format dynamically linked data labels
    By ucamms in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 01-29-2005, 02:15 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