Free Microsoft Excel 2013 Quick Reference

Pivot tables: Display all row fields even without data

Hi there,
I have a macro which creates a pivot table with three page fields, 5 row fields, and formulas which use the results of the pivot table.

Everything is hunky dory until I select a page field which only has data for two of the row fields. Since there are only two of the row fields with data, when I select said page field, my results collapse down to two row fields & screw up my formulas.

Is there any way to make a pivot table display all row fields regardless of whether or not they contain data?

Thanks,
Christina


I recently discovered the tickbox "Show all data" in the Field Settings tab
of a Pivot Table. This is just what I needed as I reference data in the
table using GETPIVOTDATA and this would return N/A if the field is not
there.

What puzzles me is how does Excel know what all the field values could be if
they are not used in the underlying list. For example I have a Cash book
that divided the year into 6 periods. During period 1 the other period are
not represented but the pivot table displayed all 6 (Actually also included
a 0 field, which is extra). How does Excel know what all the field values
could be?

To test the feature I used a list with all 6 periods represented and deleted
rows with periods other than 1. Could it be that the pivot table
"remembered" all the possible field values.

Thanks
Laurence

I recently discovered the tickbox "Show all data" in the Field Settings tab
of a Pivot Table. This is just what I needed as I reference data in the
table using GETPIVOTDATA and this would return N/A if the field is not
there.

What puzzles me is how does Excel know what all the field values could be if
they are not used in the underlying list. For example I have a Cash book
that divided the year into 6 periods. During period 1 the other period are
not represented but the pivot table displayed all 6 (Actually also included
a 0 field, which is extra). How does Excel know what all the field values
could be?

To test the feature I used a list with all 6 periods represented and deleted
rows with periods other than 1. Could it be that the pivot table
"remembered" all the possible field values.

Thanks
Laurence

My pivot table has two row fields and one data field.
The far right row field is the pub name and the left row field is available inventory. My data is monthly copies shipped. I want to sort my data in descending order by inventory. When i do field settings - advanced - descending - using field available inventory nothing happens. If I remove the pub name from the pivot table then it sorts by inventory but I need the pub name in the pivot report. Thanks for any advice.

Is there a way of having the first cell in each row completed with the field
name. Even though the cell is left blank as the field name is stated above,
having the field name repeated means the pivot table can be pasted elsewhere
and can be another database for another use.
Our Pivot table holds all our YTD actual financial transactions and budget
lines.
At present we foresee the need to reduce the database as the number of rows
in the next few months will be too long for an Excel Pivot table.
So I would like to create a summary pivot table for P1,2 and 3 and then
paste this as the data replacing the detailed and many lines.

Hi,

I'm going to make a lot of pivot tables that have dates in the row field and dollars in each of one to three data fields. Often, one of the data fields will have data from earlier dates than the others. I want to be able to display only the dates where data exists in all three data fields. If any of the three is blank, I want to exclude that date.
...Does anyone have a good way to do this? I can't seem to figure it out, but thinking it would involve using vba to uncheck the display option for a bunch of the row field items. (btw I'm using 2003, I think 2007 might have a display range function..)

Also, I'm not getting the intended effect when I uncheck "show items with no data" in the row field. For example, I am seeing a pivot table with three rows (instead of two, i.e., not displaying the middle row) when I use this for the pivot table area:
date / $$
1/1/1990 / 100
2/1/1990 /
3/1/1990 / 200

I'm beating my head against the wall on this; thanks a lot for any help on either of these two problems.

Tai

I am having trouble using a calculated item with a pivot table that has multiple row fields. The table below shows correctly.

******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA3=
ABCDEFGHIJKLMNO3Sum of Amt Month            4CustomerLocationJanFebMarAprMayJunJulAugSepOctNovDecGrand Total5Customer ALocation 1100010001000100010001000100010001000100010001000120006 Location 2100010001000100010001000100010001000100010001000120007 Location 3100010001000100010001000100010001000100010001000120008Customer A Total 300030003000300030003000300030003000300030003000360009Customer BLocation 11000100010001000100010001000100010001000100010001200010 Location 41000100010001000100010001000100010001000100010001200011 Location 51000100010001000100010001000100010001000100010001200012Customer B Total 3000300030003000300030003000300030003000300030003600013Customer CLocation 51000100010001000100010001000100010001000100010001200014 Location 61000100010001000100010001000100010001000100010001200015 Location 71000100010001000100010001000100010001000100010001200016Customer C Total 3000300030003000300030003000300030003000300030003600017Grand Total 900090009000900090009000900090009000900090009000108000Sheet2 
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

