Exceljet

Quick, clean, and to the point

Pivot table last 7 days

To create a pivot table that shows the last 7 days of data (or, more generally, the last n days) you can add a helper column to the source data with a formula to flag records in the last 7 days, then use the helper column to filter the data in the pivot table. In the example shown, the current date is August 16, 2019, and the pivot table shows the seven days previous.

Fields

In the pivot table shown, there are four fields in use: Date, Location, Sales, and Filter.  Location is a Column field, Date is a Row field, Sales is a Value field, and Filter (the helper column) is a Filter field, as seen below:

Pivot table last 7 days field configuration

Formula

The formula used in E5, copied down, is:

=AND(B5>=(TODAY()-7),B5<TODAY())

This formula returns TRUE when a record date is greater than or equal to today's date - 7, and also less than today's date. 

Steps

  1. Add helper column with formula to data as shown
  2. Create a pivot table
  3. Add fields to Row, Column, and Value areas
  4. Add helper column as a Filter, filter on TRUE

Pivot Table Training

If you use Excel, but don't know how to use Pivot Tables, you're missing out...wasting time trying to do things that a Pivot Table can do for you automatically. Core Pivot is a step-by-step Excel video course that will teach you everything you need to know to use this powerful tool. With a small investment, Pivot Tables will pay you back again and again. See details here.