Pivot Table Issues

How to Track Client Income Changes Over Time in Excel Pivot Tables

Natalie TaylorNatalie Taylor Nov 10, 2026 869 views

Question details

The user needs to identify overall income increases or decreases for clients over time and compare the variations among different individual income sources.

How to Track Client Income Changes Over Time in Excel Pivot Tables
Product
Excel
Device & OS
not provided
Scenario
Analyzing monthly financial data to evaluate client income trends and monitor fluctuations across multiple income sources.
Observed behavior
Requires a method to automatically calculate period-over-period differences for specific clients and income streams using a PivotTable.
Before you start

Ensure your client income data is organized in a continuous tabular layout with clear column headers (such as Client ID, Start Date, Income Source, and Monthly Amount) and contains no blank rows or columns.

Solution 1Recommended

Use the 'Difference From' feature in PivotTables

By configuring a PivotTable's value field settings to show data as a 'Difference From' a previous period, you can automatically calculate income variations without writing complex formulas.

This method requires chronological date fields to properly calculate the difference from the previous item. Grouping your dates by months or years beforehand can make the analysis much clearer.

1
Format data as a table

Select your entire dataset, go to the 'Insert' tab, and click 'Table' (or press Ctrl+T). This ensures that any new income records added later will automatically be included in your analysis.

2
Insert the PivotTable

With the table selected, click 'PivotTable' on the 'Insert' tab. Choose to place the PivotTable on a New Worksheet and click OK.

3
Configure Rows and Values

In the PivotTable Fields pane, drag 'Client ID' and 'Start Date' into the Rows area. Drag 'Monthly Amount' into the Values area.

4
Apply 'Difference From' calculation

Right-click on any numeric value inside the Monthly Amount column of your PivotTable. Hover over 'Show Values As' in the context menu and select 'Difference From...'.

5
Set the base field and item

In the dialog box that appears, select 'Start Date' as the Base Field. For the Base Item, select '(previous)' and click OK. The PivotTable will now display the increase or decrease in income compared to the prior date.

6
Analyze specific income sources

To see which specific income sources caused the changes, drag the 'Income Source' field into the Columns area (or directly below 'Start Date' in the Rows area).

Use the 'Difference From' feature in PivotTables
Pro Tip: If you want to view the percentage of growth or decline rather than a flat dollar amount, select '% Difference From' instead of 'Difference From' in the Show Values As menu.
WPS Spreadsheet Solutions

Easily Track Financial Trends with WPS Spreadsheet

WPS Spreadsheet offers powerful, user-friendly PivotTable functionality that allows you to calculate income differences, track financial trends, and deeply analyze your data. It provides an intuitive interface making complex period-over-period analysis simple.

  1. 1. Open data in WPS Spreadsheet: Launch WPS Office, open your financial dataset, and click 'PivotTable' under the 'Insert' tab.
  2. 2. Set up your PivotTable layout: Drag 'Client ID' and 'Date' to the Rows area, and 'Amount' into the Values box.
  3. 3. Change value settings: Right-click the data values, select 'Show Values As', and click 'Difference From'.
  4. 4. Specify the previous period: Set the Date as your base field and '(previous)' as the base item to instantly view income changes.
Fully compatible with Microsoft Excel (.xlsx) PivotTable features and formats.Built-in 'Show Values As' settings for instant period-over-period calculations.Intuitive drag-and-drop interface for rapid PivotTable configuration.Free, lightweight, and capable of processing large datasets seamlessly.
microsoft office alternative - wps office

Frequently Asked Questions

Why is the 'Difference From' option greyed out or missing in my PivotTable?

This typically occurs if you have right-clicked on a subtotal row, a grand total row, or a non-numeric field label. Ensure you are right-clicking on a standard value cell within the data area of the PivotTable to access the 'Show Values As' options.

How do I handle periods where a client had zero income and no record exists?

PivotTables cannot calculate differences for entirely missing dates. To resolve this, ensure your underlying data source includes a placeholder row with a zero amount for that specific date, or adjust the PivotTable field settings to 'Show items with no data'.

Does this tracking automatically update when I add new monthly income data?

If you formatted your original source data as an Excel Table before creating the PivotTable, new data is automatically recognized in the range. You simply need to right-click anywhere inside the PivotTable and select 'Refresh' to update the income difference calculations.

Can I format the negative changes to display in red?

Yes. Select the value cells in the PivotTable, press Ctrl+1 to open Format Cells, choose the 'Number' or 'Currency' category, and select one of the formats that displays negative numbers in red. This makes income decreases instantly identifiable.