However, when I add a calculated field for Q1 (using field "Month" =Jan+Feb+Mar) every location is displayed for each customer. Like this:

******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA3=
ABCDEFGHIJKLMNOP3Sum of Amt Month   

Hi all,

In my Pivot table I have 3 fields in the "header - section" of each row
There is also the possibilty in the Page section to choose between subjects (eg physics, chemistry, biology etc)
When all rows are displayed there are 68 in total

When I choose Physics there are about 30 customers that have a value in 1 of the rows.
Excel shows 30 rows, but I would like all 68 row to be shown, because these are the values that are important to me.

I have tried a lot of settings in the pivot table but can't find the correct 1.

What happens a lot is the the rows are "multiplied", meaning that the 1st row header has every combination of the 2nd and 3rd and so one

Which setting is needed to get what I want?

Thanks for your help
Hein

PS
I've tried the field settings in both the columns as the rows
This works for the columns, so far not for the rows

Hi! I'm trying to loop through all the row fields in two pivot tables in two sheets of a workbook. I've done this many times before when looping through page headers, but I can't figure out how to do it when I'm using row fields instead of page headers. Here's the detail:
[Summary] sheet, [PivotTable3], [Eco_Sls_Algn] field in column A (dropdown list in A10)
[Detail] sheet, [PivotTable2], [Eco_Sls_Algn] field in column A (dropdown list in A9)

After I select one [Eco_Sls_Algn] field, I go to another procedure, then back to the next [Eco_Sls_Algn] field and continue until I've gone through all [Eco_Sls_Algn] fields.

I noticed a similar posting, but couldn't make it work for me, since it had a lot more things going on than I'm looking to do.

Is there someone who would be willing to help with this? I'm sorry if I haven't posted this correctly. This is my first time posting on this site. Thank you very much for any help you are able to provide. Have a great day! ...Lori

Hi all, I have a database in the following format
Pivot Tables

I would like to get a pivot table to have row fields using date and column field using M/C. The data to be dropped in will be the percentages.

when i construct pivot table, the grand total that i see is the absolute sum of the percentages.

Instead, i would like to get the grand total of percentage based on the Total failure/total input for a particular M/C.

Can anyone help me please?

Dear all,,

I have a problem here.
Kindly refer to atteched spreadsheet.

In the worksheet, you find 2 worksheet:
1. Category : Stores the Category code and description
2. Transaction: Stores the Category Transactional information.

IN the Transaction spreadsheet, you find 2 pivot.
1. Default Pivot table view
2. Desired Pivot table view

Using the standard Pivot Table wizard, i am able to create that view. Now, i would like my pivot table ("Default Pivot Table") to display like "Desired Pivot Table", with category name.

1. It has to display the Category Name
2. It has to display all category names even if there are no transactional information for that category code.

What i have tried so far:
In order to display, my only method is to vlookup the code from Transaction worksheet's category code to return category name. I want to find out if there are ways in which i would not touch any formulas/macros.... as my data for each month will be changing, i wanna minimise the truncation of data.

Pls help...

thanks!

We have some big data sets to summarise using pivots. And Excel page-filter drop-downs are not as helpful as our users would like. I want to see if I can give them what they want in VBA without insisting on 2010 Slicer functionality.

our problem
There is a definite "hierachy" of dependent filters which is best outlined in terms of what the analysts at Amazon must have: eg:
Page Filter 1: Dept (Department)
Page Filter 2: Item

And you might have Geography for a row field, Months for Column and Sum of sales as data

The problem is that if you filter on a particualr dept the dropdown filter for Item still offers every item from every department. What we want is to just offer the items from that department (ie possible matches given the dept filter)
I need to pick up filter change events and then make some filter values invisible

Where I am so far:
I can pick up a change in the pivot using Worksheet_PivotTableUpdate
I can tell which filter (if any) changed
I can define a hierarchy of filters (hard-coded)
But how can I tell which items to display in the Page Field (for Item) if I change the Dept Page field?

