If you like Pivot Tables, you will also be using Pivot Charts and, because they add information, you may also use the Data Table feature which shows the actual numbers below the chart. The problem relates to the Data Table number formats not working. If you try and custom format the numbers it ignores it and if you have numbers in millions it seems to only differentiate the first thousand.
Chart Data Table number formats not working
As shown below, the Pivot Table is showing the correct format of the numbers (in thousands), the Pivot Chart axis seems to be correct, yet the Data Table at the bottom only shows the thousands separator the first time and it is showing the full number again.
You have very little control over the formatting of the Data Table. As a result you will need to trick Excel.
Custom Chart Data Table number formats
In this case the chart can’t show the data in thousands. In order to get the Data Table to show in thousands the workaround is to create a calculated field in the Pivot Table that takes the numbers and divides by 1 000.
You can learn more about Calculated fields in Pivot Tables (you create formulas INSIDE a Pivot Table) in the Advanced Course, but below the simple formula used to create an additional field in the number format we want.
These numbers can then be pulled into the Pivot Table and therefore the Pivot Chart and the Data Table will work.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.