Free Microsoft Excel 2013 Quick Reference

Pivot table single value in field Results

Hi guys, have been scratching my head with this one . . .

Im trying to create a Pivot table with two fields in the values section.
One entry being the Invoice Amount, the other being the waiver amount.
The waiver amount needs to be split out into the different reasons, ie. Commercial, Nonprog, Other etc.

I firstly add the the reason field to the column label section, i get the "reason" field to the column labels, then added the first Amount field (Waiver Amount) to the values section. So now i have the Waiver amount, split into the different reasons, as per the column labels.

When i add the second the amounts field (Invoice Amount) to the values section, Excel splits the Invoice Amount into the "Reasons" Which is not what i want. I would like the first amount field split into the reasons as per the column field, but when i add the second amount field, i would want it to not split into the different reasons as per the column labels.

Is it possible to have excel to show the one amount field as a total, and split out the amount field as per the column label "reasons" ?

Attached is an example of the problem i am facing!

Any help will be appreciated . . . .im stumped!


I have an a table in sheet 1 which contains a list of pipelines. The column headings are as follows (simplified)

PipeNumber, PipeDescription, Boundary

The boundary field can contain a number of values e.g. 33, 8, 40. This tells the user which boundaries the pipe crosses.

I want to create a pivot table which sumarizes which boundaries each pipe crosses. The problem is the boundary cell could contain a single number (1 boundary) or numerous numbers (numerous boundaries). Is there anyway that each number in the boundaries cell could be recognised as a column heading?

It is hard to explain but hopefully you get what I mean.

Any help would be much appreciated.


I am trying to make a pivot table where I need to filter the data by the
values in a certain data field ("field 1"). I need to allow records with
either "this or this or this" value in the field 1. The problem is that the
pivot table page filter only allows for one single value to be chosen.

I do not want the field 1 to be shown in the column fields (where it
otherwise would be possible to chose multiple values), since I am not
interested to get the data grouped by the value in field 1 and since I am
also making a pivot chart where the field 1 will blur up the picture.

Any suggestions?

I am trying to make a pivot table where I need to filter the data by the
values in a certain data field ("field 1"). I need to allow records with
either "this or this or this" value in the field 1. The problem is that the
pivot table page filter only allows for one single value to be chosen.

I do not want the field 1 to be shown in the column fields (where it
otherwise would be possible to chose multiple values), since I am not
interested to get the data grouped by the value in field 1 and since I am
also making a pivot chart where the field 1 will blur up the picture.

Any suggestions?

Hi, I'm an excel beginner trying to get my feet and looking for some help and guidance. Basically what I'm trying to accomplish here is...find all the records in a date column within a pivot table that fall within two sets of dates (From & To), and return the average for a another column (Percent Accuracy) and calculate the average for that date range. I have tried several formulas with no success. As far as I got, i was only able to retrive one value matching a single date (formula shown in image for reference below), but no luck on the range.

Have included an image file for a visual of what I'm trying to accomplish, hope it's clear enough. If not, please let me know so I can provide further details. Thank you.

1. My From date will be keyed into cell B3, & my To date will be keyed into C3
2. Pivot table ($A$6) contains a "date", "total","Errors Count" & "Percent Accuracy" field.
3. My goal is to populate a cell with the overall "Average % accuracy for the date range used" keyed in to cell B3 & C3.
4. Since my pivot table will continue to grow as data is entered, I will not post the avregae results underneath as shown in image. I just put there to clearly illustarte the data column I'm trying to calcualte out.


I have looked at the below Hide/Show Pivot Table Field Items help web pages and am getting stuck:

Hide/Show Pivot Table Field Items
Hide Pivot Table Fields Pivot Items by Criteria

I am trying to use the above, but with dates in the following format in each cell:

etc, etc

The below code is working for >200702 and removes all years/months prior to this entered value. Unfortunately the code does not work when a user enters

This relates to VBA in Excel 2007.

I am trying to set the Current Page of several pivot tables on the same worksheet to a value on that worksheet.
All pivots are based on the same data range.
Each pivot only has a single page field and they are all the same field.

The following code is giving me "Error #: 5, Invalid Procedure call or argument"

(I trap Target using worksheet_change and send it to the sub below which is stored in a module)

    Dim wsH As Worksheet 
    Dim rng1 As Range 
    Dim pt As PivotTable 
    Set wsH = Target.Parent 
    Set rng1 = wsH.Range("$A$9") 
    For Each pt In wsH.PivotTables 
        pt.PageFields(1).CurrentPage = rng1.Value 
        pt.PivotCache.MissingItemsLimit = xlMissingItemsNone 
    Next pt 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I have found that

.CurrentPage = "(All)" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

pt.PageFields(1).CurrentPage = pt.PageFields(1).CurrentPage 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
also works (the code runs and does not error but obviously the pivots stay the same).

