Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 18th 05, 11:17 PM
Colleen T
 
Posts: n/a
Default 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?

  #2   Report Post  
Old January 19th 05, 07:45 PM
Jon Peltier
 
Posts: n/a
Default

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?


  #3   Report Post  
Old January 19th 05, 10:17 PM
Colleen T
 
Posts: n/a
Default

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?



  #4   Report Post  
Old January 19th 05, 10:23 PM
Jon Peltier
 
Posts: n/a
Default

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?




  #5   Report Post  
Old January 21st 05, 11:01 PM
MW Field
 
Posts: n/a
Default

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?






  #6   Report Post  
Old January 22nd 05, 02:41 AM
Jon Peltier
 
Posts: n/a
Default

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?






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
pivot chart format Valeria Charts and Charting in Excel 1 January 14th 05 02:19 PM
changing proportion of chart and data table Dawn Parks Charts and Charting in Excel 3 January 6th 05 02:18 AM
Jon Peltier - Pivot Table Result for yesterday's "Complex Chart" Question Barb Reinhardt Charts and Charting in Excel 3 December 8th 04 02:48 AM
create space in line chart between points, linked to pivot table Mike -Z- Charts and Charting in Excel 1 December 7th 04 10:39 PM
pivot table multi line chart souris Charts and Charting in Excel 2 December 7th 04 04:56 AM


All times are GMT +1. The time now is 07:22 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017