Excel -- Pivot Tables -- Custom Calculations % of Column   % of Row  % of Total   Difference from   % Difference from   % Of   Running Total in   Remove a Custom Calculation   Download the zipped sample file	Amazon.com

In a Pivot Table, you can summarize the data by using the values in other cells in the data area. For example, you can show each Region's total as a percentage of the national total. Or, calculate the difference between the sales totals for the current year, and the sales totals for the previous year. The examples shown here are based on this zipped sample file .

% of Column In this example, the pivot table has Region in the Row area, and Total in the Data area. A custom calculation will be added, to show the percentage for each region's sales, compared to the national total. From the Pivot Table field list, drag another copy of the Total field to the Data area. If the data fields are arranged vertically, you can change them to a horizontal layout, by following the instructions here. Right-click the heading cell for the new column, and select Field Settings... In the Field Settings dialog box, type a name for the field, e.g. %Sales Click the Options button, to expand the dialog box From the Show data as dropdown list, select % of column Click the OK button

% of Row In this example, the pivot table has Item in the Row area, Region in the Column area, and Total in the Data area. The total will be changed to a custom calculation, to show the percentage for each region's sales of an item, compared to the item total. Right-click one of the cells in the Data area, and select Field Settings... In the Field Settings dialog box, type a name for the field, e.g. %Sales Click the Options button, to expand the dialog box From the Show data as dropdown list, select % of row Click the OK button

% of Total In this example, the pivot table has Item in the Row area, Region in the Column area, and Total in the Data area. The total will be changed to a custom calculation, to show the percentage for each region's sales of an item, compared to the Sales Grand Total for all Items. Right-click one of the cells in the Data area, and select Field Settings... In the Field Settings dialog box, type a name for the field, e.g. %Sales Click the Options button, to expand the dialog box From the Show data as dropdown list, select % of Total Click the OK button

Difference from In this example, the pivot table has Region in the Row area, and Total in the Data area. Date is in the Column area, grouped by Year. There are instructions here for grouping data. The total will be changed to a custom calculation, to compare the current year's sales for each region, to previous year's sales, in dollars. Right-click one of the cells in the Data area, and select Field Settings... In the Field Settings dialog box, type a name for the field, e.g. Change Click the Options button, to expand the dialog box From the Show data as dropdown list, select Difference From From the Base field list, choose Years From the Base item list, choose (previous) Click the OK button

% Difference from In this example, the pivot table has Item in the Row area, and Total in the Data area. Date is in the Column area, grouped by Year. There are instructions here for grouping data. The total will be changed to a custom calculation, to compare the current year's sales for each Item, to previous year's sales, as a percentage. Right-click one of the cells in the Data area, and select Field Settings... In the Field Settings dialog box, type a name for the field, e.g. %Change Click the Options button, to expand the dialog box From the Show data as dropdown list, select % Difference From From the Base field list, choose Years From the Base item list, choose (previous) Click the OK button

% Of In this example, the pivot table has Item in the Row area, Region in the Column area, and Total in the Data area. The total will be changed to a custom calculation, to compare each Region's sales to Ontario's sales, as a percentage. Right-click one of the cells in the Data area, and select Field Settings... In the Field Settings dialog box, type a name for the field, e.g. %Ontario Click the Options button, to expand the dialog box From the Show data as dropdown list, select % Of From the Base field list, choose Region From the Base item list, choose Ontario Click the OK button

Running Total in In this example, the pivot table has Region in the Column area, and Total in the Data area. Date is in the Row area, grouped by Year and Quarter. There are instructions here for grouping data. The total will be changed to a custom calculation, to calculate a running total of sales for each Region, over each Year. Right-click one of the cells in the Data area, and select Field Settings... In the Field Settings dialog box, type a name for the field, e.g. Sales Click the Options button, to expand the dialog box From the Show data as dropdown list, select Running Total in From the Base field list, choose Date Click the OK button

Remove a Custom Calculation To remove a custom calculation from a pivot table: Click the dropdown arrow on the Data field button Remove the check mark from the custom calculation. Note: If you remove an item from the data area, it's removed from the pivot table. To replace it, you can drag it back from the pivot table field list.

1. Pivot Tables -- Dynamic Data Source 2. Pivot Tables -- Data Field Layout 3. Pivot Tables -- Show and Hide Items 4. Pivot Tables -- Clear Old Items 5. Pivot Tables -- Field Settings 6. Pivot Tables -- GetPivotData 7. Pivot Tables -- Grouping Data 8. Pivot Tables -- Multiple Consolidation Ranges 9. Pivot Tables -- Printing   10. Pivot Tables -- Custom Calculations 11. Pivot Tables -- Pivot Cache      12. Pivot Tables -- Protection    13. Pivot Tables -- Grand Totals

Home     Excel Tips     Excel Files      Blog    Contact
		The Excel Store	Debra Dalgleish