How to Show Customers with Balances in an Excel Timeline-Filtered PivotTable
Question details
The user needs a sales transaction PivotTable to continue listing each customer with an outstanding balance even when the customer has no transactions in the selected timeline period.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user is analyzing sales transactions in a timeline-filtered PivotTable and needs customers with outstanding balances to remain visible regardless of the selected period.
- Observed behavior
- Customers without transactions in the selected timeline period disappear from the PivotTable instead of remaining listed.
Make sure the workbook supports the Data Model and Power Pivot. Prepare a transaction table with customer, date, and balance fields, plus a separate calendar table containing all dates in the timeline range.
Build the PivotTable from the Data Model and Power Pivot with a Calendar Table
Use this method when you need a true timeline filter and want customers without transactions in the selected period to remain visible in Excel.
This approach joins the transaction table to a calendar table in the Excel Data Model. The timeline is based on the calendar table, so PivotTable display options can keep row items with no data in the selected period.
In Excel, click Data > From Table/Range, select the transaction table, and choose Add this data to the Data Model before loading it.
Create a separate date table with continuous dates and load it into the Data Model, or use Power Pivot > Manage to add a calculated date table.
Open Power Pivot > Manage, switch to Diagram View, and drag the transaction date field to the calendar date field to create a relationship.
Click Insert > PivotTable > From Data Model, place Customer in Rows and Outstanding Balance in Values, then insert a Timeline using the calendar table date field.
Right-click the PivotTable, choose PivotTable Options, open the Display tab, and check Show items with no data on rows and Show items with no data on columns.
Use a Manual Date Filter in WPS Spreadsheets as a Lighter Alternative
Use this approach in WPS Office when you do not need an Excel timeline and mainly need a date-filtered customer balance list.
Need a Simpler Spreadsheet for Customer PivotTables? Try WPS Office
WPS Office is a free, lightweight Office alternative that opens Excel workbooks and gives you a familiar ribbon interface for smooth migration, along with everyday spreadsheet tools for PivotTables, filters, and customer balance analysis without requiring the Data Model, Power Pivot, or a timeline.
Frequently Asked Questions
Why do customers disappear in my timeline-filtered PivotTable?
Customers disappear because they have no transaction records in the selected timeline period and the PivotTable option Show items with no data is disabled. Enable it in PivotTable Options > Display.
Does WPS Office have Power Pivot or the Data Model?
WPS Office does not include Power Pivot or the Excel Data Model. Use Microsoft Excel for timeline and Data Model PivotTables, or use WPS Spreadsheets with a manual date filter and Show items with no data.
Can I use a regular PivotTable without the Data Model?
Yes, you can use a standard PivotTable for simple transaction data. However, to keep all customers visible by date range, enable Show items with no data and use a date filter rather than an Excel timeline.
What is a calendar table?
A calendar table is a separate table containing all dates. Relate it to the transaction date in the Data Model so the timeline filters by calendar date instead of only existing transaction dates.
How do I enable Show items with no data?
Right-click the PivotTable, choose PivotTable Options, open the Display tab, and check Show items with no data on rows and Show items with no data on columns.
