I have an existing spreadsheet and macro process I'm trying to improve, and I have multiple tables which refresh for each client, and can have between 3 and 6 rows of data (depending on whether someone has enough history to fill all 6 rows). There is a chart attached to the table which works fine when all 6 are there, but when there are fewer rows, the chart leaves a blank space on the X-axis to account for that table row.
Example of the table when full
Quarter Value
2021Q1 10
2021Q2 7
2021Q3 4
2021Q4 2
2022Q1 1
2022Q2 0
Example of the table when less than full
Quarter Value
2021Q3 4
2021Q4 2
2022Q1 1
2022Q2 0
On the second table, there will be places on the X-axis for values, rather than knowing there's no data and only showing 4 places
Is it possible for excel charts to refresh dynamically depending on what is in the source? I have found plenty of guides for what to do with the chart lines when there is no value, but not to ignore a whole row from the report.