Thoughts so far: Either:
Duplicate the pivot table. Remove the Row fields and set Item as a Row field. I should then have possible values of Item. Capture them and use to set visible values in the original pivot
OR
Use the property DataBodyRange. Find the column (Dept) and filter on the selected value. Capture the possible values for item (capturing unique)
OR
Is there a way of querying the internal data of a pivot to get what I want?
OR
Has anyone managed to do this already? Am on 2003 but could maybe get upgrade to 2007 for users.

I don't think it makes sense (yet) to post the code I have. But I would appreciate pointers for next step.
Going try my first option whilst I am waiting

I have seen this question posted many times and not seen an answer - but some indication that 2010 Slicers are the answer. Would be interested in confirmation.

Mike

1) In a pivot table with mutliple "row fields" I need the data label for
each row field to repeat on each and every line instead of grouping them with
blank cells between each unique row field.

2) Also, I have multiple data items as well. If the value is zero or
blank, is there a way to make that value for the person not show up at all
instead of a line with a blank or zero value?

I know this data would be better managed in Access....but our company does
not let us use that program.
Thanks!

When I use "show pages" at pivot table, it shows all pages by the selected
page field with or without data. How to show page only with those with data?

Can I use VBA for this?

Hi,

I have a small Excel database with the following columns:

A) Transaction Date (TD)
B) Status (can be either Complete, Dead, or Pending)
C) Commitment Fee (CF)
D) Administration Fee (AF)
E) Downpayment Fee (DF)
F) Legal Fee (LF)

I have created a small pivot table.

Along the row: Status
Along the column: Transaction Date

Data: Commitment Fee, Admin Fee, Downpayment Fee, and Legal Fee

I have further grouped Transaction Date into quarters and years.

I have added to the "Years" the Sum by using Field Settings. So now I have
the sum of each of the fees per year.

My question is, how can I get the Grand Total for all the fees per year?

Any advice is most appreciated.

Kevin

This is tricky and I am not sure if it can be done. I am using Xl97 and have create a Pivot Table with following set of data
Test_Name, Result_Text, Result_Value.

For some of the tests, result text & result values are the same, but for others they are not due to the fact that some tests do not require EXACT measurement. For example, Test 1 could have Result_Value = 0.5 and Result_text = 0.5. However Test 2 could have Resutl_value = 3 and Result_text is < 3. When I display result value in the pivot table, everything works just fine (it shows up as SUM Result_value). However, when I try to the text field, pivot table displays the data as Count which does not help me in anyway. Is there a way to display data in the pivot table that is not numeric? As in above example, I would like to show Test 2 as

1) In a pivot table with mutliple "row fields" I need the data label for
each row field to repeat on each and every line instead of grouping them with
blank cells between each unique row field.

2) Also, I have multiple data items as well. If the value is zero or
blank, is there a way to make that value for the person not show up at all
instead of a line with a blank or zero value?

I know this data would be better managed in Access....but our company does
not let us use that program.
Thanks!

Well I thought this would be a simple pivot table to throw together, but it's got me stumped.

Basically I have 3 columns of date. Transactions, date and time of day. What I'm trying to do is create a pivot table which compares the transactions at a specific hour day by day.

I've attached the data, now for some reason when I tried I had the hours in the row field. The date in the column field and placed the transactions in the data item field. When the pivot table displayed all the date fields showed 1's instead of the actual data values.

Can someone point me where I'm going wrong, thanks..

I have a Pivot Table with the Row Field populated with Client codes (ABC).
When I manually set the Feild Settings of the Row Data to Ascending I get the
following order below. Note the first code is out of order. When I put it
in decsending order I get the reverse order with the code JFM last in order.
Whey is this?

JFM
AXD
DTK
EIL
EQZ
FLD
GAJ
GOM
HNI
HUN
HZT
MSH
PGU
QAG
RJE
RUM
SLJ
TAH
UIS
VGJ
VZH
WCY
WLO

Dear Sir
In my pivot table under the row Field when there is no data it is showing
(Blank). How can I avoid appearing (Blank). I dont want to hide the whole row
as there are data in adjacent cells. If I put a space on the cells where
there is no data, it will solve my problem. But it is not easy to do all the
time. Please help

Santhosh
Kuwait

Hi all, I have a pivot table with 3 row fields, 1 column field and 1 data field.
I have a total on the first row field, which sums up the data at that level.
What I want to be able to do, on the same level is count the number of entries in the 2nd row field.
If I try to do this, it adds a second total line and counts the underlying data, not the distinct values in the pivot.

Does anyone know if there is a way to display a count of the number og records in the 2nd row field?

thanks in advance,

