Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

(80% Discount Ends Soon!)

All About Pivot Tables in Excel


I have a excel file with a pivot table and i want to distribute this
file without the links to that pivot table. I want to distribute this
file only with the data so that everybody in any part of the world
could read it.
How could i do this with a macro.
Now i copy the sheet to a new one and delete the old sheet. There must
be a better way...
Thanks in advanced

André Fonseca

How could i unlink a pivot table from it data source?


André Fonseca

I recently upgraded to a new version of excel at work (sorry I don't know the version) and have had a problem since with pivot tables.

I generally do lot of work creating pivot tables, and writing formulas against the pivot table results. Under the old version of excel I could easily write a formula against the pivot table data (e.g., formula is A1 *10, where A1 is a cell in the pivot table) and then copy it down several rows to replicate the results across several cells. Now when I write the same formula against the pivot table cell the formula automatically pre-fills with a GETPIVOTDATA function that anchors the source. That prevents me from copying the formula to other cells without first having to edit the formula to remove the anchor.

Is there any way to write formulas against the pivot table in the later version that doesn't automatically pre-fill with the GETPIVOTDATA formula?


I have data with task Start and End Dates that I need to display in a bar
chart. I am having difficulties as some of the Start date could begin in Jan,
but end in Mar, Some begin in Jan and end in Jul, What I need is for the
pivot table to create a chart that shows in Jan there are 'X' number of
tasks, this should follow through until Mar, for the first then Jul for the
next example, but it doesn't.
What I get instead is 2 tasks in Jan, then none for the rest of the year.
Grouping doesn't work as I need to see every month and every task. i.e. Task
1 should appear on the chart for Jan, Feb and Mar.

When you double-click a cell in the data area or a pivot table, Excel places
the detail data summarized in the cell on a new worksheet.

Unfortunately, the number formatting on the new worksheet doesn't seem to
follow the formatting of the source data. I can use the Format, Style,
Number to make the formatting the same for each numeric field, but I don't
want the same format for each numeric field. For example, I want some of the
columns to be comma with 3 decimal places and others to have no decimal
places just like they are in the source data for the pivot table.

Any suggestions?

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


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

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.

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

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.


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.


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.

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.



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,

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?

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
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

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?


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.



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

ACADEMY 1 Buck Snort Assortment C 65.52 Academy -

1 Buck Snort Assortment C Total 65.52

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


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,

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

How can I create a calculated field that includes formulas with MIN, MAX or
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?


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.

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?


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


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?


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?


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

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:


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


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

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

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.


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

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


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
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?



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 !!!


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

Is there a limit on the size?

Any suggestions?

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

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:


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.