Pivot table, calculating % difference of subtotal rows
This is what I need to do: I have rows of data in a pivot table that
have subtotals associated with them (automatically generated via the
pivot table). I need to to take the subtotals for 2009 and 2010 and
calculate the % difference.
Here's a screenshot of what I'm talking about:
Is this even possible or am I going to have to do this manually?
Hi I have'nt seen the screenshot as am at work , go to Pivot
>This is what I need to do: I have rows of ...PIVOT tables and publishing thrm on a webpage
Is there a way to publish a pivot table in a html page but still kee
exactly the same funcitonality that you have on a Excel spread sheet
either by converting it to a java applet or something else . Any hel
would be much appreciate
Message posted from http://www.ExcelForum.com
...Pivot Table question #4
I have spreadsheets that take data via a pivot table from an OLAP cube that
is held on a terminal server.
The spreadsheets hold figures entered by me and figures obtain from the
I want to enable the automatic update on the pivot table but the pivot table
is sorted by a project number obtained from the OLAP cube. My problem is if
a new project is added to the OLAP cube then it is automatically selected
from the drop down list. I want to be able to turn this off so that it only
selects the project I have previously chosed. For example if I have selected
the filter nu...Need large gap in chart
I am making a line graph with 2 sets of numbers that are very far apart - one
set of numbers is around 7000 to 8000, and the other set is closer to 80,000.
Of course, I get one line at the top of the chart that is pretty easy to
read, but the line on the bottom that represents the smaller numbers is just
TINY and looks pretty much straight.
My question is this: is there a way to put a "break" into the graph between
the smaller numbered data and the larger ones so they are all easier to read?
Like a jagged line showing where the jump occurs or something?
Jon has an exampl...Excel 2007
When you set reverse categories for the horizontal axis, I noticed that the
legend (which I normally placed at the bottom of the chart), did not
Is this WAD (working as designed) ? If not, how can i overcome this as the
reader would have to mentally "remap" the legend to the bar chart.
Thanks very much.
Reversing the Axis categories will not effect the legend order.
To do that you need to change the Series Order. In 2007 this is none via the
Select Data Source dialog.
Chart Tools > Design > Data > Select Data. Use the Up/Down arrows in the...Do Pivot Tables have an automatic data range expansion?
As I add new rows of data to my data base, my pivot table will not expand to
include them after refreshing. Is there any way of setting the pivot table
to include the entire database regardless of its continuously growing size?
Right now, as I add a new row of data I must delete my old Pivot table and
create a new one from scratch. Is this normal?
You can use a dynamic range that grows/contracts with your data.
Debra Dalgleish explains it all at:
> As I add new rows of data to my data base, my pivot table wil...Display order of Bar Chart
How do I get a bar chart to display my data in the order I created it rather
then in a sort ascending or descending order. I created my vertical axis
categories in the exact order I want the bar chart to display the data
however, when the chart is created is rearranged into ascending order of the
data not in the order of the categories as I developed it.
That is not how Excel behaves; when the data is in rows, the first category
of the first row is at the bottom of the bar chart and so on, regardless of
Bernard V Liengme
Microsoft Excel MVP
I created a line column chart on 2 axis. I have four data
sets. The first 3 should go on the primary Y axis as
columns and the 4th should go on the secondary Y axis as a
line. However, Excel wants to automatically put both the
3rd and 4th data sets on the secondary axis as lines. I
cannot find any command to change the 3rd data set to go
onto the primary axis as a column.
Any suggestions would be most appreciated!
On Tue, 11 Nov 2003 13:27:33 -0800, Patty =
> I created a line column chart on 2 ...Gantt Chart Missing One Task Line When Printed
I am completely stumped here with Project 2007. When printing the gantt
chart line 30 of my plan is blank. The line number is there, but the task
info and bar is blank. It shows up in print preview. All other lines show up
fine and the rest of the project prints fine as per my print settings - just
missing one line!
Printer drivers are updated, software has current update - anyone ever heard
of this? I really appreciate your help!
Do you see a different answer if you turn off 3D bars? Tools > Options,
View tab. There were some early problems with 3D ...Charts and "global" changes
I'm actually working in Microsoft Access97, which uses MSGraph 2000 for
charting, so I thought this was really a "charting" question... not an
I have an XY scatter chart (using just points... no X or Y error bars or
lines) all laid out just fine, but I have a couple of questions that would
make my work a lot easier.
When the data first displayed on the chart, every data point had a
different "style"... circles, triangles, x's, etc... I had to go into every
one individually and change them to a black dot at size 3.
Is there an pie chart type where you can explode just one of the sets of data
on the chart? I have access to both Excel/Powerpoint 97 and 2003, but there
doesn't appear to be any differences in what's available for pie charts. Is
there an add-in or something for variations on these pie or other charts.
Thanks for your thoughts or suggestions.
Create a regular pie chart in Excel
Click on the pie to select it (each slice will have
a black handle on its outer edge)
Click on the slice that you want to explode (only that slice
will now have handles)
Drag out the select...Setting series values on Excel Chart
I'm struggling very hard in getting beyond this error when trying to use VB to generate a chart from data previously inserted into the sheet.
Unable to set the Values property of the Series class.
The code works absolutely fine in XL2007 but I am having to port it back to 2003 and the inference is that its empty values that cause it to hiccup - something I cannot avoid.
My code is:
1. Retrieve selected data from database and place it at the top of the sheet
2. Add The Chart
3. Iterate through the data (amount can be variable depending upon user inputs)
For j as i...How do I pass series data x-value range to a chart from a cell?
I have a series on a line chart (x-values "=Sheet1!$A$1:$A$10"). I also have
two worksheet cells (B1 & B2) that contain the start and end date for the
x-axis of the chart. I want to be able to link the chart series data to the
start and end date values so that the chart automatically adjusts when I
change the dates in the worksheet cells. I can create a string or address for
the x-values but how do I do not know how to pass this to the chart series??
I know I can do this using a macro, but I want to do it without macros so
that friends who use MicrosoftCharts (the cheap, n...sizing a chart
(1) I have two charts that are exactly the same just showing different data.
Is there an easy way to make these charts the exact same size?
(2) How can I get them to line up with border lines I have on my worksheet
so that they look right on the printout?
Thanks in advance for any help or suggestions you may have.
Hold the Alt key while moving and resizing the charts, and they will stick
to the cell boundaries.
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
"Jordan" <Jordan@discussions.microsof...How do I create a 4-d chart?
I am trying to create a chart with 4 dimensions. In effect, I need a 3-D
column chart where the vertical bars are "stacked" colmns. How can I do this?
Have you heard about Tufte's "small multiples" or Cleveland's "trellis"
displays? These approaches create a series of small plots instead of trying
to put everything into a single overly complex chart. I would stay away form
3D charts in Excel.
Trellis is a trademarked by Insightful software. I use the term "panel
charts" to reflect trellis and small multiple charts in Excel. I have...Equation in a chart
There is any possibility to add in the area of a chart a
box or a text box or another way and to make it with a
simple = to bring me a value of a cell? either a number
or a percentage?
I currently using Excel XP edition
Thanks in advance Manos
try the following:
- insert this textbox
- select the textbox and hit F (to get into the formula bar)
- now enter your formula. e.g.
> Dear all
> There is any possibility to add in the area of a chart a
> box or a text box or another way and...Drop down list for graphs/charts
My boss wants a drop down box in Excel that will give us about ten different
graphs. I did all ten of these graphs individually on separate tabs but my
boss wants one tab with a drop down box that when we choose one of the
groups, it will give us that graph.
Can this be done?
Based on the drop-down, in a separate range select the data which you want
to plot. And plot this in one graph only. For e.g. you have data A1:A10 for
the first graph, B1:B10 for the second. Lets say in M1:M10 use a formula
which will get the data from A1:A10 or B1:B10 based on the drop-down
selection. Plot M1:M10 o...Data display incomplete after creating Pivot Table
I have created pivot table from spreedsheet. One of the fields "additional
comments" doesn't display fully in the pivot report. I have tried increasing
column widths and row widths. I have also tried using Alt+enter as suggested
in some of other discussion forum.
Nothing is working so far. Any help appreciated.
I have just put up a file for you at:-
It is item number 36 towards the top of my home page.
It works OK for me. Check out the file. I hope that it helps in some way.
If my comments have h...Pivot Table Calculated Formula If statement
In a pivot table I am trying to add this formula:
In the insert calculated field-->Formula field i type
=IF('FIELD NAME'="STRING TO COMPARE", 1,0)
Any Idea why this does not work?
The calculated field operates on a sum of the field, and the sum of a
text string is zero. The zero result won't equal the "String to
compare", so the calculated field will return a zero.
Perhaps you could do the calculation in the source data instead, and add
that field to the pivot table.
Dan McCollick wrote:
> Hi all,
> In a pivot table I am trying to a...Missing menu bar when chart is selected
I have a spreadsheet that I have been using for a couple of years but now
when I select a chart I lose my menu bar (file, edit, view etc). Also, I am
no longer able to right mouse click. If I select elsewhere on the sheet my
menu and mouse operations returns. Any help would be greatly appreciated. I
have tried resetting my worksheet menu bar by selecting tools/customise
toolbars but this doesn't help.
When you activate a chart the Worksheet Menu Bar (WMB) normally disappears
and the Chart Menu Bar (CMB) takes its place. The CMB looks exactly the same
except that it has a Chart menu...can't center a category label on a line chart
This is a multi-part message in MIME format.
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Yesterday I got some good help with figuring out the differences between
the line chart and xy chart. I've had to go with the line chart. I've
pretty much got it the way I want it. I've posted my data below. The <
means prior to 2007.
In Excel 2003 I could easily center the Year between the Major vertical
gridlines (every forth one, since this is quarterly data). But with my
pres...Updating Charts with Macro
I have set up my charts so that i can update them automatically by putting
the appropriate data in the fields. I would now like to be able to update
them using a macro, but cant seem to get it to automatically go to the next
blank cell to put in the date, infor etc. Any help would be greatly
appreciated..Thank you in advance
You haven't really given enough information to describe your requirement.
You could use dynamic ranges, with offset, index, or indirect worksheet
functions, and bypass the macros, too. See the few examples and many
I have created a chart that has multiple data.
Goal - 2000000
Present - 4,500,000
January - 450,000
I have the column stacked on top of each other.
BUT THE PROBLEM IS that if someone is reading the graph they think that
presently we are at 6,500,000. Because the goal is below it @ 2,000,000 and
it adds the presently on top of it.
I need them to over lap each other. So the numbers on the left are correct.
I have used a stacked column graph.
Don't stack the columns then. Use Cluster column chart type instead.
Set the overlap value to 100.
Andy Pope, Micr...Hide a chart
My Boss is working with Excel 97 and I'm in 2007...he hid a chart in his
version and I cannot unhide it...have looked everywhere and tried all sorts
of commands with no result...anyone else having this problem?
Assuming he hid the sheet - choose Home, Format, Hide & Unhide, Unhide Sheet.
If the chart has been hidden using the 2003 Objects, Hide command - choose
Office Button, Excel Options, Advanced, Display options for this workbook,
For objects, show: All.
"E Gray R&A" wrote:
> My Boss is working with Excel 97 and I'm in...Two filters in a pivot table
I have a pivot table that I'd like to filter for two criteria like an OR
instead of an AND.
I have two fields for animal handlers, keeper 1 and keeper 2. They are
linked to a table called keeper ID.
Animal Keeper1 Keeper2
Scooter Jane Kim
Tilli Kim Ellen
Cactus Ellen Todd
I'd like to filter for which animals have been handled by keeper 1 OR keeper
2. So I'd like to see which animals Kim or Ellen have handled, resulting in
Scooter, Tilli, Cactus. Or which animals Kim or Jane have handled resulting
in Scooter, Tilli. The Pivot Table filters filter like ...