Column Chart with Variable number of rows of data

I am trying to create a chart that can have between 5 and 20 rows of data. If I create the chart as large as it could be, i end up with a bunch of 0 columns because those rows are empty.

Attached is an excel showing 3 charts. The first two show how I want the table to look when it has 9 rows of data and 18 rows of data. The third chart shows what happens when i set the chart for 18 rows but only have 9 rows of data. I want that third chart to automatically ignore the rows that are blank, but it does not. The hidden and empty cell option does not seem to help.

Hi,

I have added a new sheet in the attached excel sheet and created a dynamic chart. However, this works only if you keep "Locations" in the X Axis. Please see if this suits your requirement.


Regards,
Chandra



Please click on ‘ * Add Reputation’ button on the left side bottom of my post if I was helpful in resolving the issue.

Hi,

An alternate way:

1. Format the Data Range as Table
2. Create a new bar chart after selecting the table
3. As and when you insert new rows into the data range, the chart gets auto updated.

I have shown this in Sheet3 of the attached file and hopefully this should suit your requirement.


Regards,
Chandra

The first option solved it perfectly. Name Manager was exactly what I needed.

Originally Posted by cbatrody

Hi,

I have added a new sheet in the attached excel sheet and created a dynamic chart. However, this works only if you keep "Locations" in the X Axis. Please see if this suits your requirement.


Regards,
Chandra



Please click on ‘ * Add Reputation’ button on the left side bottom of my post if I was helpful in resolving the issue.

Follow up question: Is there a way to make the width of the chart dynamic?

Yes there is, you mean you would have more number of Test columns in the table and the number of rows will be constant.

I want the charts visual area to be wider or smaller based on the number of rows of location data.