Pivot Table Issues

How to Create a Persian Calendar and PivotTable Grouping in Excel

WPS Content Manager Sep 28, 2026 868 views

Question details

The user wants to create a Persian calendar and correctly group Persian dates in an Excel PivotTable.

How to Create and Group a Persian Calendar in an Excel PivotTable

Product: Microsoft Excel
Device & OS: not provided
Scenario: Displaying and grouping chronological data in a PivotTable using the Persian calendar format instead of the standard Gregorian format.
Observed behavior: Standard PivotTable date grouping automatically defaults to the system's Gregorian calendar, displaying Gregorian periods even when the source cells use Persian date formatting.

Before you start

Ensure your source dates are formatted as valid serial numbers and that you have access to Power Query or the Data Model in your version of Excel.

Solution 1Recommended

Create a Dedicated Persian Calendar Table using Power Query

Build a separate calendar table in Power Query or the Data Model to extract Persian dates manually, bypassing Excel's default Gregorian grouping.

Excel stores dates as serial numbers and relies on system settings for grouping, which inherently defaults to the Gregorian calendar. Because standard PivotTable grouping ignores cell-level regional formatting, automatic date grouping will yield incorrect periods for a Persian calendar.

By utilizing Power Query or the Data Model, you can generate specific, immutable columns for the Persian year, month, and day. Using these dedicated columns ensures accurate and locale-independent grouping in your PivotTable.

Import Data into Power Query

Open Excel, navigate to the 'Data' tab on the ribbon, and select 'Get Data' to import your source table into the Power Query Editor.

Create a Calendar Query

Create a new query containing a continuous list of dates that covers the entire range of your source data.

Add Persian Date Columns

Add custom columns to extract the Persian Year, Persian Month Number, Persian Month Name, and Day. Ensure the locale is interpreted correctly during extraction.

Sort Month Names Chronologically

Select the Persian Month Name column and use the 'Sort by Column' feature to sort it by the numeric Persian Month Number column. This prevents alphabetical sorting in the PivotTable.

Load to Data Model

Close and Load the query directly into the Excel Data Model. Switch to the Power Pivot window to create relationships between your main data table and the new calendar table.

Insert the PivotTable

Go to Insert > PivotTable and choose 'From Data Model'. Build your PivotTable by dragging the newly created Persian date fields into the Rows or Columns area.

Create a Dedicated Persian Calendar Table using Power Query

Avoid Auto-Grouping: Always use the specific Persian columns you created rather than dragging the raw date field into the PivotTable, which would trigger Excel's automatic Gregorian grouping.

Organize Data with Custom Calendars in WPS Spreadsheet

If you want to avoid complex Data Model setups, WPS Spreadsheet allows you to easily extract custom calendar values using standard formulas and robust PivotTable features to achieve the same grouping results.

1. Open Your Data: Launch WPS Spreadsheet and open your existing workbook containing the date records.
2. Insert Helper Columns: Add new columns next to your raw dates and name them 'Persian Year', 'Persian Month', and 'Persian Day'.
3. Extract Date Values: Use standard date conversion formulas or text formatting functions to extract the Persian date components into these new columns.
4. Create a PivotTable: Select your entire data range, navigate to the Insert tab, and click 'PivotTable'.
5. Group by Custom Columns: Drag your new helper columns into the Rows area to successfully group and summarize your data by the Persian calendar.

Fully compatible with Microsoft Excel (.xlsx) file formatsFree built-in PivotTable features for advanced data analysisLightweight architecture runs smoothly even on older devices

microsoft office alternative - wps office

Frequently Asked Questions

Why does my PivotTable automatically group dates into Gregorian months?

Excel's standard PivotTable date grouping relies on the underlying system or model calendar, which defaults to the Gregorian calendar. It strictly groups based on the underlying serial number and ignores any cell-level visual formatting like Persian calendar displays.

Can I format dates as Persian directly in cells without using Power Query?

Yes, you can apply custom formatting or regional settings to display dates in the Persian calendar visually. However, underlying calculations and automatic PivotTable groupings will still process the date as a Gregorian serial number.

How do I ensure my Persian month names are sorted chronologically in the PivotTable?

In your calendar table, you must include a distinct numeric column for the Persian month number (1 through 12). You can then use the 'Sort by Column' feature in the Data Model to sort the text-based month names using this numeric column.

Excel & Data Problems