It is only when I try to change current page to anything else (always a value in the list for that field) that I get the error

Sorry if thats not very clear, I haven't alot of experience posting on forums.

Hope you can help!

I am working with a report where I use several pivot tables to create a dashboard report in Excel. I need to update all pivot table report filter fields to read the same values. I have code that can do it with just a single field, but I am unsure of how to do it for two filters with multiple fields.

I want the users to be able to select customers and unit serial numbers to filter the report and calculate the results. I am attaching a sample data set and also my code for doing one pivot header. It loops through all sheets and changes all pivot headers to be the same. Range"B9" is where it is located on the original file source for reference.

    Dim SPgField As String, SPgField2 As String 
     'turn off screen updating to speed up performance
    With Application 
        .Calculation = xlCalculationAutomatic 
        .ScreenUpdating = False 
         'display message in status bar to let users know what is happening
         'change the cursor to hourglass
        Application.Cursor = xlWait 'built in application running cursor
         ' makes sure that the statusbar is visible
        Application.DisplayStatusBar = True 
         'add your message to status bar
        SPgField1 = Selection 
        SPgField2 = Selection.Offset(1, 0) 
        Dim pt As PivotTable 
        Dim Wsht As Worksheet 
        On Error Resume Next 
        For Each Wsht In ThisWorkbook.Worksheets 
            For Each pt In Wsht.PivotTables 
                pt.PageFields(1).CurrentPage = SPgField1 
                pt.PageFields(2).CurrentPage = SPgField2 
            Next pt 
        Next Wsht 
         'restore default cursor
        Application.Cursor = xlDefault 
         ' gives control of the statusbar back to the program
        Application.StatusBar = False 
        MsgBox ("Done! Your Report Summary is ready.") 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thank you so much in advance!

I'm new to excel macro and new to this forum, and I have found a lot of helpful material here already.

I need a macro that will automatically change a table's autofilter to the same selections/values of the pivot table filter when the pivot table's pivot filter is changed.

For Exaple:
I have a pivot table with a pivot filter Date in B3 with value 1/1/2011, 2/1/2011, and 3/1/2011

I have a table(at A18) with a autofilter Date with the same value 1/1/2011.2/1/2011. 3/1/2011.

What I would like to do is when I change the pivot filter in B3 (for example, select 1/1/2011 and 2/1/2011), the table autofilter will be set to 1/1/2011 and 2/1/2011.

I would prefer to have two-way capabilities, but I can work with only one-way change.

This is the code I came up with so far, it only works with single selections, but I would need multiple selection capabilities. I'm using Excel 2007

    Dim KEY As Variant: KEY = Sheets("Yesterday").Range("B3").Value 
    Select Case KEY 
    Case Is = "(All)" 
        Sheets("Yesterday").Range("A18").AutoFilter Field:=2, Criteria1:=1 
        Sheets("Yesterday").Range("A18").AutoFilter Field:=4 
    Case Else 
        Sheets("Yesterday").Range("A18").AutoFilter Field:=2, Criteria1:=1 
        Sheets("Yesterday").Range("A18").AutoFilter Field:=4, Criteria1:=Sheets("Yesterday").Range("B3").Value 
    End Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thank you in advance for any help and insight!

i have a few worksheets that are identical in layout, if i create a pivot table on one data range i can set it out like i want i can select which headings to use in the row or column field and which should be used for data fields, if i use multiple data ranges i get a different layout if i select the layout setup option ive only got a page field a row field a column field and a value field, what im currently doing to reslove this is to copy one range under the other and then run a pivot table on that,

is it possible to set the layout the same as i have when only using one data range ??


Let me explain my problem in several steps:

I built a PivotTable based on an excel sheet;
I inserted the calculated elements in a field column;
these calculated elements have into the formula the elements of the column, namely:
- field column in which I calculated incorporating elements: DATA
- elements already present in the field (because present in the column of the main reference sheet): FAIL, PASS
- calculated elements: E = (+ FAIL PASS)

Then, I formatted the values as percentage numbers.
In the field of data I have the sums of the quantities of PASS and FAIL that were achieved in a single day (DATA field column), on a same GROUP (field line) and relative to the same STEP (field page).
So the calculated elements use these values.

Now the problem is: after adding the elements calculated in the field column, I need to regroup the field DATE (that I previously separated to include the elements) on the basis of months. But, when I run the command, I get an error message. This message seems to tell that I am trying to enter a field or a calculated component in a field already grouped and tells me to proceed first separating this area, inserting the element calculated and, then, proceed to the grouping. But In reality, when I get the message of error I have already passed the first two steps and I actually am in the grouping stage.
I hope someone can help me, it is pretty important for me..thanks in advance guys

Hello All, and a pre "thank you" for your help with this problem.

