Brought to you by:

Hi Reader,

Welcome to the Google Sheets Tips newsletter #365, your Monday morning espresso, in spreadsheet form!

Fall is beautiful on the Eastern seaboard of the US right now. The colors near me are peaking in an explosion of reds, oranges, purples, and yellows:

➜ News

I.
​More powerful pivot tables now available in Connected Sheets. Connected Sheets lets you use the Sheets interface to analyze BigQuery data (huge datasets with millions of rows).

Read more here >>

II.
​Google hints at AI Advanced Services in Apps Script. Ooh, let's hope this prediction comes true!

Read more here >>

Brought to you by: Rows Can You Outsmart an AI Spreadsheet? Rows.com is an AI-powered spreadsheet that helps you load, analyze, and share data faster than ever. Its built-in AI Analyst can handle just about anything you throw at it… or can it? This week Rows is challenging spreadsheet fans like you to find a prompt that Sheets or Excel can solve, but Rows can’t. Prove it, share your results using #BreakTheAnalyst and tagging @rowshq, and you could win a $50 Amazon gift card. The first 20 successful challengers win. Entries close November 8. Join the #BreakTheAnalyst Challenge

➜ Sheets Tip #365: Weeks in Pivot Tables

If you work with pivot tables a lot, you definitely want to be using the date grouping option. We featured this in tip 319 last year.

It lets you group dates by days, months, quarters, or years.

Weirdly though, the feature does not have a week grouping option.

I think it's possibly to do with the fact that some start their weeks on Sunday whilst some others start theirs on Monday.

So how can we fix this in a pivot table?

Since we can't do it directly in the pivot table, we need to add a column to our original data that calculates the week number of a given date.

If you start your weeks on a Sunday, use this WEEKNUM formula:

For a Monday start, use this one:

The extra argument "2" tells the formula to consider Monday as the first day of the week.

Our data looks like this with the additional column of week numbers (counting from the start of the year):

Back in our pivot table, we can use this new Weeknum column as our Rows setting:

The week numbers are counting up from the 1st week of the year. If you want a different week number (e.g. we want to show 1, 2, 3, 4 etc. instead of 36, 37, 38 etc.) subtract a fixed number from your WEEKNUM formula, e.g.

Have a great week!

Cheers,
Ben

P.S. The retirement equivalent of a mic drop.