Call for help - Excel chart whizzes!

Hey folks, despite trying to look for solutions online I seem to be getting nowhere with a charts query in Excel. I wonder whether anyone on here could possibly help please.

I have a spreadsheet which pulls together data from a variety of hospitals. I have a chart to show the data. However, the number of hospitals in the data changes depending on various conditions met by other formulas.

So let's say for example my chart range currently covers cells A1:B21. At present I have 20 hospitals plus the header row, and the chart range covers it perfectly. However, if I adjust my settings (for example I decide to only show smaller hospitals), there might now only be ten hospitals plus the header row, so technically my data are only showing in rows A1:B11 (as my formulas are set to show blanks in the rows afterwards).

However, my chart range is still covering A1:B21, and there are a load of empty bars on my chart with nothing in them. Sure, I can re-adjust my range manually, but this is a pain.

Does anyone know whether there are any settings in the charts somewhere which will completely ignore empty rows, ie fill all the available space with the rows which have data, even if there are a load of blanks afterwards? This would make my life sooo much easier.

Thanks in advance - hope it's possible!

It should be possible, creating a dynamic range name and getting the chart to use that name, it's not something I've done before, but I have found a few references.

When you show smaller hospitals to get data in cells A1:B11, are cells A12:B21 all blank? And in cells A1:B11 are there any blank cells? In other words, are all the empty bars at the end of the range (at the right hand end of the chart)?

Can you share a simple example table showing perhaps 10 rows or so, first with 10 hospitals, and then another example showing how the smaller list would look? What chart type are you using - XY, Line, Bar, ?

I think would a pivot chart will work?

You will just need to refresh the chart each time to update there is a change.

Did someone mention Pivot Tables!

No, pivot charts

it will work so whats the problem?

I'm sure pivot tables/charts will work but I think you need to explain to OP how to do it.

Using dynamic ranges is a bit fiddly but works. Assume we have a range of values in B2:B21 that we want to plot just as a simple bar chart without referencing any other values, eg: in A2:A21 - if we do want to reference those, we need to set up another dynamic range.

Click on the Formulas ribbon and select Define Name. Create a new name, eg "plot" (without the quotes) and in the Refers to: box type =OFFSET(Sheet1!$B$2,0,0,COUNT(Sheet1!$B$2:$B$21),1) and press OK. This creates the dynamic range that contains all the cells with values in and ignore any empty cells at the end.

Then right click on your chart and choose Select Data... and then in the Horizontal (Category) Axis Labels box on the right, click Edit. In the box, type =Test.xlsx!plot and press OK. Note that you have to put in the filename, not the worksheet name, so you may need single quotes around the filename.

Now as the number of rows with actual data in changes, the chart will change (you might need to press F9 to update if the number of rows reduces), but it won't work if there are any "empty" rows in the middle of other rows with data.

However this will only work if the empty cells contain text (eg: some empty text) or =NA() for example, if they contain a zero value it will treat it as a value to plot. To exclude zeroes, change the COUNT(Sheet1!$B$2:$B$21) to COUNTIF(Sheet1!$B$2:$B$21,">0") for example.

Possibly.

Select the data set (for the max range required) click add pivot chart on the ribbon which on my version was next to the usual charts. Select a location for the pivot table/chart (either the same sheet or a new one). Once done drag the x category (hospitals?) to the the bottom left box and the values into the bottom right box of the pivot table/chart setup. Right click the value in the bottom right box and select sum rather than count. I think from memory that’s it.
You can deselect any 0/blacking value from the table by unchecking them in the pivot tables via the drop down box. If you do this also update the x category in the right box to include new values in manual selections - it’s a check box on the drop down.

This will work with any data and not matter if there are gaps and/or blanks/non blanks etc - you can just exclude any data you want if needed.

Cheers folks, most helpful!

(though you'd think this would be an option that would come as standard!)