Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How can I make an Excel Stacked Column Chart Legend dynamically adjust to varying rows of data?

Posted on 2014-12-30
4
Medium Priority
?
220 Views
Last Modified: 2015-01-03
I've successfully setup a stacked column chart in Excel which is populated by an SQL script.  There's a cosmetic issue with the legend.  If the script returns five rows (a maximum for this instance) the graph legend looks great; however, with fewer rows (depending on the output of the script), the graph displays a legend entry "square" with no description.

Is there a way to keep the graph (via a setting perhaps) from displaying the empty legend entry automatically?

I've included a print screen image to hopefully clarify the issue.

Thanks in advance for your assistance!
0
Comment
Question by:MassiveQuantums
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 50
ID: 40524478
Hello,

no screenshot in your post.

In general, any series in the chart will by default show in the legend. There is no setting to hide unused (empty) series.

The only thing that could work without modifying the chart source or the legend is to filter the empty rows so they don't show in the data source. Filtered rows will be ignored by the chart and series in the filtered rows will not appear in the legend.

The following screenshot shows a chart with blank values for series 'dd'. The legend shows the color for dd

unfiltered
Using the filter drop-down, I unticked the "Blank" option, which results in the same chart display, but the legend omits the filtered row (series) in the legend.

filtered
cheers, teylyn
0
 

Author Comment

by:MassiveQuantums
ID: 40524500
Graph with and without data rows.
Sorry, didn't upload the file properly.  Note that the "NM1" and "MT1" are dropped; however, the colored squares still remain in the legend.

Thanks for the post Teylyn.  I know about the filtering aspect; however, this is part of an automated process, so I wouldn't be able to manually filter on a case-by-case basis.

Perhaps that clarifies the issue.  Thanks again!
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 40524882
If you cannot filter the source data, you need to edit the legend and remove the two entries. There is no "setting" that you can use to do that.

If the source data changes and has data in the series that were previously empty, then delete the legend and add it again. This way all series will be present in the legend and you can then remove the ones you don't need.

These are the steps. If you want to automate them you would need VBA.

cheers, teylyn
0
 

Author Closing Comment

by:MassiveQuantums
ID: 40529906
Thanks for the help!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question