Format Data labels to get integers.

Question

Good evening,

My workbook uses slicers and it covers multiple years, which is why you see three Septembers. There is no problem if I am selecting just one year. It is when I select multiple years I get the problem. In this workbook I have a pivot table displayed as bar graph. In the example I provided I have values 32, 32, and 26. However, the label wants to display the value as .032, .032, and .026. I am trying to adjust the number to get the who integer. I can use the "Value from Cells", but that will include all the cells whether it includes blank cells or subtotals which I don't want. I only want a whole number to be shown which will match the value of the particular in the graph. Bottom line how can I get the numbers as whole numbers.

Thank you

Answer 1

Sounds weird.

I can only think of a setting issue. Please check File -->Options -->Advanced -->see highlighted text in the image

Answer 2

Re: change data label values

I couldn't find a method to change data label values on the chart.

You can use VBA to change the values and the changes should stick.

(you should only have to run the code once)

Be aware, I quit programming charts many years ago and the practice can

get very complicated. Also, I am unfamiliar with pivot table charts.

You have to specify the series number and the data label sequence number.

This code worked to revalue the second data label on series 1 in a sample

chart I opened.

'---

Sub test()

ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).DataLabels(2).Formula = (Range("A15").Value * 10)

End Sub

'---

Nothing Left to Lose

https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU

(free excel programs)