[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 241
  • Last Modified:

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

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
MassiveQuantums
Asked:
MassiveQuantums
  • 2
  • 2
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
MassiveQuantumsAuthor Commented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
MassiveQuantumsAuthor Commented:
Thanks for the help!
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now