cumulative total in merged pivot table

nishpearl123

Board Regular
Joined
Jun 14, 2010
Messages
96
I have created a pivot table from date in 2 different sheets.

Both sheets only has date in common. Report is working fine.

My report has filed called - "Amount Receivble" (AR).

Now, i wish to to have AR and also a cumulative total of AR.

But I am not finding how to only insert AR again and do the cumulative total for that newly inserted field only.

pl. assist.

regards, nishith
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
On the Field Settings dialog click Options and select 'Running Total' in under 'Show data as'.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,957
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Add the Value field again and set its Options to display as Running Total in Row. The exact steps vary depending on the version of Excel you are using.
 

nishpearl123

Board Regular
Joined
Jun 14, 2010
Messages
96
Hi thanx for the immediate reply. (i use Excel 2007)

Since i have crated the date by giving 2 ranges both ranges in seperate sheets, in the pivot Table Field List, it gives only 4 fields :

1. Row : contains the common field of both the ranges - "Date"
2. Column : contains all the fields from both the ranges
3. Value
4. Page 1 : it is automatically put in the filter box (it has options to view data of 1st range or the 2nd range

But when i add the "value" field again in the valves box, it adds all the column fields again.

Further, if i add the calculated field, it shows me the 4 options as mentioned bove, instead of the entire fields on range1 and range2

Now, please guide me again, as how i can i avoid the repeatation of the all column fields?

and how can i use the caluclated filed.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,957
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
But when i add the "value" field again in the valves box, it adds all the column fields again.
That's because you now have two values for each column. You can either rearrange the data to show the two values within each column field, or you can have the columns grouped within the value field, but you have to show all columns for both values.

For calculated fields you are limited to what is shown in the formulas dialog.
Pivot tables built from consolidated ranges are extremely limited, IMO, which is why I never use them.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,639
Messages
5,660,026
Members
418,542
Latest member
Sandy3503

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