My application generates a report based on the template. In that application user can define Years range (column A). And application dynamically will add new rows.
This report has also Chart, and because of the dynamic row count I had to add selection for multiple rows. Everything works fine except one thing. Blank rows (series), are displayed on that chart's legend and bars have less space because of these empty bars. Is there any way to not displaying chart for blank series?
I was trying to use some macros, formulas etc. but without any success. Here is the link to the excel file
Transform your range into a table and restrict the chart to the table rows. If you add new data to the table, the table will extend automatically, and the chart will follow. There is no need to add extra rows in the chart for the years to come.
I am sorry for your lost time.
Is this solution acceptable?
Bigger table (the year has a formula to autofill). Dragging the corners of the table, the chart updates.
You can download the file from here: https://link.nithins.me/zV0is0Ub
Steps:
=2020+ROW()-ROW(A$1)
, then as you add a new row, it will calculate the new year.Hope this helped.