Pivot Table Issues

How to Show This Week and Last Week Counts in Excel Pivot Tables

Anum Mushtaq Oct 21, 2026 868 views

Question details

Display project status totals for two reporting periods side by side in a Pivot Table.

How to Show This Week and Last Week Counts in Excel Pivot Tables

Product: Excel
Device & OS: not provided
Scenario: Comparing project statuses across two consecutive reporting periods, such as this week and last week.
Observed behavior: Requires source data normalization to prevent duplicate values and achieve the correct side-by-side comparative layout in the Pivot Table.

Before you start

Ensure your source dataset is formatted as a flat table without any merged cells or blank rows before creating your Pivot Table.

Solution 1Recommended

Normalize Source Data and Configure Pivot Table Fields

By restructuring your source data and properly allocating the Pivot Table fields, you can display side-by-side weekly comparisons without duplicating counts.

If your data is spread across multiple columns for each week's status, the Pivot Table cannot easily compare them. Normalizing the data (unpivoting) ensures that each project and period combination occupies a single row. Once organized, placing the periods in the Columns area creates the desired side-by-side layout.

Restructure your source data

Organize your spreadsheet so that you have three distinct columns: 'Project', 'Period' (containing values like This Week or Last Week), and 'Status'.

Insert the Pivot Table

Highlight your newly organized data range, navigate to the 'Insert' tab on the ribbon, and click 'PivotTable'. Choose to place it on a new worksheet.

Assign fields to areas

In the PivotTable Fields pane, drag the 'Period' field into the 'Columns' area to create the side-by-side layout. Next, drag the 'Status' field into the 'Rows' area.

Add values and apply filters

Drag the 'Project' field into the 'Values' area to count the projects. To avoid duplicates and focus on specific data, drag 'Status' into the 'Filters' area and select your target status, such as 'On Track'.

Normalize Source Data and Configure Pivot Table Fields

Filtering Tip: Filtering by a specific status prevents duplicate project entries across different statuses, giving you an accurate week-by-week comparison.

Manage Data Easily with WPS Office

Easily Compare Weekly Pivot Table Data in WPS Spreadsheet

WPS Spreadsheet provides a robust, user-friendly interface for creating and managing Pivot Tables. You can easily normalize your data, configure fields, and compare weekly counts just as you would in Microsoft Excel, with full format compatibility.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office, open your .xlsx file, and ensure your data contains separate columns for Project, Period, and Status.
2. Insert a Pivot Table: Select your entire data range, go to the 'Insert' tab, click 'PivotTable', and confirm the destination worksheet.
3. Configure the comparison layout: Drag the 'Period' field to the Columns box, 'Status' to the Rows box, and 'Project' to the Values box to immediately see your side-by-side weekly counts.

Fully compatible with Microsoft Excel (.xlsx) files and Pivot Table structuresIntuitive drag-and-drop Pivot Table builder for quick data analysisAdvanced data filtering, sorting, and grouping capabilitiesFree, lightweight, and fast alternative to heavy office suites

microsoft office alternative - wps office

Frequently Asked Questions

Why are my weekly counts showing duplicate values in the Pivot Table?

This usually happens when your source data isn't properly normalized. Ensure you have one row per project per period. If a project has multiple statuses recorded in the same week, counting projects without filtering for a specific status will result in duplicates.

How can I filter the Pivot Table to show only specific statuses?

Drag the 'Status' field into the 'Filters' area of the PivotTable Fields pane. A drop-down menu will appear above your Pivot Table where you can check the specific statuses (like 'On Track' or 'Delayed') you want to analyze.

Can I group actual dates into weeks automatically in a Pivot Table?

Yes. If your source data uses actual calendar dates instead of text labels like 'This Week', right-click any date value in the Pivot Table, select 'Group', highlight 'Days', and set the 'Number of days' to 7 to group the data by weeks.

Excel & Data Problems