To filter an Excel PivotTable by week, right-click a date in the PivotTable, choose Group, select Days, enter 7 for Number of days, and set Starting at to the first Monday on or before your earliest date. Excel then creates seven-day groups you can filter from the grouped field’s dropdown; for dependable Monday-based calendar weeks, a helper column containing each date’s week-start date is often the better option.
What a weekly PivotTable filter does
A PivotTable normally treats each distinct date as an individual item. Grouping the date field combines those items into larger date intervals, so you can summarize sales, hours, orders, or other values by seven-day periods instead of selecting individual dates. Once grouped, the week groups appear as a field in the PivotTable and can be filtered like other field items.
There is an important distinction between a seven-day group and a calendar week. Seven-day grouping creates consecutive blocks based on a starting date. If the first block starts on a Wednesday, later blocks also start on Wednesdays. To make the blocks run Monday through Sunday, anchor the grouping on a Monday. A group’s starting date determines its alignment; choosing 7 alone does not tell Excel which weekday a week should begin on.
Grouping also does not change the source dates or the underlying records. It changes how the PivotTable organizes them. The values displayed for a week are still calculated from the source rows whose dates fall within that group.
Group dates into seven-day periods
- Click a date item in the PivotTable’s date field. Make sure you select a date label, not a value in the body of the table.
- Right-click and choose Group. In the Grouping dialog, select Days. If other date units are selected, clear them so the grouping is based on days.
- Enter 7 in Number of days. Set Starting at to a Monday on or before the earliest date in your source data. If useful, set Ending at to cover the last date you want included.
- Choose OK. Excel adds a grouped date field to the PivotTable. Use the dropdown beside that field to select or clear the week groups you want to display.
For example, if your earliest record is Wednesday, June 12, 2024, set the starting date to Monday, June 10, 2024. The first group will cover June 10 through June 16, and the following group will cover June 17 through June 23. Records dated June 12 are included in the first group even though the group begins two days earlier.
The exact field label Excel creates can vary with the PivotTable layout and Excel version. If the original date field remains visible, you can remove it from Rows or Columns and use the grouped field instead. Keep the grouped field in the PivotTable’s field list if you want to filter by its weekly items.
Choose the right week definition
Before grouping, decide what your organization means by “week.” Monday-to-Sunday is common for reporting, while some businesses use Sunday-to-Saturday or a custom workweek. Fiscal calendars may have periods that do not follow ordinary seven-day calendar weeks. A PivotTable grouping is only useful if its boundaries match the reporting rule readers expect.
- Monday-start calendar week: Set the seven-day grouping’s start to a Monday, or calculate Monday as the week-start date in a source helper column.
- Sunday-start week: Anchor seven-day groups on a Sunday, or calculate the Sunday date for each source row.
- Custom or fiscal week: Use a maintained calendar table or explicit week-start and week-label columns. A basic seven-day grouping will not automatically reproduce a complex fiscal calendar.
Be careful around New Year. A week that begins in December can include dates from January. That is normal for a week-start-date method, but it may not match an ISO week-number report or your company’s fiscal-year rules. Agree on the week definition first, especially when comparing reports across years.
Use a helper column for reliable calendar weeks
A helper column is usually the most transparent approach when you need every record assigned to the same calendar week regardless of the first date in the source. Add a column such as Week starts, then calculate the Monday for each source date. With source dates in A2:A100 and the first data date in A2, enter this in B2 and fill it down:
With source dates in A2:A100 and the date for the current record in A2, use =A2-WEEKDAY(A2,2)+1. For a Wednesday, June 12, 2024, the result is Monday, June 10, 2024. Format column B as a date so the result displays as a recognizable week-start date rather than a serial number.
Build or refresh the PivotTable using the Week starts column as a Row field, then filter its items in the same way you filter other PivotTable fields. This method gives every Monday-to-Sunday week one consistent key, including weeks that cross month or year boundaries. It also makes the definition visible in the source data rather than hidden in a grouping setting.
For Sunday-start weeks, use =A2-WEEKDAY(A2,1)+1 with the same source layout. For June 12, 2024, that formula returns Sunday, June 9, 2024. If your source is an Excel Table, adding the formula in a new table column generally fills it down for the table’s rows and extends it as new rows are added.
A week-start date is often a better PivotTable key than a week number alone. Week number 1 occurs every year, so grouping only by a number can combine different years if the year is not also included. A date key distinguishes each week across years. For ISO week reporting, use a properly defined ISO year and week or a calendar table; the ISO week-year can differ from the ordinary calendar year near New Year.
Filter one week or a range of weeks
After grouping, use the dropdown on the grouped date field and select the week items to show. Clearing Select All and checking a single item is a quick way to inspect one week. To show several nonconsecutive weeks, check each required item. The PivotTable recalculates its summaries for the visible groups.
If the goal is a continuous range, a date filter such as Between can be useful. Open the dropdown for the original date field and look under Date Filters for the available range options. Set the beginning and ending dates for the period you need. This filters records by date, but it does not create week labels or guarantee that the displayed totals are organized into calendar weeks. Use grouping or a helper field when weekly summaries are the objective.
For an interactive report, add the grouped week field to the PivotTable and consider inserting a slicer for that field where the Excel version and PivotTable type support it. A slicer presents selectable buttons rather than a dropdown. A Timeline is useful for navigating dates at supported levels such as days, months, quarters, and years, but it is not a general-purpose control for selecting custom Monday-to-Sunday week groups.
Refreshes, source data, and common problems
If Group is unavailable or Excel reports that the selection cannot be grouped, inspect the entire source date column. A date-looking value stored as text, a blank, an error, or a mixture of actual dates and non-date entries can prevent date grouping. Convert text dates to real Excel dates and resolve blanks or errors in the source before refreshing the PivotTable. Formatting a text value to look like a date does not convert it into a date value.
- The weeks start on the wrong weekday: The grouping begins from its Starting at date. Choose a Monday or Sunday that matches your reporting convention, or use a helper column.
- A new date does not appear: Refresh the PivotTable after adding or changing source rows. Also check that the PivotTable’s source range includes the new records; an Excel Table can make the source range easier to maintain.
- Dates with times behave unexpectedly: Source values may include time components. If records need to be grouped by calendar date, create a date-only source field and use that field for grouping.
- Weeks appear as numbers or unclear labels: Use a date-formatted week-start field or adjust the field’s number format and PivotTable layout so the group boundaries are easy to interpret.
When you add rows, refresh and check the first and last groups. If the report depends on a fixed date span, verify that the grouping range still covers the new data. With a helper column, confirm that the formula has filled down to every new record. A PivotTable summarizes its source; it does not automatically repair incomplete source ranges or missing formulas.
Grouped dates versus helper fields
Grouping is convenient for a quick analysis: it can turn a date field into seven-day blocks without changing the source table. It is a good fit when the data is clean, the week definition is simple, and the report is mainly for interactive exploration. You can change or remove the grouping without rewriting the source records.
A helper field takes a little more setup but makes the rule explicit and reusable. It is often preferable when several PivotTables must use the same week boundaries, when the data refreshes regularly, or when a report must be audited. A calendar table is more suitable still when you need fiscal periods, holidays, ISO week labels, week-year handling, or other business-specific attributes. You can relate or join calendar information to the data using the approach supported by your workbook’s data model.
Excel for Microsoft 365 on Windows is the reference here. On Mac, PivotTable grouping and field controls may be arranged differently by release, so look for the Group command for a selected date item; a formula-based helper column avoids dependence on that dialog. Excel for the web has a more limited set of PivotTable authoring features than desktop Excel, and grouping commands may not be available in a given web workflow. If you cannot group there, create the helper field in the source and use it as the PivotTable field, or open the workbook in desktop Excel.
Frequently Asked Questions
Can Excel automatically filter a PivotTable to the current week?
Not through a special built-in “current week” date filter in the standard PivotTable dropdown. You can select the relevant grouped week manually, or maintain a helper field and use a reporting process that updates the selected period. A standard date filter can also use explicit start and end dates.
Why does Group show months and quarters but not weeks?
Weeks are made by choosing Days and entering 7 as the number of days. Excel does not necessarily list “Weeks” as a separate unit in the Grouping dialog. Set the starting date deliberately so the seven-day intervals line up with the desired weekday.
Can I filter a PivotTable to a week without grouping dates?
Yes. Apply a date range filter to the original date field using that week’s start and end dates. That limits the records, but the PivotTable will still display its date field at its existing level rather than showing a distinct weekly group.
Does a seven-day group always mean Monday through Sunday?
No. It means seven consecutive days from the grouping start. Set the start to a Monday for Monday-to-Sunday blocks, or use a helper column that calculates the desired week-start date.
Will a PivotTable update its weekly totals when source dates change?
Refresh the PivotTable to recalculate it from changed source data. Check that the source range includes new rows and that any helper-column formula is present for those rows. Refreshing does not fix an incorrectly defined week boundary.
Related
- Excel Number Format for Leading Zeros: A Practical Guide
- Excel Number Format: How to Show Zero as Blank Cells
- Excel Number Format: Red Negative Numbers in Brackets
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Format Chart Area Size Greyed Out? FixesIn Excel for Microsoft 365 on Windows, a greyed-out chart-area size control usually means Excel is not treating…
- Adding Sparklines in Excel: Steps, Types, and TipsTo add a sparkline in Excel for Microsoft 365 on Windows, select the cell where it should appear,…
- Excel Gantt Chart: Build, Format, and Manage a ScheduleYes. An Excel-based Gantt chart is a project schedule displayed as task rows and date columns, with colored…
- Excel Histogram: Add Bins and Control Their WidthsTo add or change bins in an Excel histogram, select the chart’s horizontal axis, open Format Axis, and…
- Add an Upper Limit Line to an Excel ChartTo add an upper limit line to an Excel chart, create a helper series whose value repeats the…
- Excel Pivot Table: How to Sort Items in the Filter ListIn Excel for Microsoft 365 on Windows, open the PivotTable field’s drop-down and choose Sort A to Z…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.