MassiveQuantums
asked on
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!
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!
ASKER
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the help!
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
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.
cheers, teylyn