Pivot Table Issues

Keep Null Periods Blank in an Excel PivotTable Running Total

Olivia MillerOlivia Miller Nov 20, 2026 869 views

Question details

The user wants an Excel PivotTable running total to stay blank for periods with null source values.

Keep Null Periods Blank in an Excel PivotTable Running Total
Product
Microsoft Excel PivotTable and Power Pivot
Device & OS
not provided
Scenario
A PivotTable displays a running total across periods imported from a SAS dataset.
Observed behavior
The running total repeats the previous value in periods where the source value is blank or null.
Before you start

Check that the amount column contains numeric values and that missing values remain blank rather than being converted to zero. You will need an Excel version that supports the Data Model and Power Pivot measures.

Solution 1Recommended

Use a Power Pivot measure that returns blank for null periods

Use a Data Model measure to calculate the running total only when the current period has a value.

This example assumes a Data table with Date and Amount columns and a related Calendar table with a unique Date column. It treats zero as a valid value and a blank sum as a null period.

1
Prepare the source data

Import the source through Data > Get Data using a supported format, such as CSV or Excel. In Power Query, set Date to a date type and Amount to a numeric type, and preserve null amounts.

2
Load the Data Model

In Power Query, choose Home > Close & Load To and select Add this data to the Data Model. Add a Calendar table with unique dates to the model and create a relationship from Calendar[Date] to Data[Date].

3
Create the measure

Open Power Pivot > Manage and create a measure in the Data table. Adapt the table and column names in this formula: Running Total Blank := VAR CurrentAmount = SUM('Data'[Amount]) VAR CurrentDate = MAX('Calendar'[Date]) RETURN IF(ISBLANK(CurrentAmount), BLANK(), CALCULATE(SUM('Data'[Amount]), FILTER(ALLSELECTED('Calendar'[Date]), 'Calendar'[Date] <= CurrentDate)))

4
Build the PivotTable

Choose Insert > PivotTable and use the Data Model. Put Calendar[Date] in Rows and the new Running Total Blank measure in Values; use the date column rather than a date hierarchy for this example.

5
Check blank display

Right-click the PivotTable, open PivotTable Options, and check the Layout & Format settings. Clear For empty cells show if a replacement value is displayed, then verify a null period and a zero-value period.

Dates and totals: The measure accumulates over the selected dates. Check subtotals and grand totals separately, because they are evaluated over a broader filter context than an individual period.
Free Microsoft Office alternative

Explore spreadsheet summaries in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for Excel formats, making it easy to move existing workbooks. WPS Spreadsheets can create ordinary PivotTables, but it does not fix Excel's running-total behavior or replace the Power Pivot measure described above.

Create and review PivotTable summaries in WPS Spreadsheets.Open and save common Microsoft Excel formats, including XLSX.Use a familiar interface to transition from Microsoft Office.Work with a free, lightweight office suite.
microsoft office alternative - wps office

Frequently Asked Questions

Why does my PivotTable repeat the previous running total for a blank period?

Excel's built-in running-total calculation carries the accumulated value forward. Use a Data Model measure that checks the current period for a blank before calculating the total.

Will the measure also hide a period whose value is zero?

No. The example uses ISBLANK, so a numeric zero is treated as a valid value. Check the source data in Power Query to make sure nulls have not been replaced with zeros.

How can I get SAS data into the Excel Data Model?

Export the SAS data to a format Excel can import, such as CSV or XLSX. Use Data > Get Data to load and transform it, then select Add this data to the Data Model in Close & Load To.

Why is a missing period absent rather than shown as a blank row?

Use a Calendar table that contains the missing date and is related to the source table. In the PivotTable, open Field Settings for the date field and check Show items with no data if that option is available.