Matt

Hello all,

I am trying to get a calculated item to work correctly. I have a pivot table
with two row fields (code, description), three column fields (plan,
elevation, date) and one data field (amount). I am using Excel 2000 on
Windows 2000.

The row fields have a one-to-one relationship with each other (each code has
one description), therefore each code entry occupies only one line. Each plan
has three elevation values, and each elevation has two date values.

The calculation I am trying to achieve is the difference between the value
(amount) of the first date entry and the second date entry for each cost code.

What I have tried is to click on the date field in the column area,
Formulas:Calculated Item. Then in the calculated item pop-up: click on the
date field, click the most recent date and insert item, type a minus sign,
then click the old date and insert item. Then click OK.

Excel appears to hang at this point. However, I waited long enough (some
fifteen plus minutes) and finally excel came back with a solution. However,
the output was not what I wanted.

The pivot table returned ALL description values for EACH code value in the
row area, with the amount value populated only on the correct (corresponding)
description line. It also appears to have sorted the descriptions WITHIN each
code.

The formula line calculates correctly on the one line per code that has
values.

What did I do wrong?

Example output:
I hope the formatting is legible.

Plan1
Elv1
Code - Desc - Date1 - Date 2 - Formula
1 1D 100 150 50
5D
13D
8D
2D
2 1D
5D
13D
8D
2D 80 120 40

Thanks in advance.

When I use "show pages" at pivot table, it shows all pages by the selected
page field with or without data. How to show page only with those with data?

Can I use VBA for this?

Hi,

first of all I would like to thank everybody involved in this forum answering questions and solving problems.
I have learned a lot from the posts and replies.

I have a problem in excel 2007 with pivot tables.

DATA
The underlying data is a list of items which have been created in the database and the actual date the item was created on.
So basically two fields, date (DD/MM/YYYY) and summary.
At the bottom of this post I listed some demo data

FUNCTIONALITY
I want to present this data in a bar chart, grouped by month, showing for the months where no items where created an empty bar, and to have the posibility to select a date range ...

WHAT HAVE I TRIED
I create the pivot table and set the values to Count of Summary and set the row label to Date.
This creates the pivot table with for every unique date a count of items on this date. Perfect

I want to see the data grouped by month so I right click one of the date fields in the pivot and select "Group ..."
There I select Months & Years and leave the grouping to auto.
This groups the items by month, however months with no items in the underlying data are not displayed.

To display those, I set on the field settings of the Date Row, the option under layout and print : "Show items with no data".
This generates for every month with no underlying items an empty row.

At this moment everything looks ok, but now I want to filter the pivot within a certain date range.
Therefore I select again "Group ..." and change
the "starting at" to eg 01/07/2010 (DD/MM/YYYY format)
& "ending at" field to eg 01/07/2011 (DD/MM/YYYY format)

The pivot table shows a grouping field < 01/07/2010, another field > 01/07/2011, and for 2010 all the months (jan to dec) and for 2011 all the months (jan to dec).

I however would expect that
- jan / feb / mar / apr / may / jun is not shown in the 2010 group
- aug / sep / oct / nov / dec is not shown in the 2011 group.

I have the ability to deselect via a filter the < 01/07201, and also eg 'jan'.
However when I deselect 'jan' via the filter it dissapears from the 2010 group (which is good) but also from the 2011 group (which is not good)

Is this something that is possible with pivot tables?

I control the underlying data as well, so if needed I can change the layout of the datasource.
However a workaround to create for every grouping a dummy empty record in the underlying data is not an option as this would generate over 1 million records.

Demo data

Date Summary
1/01/2010 Item 1
1/03/2010 Item 2
1/05/2010 Item 3
2/01/2010 Item 4
5/05/2011 Item 5
9/07/2011 Item 6
10/12/2009 Item 7
10/12/2010 Item 8
10/12/2011 Item 9
21/01/2010 Item 10
3/01/2010 Item 11
4/01/2010 Item 12
8/01/2010 Item 13
14/01/2010 Item 14
22/01/2010 Item 15

Hopefully everything is clear ...
If extra info in needed, do not hesitate to ask ...

Thx in advance,

Hi,

if I have a pivot table, is it possible to sort the data by the field in the "data area"?

I can sort the data by the fields in the "Rows area" and in the "Columns area", but not by the field in the "data area", which is an aggregated Sum or Count etc.

Thanks
Katarina[/img]