How to Easily Group Data by Week in Google Sheets

Understanding the Need for Weekly Data Aggregation

In many business contexts, daily fluctuations can obscure underlying patterns. Sales might spike on a Tuesday due to a specific promotion or drop drastically on a holiday. When reporting to stakeholders or performing detailed operational reviews, aggregating these daily figures into weekly blocks provides a clearer, less volatile picture of performance. The week serves as a crucial unit of measure, aligning well with typical business cycles, financial reporting periods, and team performance tracking schedules.

Before grouping the data, it is fundamental to ensure data integrity. All dates must be correctly formatted within Google Sheets (often recognized as serial numbers internally) to allow the WEEKNUM() function to operate accurately. If your date column is formatted as pure text, the function will return an error, preventing the successful creation of the grouping variable necessary for aggregation.

Fortunately, achieving this grouping is remarkably straightforward using the powerful WEEKNUM() function. The following detailed example demonstrates the exact methodology required to transform a list of daily transactions into a clean, weekly summarized report.

Introducing the WEEKNUM() Function in Google Sheets

The core mechanism for weekly grouping lies in the WEEKNUM function. This function calculates the week number of a specific date within a year. Its general syntax is simple: =WEEKNUM(date, [type]). While the date argument is mandatory, the optional type argument is vital for ensuring your weekly grouping aligns with specific regional or organizational definitions of when a week starts.

The type argument dictates which day of the week is considered the starting point (e.g., Sunday or Monday) and whether the first week of the year includes January 1st or requires a full seven days. For instance, using type 1 (the default) indicates that the week starts on Sunday, while type 2 indicates Monday. If you require adherence to the international standard defined by ISO 8601, which mandates that Week 1 is the first week containing a Thursday, you would use type 21.

Selecting the correct type parameter is critical for consistent results, especially when combining data from different sources or ensuring compliance with international reporting standards. Failing to specify the type might lead to discrepancies in week numbering, particularly around the transition between years (Week 52/53 to Week 1).

Step 1: Preparing Your Source Dataset

The first foundational step involves structuring your raw data. For this demonstration, we will assume a dataset tracking daily sales figures. This dataset must contain at least two primary columns: the date of the transaction and the numerical value (e.g., sales amount) associated with that transaction. We will then introduce a third column dedicated solely to housing our calculated week numbers.

Let us begin by creating an initial dataset that illustrates the total sales achieved by a fictional company over a period encompassing several weeks. This setup is crucial for visualizing how the daily entries will eventually be grouped and summarized.

As illustrated above, Column A contains the transaction dates, and Column B holds the corresponding sales figures. We will now prepare Column C (which we can title “Week Number”) to house the grouping variable necessary for our Pivot Table aggregation.

Step 2: Generating the Week Number Column

The primary goal of this step is to extract a consistent numerical value representing the week of the year from the dates listed in Column A. This numerical value will serve as the definitive grouping key. We achieve this extraction using the WEEKNUM() function, which standardizes dates into a manageable range between 1 and 53.

To implement this, navigate to cell C2, which is the first empty cell in our new “Week Number” column. Here, we will input the formula that references the adjacent date in cell A2. For simplicity in this introductory example, we will use the default type parameter, which assumes Sunday is the start of the week.

The following formula should be entered into cell C2:

=WEEKNUM(A2) 

Once the formula is entered, press Enter to calculate the result for the first date. Cell C2 should now display the correct week number for the date found in A2. The next critical step is to apply this calculation across the entire dataset. Instead of manually typing the formula for every row, utilize the “fill handle” (the small square box at the bottom-right corner of cell C2) to drag the formula down, automatically adjusting the cell reference (A2 changes to A3, A4, and so on) for every subsequent row.

After successfully copying and pasting this formula down to the remaining cells in column C, your dataset should now appear complete with a dedicated week number for every corresponding sales transaction, preparing the data for the final aggregation stage:

Deep Dive into the WEEKNUM() Formula (Syntax and Options)

While the basic implementation using the default setting (type 1) works well for many users, understanding the optional [type] argument is essential for professional reporting. Different countries and industries utilize varied calendar standards. Ensuring your analysis follows the correct standard prevents major reporting errors.

