How to Filter Google Sheets Table for Last 90 Days of Entries

google sheets

I have a spreadsheet I update daily that's grown to be ~1700 rows. I reset it every year, but I don't really need to see more than the last 90 days of entries.

I can't use FILTER(), as I still need to make entries and edits to the list, so I'm trying to work with filter view, but the "custom formula" field just doesn't seem to accept any functions.

I've tried "=(Today()-90)" to no avail. The only option that seems to work is to pick a specific date, only I'll have to change this date every so often as the filter grows too large.

Is there really not a way to auto-filter dates based on how far they are from TODAY()?

Best Answer

In your filter, choose "Custom formula is" (bottom of the dropdown list) and enter this formula:

=A2>=TODAY()-90 (replacing A2 with the first data cell in your date column)

Click "OK."

Works similar to conditional formatting rules.

Formula

=QUERY(DATA!A:C, "SELECT A, SUM(C) WHERE A IS NOT NULL GROUP BY A PIVOT B")

Screenshot

enter image description here

This is a good alternative, but formula wise a bit different.

Formula

=QUERY(DATA!A:C, "SELECT A, B, SUM(C) WHERE A IS NOT NULL GROUP BY A, B   
   LABEL SUM(C) 'Costs'")

Screenshot

enter image description here

Example

I've added the results into your dummy sheet.

Google Sheets – Updating Too Often, Slowing Down Performance

What you are seeing is a spreadsheet package's default behaviour.

Google Spreadsehets has an option under File > Spreadsheet Settings > Recalcualtion.

But unfortunately there does not seem to be a "recalculate on user request only" option: instead all the available options include "on change". (This is different to Excel, for example, which does have a manual only option for worksheet calculation).

One option would be to to split your data into multiple sheets, so that the amount of data to be saved in each one is less.

Best Answer

Related Solutions

Google Sheets – How to Filter, SUM, and Display Multiple Values with Conditions

Formula

Screenshot

Formula

Screenshot

Example

Google Sheets – Updating Too Often, Slowing Down Performance

Related Topic