All About Pivot Tables in Excel

Help! I have a Pivot Table that calculated the inventory in average.
However, on the total row, I'd like to get the sum of the average. How to do
that? Thank you.

I want to get Pivot table data on my invoice, is it possible to set custom
views so that I can get Pivot table results on invoice format, I've tried
views that offer excel but it doesn't offer custom views
please help

Thanks

Hi All:
Hoping that someone can help. I am trying to refresh a pivot table
using the following code which was inserted on the Daily Production
Output Sheet(both sheets in the same workbook). I am using Excel 2003
Private Sub Worksheet_Calculate()


'If data on this worksheet changes, refresh the pivot table
Sheets("Daily Production
Output").PivotTables("PivotTable3").RefreshTable


End Sub


The Calculation has been set to Automatic.


However when I try to run the above code I am getting the following
error" Run Time error 1044, Application defined or Object defined
error". I think that the code worked a couple days ago.
PFL


What can I do to run the code ?
Thanks in advance

The date format appears only with the month (e.g. Jan) and not the year, even
though I have formatted the cells to show, e.g. Jan-05.

Pivot Tables in my workbooks cannot be refreshed if the sheet is protected.

Here's what I've done:

Set up my worksheet.
Formatted cells that I wish to be locked.
Selected Tools>Protect Workbook
Selected Tools>Protect Worksheet
Clicked the following checkboxes: Select unlocked cells, Use Pivot Table
reports

The actual area of the Pivot Table in my worksheet is unlocked. Whe I select
the pivot table in the protected sheet I cannont refresh, but I can format
and create charts.

Any help would be appreciated.

Mike

i have a pivot table that calculates resource availability. each resource
may work on multiple projects, and each of these projects require a certain
number of hours. the grouping is by person, by project. for each person, i
need to show hours allocated toward each project (detail) and then total
number of hours allocated (subtotal). i also need to show a calculated field
of % hours available (subtotal only) for each person.

John project 1 30 hours (detail)
project 2 15 hours (detail)
Sum of hours 45 hours (subtotal)
Caculated field (1-sum(hours)/40) (subtotal)

how can i show this calculated field only as a subtotal but hide its detail?

thanks much.

--
mt

In my Pivot Table, I have all the column headers formatted to "wrap text".
When I select new data to display in the pivot table, it removes the wrapping
on the column headers. I've deselected the AutoFormat Table option, but the
behavior continues.

Any way around this?

has followed Excel's instructions, but still could not group date (into
month, year, etc) from a column of date in a table. Message 'Cannot group
that selection' even though the whole column (cells) had been reformated in
date format. Please help.

Hi,
When I make a chart using a pivot table I see a blue rectangle box at
the top that says 'Drop Page Fields Here'. Is there a way I can hide
this box so that I can copy the entire chart to powerpoint.

Currently, when I do this, the 'Drop Page Fields Here' box gets copied
over as well thus making it look meaningless in powerpoint.

Thanks.

Hi,

Given a set of raw data shaped like this:

Cat Item Value
--------------------
1 1 45
1 2 37
1 3 40
2 1 38
2 2 34
2 3 31
3 1 36
3 2 39
3 3 40
.......

I created a pivot table that uses "Cat" as a row field and "Value" as a data
field. I then change the summary function for the "Value" field to use
"Average". Next, I add the "Value" field as a data field again and this time
change the summary function to "StDev". This all works ok.

I'd like to add a calculated item to my pivot table that allows me to take
the standard deviation of "Value" and divide that by the average of "Value"
to calculate the coefficient of variance.

