Can I add Chart label that is a count?

jbaich

Board Regular
Joined
Nov 2, 2011
Messages
132
Hi all, I have made a pivot chart and I would like to add a label above each bar that shows the number of records that make up that bar... I have the count in my pivot table and field list, but can't figure out if/how i can use it as a label without embedding into the chart as a series or axis variable... I'd insert an image of my chart, but can't seem to figure out how to do that :(

Thanks,
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

macfuller

Active Member
Joined
Apr 30, 2014
Messages
270
Office Version
365
Platform
Windows
It seems to work the same as for a regular chart. Choose "Format Data Labels" and under Label Options unclick the other options and choose Value From Cells, then highlight the range in your pivot table holding the Count you want the labels to reflect. You may have to readjust if the pivot table size changes.
 

jbaich

Board Regular
Joined
Nov 2, 2011
Messages
132
Thanks for the reply macfuller, but I seem to be misunderstanding something... I've uploaded a sample workbook to dropbox if that helps... https://www.dropbox.com/s/rs9sah24sypxxeq/Chart.xlsx?dl=0

I assumed from you response that i should make a second pivot table to drive the chart labels of Count, but I haven't had any luck with that approach either... in the sample workbook, I am trying to get the count corresponding to Code and year to display at the top of each column... I don't know how I'm not getting this...

Thanks,
Joe
 

macfuller

Active Member
Joined
Apr 30, 2014
Messages
270
Office Version
365
Platform
Windows
You can either write a measure to have the count as another column in your first table (not including it in your chart as a data series) or you can use your 2nd pivot. If you use the 2nd pivot table you need to format the 2nd pivot to have the same number of rows as the first pivot table. Get rid of the year subtotals (click the down arrow next to "Code" in the row window, choose Field Settings, and set subtotals to None) and go to tabular format so the year is on the same row as the first count value.
Then you can use the instructions above to select the range in the 2nd pivot table as the data label values. It's not immediately clear from the data if you have separate counts for the North and South regions or if the counts apply to both. If region needs to be separate then in your 2nd table add the region as a column so you can add data labels to each series as appropriate.
 

Watch MrExcel Video

Forum statistics

Threads
1,100,191
Messages
5,473,041
Members
406,843
Latest member
David_Welland

This Week's Hot Topics

Top