Search code examples
excelexcel-formulaexcel-charts

Excel chart - how to hide blank series?

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

enter image description here

Solution

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

    1. Small table enter image description here

    2. Bigger table (the year has a formula to autofill). Dragging the corners of the table, the chart updates.

    enter image description here

    You can download the file from here: https://link.nithins.me/zV0is0Ub

    Steps:

    1. In the column Year put characters, not numbers
    2. Select any cell in the range (or table) and insert a chart. Because the first column is text, the chart recognizes it as label series.
    3. Switch Row/Column in the chart's property.
    4. Put back the years as they were before. If you have a table and you write a formula like =2020+ROW()-ROW(A$1), then as you add a new row, it will calculate the new year.

    Hope this helped.