Pivot Table Issues

How to Show the Latest Product Price in an Excel PivotTable

Maira Mehtab Sep 22, 2026 869 views

Question details

The user wants to display the specific price from the most recent transaction for each product in an Excel PivotTable.

Product: Excel
Device & OS: not provided
Scenario: Analyzing product price trends and attempting to isolate the most current price using PivotTables.
Observed behavior: Standard PivotTable aggregations only show the minimum, maximum, or sum of prices, but cannot natively display the price strictly associated with the latest transaction date.

Before you start

Ensure your dataset includes clear, separate columns for the Product Name, Transaction Date, and Product Price before creating your PivotTable or formulas.

Solution 1Recommended

Use a Helper Column with the MAXIFS Function

Create a new column in your source data to isolate the latest price using the MAXIFS function, then add it to your PivotTable.

This method compares the transaction date of the current row with the maximum date found for that specific product. If they match, it returns the price; otherwise, it leaves it blank.

Insert a helper column

Add a new column next to your source data and name it 'Latest Price'.

Enter the formula

Assuming Product is in column A, Date in column B, and Price in column C, enter the following formula in row 2: =IF(B2=MAXIFS($B$2:$B$5,$A$2:$A$5,$A2),$C2,"")

Apply to all rows

Drag the fill handle down to copy the formula to the rest of the rows in your dataset.

Update the PivotTable

Refresh your PivotTable, drag the new 'Latest Price' field into the Values area, and change its Value Field Settings to summarize by 'Max'.

Formula Compatibility: The MAXIFS function is fully supported in newer versions of Excel and WPS Spreadsheet, making this a reliable and dynamic solution.

Solution 2

Extract the Latest Price using XLOOKUP

Use the XLOOKUP function with a reverse search mode to instantly find the most recent price when your data is ordered by date.

Efficient Data Analysis

Easily Manage PivotTables and Formulas with WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like MAXIFS and XLOOKUP, as well as robust PivotTable features, allowing you to seamlessly analyze and extract your latest pricing data.

1. Open your dataset: Launch WPS Spreadsheet and open your existing .xlsx data file.
2. Add a helper column: Input the MAXIFS formula in a new column to identify the latest transaction price for each product.
3. Insert a PivotTable: Select your entire data range, go to the 'Insert' tab on the top ribbon, and click 'PivotTable'.
4. Configure the fields: Drag the Product field to Rows, and the newly created Latest Price field into the Values area, setting it to display the Maximum value.

Fully compatible with Microsoft Excel (.xlsx) file formats.Supports advanced lookup and conditional max formulas like XLOOKUP and MAXIFS.Intuitive PivotTable creation and field management.Free, lightweight, and fast data analysis tool.

microsoft office alternative - wps office

Frequently Asked Questions

Can I show the latest price directly in a PivotTable without a helper column?

Standard PivotTables do not have a built-in aggregation for 'Latest Date Price'. You must use a helper column, or utilize the Data Model (Power Pivot) to create a custom DAX measure.

Why is my MAXIFS formula returning an error?

Ensure that your version of your spreadsheet software supports the MAXIFS function. If you are using a legacy version, you may need to use an array formula combining the MAX and IF functions instead.

How does the Top 10 filter work for dates in a PivotTable?

You can drag the Date field into the PivotTable filters or rows, click the filter dropdown, select Value Filters, and choose 'Top 10'. By changing it to show the 'Top 1' item, the PivotTable will filter the view to only show the most recent date, though this restricts the overall visibility of other dates.

Excel & Data Problems