Here is a detailed breakdown of the common type values accepted by the WEEKNUM function in Google Sheets:

• Type 1 (Default): Week starts on Sunday. This aligns with U.S. convention.
• Type 2: Week starts on Monday. This is common in many European and Asian countries.
• Type 11: Week starts on Monday.
• Type 12: Week starts on Tuesday.
• Type 13: Week starts on Wednesday.
• Type 14: Week starts on Thursday.
• Type 15: Week starts on Friday.
• Type 16: Week starts on Saturday.
• Type 17: Week starts on Sunday.
• Type 21 (ISO 8601): Week starts on Monday. This is the internationally recognized standard where Week 1 is the first week of the year containing a Thursday. If your organization operates globally, type 21 is highly recommended for calculation consistency.

For example, if you wanted your week numbering to adhere strictly to the ISO 8601 standard, the formula in C2 would be adjusted to: =WEEKNUM(A2, 21). Always verify the required reporting standard before applying the formula broadly, especially when dealing with fiscal or operational data that bridges calendar years.

Step 3: Utilizing the Pivot Table for Aggregation

With the week numbers correctly assigned, the next logical step is to aggregate the sales data based on these new grouping variables. While the QUERY() function could also be used, the easiest and most user-friendly method in Google Sheets is creating a Pivot Table.

A Pivot Table is a powerful data summarization tool that automatically sorts, counts, totals, or averages the data stored in a spreadsheet and displays the results in a new table. To initiate the creation of this table, you must first select the data range that includes both the variables you wish to group by (Week Number) and the values you wish to summarize (Sales).

To create the pivot table, carefully highlight all cells spanning the range B1:C11 (including the headers: Sales and Week Number, and all associated data). Once selected, navigate to the top ribbon menu and click the Insert tab, followed by clicking Pivot table.

Google Sheets will prompt you to select the destination for the new pivot table—typically, selecting “New sheet” is the cleanest option, but you may also choose an existing sheet if preferred. Confirm your selection to open the Pivot Table Editor panel on the right side of the screen.

Configuring the Pivot Table Editor for Weekly Summaries

The configuration phase is where we instruct the Pivot Table how to use the Week Number column for grouping. The Pivot Table Editor panel is divided into four main areas: Rows, Columns, Values, and Filters. For our simple weekly sales aggregation, we need to focus on the Rows and Values sections.

The grouping variable (Week Number) must be assigned to the Rows field. This action tells the pivot table to list every unique week number found in the data as a separate row entry in the resulting summary table. Click “Add” next to the Rows section and select “Week Number.”

Next, the numerical data we wish to summarize (Sales) must be assigned to the Values field. This field controls the calculation performed on the grouped data. Click “Add” next to the Values section and select “Sales.” By default, Google Sheets usually selects the “SUM” calculation, which is perfect for totaling sales. If you wished to find the average sale per week, you would change the “Summarize by” setting to “AVERAGE.”

In the Pivot table editor on the right side of the screen, ensure you choose Week for the Rows and Sales for the Values, maintaining the default summary function of SUM:

Upon correct configuration, the pivot table will instantly calculate and populate the summary table, providing a clean, concise view of the aggregated data.

Interpreting the Weekly Grouped Results

Once the settings are applied, the values in the pivot table will be automatically computed and filled in, displaying the summation of sales corresponding to each unique week number present in the source data. This aggregated view dramatically simplifies trend analysis compared to scanning hundreds of daily records.

The resulting Pivot Table provides a clear, quantitative summary. From this aggregated view, we can derive several key insights regarding the weekly performance:

• The total sales made during Week 2 amounted to 37.
• The total sales made during Week 3 reached 65, indicating a substantial increase over the previous week.
• The total sales made during Week 4 were 62, showing a slight decline compared to Week 3, but maintaining high performance levels.

In addition to the specific weekly totals, the pivot table conveniently provides a Grand Total row, summarizing all the sales across the entire period covered by the source data. We can see that the grand total of sales made across all recorded weeks was 164.

Mastering this technique, which relies heavily on the WEEKNUM() function and the robust capabilities of the Pivot Table, empowers users of Google Sheets to move beyond simple daily tracking and perform sophisticated weekly business intelligence reporting. This method ensures that time-based data is presented logically and effectively for decision-making.