# Show Percentage Subtotals In Pivot Table

Jul 18, 2007

Does anyone know a setting to display pivot tables as a % of sub total automatically within the Pivot Table Settings? I have recreated a formula on the cells H:K of what I am looking for. see Attached.

## Pivot Table - Subtotals

Jun 21, 2007

I have a seemingly very simple question but even though I've worked a lot with pivots I can't find the answer.

clientcode Amount countries
a1 1.000,00 kenia
a2 2.000,00 kenia
b3 1.000,00 kenia
b4 3.000,00 kenia
b5 2.000,00 kenia
c5 1.000,00 senegal
c6 3.500,00 senegal
c7 4.000,00 senegal
c8 5.000,00 senegal

Lets say I have a list like this and I want to count the number of clients (3) or countries(2).

I can only get the total of rows per client but not the subtotal 3 for the number of clients.
a - 2
b 3
c - 4

## Pivot Table - Just Certain Subtotals

Jul 24, 2007

I can't find a way to display "just" subtotals which are > 500k ..

## Pivot Table Subtotals Are Wrong!

Aug 27, 2007

With the following data Excel's pivottable's subtotal shows underlying MAX total instead of subtotals relating to values displayed.

SalesRepOrderNoItemValue
ACON12334
ACON12322
ACON12412
ACON12424
ACON1248

Pivottable:

Sum of ItemValue
SalesRepOrderNoTotal
ACON12356
AC ON12444
AC Max34

