Histogram from a pivot table - plot empty bins as having frequency 0

I've made a histogram from a pivot table by putting the the field "total (mm)" as my row labels. I've grouped these into bins of 20 and plotted against the "count of total (mm)". This produces a nice histogram except for the fact that it just doesn't display a bin if it is empty eg in the histogram, there is no data in the range 420-440mm and I would like it to plot a bar with 0 height but it tries to be helpful and plot nothing at all. Does anyone know how I can make it plot a bar with 0 height for an empty bin? I thought I could maybe enter some false data into my spreadsheet so that all bins have something in them but it seems like a really scrappy (not future-proof) way of doing it

I've attached the histogram that is generated and the pivot table behind it.

Thanks

Really hard to help from just pictures, which is probably why no response.

Try setting the Row Field options, Layout and Print > Show items with no data.

Pictures are not helpful for checking what setting you have used or seeing exactly what you data is.

At best they only allow people to guess at what you have setup. Luckily for you I guessed right

Seems like I have to decide between having axes which don't look right or missing bars, is this going to require VBA?

Correct. By display zero data, in order to get all the bins included, it means all the bins are included even if they are zero.

There is away around it but you need to add dummy records and add a new field.
The Value field is used as the data and the Total field is used for row labels. You can then remove the show zero items from field.

For each metal you would need a record for each band that the metal has data for.
So in the example Copper starts at 11 and has records for 16,21,26,31,36 and 41 bins.