Search Contextures Sites

Excel Pivot Table Tutorial -- Custom Calculations

% of Column   % of Row  % of Total   Difference from   % Difference from   % Of   Running Total in   Remove a Custom Calculation   Excel Pivot Table Tutorial List Download the zipped sample pivot table tutorial file	Amazon.com

In an Excel 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 in this Excel pivot table tutorial 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 in the pivot table tutorial on pivot table data layout. 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 is a pivot table tutorial here for grouping pivot table 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 is a pivot table tutorial here for grouping pivot table 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 is a pivot table tutorial here for pivot table grouping. 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 For more on Running Totals, see Excel Pivot Table -- Running Totals.

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. Download the zipped sample file for this pivot table tutorial

Excel Pivot Table Tutorial List

Excel Pivot Table -- Introduction  Excel Pivot Table -- Create a Pivot Table in Excel 2007  Excel Pivot Table -- Data Field Layout Excel Pivot Table -- Show and Hide Items Excel Pivot Table -- Clear Old Items Excel Pivot Table -- Field Settings Excel Pivot Table -- GetPivotData Excel Pivot Table -- Grouping Data Excel Pivot Table -- Multiple Consolidation Ranges Excel Pivot Table -- Printing    Excel Pivot Table -- Custom Calculations  Excel Pivot Table -- Pivot Cache      Excel Pivot Table -- Protection   Excel Pivot Table -- Grand Totals Excel Pivot Table -- Running Totals   Excel Pivot Table -- Filter Source Data

Excel Pivot Table Tutorials - Pivot Table Blog Totaling Hours in a Pivot Table Time Field   Counting Blank Cells in Source Data   Create a List of Pivot Table Formulas

Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact
Privacy Policy		Contextures Inc., Copyright © 2009. All rights reserved.	Debra Dalgleish