SOLVED
Home

Pie Chart - legend missing one category (edited to include spreadsheet)

%3CLINGO-SUB%20id%3D%22lingo-sub-923870%22%20slang%3D%22en-US%22%3ERe%3A%20Pie%20Chart%20-%20legend%20missing%20one%20category%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-923870%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F412052%22%20target%3D%22_blank%22%3E%40kkb_IHC%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EManaged%20to%20recreate%20your%20issue%20and%20suspect%20that%20you%20only%20copied%20the%20data%20into%20the%20graph%20but%20not%20the%20label.%20Please%20have%20a%20look%20at%20the%20attached%20screenshot.%20In%20the%20first%20graph%20I%20only%20added%20the%20yellow%20cell%20and%20created%20the%20issue%20that%20you%20describe.%20In%20the%20second%20graph%20I%20added%20the%20yellow%20cells%20and%20all%20looks%20how%20you%20want%20it%20to%20be.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERight%20click%20in%20the%20chart%20and%20press%20%22Select%20data%20source%22.%20Make%20sure%20that%20the%20range%20for%20%22Horizontal%20(category)%20axis%20labels%22%20includes%20all%20the%20labels%20you%20want%20to%20be%20included.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPS%3A%20I'm%20working%20on%20a%20Mac%2C%20so%20your%20screens%20may%20look%20a%20bit%20different.%20But%20you%20should%20be%20able%20to%20find%20the%20horizontal%20axis%20settings%20as%20describe%20above.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-923880%22%20slang%3D%22en-US%22%3ERe%3A%20Pie%20Chart%20-%20legend%20missing%20one%20category%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-923880%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%20I%20am%20selecting%20the%20cell.%20My%20data%20and%20their%20related%20categories%20are%20all%20over%20the%20worksheet%2C%20so%20I%20used%20CTRL%20and%20pick%20each%20one%20separately.%20My%20other%20pie%20charts%20are%20working%20fine%2C%20as%20far%20as%20this%20issue%20goes%2C%20other%20than%20one%20that%20has%20the%20same%20issue.%20I%20will%20probably%20just%20need%20to%20delete%20those%20two%20and%20start%20over.%20I%20had%20started%20from%20one%20chart%20initially%2C%20got%20it%20the%20size%20I%20wanted%2C%20etc.%2C%20and%20then%20copied%20it%20a%20bunch%20of%20times.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-924120%22%20slang%3D%22en-US%22%3ERe%3A%20Pie%20Chart%20-%20legend%20missing%20one%20category%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-924120%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F412052%22%20target%3D%22_blank%22%3E%40kkb_IHC%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOkay.%20Upload%20your%20chart%20if%20you%20can't%20get%20it%20to%20work.%20Than%20I%20(or%20someone%20else)%20can%20hav%20a%20look%20at%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-924141%22%20slang%3D%22en-US%22%3ERe%3A%20Pie%20Chart%20-%20legend%20missing%20one%20category%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-924141%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUnfortnately%20my%20workbook%20has%20all%20sorts%20of%20patient%20data%20in%20it%20so%20I%20can't%20upload%20it.%20I'll%20see%20if%20I%20have%20time%20to%20make%20a%20copy%20of%20just%20the%20data%2C%20without%20the%20related%20formulas%2C%20to%20upload.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-923040%22%20slang%3D%22en-US%22%3EPie%20Chart%20-%20legend%20missing%20one%20category%20(edited%20to%20include%20spreadsheet)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-923040%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20set%20of%20pie%20charts%20and%20when%20I%20add%20data%20to%20some%20of%20them%2C%20there%20is%20one%20category%20name%20that%20is%20missing%20in%20the%20legend%20(it%20is%20also%20missing%20in%20the%20%22Horizontal%20(category)%20Axis%20Labels%2C%22%20see%20below)%20even%20though%20I%20have%20selected%20it%20a%20million%20times!%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F138787i6AE1F4E918435EDE%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_1.png%22%20title%3D%22clipboard_image_1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20data%20for%20this%20category%20does%20show%20up%20in%20the%20pie%20chart%20and%20the%20color%20for%20the%20category%20also%20shows%20up%20in%20the%20legend%20(see%20below).%20I%20tried%20putting%20the%20category%20name%20on%20the%20data%20label%20to%20see%20if%20it%20would%20show%20up%2C%20and%20it%20did%20not%20show%20up%20there%20either.%20I've%20also%20tried%20making%20the%20category%20name%20short%2C%20removing%20the%20legend%20%26amp%3B%20adding%20it%20back%2C%20and%20making%20the%20legend%20larger.%20None%20of%20these%20fixed%20it.%20I%20also%20deleted%20all%20the%20category%20labels%2C%20selected%20all%20new%20ones%2C%20and%20all%203%20did%20show%20up.%20When%20I%20selected%20the%20real%20ones%20again%2C%20that%20one%20does%20not%20show%20up.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F138801i56223949DAE1A499%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_3.png%22%20title%3D%22clipboard_image_3.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThis%20is%20driving%20me%20crazy.%3C%2FP%3E%3CP%3EPlease%20help.%3C%2FP%3E%3CP%3Ethanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-923040%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECategory%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Epie%20chart%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-924514%22%20slang%3D%22en-US%22%3ERe%3A%20Pie%20Chart%20-%20legend%20missing%20one%20category%20(edited%20to%20include%20spreadsheet)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-924514%22%20slang%3D%22en-US%22%3EExcel%20is%20getting%20confused%20by%20your%20merged%20cells.%20If%20possible%2C%20unmerge%20the%20cells%2C%20and%20link%20the%20label%20to%20a%20single%20cell.%3CBR%20%2F%3EIf%20you%20don't%20want%20to%20unmerge%2C%20then%20change%20the%20label%20refs%20in%20the%20series%20formula%20for%20the%20chart.%3CBR%20%2F%3EClick%20on%20the%20pie%20chart%2C%20and%20in%20the%20formula%20bar%2C%20change%20the%20merged%20cell%20refs%20to%20a%20single%20cell%20ref%3A%3CBR%20%2F%3E--%20instead%20of%3A%20%24O%2421%3A%24S%2421%20put%20%24O%2421%3CBR%20%2F%3E--%20instead%20of%3A%20%24O%2420%3A%24S%2420%20put%20%24O%2420%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-924548%22%20slang%3D%22en-US%22%3ERe%3A%20Pie%20Chart%20-%20legend%20missing%20one%20category%20(edited%20to%20include%20spreadsheet)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-924548%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F183572%22%20target%3D%22_blank%22%3E%40Debra%20Dalgleish%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%20I%20did%20try%20un-merging%20them%20to%20see%20if%20that%20was%20the%20problem%20and%20it%20did%20not%20fix%20the%20issue%2C%20but%20I%20see%20now%20that%20the%20ones%20I%20un-merged%20were%20not%20the%20ones%20having%20the%20issue.%20Your%20suggestion%20has%20fixed%20the%20problem.%20I%20can't%20thank%20you%20enough!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-926978%22%20slang%3D%22en-US%22%3ERe%3A%20Pie%20Chart%20-%20legend%20missing%20one%20category%20(edited%20to%20include%20spreadsheet)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-926978%22%20slang%3D%22en-US%22%3EYou're%20welcome!%20Thanks%20for%20letting%20me%20know%20that%20it's%20working%20now%3C%2FLINGO-BODY%3E
kkb_IHC
Occasional Contributor

