I am trying to represent ages of a population as a bar chart I've ,ade itby using a pivot tabel so I've also split the bars by sex.
Excel is charting the age as a data label, but I want to treat it as a numeric axis so that ages which haven't got any values are marked on there too.
You need to convince the Pivot Cache that these categories exist even when there is no data for them.
Add some fake data to your dataset temporarily, with one entry for every age you think should be in your age range (eg 1 to 120, or 18 to 100 or whatever is appropriate to your context).
Refresh the PivotTable / PivotChart
Right click the PivotTable / PivotChart > Options > Data tab For "Number of items to retain per field" select "Max". This means the PT will no longer "forget" categories that are not in the dataset each time round. But you won't see them yet.
Right click the Age field in the PivotTable, or use the drop-down in the field selection pane > Field Settings > Layout & Print. Select "Show items with no data"
Remove the fake data and refresh your PivotTable.
Easier than adding bogus data to the Pivot Table source data.
Make a Regular Chart from the Pivot Table.
Below I have a simple pivot data range and Pivot Table.
Select a blank cell that is surrounded by other blank cells and is not touching the Pivot Table, and insert a stacked column chart (top-left chart, which is totally blank).
Right-click the chart, choose Select Data. Click Add, click on the cell containing "M" (E3) for Series Name and the cells containing the first data column (E4:E13) for Series Values, and click OK. Then click on Edit under Category Axis Labels, and select the ages (D4:D13). This produces the top-right chart.
Back in the Select Data dialog, click Add again, select the cell containing "F" (F3) for Series name and the range containing the second data column (F4:F13) for Series Values, and click OK (bottom-left chart).
Finally, click on the horizontal axis and hit Ctrl+1 (numeral one) to format it. Under Axis Options > Axis Type, select Date Axis. This treats the ages as day numbers, and includes a slot even for missing ages in the Pivot Table.
