Solved

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

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

Expert Comment

by:teylyn
Comment Utility
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
Comment Utility
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:
teylyn earned 500 total points
Comment Utility
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
Comment Utility
Thanks for the help!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now