Google Sheets has a nice advantage over Excel - their pivot table automatically refresh, while Excel's pivot tables rely on clicking a manual UI refresh button (or - if the right pivot setting is changed from its default state - by reopening the relevant file).

But Google Sheets' pivot tables do not get refreshed in certain cases.

If you turn this data into a pivot table:

Name Date Value
Foo =RANDBETWEEN(today()-1, today()) 1
Bar =RANDBETWEEN(today()-1, today()) 2

And add a date filter of today:

Pivot table filtering today

Let's say luck has it that on the original run you get: | Name | Date | Value | | -------- | -------------- | - | | Foo | =today() | 1 | Bar | =today()-1 | 2

Then until the end of days you'll see Foo in your pivot table, unless you change something else of course.

That's because Google Sheets' pivot tables don't recognize the change of days (when the clock spins and yesterday becomes today, in this case).

Is there a way around it?

FYI, if you adjust the file to make hourly calculations it makes no difference! As in:

Time based recalculation of Google sheets

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.