How do I show a value instead of a count in a pivot table?

In the PivotTable, right-click the value field, and then click Show Values As. Note: In Excel for Mac, the Show Values As menu doesn’t list all the same options as Excel for Windows, but they are available.

How do I change the values fields in a pivot table?

Select a field in the Values area for which you want to change the summary function of the PivotTable report. On the Analyze tab, in the Active Field group, click Active Field, and then click Field Settings. The Value Field Settings dialog box is displayed.

How do I stop my pivot table from counting?

To stop a pivot table counting blank cells and filling them with the word “blank,” follow these steps:

  1. Click inside the pivot table and choose Control + A to select all the data on the page.
  2. Select Home > Styles > Conditional Formatting and New Rule.
  3. In the box that opens, select Format only cells that contain.

Why is Excel showing count instead of sum?

This happens when your values are in text format and sum can not be performed and only Count can be performed. To test this, put few numbers in few cells and select them. The SUM should appear.

How do I get rid of a count in Excel?

Remove subtotals

  1. Select a cell in the range that contains subtotals.
  2. On the Data tab, in the Outline group, click Subtotal. The Subtotal dialog box is displayed.
  3. Click Remove All.

How do you use values in a pivot table formula?

Click the PivotTable. On the Options tab, in the Tools group, click OLAP tools, and then click Convert to Formulas. If there are no report filters, then the conversion operation completes. If there are one or more report filters, then the Convert to Formulas dialog box is displayed.

How do I create a sum in a pivot table?

In the PivotTable, right-click the value field you want to change, and then click Summarize Values By. Click the summary function you want. Note: Summary functions aren’t available in PivotTables that are based on Online Analytical Processing (OLAP) source data. The sum of the values.

How do I change the number format in a pivot table?

Change the number format for a field

  1. In the PivotTable, select the field of interest.
  2. On the Analyze or Options tab in the Active Field group, click Field Settings.
  3. Click Number Format at the bottom of the dialog box.
  4. In the Format Cells dialog box, in the Category list, click the number format that you want to use.

Why is pivot table showing count?

By default, a Pivot Table will count all records in a data set. To show a unique or distinct count in a pivot table, you must add data to the object model when the pivot table is created. In the example shown, the pivot table displays how many… Pivot tables make it easy to quickly sum values in various ways.

How do I get a pivot table to not count blanks?

Filtering to remove blanks

  1. Click the arrow to the right of a row or column heading in the pivot table. A drop-down menu appears.
  2. Click to uncheck the (blank) check box. You may need to scroll to the bottom of the list.
  3. Click OK.