To get weekly totals in an Excel PivotTable, put a valid date field in Rows and a numeric amount field in Values, then right-click a date and choose Group > Days > 7. For calendar weeks that always start on the same weekday, add a Week Starting helper column first and use that field in the PivotTable; grouping every seven days does not always create Monday-to-Sunday weeks.
How weekly totals work in a PivotTable
A PivotTable summarizes rows of source data into categories. For weekly totals, the date determines which week a record belongs to, and the amount field is added together for all records assigned to that week. For example, a sales table might contain one row per transaction, with columns for Date, Product, and Sales. Putting Date in Rows and Sales in Values can show a total for each date; grouping or using a weekly helper field rolls those daily records into weekly totals.
The source data should have one clear header row and consistent columns. Each transaction or event should normally occupy one row, rather than spreading dates across separate columns. The date column needs to contain real Excel dates, not text that merely looks like a date. The amount column should contain numeric values if you want a sum.
For a concrete example, suppose three transactions fall on Monday, March 2, 2026, Wednesday, March 4, and Sunday, March 8, with sales of $120, $80, and $50. A Monday-start weekly summary should show $250 for the week beginning March 2. The PivotTable is calculating that sum from the source rows; it is not estimating or averaging the activity.
Group dates into seven-day periods
In Excel for Microsoft 365 on Windows, click inside the source data and choose Insert > PivotTable. Choose the table or range, select where to place the PivotTable, and select OK. In the PivotTable Fields pane, drag the date field to Rows and the amount field to Values. If Excel initially places the amount under Rows or Columns, drag it into Values.
When the dates appear in the PivotTable, right-click one of the date labels and choose Group. In the Grouping dialog, select Days, set Number of days to 7, and check the Starting at and Ending at values. Clear other selected time units if you want only seven-day groups, then choose OK. Excel displays one row for each group and sums the amount field when its summary setting is Sum.
This method is quick when consecutive seven-day blocks are all you need. The Starting at value matters: it anchors the first block, and later groups follow in seven-day increments. If the first record is a Wednesday, a group beginning there can run Wednesday through the following Tuesday. It is not automatically aligned to the calendar’s Monday-to-Sunday week or to a company’s chosen reporting week.
On Mac, the general process is also to insert a PivotTable, arrange its fields, and group the date field; the dialog and field-list layout can differ from Windows. In Excel for the web, available PivotTable commands may vary. If you cannot find the grouping controls or need a precise week definition, use the helper-column method in desktop Excel.
Use a helper column for consistent calendar weeks
A helper column makes the week boundary explicit. For a Monday-start week, it returns the Monday date for each source row. With transaction dates in A2:A100, put the heading Week Starting in B1; with the date for the current row in A2, enter this in B2:
With transaction dates in A2:A100 and the helper column beginning in B2, this formula returns the Monday of the week containing the date in A2: =A2-WEEKDAY(A2,2)+1. For example, if A2 is March 8, 2026, the result is March 2, 2026. Format column B as a date, then fill the formula down alongside the source rows.
Now build or update the PivotTable using the source data that includes the helper column. Put Week Starting in Rows and Sales in Values. Rows with the same Monday date are combined, so a Monday-through-Sunday week is grouped together regardless of which day has the first transaction. For a Sunday-start week, the weekday calculation needs a different convention; decide which day begins your reporting week before creating the helper.
A real date is usually better than a text label such as “Week 10.” It sorts chronologically, remains useful for date filters, and avoids ambiguity when the same week number occurs in different years. If you want a friendlier display, format the helper dates or add a separate label, but keep a date field available for sorting and analysis.
Choose the right week definition
“Weekly” can mean different things in different reports. A calendar week, a rolling seven-day period, and a numbered week are not interchangeable. Choose the definition that matches the question your totals are meant to answer.
- Calendar weeks: Use a helper date for the agreed start day, such as Monday. This is usually the clearest choice for comparing weekly business activity.
- Seven-day blocks from a chosen date: Use Group > Days > 7 and set the starting point deliberately. This can suit a schedule that runs in repeating seven-day cycles.
- ISO week reporting: ISO weeks begin on Monday, but week 1 is defined by the ISO calendar and can cross a year boundary. A Monday week-start date is helpful for grouping, but a simple calendar year plus week number may not reproduce ISO week-year rules.
Pay particular attention to weeks that cross December and January. A week beginning in late December can contain January dates. Grouping by a Monday start date keeps the whole week together, while grouping only by a week number can mix years unless the year is included in the category. For formal reporting, agree on the week convention and year treatment with the people who use the report.
Check the value field and the result
Excel may summarize a field as Count instead of Sum, especially if the source column contains text, blanks, or a mixture of numbers and text. In the PivotTable, open the menu for the value field and choose Value Field Settings. Select Sum, then choose OK. If Sum is unavailable or the total still behaves unexpectedly, inspect the source values: numbers stored as text may need to be converted to numbers.
Check a few weekly totals against the source rows before relying on the report. Filter or sort the source data to a particular week and add its values, then compare that result with the PivotTable. Also verify that the final partial week is included if the source begins or ends in the middle of a week. A PivotTable will only summarize rows present in its source range.
Formatting is separate from the calculation. To show currency or decimal places, right-click a value in the PivotTable, choose Number Format, and select an appropriate format. Using Number Format for the value field helps preserve the display after refresh; applying a format only to selected cells may be less reliable if the PivotTable changes size.
Common problems and practical fixes
- “Cannot group that selection”: Check that every used cell in the date field contains a valid date or is handled appropriately. Text dates, error values, and inconsistent entries can prevent grouping. Convert the source dates to real Excel dates and remove or correct invalid entries, then refresh the PivotTable.
- Dates are grouped from the wrong weekday: The seven-day grouping starts from its chosen anchor, not necessarily the weekday you expected. Use a helper column if the report requires fixed Monday-to-Sunday or Sunday-to-Saturday periods.
- The PivotTable shows Count instead of Sum: Inspect the source amount column for text values or mixed data types, then choose Sum in Value Field Settings after correcting the data.
- New rows are missing: A PivotTable may use a fixed source range that does not include rows added later. Convert the source range to an Excel table with Insert > Table before building the PivotTable, or adjust the PivotTable data source. Refresh the PivotTable after adding or changing source records.
- Weeks appear out of order: Use actual date values in the Week Starting field rather than text labels. If labels are required, retain a date field for chronological sorting.
When the source changes, right-click the PivotTable and choose Refresh, or use Data > Refresh All to update workbook connections and PivotTables. Refreshing recalculates the summary from the source; it does not necessarily expand a fixed source range to include new rows. An Excel table as the source helps the range expand as records are appended.
When a PivotTable is not the best fit
A PivotTable is useful when you want to rearrange categories, filter records, and explore totals without writing a separate summary formula for every week. It is also convenient when the source has many transactions and you want to compare weeks by product, location, or salesperson. Add another field to Rows or Columns, or use a filter, to break down the weekly totals.
A formula-based summary can be preferable when a report needs a fixed layout for printing, when the results must feed a specific template, or when each weekly output has custom logic. A helper column can still define the week consistently, even if the final report is built another way. For most readers who want a refreshable summary and interactive filters, a PivotTable is the straightforward option.
Frequently Asked Questions
Can Excel group PivotTable dates by week?
Yes. Right-click a date in the PivotTable, choose Group, select Days, and enter 7. Choose the starting date carefully: seven-day grouping follows its anchor and may not match calendar weeks.
How do I make a PivotTable show Monday-to-Sunday totals?
Create a Week Starting helper column that returns the Monday date for each source date, then place that field in the PivotTable Rows area and the numeric field in Values. This gives every date in the same Monday-start week a common category.
Why does Excel say it cannot group the dates?
The source date field may contain text, blanks, errors, or values Excel does not recognize as dates. Correct the inconsistent entries, ensure the dates are real Excel dates, and refresh the PivotTable.
Why is my weekly total a count?
The value field may contain text or mixed data instead of consistently numeric values, or Excel may have chosen Count as its summary. Correct the source data, then use Value Field Settings to select Sum.
Will a PivotTable include new rows automatically?
It depends on the source range. If you add records outside a fixed range, the PivotTable may omit them. Using an Excel table as the source helps include appended rows; refresh the PivotTable after updating the data.
Related
- Power Query Split Cell Into Rows: Steps and Examples
- Power Query Editor: How to Split Text into Multiple Columns
- Power Query Union Tables: How to Append Queries
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- How to Make a Histogram Start at 0 in ExcelTo make histogram bins start at 0 reliably in Excel, create bin intervals beginning at 0, count the…
- Pivot Table Data Source Reference Not Valid? How to FixIf Excel says “The PivotTable field name is not valid” or “Data source reference is not valid,” check…
- How to Embed an Excel Chart in PowerPoint and Edit ItTo embed an Excel chart in PowerPoint, select the chart in Excel, copy it, then in PowerPoint choose…
- How to Change Date Format in an Excel ChartTo change date labels in an Excel chart, right-click the date axis, choose Format Axis, then use the…
- Google Sheets Chart Series Name: Meaning and FixesIn Google Sheets, a chart series name is the label that identifies one set of plotted values, usually…
- Google Sheets Pivot Tables: Create, Customize, and RefreshYes, you can create a pivot table in Google Sheets using Insert > Pivot table. Select a range…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.