You can use a pivot table to display the top or bottom values in a set of data. In the example shown, one pivot table is used to show the top 3 scores in a set of data, and another pivot table is used to show the bottom 3 values in the same set of data. Because one pivot table was cloned from another, they share the same pivot cache, and both will update when either pivot is refreshed.

How to make this pivot table

  1. Create a new pivot table on the same worksheet
  2. Add the Name field to the Rows area
  3. Add the Score field to the Values area
  4. Rename the Score field from “Sum of Score” to “Score " (note trailing space)
  5. Filter values to show “Top 3 items by Score”
  6. Set sort to “Descending by Score”
  7. Copy entire pivot table and paste at cell I4
  8. Filter values to show “Bottom 3 items by Score”
  9. Set sort to “Ascending by Score”
  10. Disable grand totals if desired

To build a pivot table to summarize data by month, you can use the date grouping feature. In the example shown, the pivot table is uses the Date field to automatically group sales data by month.

Pivot Table Fields

In the pivot table shown, there are three fields, Name, Date, and Sales. Name is a Row field, Date is a Column field grouped by month, and Sales is a Value field with the Accounting number format applied.

pivot table sum by month field configuration - 1

The Date field is grouped by Month, by right-clicking on a date value and selecting “Group”.

Pivot table sum by month date grouping - 2

Steps

  1. Create a pivot table
  2. Add fields to Row, Column, and Value areas
  3. Right-click a Date field value and set “Group” setting as needed

Notes

  1. Number formatting doesn’t work on grouped dates because they behave like text.
  2. As a workaround, add helper column (s) to source data for Year and Month, then use those fields for grouping instead.