Rather than use SQL (I don't have data on SQL server) or re-organising data by order number, is there a fix using calc items, calc fields or grouping to show correct MAX figures? Why Excel doesn't simply evaluate visible cells seems crazy to me!

## Pivot Table Subtotals Are Wrong Instead Of Zero

Dec 21, 2009

I've just started using Pivot Tables and have a problem right away. Couldn't find anything with the search function, but am not really sure, what to search for, either

Part of my data looks like this

NameLot #BinAmountABC123CB18C174.025 ABC123CB18C-24.975 ABC123CB18C-24.975 ABC123CB18C12.616 ABC123CB18C-12.616 ABC123CB18C-24.975 ABC123CB18C-24.100 ABC123CB18C-25.000 ABC123CB18C-25.000 ABC123CB18C-25.000

Now this is a material that is stored on a pallet with the code CB18C. The total sum of material that is added to/taken from this pallet adds up to Zero. My Pivot Table, however, believes the total is something like 2.84217E-14.

Sum of Amount　　　NameLot #BinTotalABC123CB18C2.84217E-14　123 Total2.84217E-14ABC Total　　2.84217E-14Grand Total　　2.84217E-14

This problem occurs several times, the raw data adds up to Zero, but the Pivot Table calculates these tiny, tiny totals. Is this a known bug or can Pivots not handle 3 decimal data?

## Suppress Pivot Table Subtotals

Jan 18, 2007

When I have two or more fields on the left of the pivot layout, sometimes I only want the line item data and the grand totals, but not the various subtotals.

Is there a way to suppress the various subtotals created by a pivot table?

## Default Pivot Table To No Subtotals

Jun 6, 2007

Pivot tables. They have a habit of adding automatic subtotals to each field of data you drop into the pivot. Is there any way of setting the default to "none" rather than "automatic". It's driving me insane.

I am toying with the idea of writing some code to eradicate this issue, but if there's something native to Excel which will do the trick, that would be much more efficient.

## Pivot Table Columns/rows And Subtotals

Oct 13, 2009

attached Pivot Table. There is a section, which I've marked, that shows how I want my Pivot Table report to look. I've had Excel 2007 kick me out a number of times when I've tried to filter and shift rows and columns to accomplish this and areas are frequently grayed out. Can someone please assist?

I'm attaching a file rather than going into a lot of detail here because I think a visual is easier here. The raw data, my attempt at the Pivot Table and my desired outcome all show on the same tab.

## Totals And Subtotals Of A Calculated Item In A Pivot Table?

Mar 17, 2014

I have an issue with a calculated item in my pivot table, because in the totals and subtotal it shows the sum of the column but I want to show the formula that I've specified to the calculated item.

For example, in the attached file, I have in rows the field "name" that has four values (A,B,C,D), in columns the field "Groups" that has "G1" and "G2". I add in the rows the calculated item "G1/G2" that has the formula G1/G2. The problem is that in the total the column "G1/G2" doesn't show the division of the total of "G1" and "G2".

## Excel 2010 :: Expanding Subtotals In Pivot Table

Oct 29, 2012

I have two pivot tables where if one expands a subtotal (in this case 'manufacturer') to give the lines below (products made by the manufacturer), is it possible for this to be mirrored in the second pivot? I noted a previous comment on using slicers across two pivots but I cannot do this in this instance, although I am using Excel 2010.

## Change Font In Bold For Subtotals Only In Pivot Table

Jun 30, 2006

I have a pivot table with 10 sub total rows, I would like to know if it is possibe to change the font into bold for rows who are subtotals.

## How To Calculate Percentage In Pivot Table

Jul 6, 2012

I have a pivot table in which 50 rows data and each row contains in the end %age. End of report i get grand total but it sums the percentage column. I need to calculate percentage in the end of grand total. How can i get it.

## Display Percentage Of In Pivot Table

Apr 4, 2008

I have 2 columns in a Pivot Table. One called Sum of AD Total, the other called Sum of Total Cost.

I want to add a third field that displays the Sum of AD Total as a percentage of the Sum of Total Cost.

So I assumed I would use the "% Of" facility in the "Show data as" drop down, and "Total Cost" in the Base Field drop down. But what goes in the Base item field as whenever I try and put something in I get N/A returned as a result in my PT?

I'm attaching a very simplified version.

## Pivot Table - Calculating Percentage Of Two Columns?

May 14, 2014

why it works (but it obviously does). I crabbed off the sheet, modified it with my data and the percentages calculate reliably.

What I can't figure out is that in the Pct calculation in the table is the formula:

=GETPIVOTDATA("S_FCR",PT_1,"Center",[Center],"Date",MONTH([Date]))/GETPIVOTDATA("C_ID",PT_1,"Center",[Center],"Date",MONTH([Date]))^2

The formula itself makes perfect sense in terms of the numbers and filtering involved....but why does it actually work and what does the "^2" do?

## Remove Percentage Of Row Total In Pivot Table?

Feb 7, 2014

how to remove the percentage of row total in a pivot table. I only want the sum of the row total but the percentage of row column always say 100% and I want it to be removed.

## Finding Cumulative Percentage In Pivot Table?

May 21, 2014

I have data of supplier spend and need to find percent of suppliers having top 80% spend.

How to get the cumulative spend within the pivot table itself. I can do it outside the table by calculations, but how to get the results directly using pivot table, because i am using that data in a KPI metric and a dashboard.

if not possible in pivot table, can i get it in a single step process using percentrank or percentile formula or anything else?

## Pivot Table: Adding A Percentage Field As '% Of Subtotal'

Nov 21, 2007

In the attached Excel file, there is a pivot table.

In the Data part of the table, there are two columns. The second is the exact same field than the first one but expressed as percentage (Field settings/Options/Show field as "% of" + "Total".

The problem is: I don't want to express this field as a % of Total ('Grand total') but rather as a % of Subtotal (e.g. Danemark Total).

## Pivot Table Percentage Of All Data (including Filtered)

Oct 14, 2013

How to show my data as % of grand total and include the filtered data. Whenever I filter out a category in my pivot table the % values adjust for everything unfiltered. Is there a way to have my percentages include the filtered data?

## How To Compare Percentage Difference Between Date Range Through Pivot Table

May 9, 2014

I would like to compare % difference between date range through pivot table

Date RangeProductAmt
1st weekABC1000
2nd WeekABC2000
3rd WeekABC1259
1st weekXYZ3000
2nd WeekXYZ4000
3rd WeekXYZ2500

How to plot a Pivot table to compare the % difference between 3rd Week Vs 2nd Week Vs 1 Week for each product through pivot table?

## Excel 2007 :: Calculated Field To Get Percentage Of Two Columns In Pivot Table

Oct 2, 2013

I'm having an issue getting the correct results from a calculated field in a pivot table in Excel 2007.

In column A I have "Business Name", in B I have a sum of the amount of lines a customer has, and C is a MAX of the number of employees the customer has at their location.

In one example I have a business with sum 50 lines and max 30 employees but when I try to make the calculated field 50/30 (should equal 166%) i get 9.2% instead.

The reason appears to be because there are 18 types of lines the customer has and 18 x max 30 = 540 and 50/540 is 9.2%. In the attached example it's all the same business location so the total number of employees (30) is the same for each row and each row in the raw data is a set of lines with similar features.

I attached an example. CalcFieldProblem.xlsx

## Excel 2010 :: Adding Percentage Change Column To Pivot Table?

Mar 17, 2012

I am trying to add columns to my Excel 2010 Pivot Table to calculate % change of sales from year to year. The field name is "Year", while the item columns in the field are 2007, 2008, 2009, 2010, and 2011. How do I add a column between the years that will calculate the % change os sales up or down from the previous year?

## Pivot Table Show All

Jan 22, 2009

I am trying to form a macro in VBA that will basically uncheck the "Show All" function of the Pivot Table filter user interface, and then select only the one PivotItem that I want. In context, I have about 50+ different project numbers, each with a different worksheet and its corresponding pivot table. Here is what I have so far:

Dim pvtitem
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Project #")
For Each pvtitem In .PivotItems
pvtitem.Visible = False
Next
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Project #")
.PivotItems("525064").Visible = True
End With

When I try to run it, it gives me the error: "Unable to set the Visible property of the PivotItem class". It looks like the error occurs in the loop part of the macro.

## Getting Pivot Table To Only Show Top 10 Items?

Nov 29, 2011

Is there a way to have a Pivot Table show only the Top 10 items based on dollar amount. Data covers a month of daily activity (+/- 250 rows), but i only want the Top 10 items based on Dollar amount. Is this possible?

I know filters can do top 10 but it doesn't consolidate similar items.

## Show Pivot Table Data By Name

Jan 22, 2008

I have a list of Insurance payers - 20 or so. They are listed in a Pivot Table on sheet2 with rate data and such. On sheet1 I have a listbox with the Payers listed and can be multi selected by the user. I wish to have the user select some payers in the listbox, goto sheet2 and view the Pivot Table only containing those Payers selected from the listbox.

## Pivot Table, Show Empty Rows

Feb 16, 2009

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?

## Make Multiple Name Show Up Only Once In Pivot Table?

Mar 27, 2009

In the attached file (xlsx) under 'Database' Tab poeple have indicated their preferences (multiple choices) for different food items "specialties". The specialties are grouped under broader buckets called "groups". The specialties are bucketed into groups in a way that people end up more than once in each specialties and groups due to their muliple selections. When we create a pivot by specialties (Pivot Specialties tab), each person appears only once for each specialty...it's great. But, when we create a "group" pivot (each group has multiple specialties), now, people appear more than once for each group. Is there a way, each person can show up only once under each group so the group count does not appear to be misleading?

## Show ONLY Grand Total In Pivot Table

Feb 6, 2013

I am building a Pivot table and I need to show ONLY the Grand totals at the end of the table. In the example below I want to show only Total volume Stage movement at the end and not the subtotals in the columns.

Column Labels
Pipeline
Best Case
Commit
Closed

[Code]...

## Pivot Table With Show Pages Macro?

Feb 22, 2013

I am trying to to create a macro that will complete a Pivot Table, then use the Show All pages function. The data has the same number of columns each time, but the rows will differ each and everytime. The content of the column which houses the data which eventually will be a page can differ as well. I continue to get an errro and below is highlighted when debug is selected.

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Table1", Version:=xlPivotTableVersion12).CreatePivotTable TableDestination _
:="Sheet4!R3C1", TableName:="PivotTable2", DefaultVersion:= _
xlPivotTableVersion12

This is a very repititive pivot that is done many times during a month. I have tried just using the data as is then the pivot, and converting the data into a table, doesn't matter still get the error.

## Pivot Table Filter To Show Null / Zero Value?

Mar 11, 2014

I would like to be able to filter my pivot table to shows the rows with no data ?

I can make "no value" appear in the pivot table in 2 ways - either by using column field as well as row fields or by using the "show items with no data" option in value field settings but I cant get filter to show only the "no value" rows

Sum of rev
Column Labels
Row Labels

[Code].....

## Show Detail Event In Pivot Table

Nov 19, 2006

When you double click on a number in a pivot table data area you get a new sheet with that information showing the detail, is there anyway to "trap" this action?

I want to run a macro on the detail data but can't see a way to do it automatically, have tried, BeforeDoubleClick, PivotTableUpdate, NewSheet events but all have there draw backs if you are doing something other than showing the detail for the pivot table. Of course I can just run the macro after the sheet is made but it would be nice to do it on its own.