Filter Data by Week (This, Last, or N Weeks Ago) – Google Sheets

Published on

Want to filter data by drop-down selection such as this week, last week, 2 weeks ago, 3 weeks ago, and so on in Google Sheets? You’re in the right spot.

I’ve covered everything from creating a dynamic week selector drop-down for this purpose to providing step-by-step instructions to implement my formula. In addition, you will find a breakdown of the formula.

For this example, we will use three sheets:

  1. A database sheet named ‘db’.
  2. A sheet to apply the drop-down menu and the filter formula called ‘dashboard’.
  3. A third sheet called ‘helper’ for drop-down values.

All within the same file.

The sample data in the ‘db’ sheet consists of dates in column A and the number of active users of a blog in column B, where A1 and B1 are field labels for ‘Date’ and ‘Active Users,’ respectively.

Sample data filtered by This Week, Last Week, and N Weeks Ago

Set Up a Dynamic Week Selector

In cell A1 of the ‘helper’ sheet, enter the following formula:

=SEQUENCE(52, 1, 0)

This formula creates a sequence of numbers from 0 to 52, representing weeks. You can adjust the number 52 to control how many weeks appear in the drop-down.

In cell B1, enter the following formula to add specific texts to week numbers.

=ArrayFormula(IF(A:A="", ,IF(A:A=0, "This Week", IF(A:A=1, "Last Week", A:A&" Weeks Ago"))))

This logical test returns “This Week” if the number is 0, “Last Week” if the number is 1, and adds ” Weeks Ago” for all other numbers.

Helper sheet for dynamic week selector drop-down menu

Creating the Dynamic Week Selector Drop-down:

  • Navigate to cell A1 in the ‘dashboard’ sheet and click on Insert > Drop-down.
  • Select Drop-down (from a range) under Criteria in the sidebar that appears.
  • Click the table icon in the blank field below and select column B in the ‘helper’ sheet (helper!B:B) in the window that appears.
  • Click OK to close the window.
  • Click Done to close the sidebar panel and create the drop-down.

Next, we code the formula in cell B2 in the ‘dashboard’ sheet to filter the data by this week, last week, and n weeks ago.

Filter by Week (This Week, Last Week, N Weeks Ago)

To filter data by “This Week,” “Last Week,” “2 Weeks Ago,” and so on, we need to determine the corresponding start and end dates for each week. These dates will be used in the condition part of the FILTER function.

Here’s how we can achieve this using the LET function to define week start and end dates named week_start and week_end, respectively, and then incorporate them into the FILTER formula:

=LET(
   week_start, 
     (TODAY()-WEEKDAY(TODAY(), 1)+1)-
     XLOOKUP(A1, helper!B:B, helper!A:A)*7, 
   week_end, 
      week_start+6, 
   FILTER(db!A2:B, ISBETWEEN(db!A2:A, week_start, week_end))
)

Enter this formula in cell B2 of the ‘dashboard’ sheet. This will filter the source data based on the week selected in cell A1.

Filter data by week: options include This Week, Last Week, and N Weeks Ago

Important Customizations

Defining the Week:

“This week” refers to the current week, and “Last week” refers to the previous complete week (based on a Sunday-Saturday week format). This is a common definition, but some users might prefer a Monday-Sunday format. Here’s how you can adjust the formula for that:

To switch to a Monday-Sunday week format, change the 1 parameter in the WEEKDAY function (highlighted in green) to 2.

Cell/Range References:

  • A1: Cell containing the drop-down menu.
  • helper!A:A: Cell range containing the week numbers from 0 to n (52).
  • helper!B:B: Cell range containing the values used for the drop-down in cell A1.
  • db!A2:B: The source data to filter (excluding headers).
  • db!A2:A: The date range in the source data (excluding headers).

The breakdown is provided below for reference, but it is not essential to follow the core instructions.

Filter by This Week, Last Week, N Weeks Ago: Formula Breakdown

The formula in cell B2 utilizes the LET function for simplification:

Syntax:

LET(name1, value_expression1, [name2, …], [value_expression2, …], formula_expression)

This allows us to define names for value expressions and use those expressions in subsequent value expressions and the formula expression.

It avoids repeated calculations and improves performance.

Names and Value Expressions:

  • name1: week_start
  • value_expression1: (TODAY()-WEEKDAY(TODAY(), 1)+1)-XLOOKUP(A1, helper!B:B, helper!A:A)*7 Returns the start date of the selected week for filtering.
    • TODAY()-WEEKDAY(TODAY(), 1)+1 returns the start date of the current week.
    • XLOOKUP(A1, helper!B:B, helper!A:A)*7 looks up the value in A1 within helper!B:B, retrieves the associated numeric value from helper!A:A, and multiplies it by 7.
  • name2: week_end
  • value_expression2: week_start + 6 Returns the end date of the selected week for filtering.

Formula Expression:

FILTER(db!A2:B, ISBETWEEN(db!A2:A, week_start, week_end))

The FILTER function filters the source data range db!A2:B, where the date range in the source data (db!A2:A) falls between week_start and week_end, inclusive.

Compare Performance Across Weeks

One purpose of filtering data by weeks—such as “This Week,” “Last Week,” or “N Weeks Ago”—is to compare performance.

For example, based on our sample data, you can compare the number of active users this week with the number of active users last week (for the same days of the week) to observe trends.

To achieve this:

  1. Copy and paste the drop-down from cell A1 into cell D1.
  2. Use the same formula from cell B2 in cell E2, but replace A1 with D1 within the formula to reference the correct drop-down.
  3. Select “This Week” in the drop-down in cell A1 and “Last Week” in the drop-down in cell D1.
  4. Compare the data side by side to analyze performance trends.
Compare Performance Across Weeks in Google Sheets

This allows you to compare metrics between different weeks to observe increases, decreases, or consistency in performance over time.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Highlight Upcoming Birthdays in Google Sheets

When highlighting upcoming birthdays in Google Sheets, one important aspect is ignoring the year...

Compare Two Tables for Differences in Excel

To compare two tables with similar data for differences, you can use the XLOOKUP...

Calculate Weighted Average in Pivot Table in Google Sheets

You can calculate a weighted average in a Pivot Table using a custom formula...

Summarize Data and Keep the Last Record in Google Sheets

In Google Sheets, we can summarize data (like sum, average, min, max, count) using...

More like this

Highlight Upcoming Birthdays in Google Sheets

When highlighting upcoming birthdays in Google Sheets, one important aspect is ignoring the year...

Calculate Weighted Average in Pivot Table in Google Sheets

You can calculate a weighted average in a Pivot Table using a custom formula...

Summarize Data and Keep the Last Record in Google Sheets

In Google Sheets, we can summarize data (like sum, average, min, max, count) using...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.