To create a pivot table with a filter for day of week (i.e. filter on Mondays, Tuesdays, Wednesdays, etc.) you can add a helper column to the source data with a formula to add the weekday name, then use the helper column to filter the data in the pivot table. In the example shown, the pivot table is configured to show data for Mondays only.
Pivot Table Fields
In the pivot table shown, there are four fields in use: Date, Location, Sales, and Weekday. Date is a Row field, Location is a Column field, Sales is a Value field, and Weekday (the helper column) is a Filter field, as seen below. The filter is set to include Mondays only.
The formula used in E5, copied down, is:
- Add helper column with formula to data as shown
- Create a pivot table
- Add fields to Row, Column, and Value areas
- Add helper column as a Filter
- Set filter to include weekday(s) as needed
- You can use the helper column to group by weekday as well