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?
0
T (153)
1/18/2005 10:17:03 PM
excel.charting 18370 articles. 0 followers. Follow

4 Replies
537 Views

Similar Articles

[PageSpeed] 56

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?
> 
> 
0
ColleenT (1)
1/19/2005 9:17:09 PM
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?
>>
>>

0
1/19/2005 9:23:02 PM
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?
> >>
> >>
> 
> 
0
MWField (1)
1/21/2005 10:01:06 PM
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?
>>>>
>>>>
>>

0
1/22/2005 1:41:33 AM
Reply:

Similar Artilces:

How to select series in chart?
I know I asked this question before, but (sigh) I cannot find the answer now, when I need it of course. How can I select a series in an Excel chart (XY Scatter) using the keyboard, not the mouse? The issue is: I have overlapping series, so it is difficult for me to select a series by moving the mouse cursor to a point in one series and right-clicking it, as I normally do. Someone once mentioned a ctrl and/or shift key combination (I think) that would allow me to select each series explicit in round-robin fashion. That is what I am looking for again. More generally, how could I have found...

double clicking and draging a column in a chart to chg data
in Excel 2003, double clicking on a column in a chart and then dragging the column up or down would change the data in a table upon which the chart depended. How does one do this in excel 2007? Tom Hi, That feature has been removed in 2007, there is no way to do it. If this helps, click the Yes button. -- Thanks, Shane Devenshire "Tom of inns" wrote: > in Excel 2003, double clicking on a column in a chart and then dragging the > column up or down would change the data in a table upon which the chart > depended. > > How does one do this in excel 2007? &g...

Linking a Chart with graphics to ppt
Dear group, when I create a chart with scatters, which carry graphics like Excel-Block-Arrows and link this to Powerpoint, the graphics are shown there in a very low quality with large pixels. Any ideas how to improve this? Thanks in advance, Holger. ...

PA Misc. Log Tables
Hi, How can I find the difference between Billed and Un-billed Miscelleneous Logs? Where Can I see Un-billed Misc. Logs and which tables. Hari I have a spreadsheet with the PA tables listed in it. Send an email message to charles.zarzour@intergraph.com and I will send you the spreadsheet. I do an excel spreadsheet for each Dictionary. Makes it easy to lookup tables. "Hari" wrote: > Hi, > How can I find the difference between Billed and Un-billed Miscelleneous > Logs? > Where Can I see Un-billed Misc. Logs and which tables. > > Hari > > Can I ...

chart MAcro to change on activecell
Here is the macro below.. The only problem is that the columns change every month. so range (columns)keeps widening.... I have sorted it by selecting range upto column N. so it is provided for all 12 months. But now the "Grand total" column which is always the last column also gets included..(.which shouldnt be included in the range for the chart) Is there a way to modify this macro ? Sub updatechart1() Dim ThechartObj As ChartObject Dim Thechart As Chart Dim Userrow As Long Dim CatTitles As Range Dim SrcRange As Range Dim SourceData As Range If Sheets("summary").Ch...

Multiple stacked bar charts
I have a project where i have to chart the compliance of three groups over a number of months. So for each data set there is a Compliance#1 stacked upon NonCompliance#1, then Compliance#2 stacked upon NonCompliance#2 and finally, you guessed it, Compliance#3 stacked upon NonCompliance#3. When i design a chart in excel it wants to place everything on top of each other producing one column of six not the required three columns of two for each week in the display. Can anyone point me i the right direction for this one?? Hi you can achieve this effect by having two layers in your x axis the at...

transparent areas in charts?
How do you create transparent areas in imbedded charts? (for Excel 2003). When I click on "Format Chart area", then "Fill effects", there is a place where you can change the transparency settings, but it is faded out and will not work. Any Ideas? You can make most elements totally transparent. Ignore the transparency button, which is only for decoration. Instead, if you set an element's area to none, the element will be transparent. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech....

printing 2 charts on single page
Using Excel 2k, Win 2k I was trying to print to line charts on a single page to demonstrate the corelation between the two data sets. I would like to print in portait mode with one chart on top of page and one on bottom. Excel only seems to offer putting chart 2 on a separate page or embedding it in chart one. I am not very familiar with excel charting capabilites can anyone suggest a resorce? Thanks ...

