Yes. In Excel for Microsoft 365 on Windows, put a real date field in a PivotTable’s Rows area, right-click a date, choose Group, select Days, and enter 7 for Number of days. That makes seven-day groups; for weeks that always start on Monday or Sunday, add a week-start field to the source data instead and use that field in the PivotTable.
Choose between seven-day groups and calendar weeks
These two approaches answer slightly different questions. Grouping by seven days creates consecutive periods of seven days, but the first period is anchored to the starting date used in the grouping dialog. It does not necessarily begin on a Monday or Sunday. This is useful for rolling operational periods or when the first date in the data should define the reporting cycle.
A calendar week has a consistent boundary, such as Monday through Sunday. If a report must align with a business calendar, a week-start field is usually clearer and more dependable. Each date is assigned the date of the Monday (or Sunday) at the start of its week. The PivotTable can then group records by that date, even when the source data covers multiple years.
There is also a difference between a week and a week number. A week number such as 1 or 12 is a label, not a unique period: the same number occurs every year, and some calendar systems assign dates near New Year to a different week-year. For reports spanning years, use a week-start date or combine the week-year and week number.
Prepare the source dates
Before building the PivotTable, check that the date column contains actual Excel dates, not text that only looks like dates. Select a few cells and inspect the formula bar or change the number format; valid dates can be displayed in different formats while retaining their underlying date values. A date with a time component is still a numeric date-time value, but its time can affect a helper calculation unless it is removed.
Use one header row, give the date field a clear name such as Order Date, and avoid blank rows or merged cells in the source range. If practical, convert the source range to an Excel table with Insert > Table. A table can expand as rows are added, and a PivotTable based on it can include those rows after refresh.
- Keep dates in one column and quantities or values in separate columns.
- Check for blank dates, text entries, errors, and inconsistent date formats.
- Decide whether weeks start on Monday, Sunday, or another business-defined day.
- Keep the original date column; a helper field supplements it rather than replacing it.
Group dates into seven-day periods
- Select a cell in the source data and choose Insert > PivotTable. Confirm the table or range and choose where to place the PivotTable.
- In the PivotTable Fields pane, drag the date field to Rows. Drag a measure such as Sales or Quantity to Values, and choose the appropriate summary, such as Sum or Count.
- In the PivotTable, right-click one of the displayed date labels and choose Group.
- In the Grouping dialog, select Days. Set Number of days to 7, review the Starting at and Ending at values, and choose OK.
Excel displays grouped date ranges as row labels, with the selected measure summarized for each range. The starting and ending boundaries matter: if the first date is a Wednesday, a seven-day grouping can run Wednesday through the following Tuesday. If you need a particular start date, set Starting at to the desired date when the dialog allows it, then check that the resulting ranges match your reporting convention.
In Excel for Microsoft 365 on Windows, the route is right-click a date label > Group. On a Mac, Control-click the label or use a two-finger click to open the context menu, then choose Group if available. Excel for the web can have different PivotTable editing options; if the grouping command is unavailable, use the desktop app or add a week-start field in the source data.
Create Monday-based weeks with a helper field
A helper column is a good choice for Monday-to-Sunday reporting, predictable week labels, or week-based analysis across several years. Suppose the source table has dates in column A, with the first record in A2. Add a column named Week Start. With a date value in A2, enter this formula in the helper column’s first data row:
With the source date in A2, this formula returns the Monday on or before that date: =A2-WEEKDAY(A2,2)+1. For example, if A2 contains March 5, 2025, a Wednesday, the result is March 3, 2025.
WEEKDAY with the second argument set to 2 numbers Monday as 1 and Sunday as 7. Subtracting that weekday number and adding 1 moves the date back to the Monday of the same week. Format the helper column as a date, then refresh or create the PivotTable and place Week Start in Rows. Grouping is not required for this method.
If the source dates include times, remove the time portion before calculating the week start. With a date-time value in A2, use =INT(A2)-WEEKDAY(INT(A2),2)+1. INT removes the fractional part that represents the time, so records on the same calendar day receive the same week-start date. In an Excel table, a formula entered in a calculated column generally fills down for the other rows.
For a Sunday-start week, with the source date in A2, use =INT(A2)-WEEKDAY(INT(A2),1)+1. For example, a Wednesday, March 5, 2025, maps to Sunday, March 2, 2025. State the chosen convention in the report so readers know what each period includes.
Build and read the weekly summary
Once the date grouping or helper field is ready, arrange the other PivotTable fields around the question being asked. Put the week or Week Start field in Rows, a category such as Product or Region in Columns or below the week in Rows, and a numeric field in Values. To count orders, use an order identifier and select Count; to total revenue, use a numeric revenue field and select Sum. If Excel defaults to Count for a field expected to total, check whether the source column contains text or blanks.
For example, a sales table might have Order Date, Region, Order ID, and Sales columns. Place Week Start in Rows, Region in Columns, and Sales in Values summarized by Sum. The result shows sales by region for each Monday-based week. Adding Order ID to Values and choosing Count gives a separate view of order volume; it is not the same as summing sales.
Format the row labels as dates, and consider a clear custom display such as a month-day-year format. Avoid showing only a week number when the report spans years. If users need to filter to a year or product, add the appropriate field to Filters or use a slicer from the PivotTable Analyze tab. A week-start date is also useful for chart axes because it identifies the actual boundary date.
Week numbers, year boundaries, and labels
Weeks around New Year deserve special attention. Depending on the reporting standard, a week may belong to the year containing its Thursday, the year containing January 1, or another defined week-year. Excel’s week-number conventions can differ, so a bare label such as Week 1 can be ambiguous. A Monday-start date avoids that ambiguity because it is a real, unique date for each weekly period.
If a business requires official week numbers, confirm its exact rule before building the report: when the week begins, what counts as the first week, and which year owns a week that crosses December and January. Use the same convention in the source data and in any external report. Do not assume that a seven-day PivotTable grouping, a calendar week, and an organization’s fiscal week mean the same thing.
Refresh, sorting, and common problems
A PivotTable does not necessarily update its summary just because source cells change. Right-click inside the PivotTable and choose Refresh, or use PivotTable Analyze > Refresh. If the source is an Excel table, new rows can be included when refreshed; if the source is a fixed range, new rows outside that range may need the source range changed through PivotTable Analyze > Change Data Source.
- Group is unavailable: inspect the date column for blanks, text dates, errors, or mixed values. Correct the source and refresh. A helper week-start column is an alternative when grouping remains unsuitable.
- Groups start on the wrong day: this is expected when seven-day bins are anchored to a different starting date. Set the starting boundary if appropriate, or use a Monday- or Sunday-based helper field.
- Dates appear as months or quarters: Excel may have created automatic date groups. Use the date field that represents the intended level, or ungroup and create the desired grouping.
- Weeks sort unexpectedly: check whether the row labels are text such as Week 1, Week 10, and Week 2. A real week-start date sorts chronologically more reliably than text labels.
- New records are missing: refresh the PivotTable and confirm its source includes the added rows. A source Excel table can make range expansion easier.
For troubleshooting, inspect a few source records on either side of a week boundary. Confirm that the dates belong to the intended week and that the PivotTable is summing or counting the intended field. This small check can catch a wrong start day, a text-formatted date, or a mistaken Values summary before the report is shared.
Which method should you use?
Use Group > Days > 7 when you want fast, consecutive seven-day bins and the starting boundary is acceptable. It keeps the source simple and is convenient for one-off analysis. Use a helper field when weeks must align to a named weekday, when the report spans years, or when the same weekly definition needs to be reused across multiple PivotTables.
A helper field also makes the definition visible in the data and easier to audit. Its trade-off is that the source needs an extra calculated column and the formula must be filled for new rows. If workbooks are shared with people who may not know the rule, label the field clearly, for example Monday Week Start, rather than simply Week.
Frequently Asked Questions
Can Excel group PivotTable dates by week automatically?
Excel can group dates into seven-day periods: place the date field in Rows, right-click a date, choose Group, select Days, and enter 7. The resulting periods may not align with Monday or Sunday, so use a helper field when the week must follow a particular calendar boundary.
Why is the Group option greyed out?
Common causes include blank cells, text instead of genuine dates, errors, or mixed data in the source date column. Check and correct the source values, refresh the PivotTable, and try again. A week-start helper column can also avoid relying on date grouping.
How do I make weeks start on Monday?
Add a Week Start column using the Monday formula above, then put that field in the PivotTable Rows area. Format it as a date. Each date, including dates in different months or years, maps to the Monday of its week.
Can I show week numbers instead of dates?
You can use a week-number field, but include the year or week-year as well so labels are not repeated across years. Confirm the business’s week-number convention first. A week-start date is often a clearer label and sorts naturally.
Does a PivotTable update when I add new rows?
Refresh the PivotTable after changing the source. If the source is a fixed cell range, extend that range to include new records; an Excel table is often easier to maintain because it can expand as data is added.
Related
- Protect Sheet in Excel: Allow Editing Specific Cells
- Excel Conditional Formatting Icon Sets Explained
- Markdown Table Merge Cells? What You Can Do
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Add a Bar Chart in Excel: Steps and TipsTo add a bar chart in Excel for Microsoft 365 on Windows, select the category labels and numeric…
- Bubble Chart in Excel: How to Create and Read OneTo create a bubble chart in Excel for Microsoft 365 on Windows, arrange your data in three columns—X…
- KPI Chart in Excel Using a Pivot Table: Setup and TipsTo make a KPI chart in Excel, summarize your source data in a PivotTable, insert a PivotChart, and…
- Excel Graph Not Updating? Fix Charts That Won’t RefreshIf an Excel graph is not updating when its data changes, first check that the chart points to…
- Combo Charts in Sigma: How They Work and When to Use ThemYes. Sigma supports combo charts, which combine visual forms such as bars and lines in one chart so…
- How to Update and Refresh a Pivot Table in Google SheetsTo update a pivot table in Google Sheets, change the source data and the pivot table will usually…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.