+ Reply to Thread
Results 1 to 16 of 16

Data labels not displayed correctly

  1. #1
    Registered User
    Join Date
    07-07-2019
    Location
    India
    MS-Off Ver
    2016
    Posts
    12

    Data labels not displayed correctly

    I have a 3 series stacked bar chart and the fourth series is a data label. The data label is a date value that selects values from the date column. The Primary axis is categorized based on 2 values. The secondary axis is Month. The data labels are displayed accurately as per the month except the 3 labels.

    The first series is the difference between F and E.The second series is the difference between the J and K column. The third series is the difference between L and M column. The fourth series is a date column with the data label displayed on the stacked bar chart.

    The three data labels alone are not displaying below the exact Month and what might be the reason for this?

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Data labels not displayed correctly

    How is this thread different from your other thread here?
    https://www.excelforum.com/excel-cha...ata-chart.html
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    07-07-2019
    Location
    India
    MS-Off Ver
    2016
    Posts
    12

    Re: Data labels not displayed correctly

    This thread is about the data labels not accurately displayed as per the Month in the axis. The thread that you are pointing to is about the colour in the data labels according to a specified condition.

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

    Re: Data labels not displayed correctly

    I am having trouble visualizing what you have. It appears that you have a bar + something combination chart with both primary and secondary axes. I would guess that the problem is in a difference between the primary and secondary axes, but I cannot be more specific without seeing your chart.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Registered User
    Join Date
    07-07-2019
    Location
    India
    MS-Off Ver
    2016
    Posts
    12

    Re: Data labels not displayed correctly

    QA Series : Difference between two columns ( QA Start and QA End)

    MFG Series: Difference between two columns ( MFG Start and MFG End)

    LGS Series : Difference between two columns (LGS Start and MFG End)


    1) If there are values only for the GO SALE column, the values getting displayed only at the front irrespective of choosing INSIDE BASE. I need to forcefully move the labels by entering a dummy date in the other columns.

    I need to go everytime and click the labels and edit the date I want to display the range of dates.

    2) LGS Series: If the MFG End and LGS Start - For example: For the Row 1, it ends at 11 July 2019 and GO SALE 2 DATE displays immediately after 11 JULY 2019 instead of it displayed at 16 JULY 2019

    3) I need to display range of date values in the labels in the chart.

    What I did: Initially all the cells in the SALE column were set to DATE. Now, I format SALE Column, to be of Text / General, include the range of date values, set the primary primary horizontal axis and secondary axis to be of TEXT / GENERAL but when I update the graph with new data, I need to change the columns to date format with a single date entry and thereby need to change the axis to DATE as well.


    I am not able to attach files or insert images. I do have png images for the chart as well as for the data.
    Last edited by hawkbase85; 08-06-2019 at 06:12 AM.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Data labels not displayed correctly

    Why are you unable to attach your workbook?

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  7. #7
    Registered User
    Join Date
    07-07-2019
    Location
    India
    MS-Off Ver
    2016
    Posts
    12

    Re: Data labels not displayed correctly

    I did that. I am neither able to attach or insert images. I could only select images and there is no option for OK in the dialog box

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Data labels not displayed correctly

    Why are you unable to? What message are you getting? There is no restriction on attaching workbooks for any new member - only size could be an issue.

  9. #9
    Registered User
    Join Date
    07-07-2019
    Location
    India
    MS-Off Ver
    2016
    Posts
    12

    Re: Data labels not displayed correctly

    If I try to attach by clicking the attach icon, there are nothing below it and just a blank white column. If I try to insert images, I am getting options either to insert the images from Computer or URL and after selecting it, when I click enter, nothing happening and there is no option to click OK in the dialog box once I select the images.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Data labels not displayed correctly

    Then you are not following the instructions that I gave you in post #6. Here they are again:

    Unfortunately the attachment icon doesn't work at the moment (it hasn't worked for years, and despite our repeatedly asking the technical team who own the forum to fix it, they can't be bothered to do so), so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

  11. #11
    Registered User
    Join Date
    07-07-2019
    Location
    India
    MS-Off Ver
    2016
    Posts
    12

    Re: Data labels not displayed correctly

    I am attaching two files.
    Attached Images Attached Images

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Data labels not displayed correctly

    No - please attach the workbook itself - NOT images.

  13. #13
    Registered User
    Join Date
    07-07-2019
    Location
    India
    MS-Off Ver
    2016
    Posts
    12

    Re: Data labels not displayed correctly

    I am attaching my workbook

    QA Series : Difference between two columns ( QA Start and QA End)

    MFG Series: Difference between two columns ( MFG Start and MFG End)

    LGS Series : Difference between two columns (LGS Start and MFG End)


    1) If there are values only for the GO SALE column, the values getting displayed only at the front irrespective of choosing INSIDE BASE. I need to forcefully move the labels by entering a dummy date in the other columns.

    I need to go everytime and click the labels and edit the date I want to display the range of dates.

    2) LGS Series: If the MFG End and LGS Start - For example: For the Row 1, it ends at 11 July 2019 and GO SALE 2 DATE displays immediately after 11 JULY 2019 instead of it displayed at 16 JULY 2019

    3) I need to display range of date values in the labels in the chart.

    What I did: Initially all the cells in the SALE column were set to DATE. Now, I format SALE Column, to be of Text / General, include the range of date values, set the primary primary horizontal axis and secondary axis to be of TEXT / GENERAL but when I update the graph with new data, I need to change the columns to date format with a single date entry and thereby need to change the axis to DATE as well.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    07-07-2019
    Location
    India
    MS-Off Ver
    2016
    Posts
    12

    Re: Data labels not displayed correctly

    Any solution?

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

    Re: Data labels not displayed correctly

    I don't think I have a final solution for you, but I can explain why the data labels are positioned where they are, and maybe that will allow you to look at your spreadsheet and see a solution.

    The first things I did were to format the "invisible" elements so I could see them. Add a legend, format the invisible data series to something visible, format the primary and secondary axes so I can see them, and add vertical gridlines so I can more easily pick out the dates at the end of each stacked bar. The idea is that I'm not going to be able to debug this unless I can see what is going on inside the chart.

    With everything visible, I go through each data series and identify which data series are on the primary axis and which are on the secondary axis. After going through the identification, I note that both of the series with data labels are on the primary axis, so I know that I am going to be focusing on the data series that are on the primary axis (mfg start, mfg calc, lgs calc, go sale 1, and go sale 2). That is also the order they seem to be stacking to the right of each other in the stacked bar chart. go sale 1 and go sale 2 are the data series with the labels, and the data labels are formatted to display at the left end of the bar.

    Understanding what I am looking at, now I start comparing values in the spreadsheet with values in the chart to see what is going on. I start with item 9 and note that mfg start, mfg calc, lgs calc, and go sale 1 are all empty string "" (which the chart will interpret as 0). The only non-zero value is go sale 2 which is 43700 (the serial number for 13 aug 2019). So, I can see that the bar correctly extends to 13 aug '19, and the data label is positioned at the left end of the bar and, being the only bar in the stack, is next to the left side of the chart.

    Turning to item 8, mfg start is 19 July '19, and it's bar extends to that date correctly. mfg calc is 24, so its bar is 24 days long, ending at 12 aug '19. lsg calc and go sale 1 are both 0, and go sale 2 is 43700+, so it adds a very long bar that extends off the right side of the chart. The data label (17 aug '19) is placed at the left end of the bar (the left end of the bar is at 12 aug, so it is not as much in error as item 9). If needed, you can continue with the other items.

    I don't have a quick solution. Ultimately, the problem is that the left starting end of the go sale 1 and go sale 2 bars are not positioned at the date indicated by the data label. Fixing the problem is a matter of figuring out how you want to insure that the left starting end of these two series is in the correct position. Maybe spend some time with the mfg calc and lgs calc formulas in K and L and see if they need to be changed. Or perhaps add another column/data series (that will put another invisible bar in between lgs calc and go sales 1 and 2). The formula in this column will need to look at the other columns, account for the ""/0 values and compute a value that will correctly position the start of go sales 1 and 2.

    Are the formulas in K and L correct, or should they be edited? Or would you prefer to add another column to the spreadsheet and data series to the chart? What help will you need with your chosen solution?

  16. #16
    Registered User
    Join Date
    07-07-2019
    Location
    India
    MS-Off Ver
    2016
    Posts
    12

    Re: Data labels not displayed correctly

    First of all, apologies for not responding in time. Thanks for the awesome support. I din't have computer / internet access. I will post in detail this weekend for what you have asked for as I am restoring my laptop from the back up.

+ 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. [SOLVED] Data labels in chart being displayed only if over a certain $ amount
    By avidcat in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 05-25-2013, 01:55 PM
  2. chart data is not displayed correctly
    By eran3185 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 01-18-2013, 05:30 AM
  3. UK Date not displayed correctly in cell
    By ES_Jake in forum Excel General
    Replies: 6
    Last Post: 10-09-2009, 04:36 PM
  4. max labels displayed
    By emm8080 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 05-14-2009, 12:42 PM
  5. Chart data labels are not correct or not displayed
    By Bolton User in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 04-11-2006, 12:30 PM
  6. How to add data labels from a series not displayed on a chart
    By doctor bm in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-28-2006, 08:55 PM
  7. Save as .xml not displayed correctly
    By Anthony in forum Excel General
    Replies: 1
    Last Post: 09-21-2005, 07:05 AM

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