How do I show summary totals from a pivot table on a bar chart

I created a pivot table which shows the both individual and grand totals
based on the fields I selected. I then created a bar chart based on this
pivot table. What I would like to do is show the grand totals as well as the
individual totals. Is this possible, if so how do I do it?

Colleen -

You can add textboxes to the chart, which are linked to worksheet cells. Select the
chart, press the equals key, then navigate to and click on the cell and press Enter.
The contents of the cell are displayed in a new textbox in the middle of the
chart. Move and format this textbox, and add the next one.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Colleen T wrote:

> I created a pivot table which shows the both individual and grand totals
> based on the fields I selected. I then created a bar chart based on this
> pivot table. What I would like to do is show the grand totals as well as the
> individual totals. Is this possible, if so how do I do it?

When I try this (link to the pivot table's summary total) I get an error
saying not a valid function. The function line contains
=GETPIVOTDATA("Personnel no.",'P2'!$A$3)

"Jon Peltier" wrote:

> Colleen -
>
> You can add textboxes to the chart, which are linked to worksheet cells. Select the
> chart, press the equals key, then navigate to and click on the cell and press Enter.
> The contents of the cell are displayed in a new textbox in the middle of the
> chart. Move and format this textbox, and add the next one.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
> Colleen T wrote:
>
> > I created a pivot table which shows the both individual and grand totals
> > based on the fields I selected. I then created a bar chart based on this
> > pivot table. What I would like to do is show the grand totals as well as the
> > individual totals. Is this possible, if so how do I do it?
>
>

Oh yeah, I forgot about that PITA. You have to type in the cell reference if the
cell resides within a pivot table, because a formula in a textbox or other chart
element must refer only to a range address or a defined name. You need the sheet
name also, so after pressing =, type something like this:

=Sheet1!A1

if the sheet name has spaces, you need to surround it with single quotes:

='Sheet One'!A1

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Colleen T wrote:

> When I try this (link to the pivot table's summary total) I get an error
> saying not a valid function. The function line contains
> =GETPIVOTDATA("Personnel no.",'P2'!$A$3)
>
> "Jon Peltier" wrote:
>
>
>>Colleen -
>>
>>You can add textboxes to the chart, which are linked to worksheet cells. Select the
>>chart, press the equals key, then navigate to and click on the cell and press Enter.
>> The contents of the cell are displayed in a new textbox in the middle of the
>>chart. Move and format this textbox, and add the next one.
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>Colleen T wrote:
>>
>>
>>>I created a pivot table which shows the both individual and grand totals
>>>based on the fields I selected. I then created a bar chart based on this
>>>pivot table. What I would like to do is show the grand totals as well as the
>>>individual totals. Is this possible, if so how do I do it?
>>
>>

Hello,

You’re on the right track to putting automatically updated grand totals on
your pivot charts, but there are a couple of problems you might encounter
with that technique.

Here’s one solution that admittedly lacks elegance, but works. It’s what
you would expect to be able to do in one step, but Excel won’t permit that.
It works just the way you want if you do it in two steps, strangely.

Step 1: Create another worksheet in your workbook as a staging area for
the totals you want to collect. Eg.) For human resource work force
analysis:

| A | B
--+---------------------+--------------------------------------------------------
1 | Total # of Poets | For each row in this column, enter the equals
sign, and
| | point at the wily grand total you want to capture
on the
| | PivotTable worksheet. The result will be something
like:
| | =GETPIVOTDATA(….)
--+---------------------+--------------------------------------------------------
2 | Total # of Skiers | =GETPIVOTDATA(….)
--+---------------------+--------------------------------------------------------
3 | Total # Employees | =GETPIVOTDATA(….)
--+---------------------+--------------------------------------------------------

Step 2: As per Jon’s instructions, go to the Pivot Chart and click anywhere
to select the chart. Create a calculated text field by entering = in the
formula bar, and point at the column B value in the intermediate worksheet.
These totals will change automatically and correctly as you change the values
of the dynamic fields on your pivot chart and pivot table.

Why does this work when we’re not permitted to enter the GETPIVOTDATA
function directly in the pivot chart formula bar? Why is the sky blue?
This is also the way to get around other restrictions on the functions you
can use in that context, for example, putting the current date on a report
with the TODAY() function.

Regards,
Margaret


"Jon Peltier" wrote:

> Oh yeah, I forgot about that PITA. You have to type in the cell reference if the
> cell resides within a pivot table, because a formula in a textbox or other chart
> element must refer only to a range address or a defined name. You need the sheet
> name also, so after pressing =, type something like this:
>
> =Sheet1!A1
>
> if the sheet name has spaces, you need to surround it with single quotes:
>
> ='Sheet One'!A1
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
> Colleen T wrote:
>
> > When I try this (link to the pivot table's summary total) I get an error
> > saying not a valid function. The function line contains
> > =GETPIVOTDATA("Personnel no.",'P2'!$A$3)
> >
> > "Jon Peltier" wrote:
> >
> >
> >>Colleen -
> >>
> >>You can add textboxes to the chart, which are linked to worksheet cells. Select the
> >>chart, press the equals key, then navigate to and click on the cell and press Enter.
> >> The contents of the cell are displayed in a new textbox in the middle of the
> >>chart. Move and format this textbox, and add the next one.
> >>
> >>- Jon
> >>-------
> >>Jon Peltier, Microsoft Excel MVP
> >>Peltier Technical Services
> >>Tutorials and Custom Solutions
> >>http://PeltierTech.com/
> >>_______
> >>
> >>Colleen T wrote:
> >>
> >>
> >>>I created a pivot table which shows the both individual and grand totals
> >>>based on the fields I selected. I then created a bar chart based on this
> >>>pivot table. What I would like to do is show the grand totals as well as the
> >>>individual totals. Is this possible, if so how do I do it?
> >>
> >>
>
>

Hi Margaret -

> Why does this work when we’re not permitted to enter the GETPIVOTDATA
> function directly in the pivot chart formula bar?

The only place you can put formulas that need to calculate something or use some
kind of look up function (which GETPIVOTDATA is) is in the worksheet, either in a
worksheet cell, or in a defined name. Any other Excel object that accepts formulas
only works with formulas that are basically simple links. Textboxes, titles and data
labels in charts, these all need just a qualified cell address.

By manually entering the cell references into the textbox links, I was hoping to
avoid the intermediate GETPIVOTDATA formulas.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

MW Field wrote:
> Hello,
>
> You’re on the right track to putting automatically updated grand totals on
> your pivot charts, but there are a couple of problems you might encounter
> with that technique.
>
> Here’s one solution that admittedly lacks elegance, but works. It’s what
> you would expect to be able to do in one step, but Excel won’t permit that.
> It works just the way you want if you do it in two steps, strangely.
>
> Step 1: Create another worksheet in your workbook as a staging area for
> the totals you want to collect. Eg.) For human resource work force
> analysis:
>
> | A | B
> --+---------------------+--------------------------------------------------------
> 1 | Total # of Poets | For each row in this column, enter the equals
> sign, and
> | | point at the wily grand total you want to capture
> on the
> | | PivotTable worksheet. The result will be something
> like:
> | | =GETPIVOTDATA(….)
> --+---------------------+--------------------------------------------------------
> 2 | Total # of Skiers | =GETPIVOTDATA(….)
> --+---------------------+--------------------------------------------------------
> 3 | Total # Employees | =GETPIVOTDATA(….)
> --+---------------------+--------------------------------------------------------
>
> Step 2: As per Jon’s instructions, go to the Pivot Chart and click anywhere
> to select the chart. Create a calculated text field by entering = in the
> formula bar, and point at the column B value in the intermediate worksheet.
> These totals will change automatically and correctly as you change the values
> of the dynamic fields on your pivot chart and pivot table.
>
> Why does this work when we’re not permitted to enter the GETPIVOTDATA
> function directly in the pivot chart formula bar? Why is the sky blue?
> This is also the way to get around other restrictions on the functions you
> can use in that context, for example, putting the current date on a report
> with the TODAY() function.
>
> Regards,
> Margaret
>
>
> "Jon Peltier" wrote:
>
>
>>Oh yeah, I forgot about that PITA. You have to type in the cell reference if the
>>cell resides within a pivot table, because a formula in a textbox or other chart
>>element must refer only to a range address or a defined name. You need the sheet
>>name also, so after pressing =, type something like this:
>>
>> =Sheet1!A1
>>
>>if the sheet name has spaces, you need to surround it with single quotes:
>>
>> ='Sheet One'!A1
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>Colleen T wrote:
>>
>>
>>>When I try this (link to the pivot table's summary total) I get an error
>>>saying not a valid function. The function line contains
>>>=GETPIVOTDATA("Personnel no.",'P2'!$A$3)
>>>
>>>"Jon Peltier" wrote:
>>>
>>>
>>>
>>>>Colleen -
>>>>
>>>>You can add textboxes to the chart, which are linked to worksheet cells. Select the
>>>>chart, press the equals key, then navigate to and click on the cell and press Enter.
>>>> The contents of the cell are displayed in a new textbox in the middle of the
>>>>chart. Move and format this textbox, and add the next one.
>>>>
>>>>- Jon
>>>>-------
>>>>Jon Peltier, Microsoft Excel MVP
>>>>Peltier Technical Services
>>>>Tutorials and Custom Solutions
>>>>http://PeltierTech.com/
>>>>_______
>>>>
>>>>Colleen T wrote:
>>>>
>>>>
>>>>
>>>>>I created a pivot table which shows the both individual and grand totals
>>>>>based on the fields I selected. I then created a bar chart based on this
>>>>>pivot table. What I would like to do is show the grand totals as well as the
>>>>>individual totals. Is this possible, if so how do I do it?
>>>>
>>>>
>>