When I try to add a calculated item to calculate the coefficient of variance
(i.e. STDEV(value) / AVG(value) , I get the following error from Excel:

"Multiple data fields of the same field are not supported when a pivottable
report has calculated items"

However, even if I eliminate this problem, I get another message from Excel
when trying to add a calculated item as follows:

"Averages, standard deviations, and variances are not supported when a
pivottable report has calculated items"

Can anyone suggest how I would go about adding this calculation (i.e.
STDEV(value) / AVG(value) to my pivot table?

Thanks in advance,
Jim

I have an inventory database that has separate fields for receipts and
dispersements. How can I make a pivot table to show the part numbers, the
receipts and dispersements and a running balance?
tia

gremlins have been @ my file since friday.

i have data in one w/sheet & pivot tables in another. all was working fine on friday.

opened both files this morning. changed & saved data. went to refresh P/table & get message

Microsoft excel cannot make this change because there are too many row or colums items. Drag at least one row or column field off the pivot table, or to the page position. Alternatively, right click Hide or Hide Levels on the shortcut menu.

When i try to change my pivot table, it says that my pivot table is not valid ??? I don'T understand what's going wrong ?

i've tried saving both files, closing them down. closed all other applications down. had a cup of tea. but i'm still baffled. please help!

It's amazing how one can take pivot table data -- double-click
in the data section and it "converts" the PT format to a data-table format
(fit for a database environment, say access)...
Well any way, I receive files that are in a sort of a crosstab format,
which I'd love to be able to get to respond to (the creation) of a Pivot
table.
Is there a way to make this "conversion"?
Thanks in advance for any direction offered..

Hi everyone.


I have a problem that at first seemed simple, but I can't find the
solution. I'm using a Pivot Table to provide a report of my household
inventory, having columns of quantity, type (each, pair, set) and
description.


What I want to do is provide a report to my moving company which lists
each item in a row, example, 1 each bed, 1 each cupboard, 1 each table.
However, the pivot table summarizes the data and I only get one "1" and
one "each", but individual rows for the description, as you would
expect. What I actually need is a "1" and an "each" on every row for
the moving company report.


I've tried looking at field settings but that doesn't seem to help.
Therefore, is there any way of not summarizing data that is the same,
and treating each row as unique?


Thanks in advance.

I am trying to link an Access query that prompts for several criteria to an
Excel pivot table. When I refresh the pivot table, I want it to open the
database and run the query to update the data in the pivot table
automatically. When I try to link to the data source, when I get to the end
of the "Query Wizard", it gives me the error of "Not enough parameters.
Expected 4". But it never prompts me for the parameters. Please help. Is
there a setting I need to change or do I link the query differently to avoid
"Microsoft Query" tool?

Hi

I have generated a pivot table using data from an Access Query. I'm
able to generate an Average column in my pivot table but want to be
able to calculate a mode. I haven't found this option within the pivot
table and was hoping someone could point me in the right direction or
explain how to generate this.

Many thanks - David


--
David Gibson

I've got a huge stack of procedural data and I'm using a pivot table to get control of it. When all i wanted to do was MEAN and COUNTIF type functions, this was great, since it allowed me to divide up the data into meaningful clumps.

Now I need to find the 95th Percentile in the data [ equivalent to PERCENTILE (array, .95) ] but that isn't a default operation in a pivot table.

I'm already using VBA in this project, so I'm not afraid to add some more code if required, but I'm really stumped.

Thanks,

Andre

Some of the formatting on my Pivot Tables remain after refreshing, some
formatting does not. I have "preserve formatting" checked, "Autoformat
Table" unchecked. Excel 2002. Any help would be appreciated - I have had
formatting problems with Pivot Tables for years!!

I've created a pivot table and because of the length of the characters
in the page, the column width keeps changing. How do I keep the column
width the same?

Below is sample data. What I am trying to do is that I have the Headings on
1 row and different columns. Second row has the detailed data in the rows
and columns below, (as normal). I have the spread sheet sorted bye
Customer ID and Item Description.

The third row is the subtotal row. I only need the data on the Subtotal
Row. However, I need on that same Line the second line data for the
headings. When I use the subtotal command - it only shows on the subtotal
line the total for the Quantity and Item description. Because I told it to
subtotal those fields when the Item description changes. I am getting the
correct total, but don’t need the details. But don't want to have to retype
the customer ID Sales and other headings on the subtotal line. If this is
not to confusing. I was looking at Pivot table and kind of understood but
little confuse on the process. Of course whatever way is better.



CustomerID Quantity ItemDescription Sales
Customer_Bill_Name


ACADEMY 1 Buck Snort Assortment C 65.52 Academy -
Houston


1 Buck Snort Assortment C Total 65.52

Can I create a Pivot Table using a combination of two rows as the header?

Example:

Row B1 = May_05
Row B2 = June_05

A3 thru A10 = Dates

B3 thru B10 = Values

I want to establish B1 & B2 together is the header row. I need to do this because C1 = May_05, C2 = Aug_05. My header is a pair of months.

Does anyone know if this is possible?

Thank You,
Steve

I have just created a pivot table with two column fields, 1 row field and six
data fields. I can select on the column and row fields but when i try to
select items on
the data field, the unselected items disappear when i go back into drop down
menu on data field to make another selection. The only way to get it back is
to add the field to the data. Is there a way of fixing the fields in data.

Yours desperately
George

How can I create a calculated field that includes formulas with MIN, MAX or
AVERAGE?
Becasue the summary form calculated field of a pivot table cannot be changed
(is always SUM), Excel returns a different number when I try to insert a
field that includes such a formula.
I am trying to include a field like this to calculate minimum possible cost:
=MIN('Price')*SUM(Order Quantity)
Any suggestion on how to do it?

Hi,

I'm using Excel 2000. My pivot table contains calculated fields, the problem
I'm experiencing is that the grand total for the field is almost 2 times
higher than it should be. However, if I look at the individuals rows that
calculation is correct, if I sum the calculated field column (with exception
to the grand total) the sum is correct but the highlighted column doesn't
match it's grand total....not sure why.

I'm not user if the problem is with my source data worksheet, the only thing
I'm doing which maybe an issue is- for the rate column, I have it equal a
cell that's resides on another work sheet.
--
Linda

Going a little bananas with trying to get a Pivot Table Report to accept the
order of the fields as they are in the Pivot Table. It's importing them in
ascending order by default, and I can't select Data Source Order from the
toolbar's Sort and Top 10/AutoSort options. The "manual" selection is the
default, but I don't want to have to drag every field around each time.
What's up?

I am trying to get a pivot table to show a variance column for the subtotals of 2 sets of grouped data. This is for summarising all financial transactions by period (column) by funding source and cost centre (rows), and then comparing this to the budget amount.

I have added a pivot table field using options to find the difference between these 2 groups (transactions and budget). It does return the correct calculation but also inserts a variance column for each individual column and I can't figure out a way to hide or disable the individual variance columns whilst leaving the subtotal variance column showing.

As an alternative to the above I have also tried using the calculated fields option to create subtotals of the 2 sets of data but without success, and using calculated items inserts too many extra rows (for example funding source of capital shows all cost centres not just the cost centres that are capital funded).

Any advice appreciated!

Hi! Can I use pivottables in a shared workbook? The reason I ask is that its
greyed out in a shared WB but not in a non-shared. I can't find anything on
this, perhaps somebody can help?

thanks!
Chris

I want to exclude certain records from a pivot table... i.e.,

Buy/Sell Quantity
B 5,000,000.00
S 5,000,000.00
B 1,000,000.00
B 10,000,000.00
S 7,000,000.00
B 500,000,000.00
S 200,000,000.00

Only inlcude "B" records.
Can a pivot table be set up to filter?

Thanks ChrisP

Hi,

I have a client with a mid-sized workbook. On one page is a pivot table.
Occasionally, upon opening the workbook an message appears about errors
being detected in the pivot table. The result is that the workbook opens,
but the worksheet with the pivot has now become "flat data". The buttons are
gone, and the data is just static data, the ability to do any pivot
manipulation is gone.

This problem is not consistent.

Has anyone run across this before? Any ideas to avoid it?

Thanks,
KB

I have a pivot table, and within the row field, I would be able to write a macro to move a certain row to the bottom, regardless of the number of rows. Currently, if I move the row to the bottom while recording the macro, it will list the position number, such as

ActiveSheet.PivotTables("PivotTable2").PivotFields("Carrier").PivotItems( _
"Other").Position = 8

I found that I will not get the result I want unless there are exactly 8 items in the row section. Does anyone know if there is a way to make sure that it moves to the "Last" position?

Thanks

I am about ready to give up on this...but maybe someone out there has a
suggestion.

I have set up standard pivot table reports in 4 different workbooks that are
drawing on the same worksheets, but within their respective workbooks.
Within some of these workbooks, there is no value (or zero) for the fields
that are the source data for the pivot tables. When this is the case, the
pivot table, instead of displaying a - (for a null value as I indicated in
the table options) drops the row value heading all together and completely
screws up the standardization I was trying to achieve across all workbooks.


For example, if I had data for:

USA
Canada
Japan

If the value for the profits for Japan were 0, the pivot table would only show

USA
Canada

I already tried to preserve the formatting and to show rows for null values
options but nothing works!

This pretty much renders pivot tables useless if they are unable to display
null values with changing their format!

Please help!

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

In my source data, I have a 'Date' field. In my pivot table, I want to use
the 'Date' field twice:

* Once to sort by 'Year' (i.e. simply changing the format to 'YYYY')

* And a second time to sort by 'Month' (i.e. simply changing the format to
'MMMM')

Can I do this without the requirement of adding fields to my source data?

Thanks.

is there any functionality to update or change the underlying data set using
the pivot table itself. i.e. i have pivot table that consolidates a large
budget, and want to update certain items in the pivot table display... can
these changes be pushed back to the data set.

I have the following tabel
A B C
Region Customer Product
1 ASIA Custo Apple

Since one customer can have more products and a region more customers I used
in the table mergd cells to keep the overview. However when I am using this
data in my pivot table only the first complete row is stated in the pivit
table. Any ideas how to solve this?

regards

Johan

How do I restore my complete data set for a pivot table? I have a table that
has a grouping at the top (say group "A"), a series at the right which are my
pivot table columns (1,2,3,...20), and data at the bottom which are my rows
(a city listing). Whenever I change the "data" selection at the bottom, the
other selections disappear and I cannot get them back unless I undo. How do
I move the selections around but still have access to my original data set?

Andrew C

I have built a pivot table from a table containing a list of people.
My pivot table will count people based on the field Name.
Some people names might have several entries in the table but in this case,
I want the name to be counted a 1.
What is the appropriate set up for this ?
Thankk you for your help.

I am working on a compliance scorecard using Excel to measure if student homework are submitted on time. I get results in pivot table from a SQL db, the basic results in excel a

for On-time, I get number '1'
for Missing, I get number '0'
for Late, I get number '3'

How can I convert these numbers to a reader friendly symbol, say convert 1 to a green 'check mark', 0 to a red 'X' and convert 3 to a 'black flag' ?

Thanks !!!

Friends,

The data I am accessing for my pivot table would comprise only 500 rows if
Excel would let me put the 5 rows into the Row area, but I get the following
error message:

"Microsoft Excel cannot make this change because there are too many row or
column items. Drag at least one row or column field off the Pivot Table, or
to the page position. Alternatively, right click a field, and then click Hide
or Hide Levels on the shortcut menu."

Why is Excel giving this error when I am no where near the row or column
limit of Excel?

Thanks so much for your help ...

bill morgan

My source data table is 74 columns and 1,297 rows. I am trying to create a
pivot table with multiple columns from the source data as the "data" in the
pivot table. When I try to do this I get an error that says...
"Excel cannot make this change because there are too many row or column
items...."

Is there a limit on the size?

Any suggestions?
Thanks!
Jenni

I have a pivot table set up to tell me how many hours were incurred during
each month before an event. (Eg. 5 Month Prior: 152 hours, 4 Month Prior: 38
hours.) If no hours are incurred, my data sheet does not show zero, it just
doesn't have anything.

I set the Total Row to be an average of the data, but the average doesn't
pick up records with no hours incurred. (If one program had no hours, the
average will not include that as being zero, it just excludes it from the
average)

Is there a way to have blank entries included in the average? (I already
have empty cells set to display 0)

In a workbook, I made a GETPIVOTDATA formula to link to a Pivot table in
another workbook, but...

If I open the workbook with the formula while the pivot table workbook is
closed, and then update links, I receive the dreaded "#REF!" error in the
cell where the formula is. (By the way, the fomula points to the correct
full path of the starting cell of the pivot table, and the formula works
correctly when the pivot table file is open.)

Is there any way to make GETPIVOTDATA to read pivot tables in other
workbooks that are closed???

I would prefer to use the GETPIVOT DATA formula rather than formulas
directly referencing cells in case the table format changes.

Thanks for any advice.

I can't seem to get data or change the data source. When I refresh, I get
the error message "Problems Obtaining Data" When I start the Pivot Table
Wizard, the back button is grayed out so that I can't go back and change
data sources.

Any help is appreciated.

My formats reset when I update my pivot tables.. I unchecked the "Auto Format" option in "table options", but this hasnt helped. Specifically I lose the "Wrap Text" and Column Width settings. Any solutions?

Also, is there a way to update all pivot tables in a workbook at once, without clicking on each page and then hitting the exclamation point?

Hi All,

I have a grouped pivot by date. Works fine except for the format of
the date doesn't allow sorting in the way that I need.

The data has the dates in the format: 4/17/2006 14:21

After grouping in the pivot on that field by day (to remove the time
portion) I get: 10-Apr


Sorting the pivot gives:

10-Apr
10-Mar
11-Apr
12-Apr
13-Apr
13-Mar
14-Apr
14-Mar
15-Apr
15-Mar
16-Mar
17-Apr


Rather than by year/month/day. I tried changing the field format in
the pivot and it doesn't do anything at all to how the format in the
pivot looks? I need the pivot data to sort as actual days are in
calendar order. In my example data, it would have all March data
followed by April data rather than the tenth day of either month
followed by the eleventh day of either month etc...

Any ideas? Thanks for any help in advance.