How to Change the Pivot Table Value Type

After making a simple Pivot Table (please learn from here), you can change the calculation type, for example, change sum to count, or average, max, min etc. Please see the steps below:

Step 1: Move your mouse to the column "Sum of Salary", and right-click;

Step 2: In the dialog box, click "Summarize Values By", and select the calculation type from the list, e.g., count.

Step 3: You will see the calculation type is changed from SUM to COUNT.

Alternatively, you can also change the calculation type by using "Value Field Settings" with steps below:

Step 1: Right-click on any number in Column "Sum of Salary", and click "Value Field Settings" from the list;

Step 2: Select "Average" (or any other calculation type) from the list in the Tab "Summarize Values by", then click "OK" at the bottom;

Step 3: The calculation in the Pivot Table will be changed from "Sum of Salary" to "Average of Salary";

Step 4: To format the cells to have the dollar sign and to keep 2 decimals. Select all the cells in the calculation column, right click, then in the "Format Cells" window:

1. Click "Number" Tab;
2. Click "Currency" from the navigation bar;
3. Select or type "2" in the box after "Decimal places";
4. Select "$" sign from the Symbol drop-down list;
5. Decide how to present negative numbers;
6. Click "OK" at the bottom.

Leave a Reply