none
Excel Chart Incorrect RRS feed

  • Question

  • Windows 10, Latest update, Office 2019, Excel

    The goal is to create some charts about COVID infections.  Three columns are selected: Date, Infected Daily Change, Death Daily Change.  The second two columns have been formatted as a number with no decimal places and thousands separators.  Neither of them are formatted as percentage.  As the image shows, Excel builds the chart as a percentage.

    Notice that the Y axis is labeled as a percentage.  Those columns are NOT percentages.  What must be done to convince Excel that these columns are not formatted as percentages.

    Here is a view just before, or after, creating the chart, showing the second two columns selected.

    Please also notice the column header.  Excel uses "Series 1" and "Series 2"  How can it be coerced into putting the actual value from the top into the chart legend.  When columns I and K are charted, the legend is correct.

    Thursday, May 14, 2020 2:43 AM

Answers

  • Hi bkelly77,

    >> Please note from the images, there are headers, not blank, and the columns selected are NOT percentage.

    << Did you merge 6 cells from I to N for green text "United States"? To select Columns J and M, do you select the cells start cells of "United States"? or just start from cells of "Infected Daily Change" and "Death Daily Change"?

    << When I choose the cells start from text of "Infected Daily Change" and "Death Daily Change", the legend show as expected and desired.

    Besides, I could not reproduce your issue with number format error, maybe there is something wrong with your excel file, please copy all data to a new blank sheet, keep values, then insert the chart for a test.

    Regards,

    Emily


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    • Marked as answer by bkelly77 Tuesday, May 19, 2020 1:28 AM
    Monday, May 18, 2020 9:02 AM
    Moderator
  • re: Did you merge 6 cells from I to N for green text "United States"?

    Yes I did. Based on your question I un-merged them and copied the text to each of the unmerged cells.  That corrected the problem.

    It worked for the first column so I presumed the same for the others.

    Thanks for your assistance.

    Edit:  After thinking a bit I presume this is not an unusual situation. There are some number of repeating column headers, grouped, several at a time, under a larger header.  In this case, the larger headers are: World Wide, United States, California, etc.  Under each major header is a set of sub headers, identical for each group.  In this case:  Infected, Daily Change, %Change, Deaths, Daily Change, %Change.

    The worksheet looks much better when the top headers are merged into one cell.

    So: What is the proper way to title the columns of this type of worksheet?


    • Marked as answer by bkelly77 Tuesday, May 19, 2020 1:28 AM
    • Edited by bkelly77 Tuesday, May 19, 2020 2:40 AM
    Tuesday, May 19, 2020 1:28 AM

All replies

  • Hi bkelly 77,

    1. Please click the Y axis > Format Axis > Number, check whether the Category is Number, or you may check the box of "Linked to source".

    2. To show the column headers in legends, we need to choose the data start from J2 and M2, make sure the ranges start without empty cells, but with the column headers.

    Based on the existed chart, you could also go to Select Data > Edit the series to change legend names.

    If you have any updates, please feel free to let us know.

    Regards,

    Emily


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Thursday, May 14, 2020 8:20 AM
    Moderator
  • The format you mentioned was hidden but with your guidance I found it and made that change.


    Please note from the images, there are headers, not blank, and the columns selected are NOT percentage.  I delete and create the charts daily.  Two to four each for each of World number, United States numbers, California Numbers, and two more.  When columns I (Total Infected) and L (Total Deaths) are selected the number type and the legend are as expected and desired.  When the adjacent columns are selected, J and M, the number type is wrong and the series name is wrong.

    Is there something I can do different to get it right the first time?

    Thursday, May 14, 2020 1:40 PM
  • Hi bkelly77,

    >> Please note from the images, there are headers, not blank, and the columns selected are NOT percentage.

    << Did you merge 6 cells from I to N for green text "United States"? To select Columns J and M, do you select the cells start cells of "United States"? or just start from cells of "Infected Daily Change" and "Death Daily Change"?

    << When I choose the cells start from text of "Infected Daily Change" and "Death Daily Change", the legend show as expected and desired.

    Besides, I could not reproduce your issue with number format error, maybe there is something wrong with your excel file, please copy all data to a new blank sheet, keep values, then insert the chart for a test.

    Regards,

    Emily


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    • Marked as answer by bkelly77 Tuesday, May 19, 2020 1:28 AM
    Monday, May 18, 2020 9:02 AM
    Moderator
  • re: Did you merge 6 cells from I to N for green text "United States"?

    Yes I did. Based on your question I un-merged them and copied the text to each of the unmerged cells.  That corrected the problem.

    It worked for the first column so I presumed the same for the others.

    Thanks for your assistance.

    Edit:  After thinking a bit I presume this is not an unusual situation. There are some number of repeating column headers, grouped, several at a time, under a larger header.  In this case, the larger headers are: World Wide, United States, California, etc.  Under each major header is a set of sub headers, identical for each group.  In this case:  Infected, Daily Change, %Change, Deaths, Daily Change, %Change.

    The worksheet looks much better when the top headers are merged into one cell.

    So: What is the proper way to title the columns of this type of worksheet?


    • Marked as answer by bkelly77 Tuesday, May 19, 2020 1:28 AM
    • Edited by bkelly77 Tuesday, May 19, 2020 2:40 AM
    Tuesday, May 19, 2020 1:28 AM
  • Hi bkelly77,

    I am glad to hear your problem has gone away. Now I will make a brief summary according to your post.

    ====================

    Issue Symptom:

    1. The format of values on Y-axis are different from source data.

    2. The chart legend could not be created automatically.

    ====================

    (Possible) Cause:

    The title of table is in a merged cell, it easily causes Excel misidentified the range that we select to create charts.

    ====================

    Solution:

    Un-merge cells.

    ====================

    Regards,

    Emily


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.


    Tuesday, May 26, 2020 8:46 AM
    Moderator