Pivot Tables and Charts


May I please ask for your kind help?

I have 5 variables - a, b, c, d, and Fitness.  When I 
create a Pivot Chart, with Fitness=height, I obtain one 
chart with ONE surface.  

It is very useful to plot everything on one chart, however 
I would like to plot several surfaces on the chart. In 
other words, to me a surface is a 3D plot of variable 
Fitness vs variables a and b, holding values for variables 
c and d constant.  

Example 1.  Suppose variable c can take on 3 values, and 
variable d can take on 4 values.  This means that there 
can be 3*4=12 combinations of variables c and d.  For this 
case, I would like to display 12 surfaces on one chart.

Is this possible?

Example 2.  Here is another way to look at this problem.  
This problem shows up in 2D too.  For instance, below we 
have 3 Downward-Sloping lines, representing the behaviour 
of Fitness for different values of variable b:

   |\                 \
   |   \     \           \
   |      \     \           \
   |               \   
    a1 a2 a3 a1 a2 a3 a1 a2 a3
       b1       b2       b3

But, unfortunately, in Excel Pivot Charts only ONE 
surface/line is displayed [instead of 3 lines], as 
illustrated below:

   |\                 \
   |   \    /\       /    \
   |      \/    \   /        \
   |               \  
    a1 a2 a3 a1 a2 a3 a1 a2 a3
       b1       b2       b3

This makes the plot confusing and hard to understand.  
When one sees a zig-zag line like the one on the figure 
above, it is difficult to understand that the true pattern 
is really downward sloping [as variable a increases, for 
all values of variable b].

So, again my question is - how can we show separate 
surfaces [on one Pivot chart], instead of one confusing 

This is my first time using Pivot tables.  I have searched 
Excel help as well as the archived messages in this 
newsgroup, but I couldn't find any mention of this 
problem.  I am sure that the reason for this is that I 
don't know the right keywords to use...


9/5/2003 9:43:48 PM
excel.misc 78881 articles. 5 followers. Follow

0 Replies

Similar Articles

[PageSpeed] 18


Similar Artilces:

Subtotal in a pivot table
I have a question on calculating something in a pivot table. What I am trying to get is a % of the subtotal break in my table. I have a calculated field but cannot figure out how to get it to calc. the % off the shop total not the Total for the table. See the example below. For Shop 246-Database I want the Sum of % of shop total to be based off the Database DeliveryAmt (10575) / Shop 246 total DeliveryAmt (17317) = 61%, not the pivot table total (185852) = 5.7%. Retail should be Retail DeliveryAmt (6742) / Shop 246 total DeliveryAmt (17317) = 39%, not the pivot table total (185852)...

Pivot.sourcetable property incorrect?
I went to create a new pivot table in my workbook and a message came up advising me to use the same source as my existing table. I thought I *had* used the same source so before replying 'Yes', I thought I better check the source of my existing pivot table. The only way I could find to do that was VBA "msgbox activesheet.pivottables(1).sourcedata". To my surprise this showed as "ReportData!C1:C17" which I *know* is *wrong*. It should be something like 931 rows by 16 cols! Can anyone throw any light on this ie - Why would the sourcedata property not be ...

lookup tables across two workbooks
Hi I have two workbooks to which i wish to do a lookup for. The first wookbook contains totals via subtotals as below A1 0741.702.113 B1 $250.00 C1 EXPENSES A5 0745.753.112 B5 $2510.00 C5 BANK FEES The other workbook contains a vlookup to get the dollar amount from the first workbook and inserts into the second workbook.(column 2) It appears it doesn't work because the first workbook is total made up of subtotals with the other rows being hidden. Do i have to use the vlookup combined with match etc?? or maybe the format is the problem?? Thankyou in advance Darr...

excel series charts
Hi, I am trying to create a line chart in excel with two series. Conside the following data: np time1 time2 1 1000 100 2 900 90 3 850 80 4 700 70 5 600 60 6 400 30 7 300 10 8 270 5 The first series is plotted correctly but the second series i completely wrong. I was wondering if someone else has encountered suc a problem with two series charts. Can anyone suggest a solution to thi problem. thanks, sami -- samit70 ----------------------------------------------------------------------- samit700's Profile: http://www.excelforum.com/memb...

Pivot Table
I have a Pivot Table based on data that gets imported. The number of rows in the Pivot Data varies depending on the underying data. Below the Pivot Table are some calculations and then a couple more Pivot Tables. I'm building a Profit & Loss report and each Pivot table summarises the relevant report sections (e.g. Sales Pivot Table, Cost of Sales Pivot Table). The problem is, when the pivot table refreshes, it can overwrite formulas and tables below it if the table increases in rows. Any ideas/suggestions please to prevent this. Thanks T Hunt I don't know if you can preven...

