Keep Null Periods Blank in an Excel PivotTable Running Total
Question details
The user wants an Excel PivotTable running total to stay blank for periods with null source values.

- 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.
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.
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.
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.
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].
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)))
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.
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.
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.

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.