I cannot get the pictures to show on screen in Publisher web previ
I have tried embedding and linked but nothing seems to work. Do I have to apply a master page? It is to be a one page web site. This is probably so basic but I am trying to teach myself how to use it. What do I have to do to have them show in preview and once uploaded? Thank you. Are you using FireFox to preview or IE? DavidF "rendul" <rendul@discussions.microsoft.com> wrote in message news:C9B0A640-6C87-4E9A-8A0E-5F32F7DAC0DE@microsoft.com... >I have tried embedding and linked but nothing seems to work. Do I have to > apply a master page? It is to be a one ...

Can I show a number like 1,935,674,923 as 1.9B?
I would like to reformat 10- and 11-digit the numbers (e.g. like 1,935,674,923 as 1.9B). Can this be done? Custom format: 0.0,,,"B" best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Dave" <Dave@discussions.microsoft.com> wrote in message news:262241F3-063E-4F97-B864-A629EC670112@microsoft.com... > I would like to reformat 10- and 11-digit the numbers (e.g. like > 1,935,674,923 as 1.9B). Can this be done? Dave, Use a custom format of #.#,,,"B" or 0.0,,,"B" depending on ho...

visual basic
Hi, I trying to retrieve values from a table to calculate the 14days average value of a stock closing price. However, i encounter some problem as stated beside the code as follows: Function DaysAvgs() 'Calculate the average value of a given value. Dim db As DAO.Database Dim rst As DAO.Recordset Dim varBookmark As Variant Dim numAve, numDaysAvg As Double Dim intA, intB, lngCount As Integer Set db = CurrentDb 'Open Table Set rst = db.OpenRecordset("SGX Individual Historical", dbOpenTable) rst.MoveFirst Do While Not rst.EOF intA = 1 intB = 0 varBookmark = rst.Bookmark n...

Macro for Sharepoint List and another Table?? Same # records?
I am creating a database which we have a form made up of prepopulated data (from a sharepoint list) and also data input needed by a user (for example, comments, etc.). I have two tables: one sharepoint list that is linked to sharepoint website and another table that houses the additional user inputs [table 2]. I have linked the files based on the primary key. So, every record in my table 2 has a plus sign and it will expand and you will see the linked data from the sharepoint list. Is there a way that I can automatically create a record for every corresponding record ...

Showing 3:54PM instead of 3:54:03PM
How do you get rid of the seconds in the time area. I have changed the formatting in the time. I use the excel file as a data source. I include the time in the mail merge. It always shows up with the seconds in the time. Very frustrating. HELP PLEASE!! TJ it may be formatted as text, so won't respond to changing the time format. if it is text, the TIMEVALUE formula will convert it to a decimal-based time value which can then be formatted by using Excel's normal Number formatting-- to get rid of the seconds. Eddie O "TJ" wrote: > How do you get rid of the secon...

Report to show Item Class Distribution Amounts
We would like to create a report, using Crystal Reports, that would show the following: dollar amount break down of the Sales Distribution accounts (COGS and Sales) per item class based on a date range. What is the most accurate way of going about this? We could only think of this method: (in short) sum the Ext Price based on SOP30300.CSLSINDX and SLSINDX and hope it matches the SOP10102 summed distribution amounts. Any advice would be appreciated. Thanks in advance. With the SLSINDX you would use the Extended Price and the CSLSINDX you would use Extended Cost. You would probably ...

Doesn't show busy,when scheduling meeting
Hello I have a user, who has meetings scheduled and shows up in her Calendar fine. But, when she or someone else tries to schedule new meeting, invite her, it doesn't show her busy schedule. It indicates as if she is available all time. Secondly, when she edits the meeting and marks as "Out of Office", than it shows up as busy. Her Out of Office is Off. Outlook 2000 with Exchange corporate setup. Any help will be appreciated. Thanks ...

COUNTIF on Summary Fields
Can't use on a cell that "sums" with arithmetic operators "+". Is there a workaround, etc.? a bit more explanation? -- Don Guillett SalesAid Software donaldb@281.com "DLC" <dlcopesr@yahoo.com> wrote in message news:117v954kaf4s2f6@corp.supernews.com... > Can't use on a cell that "sums" with arithmetic operators "+". Is there a > workaround, etc.? > > ...

Calculating totals
I have two columns on a worksheet: "Project Codes" and "Totals." I would like to have columns on another worksheet that will automatically total up the different project numbers "A,B,C,etc." How do I do that? Thanks in advance for your help, Technically Handicapped Enter a *unique* list of your "Project Codes", starting in A2 of Sheet2. In B2, enter this formula: =SUMIF(Sheet1!A:A,A2,Sheet1!B:B) And copy down as needed. -- HTH, RD ===================================================== Please keep all correspondence within the G...

item class table
I am creating SOP IM import. I need to fill the distribution fields with a rev account that is part of the item class. I would like to find a table that would hold the item class accounts. I looked in IV40400 and did not see any distribution accounts. What is the best table to pull these accounts. If the accounts have been defined on the Item Class, they will appear on the records in the IV40400 table. They're in the fields IVIVINDX, IVIVOFIX, etc - and they're just the keys to the actual account definitions in the GL00100 table. If a particular account type isn't defined ...

Cell with large amount of data not showing all data
I'm running Excel 97. I have a cell with 358 words (1928 characters with spaces). Word wrap is on for the cell. Only part of the text is displayed even though the cell is big enough to show everything. If I make the cell wider (wider than a page) more of the text shows but not everything. I tried a new worksheet with the same text and had the same problem. Is this a known issue with excel? Is there a solution? Thanks, Brad Left to its own devices, excel will only show about 1000 characters in a cell. But you can add some alt-enters (to force a new line within the cell) and see more s...

total group & max function
I am using Access 2003 I created a Db for Machine Mtce. The table consists of 3 tables, (1) Basic Mach Infor (ie Mach#, Mach Name, Mfg etc) - (2) Operations Table - consists of different prev mtce (PM) that need to be completed at various time frames. Ex: "Mach 100" could have 3 different operations; Oper # 100A to change the oil due every 90 days, Oper # 100B to check the belts due every 180 days, Oper # 100C to grease the machine due every 365 days. The 3rd table is the "PM History" table which includes the history of all of the PM's completed with Work Order...

Adding blank "separator" Rows in a Pivot Table
Does anyone know how to insert blank rows into a pivot table (say, between groupings, etc.)? There has to be a way, since some of the various table formats that come with Excel include blank rows. Double-click the field button that you want to format Click Layout Add a checkmark to Insert blank line after each item Click OK, twice Bucko wrote: > Does anyone know how to insert blank rows into a pivot table (say, > between groupings, etc.)? There has to be a way, since some of the > various table formats that come with Excel include blank rows. -- Debra Dalgleish Contextures http...

copy chart from Excel to Powerpoint
I am trying to copy a chart from Excel to Powerpoint, but the button is grayed out and it won't let me copy it. You can convert the chart to a GIF (a picture) with my Excel Objects converter. http://www.geocities.com/excelmarksway "Hoffperson" wrote: > I am trying to copy a chart from Excel to Powerpoint, but the button is > grayed out and it won't let me copy it. ...

Help! charts disappearing, new charts crashing powerpoint
When i open a presentation the charts vanish...i have restarted, they come back. I try to edit a chart, and it crashes powerpoint after a few clicks. What can I do...whole afternoon going crazy! If you are using v 2007 there is a hotfix which is meant to cure some chart crashing problems http://support.microsoft.com/default.aspx?scid=kb;en-us;976479&sd=rss&spid=11264 -- john ATSIGN PPTAlchemy.co.uk Free PPT Hints, Tips and Tutorials http://www.pptalchemy.co.uk/powerpoint_hints_and_tips_tutorials.html "UKExcelgeek" wrote: > When i open a pres...

basic pivot table group
I have looked at some basic Pivot Table tutorials, but I have a very basic question not answered by their examples. . . . I have data in a spreadsheet formatted like this, each row representing an event: Timestamp Process Message ID 11:23:45:123 processA message123 11:23:45:124 processB message123 11:23:45:136 processC message123 11:23:46:123 processA message456 11:23:47:123 processB message456 11:23:47:678 processC message456 Can a pivot table help me reformat the data like below? ...

Sum items in a lookup table.
I work for a bus company that has 240 different routes. I also have a table in Excel that lists monthly ridership for each route. I use it as a lookup table to get data for another report. The problem? There are 6 of the 240 routes that are served by more than one garage so they appear twice in the lookup table and I would like to get the sum of the ridership for that particular route from the table. Is this possible and if so how do I do it? I gather you're using a lookup function now to find the ridership from a given route. VLOOKUP will return (0 or) 1 value. If you use SU...