I have set of pie charts and when I add data to some of them, there is one category name that is missing in the legend (it is also missing in the "Horizontal (category) Axis Labels," see below) even though I have selected it a million times! 

clipboard_image_1.png

The data for this category does show up in the pie chart and the color for the category also shows up in the legend (see below). I tried putting the category name on the data label to see if it would show up, and it did not show up there either. I've also tried making the category name short, removing the legend & adding it back, and making the legend larger. None of these fixed it. I also deleted all the category labels, selected all new ones, and all 3 did show up. When I selected the real ones again, that one does not show up.

clipboard_image_3.png

This is driving me crazy.

Please help.

thanks!

7 Replies

@kkb_IHC 

Managed to recreate your issue and suspect that you only copied the data into the graph but not the label. Please have a look at the attached screenshot. In the first graph I only added the yellow cell and created the issue that you describe. In the second graph I added the yellow cells and all looks how you want it to be.

 

Right click in the chart and press "Select data source". Make sure that the range for "Horizontal (category) axis labels" includes all the labels you want to be included.

 

PS: I'm working on a Mac, so your screens may look a bit different. But you should be able to find the horizontal axis settings as describe above.

@Riny_van_Eekelen 

Thank you. I am selecting the cell. My data and their related categories are all over the worksheet, so I used CTRL and pick each one separately. My other pie charts are working fine, as far as this issue goes, other than one that has the same issue. I will probably just need to delete those two and start over. I had started from one chart initially, got it the size I wanted, etc., and then copied it a bunch of times.

@kkb_IHC 

 

Okay. Upload your chart if you can't get it to work. Than I (or someone else) can hav a look at it.

 

@Riny_van_Eekelen 

Unfortnately my workbook has all sorts of patient data in it so I can't upload it. I'll see if I have time to make a copy of just the data, without the related formulas, to upload.

Solution
Excel is getting confused by your merged cells. If possible, unmerge the cells, and link the label to a single cell.
If you don't want to unmerge, then change the label refs in the series formula for the chart.
Click on the pie chart, and in the formula bar, change the merged cell refs to a single cell ref:
-- instead of: $O$21:$S$21 put $O$21
-- instead of: $O$20:$S$20 put $O$20

@Debra Dalgleish 

Thank you. I did try un-merging them to see if that was the problem and it did not fix the issue, but I see now that the ones I un-merged were not the ones having the issue. Your suggestion has fixed the problem. I can't thank you enough!

You're welcome! Thanks for letting me know that it's working now
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies