I have the following data in a pivot table:
Row Labels Inbound MoM % Change In Outbound MoM % Change Out Total MoM % Change Total
Jan | 872 | 1,130 | 2,002 | |||
Feb | 891 | 2.18% | 929 | -17.79% | 1,820 | -9.09% |
Mar | 1,010 | 13.36% | 1,192 | 28.31% | 2,202 | 20.99% |
Apr | 939 | -7.03% | 1,498 | 25.67% | 2,437 | 10.67% |
May | 790 | -15.87% | 1,897 | 26.64% | 2,687 | 10.26% |
I have created a combo chart from the data, with the "MoM % Change" (Month-over-Month) data in a line graph on a secondary axis. What I would really like to do is to show only the 3 value columns, with a label above each column with the value of the "MoM % Change". Preferably, the value would be green if positive and red if negative, and ideally it would be accompanied by a green or red triangle, pointing in the appropriate direction.
Is this doable, and can anyone here help me?
Thanks in advance!
Hi Tom,
Please create a small mock-up Excel file containing your PivotTable and sample data. From there we can try to help you. I suspect you might have to revert to creating a regular chart from the PivotTable.
Mynda
Mynda, thanks. Attached is a sample Excel file with data and a pivot table. I will be very interested to see how this may be done! I should mention that I am using Excel 2016.
Tom
Hi Tom,
Thanks for providing the file and clarification as to what version of Excel you have.
In the file I've attached here you'll see what I think is what you asked for, although you weren't clear on which 3 value columns you wanted, so I took a guess.
I used custom number formats with symbols to show the directional arrows and colour coding. Personally I think the overall effect is too cluttered, even if you choose better shades of green and red for the colour coding.
Nonetheless, I hope that points you in the right direction.
Mynda
Mynda, I agree with you, and your reference to the Custom Number Format and Custom Chart Labels articles gave me what I needed. A pivot table is unnecessary also.
I have attached the revised spreadsheet, and everything is as I want it, EXCEPT that I cannot get the up arrows to be green and the down arrows to be red.
I created a custom number format, using 1 decimal place, but when I go to format data labels, my custom format is not there, only one that has no decimal places, and in any case it doesn't change the color of my labels.
Hopefully you can determine whether I am missing something.
Thanks,
Tom
Hi Tom,
Chart custom number formats are added via the Format Data Labels > Number pane. To enable this pane, right-click the chart labels > Format Data Labels. In the Label Options tab go to the Number area and you'll be able to choose the category 'Custom' and then add your number format.
Mynda
Hi Mynda, thanks for the clarification. I added the number format to the chart labels. My only problem now (refer to the earlier attachment "Call-Volumes-Sample-3.xlsx) is that, on Sheet1 I am using the % Change as Category Name, rather than Value, so it does not respond to the Custom Number Format. Is there a way around that, or do I need to manually change the color of each label?
Hi Tom,
Category names are text and therefore cannot have a custom number format applied to them that is dependent on a number value. You need to set your chart up the way I did in my example file, or change them manually.
Mynda