Pivot Table Issues

How to Track Monthly and Yearly Production Totals in Excel

Maira Mehtab Sep 28, 2026 869 views

Question details

The user needs to create an Excel worksheet to record daily production counts and summarize the data by month and year. They also want to calculate and display the totals in dozens to easily identify the most productive periods.

Product: Excel
Device & OS: not provided
Scenario: Tracking daily production quantities over time and generating aggregated monthly and yearly reports.
Observed behavior: Requires a structured method to summarize daily data logs into grouped periods using PivotTables or formulas.

Before you start

Ensure your daily data is organized in a clear tabular format with continuous, properly formatted dates in one column and numerical quantities in another.

Solution 1Recommended

Use Helper Columns and a PivotTable

By adding simple formula columns to your source data and inserting a PivotTable, you can automatically group daily records into months and years without complex configurations.

To easily view your data in dozens, it is highly recommended to add a division calculation in your original data source before building the PivotTable.

Set up your source data columns

Create four columns: 'Date', 'Daily Count', 'Month', and 'Year'. In the 'Month' and 'Year' columns, use the formulas =MONTH(A2) and =YEAR(A2) pointing to your date cell.

Calculate dozens in a helper column

Add a fifth column named 'Count in Dozens'. Use a simple formula like =B2/12 to convert your daily production count into dozens.

Insert a PivotTable

Select your entire data table, go to the 'Insert' tab on the ribbon, and click 'PivotTable'. Place it on a new worksheet.

Configure the PivotTable fields

In the PivotTable Fields pane, drag 'Year' and then 'Month' into the 'Rows' area. Drag the 'Count in Dozens' field into the 'Values' area to see your aggregated totals.

Built-in Date Grouping: Alternatively, you can just drag the raw 'Date' field into the Rows area, right-click a date in the PivotTable, select 'Group', and choose both 'Months' and 'Years' to bypass the need for Month/Year helper columns.

Data Summarization Tool

Track and Summarize Data Easily in WPS Spreadsheet

WPS Spreadsheet offers powerful, intuitive PivotTable features that let you group daily records into monthly and yearly summaries with just a few clicks.

1. Open your data: Launch WPS Spreadsheet and open the file containing your daily production records.
2. Insert a PivotTable: Select the data range, go to the 'Insert' tab, and click the 'PivotTable' button.
3. Group by periods: Drag your Date field to Rows, right-click any date cell in the PivotTable, select 'Group', and choose Months and Years.

Fully compatible with Microsoft Excel (.xlsx) filesIntuitive PivotTable builder with built-in date groupingSupport for advanced formulas, helper columns, and calculated fieldsFree, lightweight, and fast alternative for daily data analysis

microsoft office alternative - wps office

Frequently Asked Questions

How do I group dates by month and year in a PivotTable?

Right-click a date within the PivotTable Rows, select 'Group', and highlight both 'Months' and 'Years' in the dialog box. Ensure your source data contains valid date formats without any blank cells.

How can I calculate totals in dozens within my PivotTable?

You can either add a helper column in your source data (e.g., =B2/12) and use that field in your PivotTable, or insert a Calculated Field (PivotTable Analyze > Fields, Items & Sets) using the formula =Count/12.

Why won't my dates group properly in the PivotTable?

Date grouping usually fails if there are blank cells in the date column, or if the dates are formatted as text instead of actual dates. Reformat the column to 'Short Date' and remove any empty rows before refreshing the PivotTable.

Excel & Data Problems