I have data which I need composed into a pivot table without using an actual pivot table. My file increases from 60 to 116meg (116meg is unsable over wireless) when I incorporate pivot here's the question. (30,000 rows)/(22 cols)

I need to find the "Largest" sums for CEID/Route (which is the Conc field name) but only for a selected CEID

=large(value1:value30000,1) gets me the highest value, but not in a summary form by CEID/Route for a single CEID

I've tried mulitple statements by combining large with match, index and sumproduct without success...any and all help is very appreciated.

AutomaticConcLotQtySilicon artExp NameRouteCEIDTypeYearWWCostsValue1CNNonEN101254848A1EN10CNNonSUST20081 10 $ 2501CNNdiDN111104848A4DN11CNNdiSUST20081 5 $ 50 1ABCdiDN121104848A4DN12ABCdi
SUST20081 5 $ 50 1ABCdiDN121104848A11DN12ABCdiSUST20081 5 $ 50 1CNNdiDN111104848A12DN11CNNdiSUST20081 5 $ 501ABCdiDN132104848A3DN13ABCdiSUST20081 5 $ 50 1ANNdiDN142254848A5DN14ANNdiSUST20081 5 $ 125

I am using a pivot table to display data ina user friendly way rather than to
really analyze anything. I want to get the "value" of a text field as a data
element in a pivot table. There is only a single value for each of 5 years
for 150 different programs. I thought by asking for MAX value i could fake
it out, but it comes back with 0's rather than the text value. What I want is
something like this:
year 1 year 2 year 3 etc

progarm 1 satisfac excellent fair
program 2 fair poor fair
program 3

In a pivot table how does one set up conditional formatting with a formula
with references to calculated field values such that the conditional
formatting can be copied to other cells in the pivot table? I can set up the
conditional formatting for a single cell in the pivot table just fine. But
when I attempt to copy the formatting from one cell in the data area to
another cell, the formula in the conditional formatting is not using relative
references. I am running Office XP.

I'm developing a reporting pack that references one Excel data list, but
contains 20+ pivots each with different page, row, column and data fields
and some with certain page field values selected.

For time to time, usually when the file gets contaminated with a bug, I have
to manually re-create all the pivots - a manual and painful process.

Does anyone have code for rapidly creating pivots from variables placed in a
single worksheet? I had in mind something similar to John W's custom menu

Thanks, John

Hello experts,
I've reviewed PT0021 Change All Page Fields sample Excel file (for 2007) on and am still having trouble
with my complex workbook with several pivot tables/charts. I have found in
this sample file that it works when you choose an explicit value in the
filter, however when you re-set the filters back to 'all' or choose multiple
values, this does not apply to subsequent pivot tables and worksheets. Is
this only possible with explicit values in the main filter or can the code be
modified to work when choosing 'all'?

Also, my workbook contains several worksheets of data, and subsequent
worksheets of various pivot tables for each set of data. The field names
would be consistent across each worksheet of data, however in my ideal world,
I'd like one main set of filters for a main pivot table to control ALL pivot
tables on subsequent worksheets, which feed from a variety of data worksheets
(all in the same workbook). Am I dreaming? So far, Excel pros that I've
solicited help from using the sample file referenced above have not been able
to make this work. Thank you.

Hello all,

Well I have a question which I believe can be answered in two ways.

Currently I have a database in excel which has been made into a pivot table.
It is just a simple catalog of some books. The row fields are organized as
category, field, sub field and then title. The data items are a brief
summary, catalog number, and where the book is currently located (in the
office or someone else has it).

My problem is all of the items in the data field are currently being
displayed as numbers, which are all 1s since it gets that specific with the
title included as a row field. I am wondering if there is anyway to display
these 1s as the actual text that the value is on the original worksheet where
all this information has been input. In other words, the brief summary
actually being displayed on screen.

Now this is a solution but I would prefer the table to be a bit more
interactive so it does not become overwhelming with all the summaries on
screen, in other words being able to click on the summary and have it come
up. Well this is possible by double clicking, that brings you to a new sheet
with all of the information on that entry but there is no way to easily get
back to the pivot table. Is there anyway to include some kind of button
directly on the spreadsheet to link you back?

I was thinking comments would be a good idea since they become hidden, and I
found a macro that would be a good idea which I found on this site: and is described as Macro
to populate comments in a range with text values of another range

Macro posted by Dave Ramage to obtain cell comment values for a single
range from the text values of another matching single range (based on cell
count of 1st range). Modified to use TEXT value which is the displayed value
instead of value.

But that does not work since the ranges are not the same and stuff will be
added to this spreadsheet as time goes on.

Well any help would be appreciated. I am kind of new to Excel and I figure
these would be pretty simple questions to answer for someone who is familiar
with the program.

Thanks if you were able to read down here! I appreciate your time.

