pivot table totals

JLa

New Member
Joined
Oct 29, 2002
Messages
4
I'm trying to get a pivot table to cum the subtotals within the pivot table by using a calculated field and can't figure out how to do this. I have data as listed below. The field I'm trying to insert for multiple categories within the pivot table is the CTD.

Thanks for any help you can provide. Jan Feb Mar Apr
cat 1 10 10 10 10
Tot 10 10 10 10

CTD 10 20 30 40
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
Like this?
Book1
ABCDEFGHIJKLM
1CategoryValueMonthMonth
2cat110JanCategoryDataJanFebMarAprGrandTotal
3cat110Febcat1SumofValue1010101040
4cat110MarCTD10203040
5cat110AprTotalSumofValue1010101040
6TotalCTD10203040
7
Sheet6
 

JLa

New Member
Joined
Oct 29, 2002
Messages
4
Yes, that is exactly what I need.

How were you able to do that? Thanks for your help.
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
You must reorganize your data into 1st Normal Form (1NF) as shown above. If I understand you original post your data contains a "repeating group" of values indexed by month.
This message was edited by Mark W. on 2002-11-04 14:09
 

JLa

New Member
Joined
Oct 29, 2002
Messages
4

ADVERTISEMENT

Mark,

I'm not sure I understand what you mean.

I believe the data is already in 1NF (although I'm not sure exactly what that is) and the data does have repeating values for multiple categories that is indexed by month/yr.

From the example you gave me, I still don't understand where you CTD came from. Thank you very much for your help and I don't mean to be so ignorant.
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
By definition 1NF is devoid of repeating groups. You should not have separate columns for each month.
 

JLa

New Member
Joined
Oct 29, 2002
Messages
4

ADVERTISEMENT

Mark,

My data is properly formatted.

Is your CTD a calculated field? I can't tell from your example how that field is being generated.
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
No, 'CTD' is created by dragging another copy of 'Value' to the PivotTable's DATA area, changing its name to 'CTD', clicking the [ Options >> ] button, and setting "Show data as" field to "Running Total in" and the "Base field" to "Month".
 

attc

Board Regular
Joined
Oct 11, 2002
Messages
87
As part of my learning from a separate post on a similar topic, I came across the following tip from the j-walk site that people may find useful:

http://www.j-walk.com/ss/excel/usertips/tip068.htm

In simple terms, this is basically a technique for organizing your data better for the purposes of summary and analysis.
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
I rather like John Carter's Programming in SQL with ORACLE, INGRES, and dBASE IV. His section on Normalization in chapter 2 is very comprehensible. The key is to put Excel data lists in 1NF to maximize the utility of PivotTables.
 

Forum statistics

Threads
1,144,741
Messages
5,726,016
Members
422,653
Latest member
mntsiki

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top