Excel Charts #19
I am making a chart with weird X-values. Instead of single, whole numbers, I need each value to be as follows: 1-2, 3-5, 6-10, 11-15, etc. Please help me!! My chart keeps going to whole numbers instead and its been driving me crazy for days! How can I fix this problem?!! ...

Saving chart templates
I have created some charts and saved them as templates (crtx). I can apply them to existing chart and, subject to some occasional odd behaviour (plotting agains the ''wrong' axis) it works ok. Not a huge timesaver but you take what you can get. However, if i 'Insert' a new chart using any of three templates I have created - and then attempt to add data to it the workbook is corrupted when I try to save and ALL charts and Range names disappear. I am using the name manager from peltiertech but that has always worked before and even if i avoid using it the pr...

Flowcharting Table relations
Hey guys and girls. New one for you. I am doing an IT audit of Great Plains and need to flowchart how the data files are related to each other. In other words, how does one file feed into another file, and what field or fields in a particular table is used to relate/link to another table. Does anyone where I can find such information? Thanks Check out the Great Plains SDK available on the installation CDs. There are several diagrams that describe table relationships. Also, there are module documents that contain posting flowcharts. Regards, Kevin Rood Corporate Software Consultants...

Import Contacts from a Public Folder to Ms Access Table
After few trial and errors and digging the net I've found a way to do that. Using Access (since version 2002) you can link tables directly to Exchange and Outlook containers. While linking to Exchange directly gives several troubles with accounts that use offline connections, linking to Outlook (that of course must be installed with a configured and working underlying profile - but does not need to be open) does not give any trouble. I hope someone could find this info useful. Diego Volponi ...

Pivot Table-Running Total
Hello, I have created a Project Finance Reporting Tool, which is based on Pivot Table. The Source data has several fields in addition to the ones below. I can create a running total in Excel (thats no problem) but I am unable to apply the same cell reference formula to achieve the same result in the Pivot Table Formula . The problem is Running Totals available in Pivot Table is not giving me the desired result. Column A = Cash-In Column B = Cash Out Coulmc C = Runing Balance Any Help is greatly appreciated Rushdhi ...

charting performance speed
So am I correct in finding that the charting preformance speed of 2007 is an absolute dog? Trying to chart 16 datasets with 2055 data pointrs each on an x-y chart, with the colours of some of the lines adjusted to keep them in four "matching sets". I have to wait for nearly a minute for a redraw when you flick back a tab. Can't type any headers without taking a break for a coffee. Am I missing something please? Your impressions match my own. Excel 2007 with SP1 installed is slightly better in some situations. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials ...

Table lookup using multiple qualifiers
I asked this question in a previous post but it kind of fizzled. I am trying to return values from a table using two qualifiers to obtain the data. A B C 1 SIZE LENGTH STRENGTH 2 .5 1/2 100 3 .5 3/4 150 4 .75 1/2 150 5 .75 3/4 200 Cell D6 is an input cell for "size" (Example = "1/2") Cell D7 is an input cell for "strength" (Example = "138" Cell D8 is the resultant lookup I want to do a vlookup(?) that finds the...

Can you combine data from 2 worksheets into 1 chart?
My son is working on a science project. He had to track the temperature and precipitation levels in Daytona Beach for 9 years. I put the data in 2 worksheets, one for temperature and one for precipitation. He now has to make charts for the data. We've done the 2 separate charts but now we need to make 1 chart showing all the data. Is there a way to take the data from the 2 separate worksheets and put it into 1 chart? http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom So...

Is there an easy way to denote significance on charts
The only way I know how to add significance to charts, such as an asterisk above a bar chart, is by adding a text box. Any adjustments to the chart require rearranging the text box. Adding significance to charts is quite common and I can't believe Excel would not have an easier option for this. Another option is to apply shapes to hidden series. For example, if the shape were an asterisk you could then control the position of the asterisk based on your source data. The asterisk could always appear over the bar even if the bar moved when the source data changed. -- John Mansfie...

Overlapping 2 charts (XL2003)
I have a 5 row 3 column sheet in A1:C5. Here are the 15 values (first 3 are column headings): type day amount A,1,10 A,2,11 A,3,12 B,3,7 I'm aiming to have a chart with 1,2,3 on x axis, and 3 blue values: 10 units tall over the 1; 11 over the 2; 12 over the 3. Then I want a red 7 over the x value 3. So I want to see both 12 and 7 above the 3 on the x axis, either overlapping or side by side. I created a chart by highlighting C2:C4 and hitting F11. I can name that series as A. So far so good. Now I want to overlay B data, so under Source Data I add a series named B with values =sheet1!c5...

Finding data for a chart in word
> This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3325082853_2433946 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit After inserting a chart in a word document I cannot seem to get back to the excel worksheet update data on the chart. Advice please. Thank you. Joe --B_3325082853_2433946 Content-type: text/html; charset="US-ASCII" Content-transfer-encoding: quoted-printable <HTML> <HEAD> <TITLE>Finding data for a chart in word<...

Perspective setting won't save in a 3-D column chart
I have a 3-D column chart that has the perspective setting as 20, the elevation as 24 degrees and the rotation as 198 degrees. I change the settings, close the dialog box and click save. Yet the perspective setting resets itself to 15 every time I open the file. I am using Excel 2007. Any suggestions? Thanks. Hi Kathi, I have been able to duplicate your problem, I suggest this is a bug, although there might be some reason for this behavior, I can't think of any. It's probably not worth it but I suppose you could attach a macro to the Open_Workbook event and reset it to...

how to view 2 tables in design view side by side (access 2007)
I used to use access 2003 and was able to show side by side two tables in design view. How is this done in access 2007. Click on the Office Button, at the bottom right click the Access Options, select Current Database, under Applications Options - Data Window Options unselect Display Document Tabs and select Overlapping Windows. You will need to restart Access to take affect. -- Build a little, test a little. "celeste" wrote: > I used to use access 2003 and was able to show side by side two tables in > design view. How is this done in access 2007. ...

XY Scatter Chart Not Showing X Axis
I'm trying to create a quadrant analysis. I have the four quadrants as stacked columns and the real data as a XY Scatter. The x axis for the XY Scatter is not showing. The real data series are marked as the secondary axis. Does anyone know how to make the X axis appear? Usually Excel gives you both secondary axes when you add an XY series to a column chart. Go to Chart menu > Options > Axes, and check the box for the secondary X axis. If you're using Excel 2007, it's found on the middle of the three charting tabs, under Axis. - Jon ------- Jon Peltier, Microsoft ...

Formatting Linked Charts Now An Issue
We just got upgraded to Micro. 2003 and let me tell you it's not pretty. I previously had a lengthy Word doc with a bunch of charts linked from an Excel worksheet I had. Since the upgrade I had to re-link every chart because the links no longer work, why is this?????? Also after I cut and paste special as a link each chart I can no longer freely move the chart around or even right click to get properties and select the size I want the chart to show. I paste it in the right column of a word doc table so I need to size it to fit into the column and I can no longer do this. This i...

Outlook 2003
On outloook 2003, view by sender table font header is in gray font color. I can not stand it. Can anyone advise how change font color? My preference is black and bold. Thank you in advance. I think you'd need to change this in Windows. You might have to play around with the Windows colors to see which one controls the column header font color. -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** In news:941DCDC9-2491-4819-B951-C0FC4305AD3C@microsoft.com, Nils wrote: &...

Pivote table adding Fileds
Dear All Happy new year I had creat pivote table on table that was exported from Access (it was exported from query that based on relation beteen tabled ) ,if i make refreshing data on the same filds the new data will be refrexhed withot any problem , but when i add new filds in the query and exported again (with the same name and the same orginal fields but with one or two more additional fileds ) if i make refresh he can get and match the old filds but not the new filds , so i can not add this filds to the pivote table unless i restart the pivote table from the start , MY Question is , is...

Append a column to a table
I have what i think is a simple task, but i am new to access and need some advice. Basically, i am trying to use a table as a report and need to update the table by adding a list of phone numbers as a new column. The table that has the phone numbers is alongside a list of provider numbers, while the table that I need to append also contains those same provider numbers (i.e., so the provider numbers should allow me to link the data in somehow). The table that I wish to append does have duplicate provider numbers, whereas the table with the phone numbers DOES have duplicates. What is the best ...

Blank Cells in Pivot Tables
It's been mentioned here about filling in blank cells automatically in a list of data. Can this be done within a pivot table automatically? I create pivot tables but the row headings are grouped so that some of the items have blank cells next to them. This is frustrating when you want to run a pivot table over the top of the original as it considers these cells blank. As a result you have to copy and paste the row headings into the blank cells and this can be time consuming if you have many lines of data. Can it be done automatically within the pivot table? Can pivot tables be automa...

Data Bar / Progress Meter / Bar Chart
Hi, Could anyone suggest a method of having a text field (or similar) have the behaviour of a data bar in excel 2007. I have a field that displays a percentage and would like to present the information to the user in a bar graph / data bar / progress meter style appearance. The value is only ever updated when the form is first loaded, ie. the values will not require updating/refreshing. The data comes from a query. It would be really great if it could change to yellow at 75% and red at 90% as some sort of conditional format as well? Thankyou. You can insert the microsoft progress b...