Go Premium for a chance to win a PS4. Enter to Win

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
?
227 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
  • 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

824 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