Solved

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

Posted on 2014-12-30
4
215 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 500 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

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.
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

627 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