I created a pivot table that somehow interpreted one of my data points in two
different ways. In the table the value is shown as a left justified value and
a right justified value even though the source data is all General, Left
(Indent). So there are two of the same value in the drop down box for
selection in the table and I have to select both in order to include all data
points with this single value. I have completely recreated the table and I
have made sure that the source data format is the same for all occurences of
the data field and then refresh the table. Still shows the two versions of
the single value. This is driving me crazy because I cannot fathom why Excel
does this.


Message posted via

Although I like a lot the way that Excel handles data analysis (and
especially the Pivot Tables) I think that there is still significant "room"
for improvement.

Here is what I suggest:

Regarding FILTERS we need the following improvements on functionality and

1) to be able to formulate more than 2 conditions - probably 10 would be ok;
I know that this could be accomplished by using Advanced Filter but this is
very time-consuming
2) possibility to create conditions that relate current cell with the
coresponding cell from other column
3) to filter after more than one value and on multiple columns when we click
on "AutoFilter" icon - according to the cells we have selected while keeping
CTRL pressed
4) to obtain a "Show excluding" filter when we click on "AutoFilter" icon
while pressing Shift
5) a corresponding button to "Show All" which enables us to remove only the
filter of the current column (or selected columns) - a shortcut key like F6
would also be very useful - so if we have 5 active filters we could easily
cancel one and keep the other 4
6) to be able to see (and activate) each one of the last 10 different filter
conditions used when we click on the filter arrow attached to the header cell
of a filtered column - this could be shown either on the left or the right
7) to be able to save and load certain set of filters - this is very
important when you work with more than 3 custom filters and occasionally
press "Show All" or when you need to switch between different sets of filters
8) to view the current filter condition when we float the mouse pointer over
the column's header cell
9) enhanced visibility for the active filters - not only the small blue
arrow on the grey square


1) to save the pivot table "settings" in order to be able to switch quickly
from one "look" to another - I often have to make at least 4 different copies
of the same pivot in order to quickly show the desired aspects of different
analysis (on the same data set) to my superiors
2) when we make a copy of one pivot table in the same workbook Excel should
base the new pivot, by default, on the "database" used by the original pivot;
3) when we double-click on one of the detail fields the new sheet that
appears should keep the same formatting as the "database" sheet; by
formatting I also refer to the settings like Group/Ungroup and Freeze Panes
4) it is extremely necessary to have a function that allows us to copy the
data from the TOTAL field in a pivot table to a special field in the pivot's
"database"; this would help us when we need to group the data we analyse in
different categories according to the sum in that speciffic field - one usage
is when we need to separate the products that were sold above a minimum
value (or quantity); at this time we can only run a very slow SUMIF on all
data or a VLOOKUP over the pivot
5) there are many situations when our pivot table is based on an enormous
database but we only need to study just a part of it at a time - it would
help us a lot to be able to automatically generate a pivot table with the
same design as the main pivot but based on our specific selection from it
6) each pivot table should be able to auto-freeze its header according to
the changes we brought to its page, column, row and data fields

Other issues:

1) the status bar should be able to display simultaneously at least 3
informations chosen by user: SUM, COUNT and NO DUPLICATES (something like
MODE function) would be a good default option
2) we need an enhanced VLOOKUP function that would be able to search in a
database by more that one single criteria (example: NAME + SURNAME) and to
return more than one field (example: AGE, COUNTRY, SALARY, JOB); I know that
multi-criterial search could be accomplished through DGET but this works only
for one record at a time
3) we need to have an option that would automatically re-order the columns
on a second table based on the columns in the master table. This is extremely
useful when you have to append periodically some data to an existing table or
to merge two tables in order to run an agregated pivot. Please note that this
can't be achieved through horizontal sort since the header of a table isn't
usually sorted alphabetically.

Above is what I consider that would help me, my colleagues and a lot of
power users that I know to use Excel more efficiently. Please let me know
what do you think about my suggestions.

Thank you very much for your attention.

This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

There were a few things I was hoping MS would change for this new version of
Excel, and one of them was being able to set your own DEFAULTS for Pivot
Tables. I haven't found any way to do this yet... in fact, it looks like
it's more work for me to get the table the way I want it to be.

Can anyone tell me whether it’s possible to change some of the basic
defaults for Pivot Tables? The things I’m interested in a
- having the subtotals defaulted to 'none'
- having the pivot table borders defaulted to how they used to be (they
offer dozens of new styles, yet none are how the standard border default used
to be)
- having numbers in my Values column defaulted to currency, no decimal places
- when I create a new Pivot table, the new default is an Outline style,
whereas I need the old tabular – I can change this but it means I have to go
into the field options *for every single field* and change it tabular.

This was one of the huge time-wasters previously... and now it looks like we
have to waste even more time.
Any help on this appreciated!

No luck finding an answer? You could always try Google.