PivotTable Fields is a Task Pane associated with a PivotTable. By default, Excel Pivot table shows sum of numbers if you drag a number column to the value field. Right-click any cell in the pivot table and select Show Field List from the menu. Method #1: Show the Pivot Table Field List with the Right-click Menu. This will be a time card's work date. I did tried your first Pivot Table Option 1 to change the date under Excel 2016 version. Click OK to create the new calculated field. plz help me, m sure u can help me with this. Pivot Tables dates weren’t grouped in earlier versions of Excel, but that behaviour changed in Excel 2016. The calculation won’t show up in the pivot table automatically. Consider Calculated Field as a virtual column that you have added using the existing columns from the Pivot Table. It's kind of a kludge, but it does what you need and should accommodate new data assuming your pivot source range gets updated. Click on Sum of Order Amount in ∑ VALUES area. Select Value Field Settings from the dropdown list. Instead, a new field will appear in the Pivot Table Fields list. The Calc column depicts the type of calculation and there is a Serial number for each. These fields are the sum of the Quantity as well as the sum of the Total cost of an order. Pivot table does not recognize date field in a table as dates I have a pivot table based on a table and I want to group the dates by month in the pivot table. The CountB field appears in the Values area of the pivot table, and in the field list in the PivotTable Field List. Add A Measure. An Hourly Summary if You Have Time with Dates Step 3: Once you insert the field into the pivot table, click on any cell in the pivot table and go to Analyze > Fields, Items, & Sets > Calculated Field. A zero appears if the count is not greater than 2, meaning that the formula result is FALSE. Another amazing use for a measure in a Data Model pivot table is to use the CONCATENATEX function to move text into the values area of a pivot table. Date Grouping in Pivot Tables. In the popup menu, click Number Format. Place the field in the "Value" section of the pivot table tools. Row or Column Labels are used to apply a filter to rows or columns that have to be shown in the pivot table. (You can skip this step. To quickly display or hide the current subtotal, right-click the item of the field, and then select or clear the check box next to Subtotal "