Function Problems

How to Find the Last Cumulative Value for Each Date in Excel

Aamir Naveed Akram Sep 28, 2026 869 views

Question details

The user needs to extract the final running total (cumulative value) for each distinct date from a dataset containing multiple entries per date, in order to plot these final daily values on a line chart.

How to Find the Last Cumulative Value for Each Date in Excel

Product: Excel
Device & OS: not provided
Scenario: Data analysis and charting for financial or sequential time-series data.
Observed behavior: The current table has multiple rows for the same date with running profit values, preventing a clean line chart of the end-of-day cumulative totals.

Before you start

Ensure your dataset is sorted chronologically by date and time so that the last occurrence mathematically represents the final cumulative value for that day.

Solution 1Recommended

Use UNIQUE and XLOOKUP to Extract the Last Value

The most efficient way to get the final entry for each date is to extract a distinct list of dates and perform a reverse XLOOKUP.

The UNIQUE function easily generates a list of distinct dates without duplicates.

By setting the 'search_mode' parameter in XLOOKUP to -1, it searches the array from bottom to top, successfully returning the last occurrence of the specified date.

Extract unique dates

Click an empty cell (for example, D2) and enter `=UNIQUE(A2:A7)` to generate a list of distinct dates from your original date column.

Retrieve the last cumulative value

In the adjacent cell (e.g., E2), enter the formula `=XLOOKUP(D2#,$A$2:$A$7,$B$2:$B$7,,,-1)`. This formula searches for the unique dates in column A and returns the corresponding last value from column B.

Create the line chart

Select the newly generated unique dates and their corresponding last cumulative values. Go to the 'Insert' tab on the top ribbon, click 'Insert Line or Area Chart', and choose a standard 'Line Chart'.

Use UNIQUE and XLOOKUP to Extract the Last Value

Dynamic Array Spilling: Using the `#` symbol in `D2#` ensures that the XLOOKUP dynamically spills down the column to automatically match all the unique dates generated by the UNIQUE function.

Solution 2

Use LOOKUP Function as an Alternative (For Older Excel Versions)

If you do not have access to the newer XLOOKUP function, you can use the traditional LOOKUP function to achieve the same result.

Effortless Data Analysis with WPS Office

Extract Cumulative Values Easily in WPS Spreadsheet

WPS Spreadsheet fully supports advanced array functions like UNIQUE and XLOOKUP, allowing you to streamline your data extraction and charting process seamlessly without paying for expensive subscriptions.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook containing your cumulative data.
2. Generate unique dates: Type `=UNIQUE(A2:A7)` in a blank column to get the distinct list of dates.
3. Fetch the final cumulative profit: Enter `=XLOOKUP(D2#,$A$2:$A$7,$B$2:$B$7,,,-1)` in the next column to extract the last value for each date.
4. Insert a line chart: Highlight your new data range, navigate to the 'Insert' tab, and click 'Chart' to quickly insert a Line Chart.

Fully compatible with Microsoft Excel formulas including XLOOKUP and UNIQUECreate professional line charts with just a few clicksFree, lightweight, and fast data processingFamiliar interface requires no learning curve

microsoft office alternative - wps office

Frequently Asked Questions

What if my version of Excel doesn't support XLOOKUP?

If you are using an older version without XLOOKUP, you can use the formula =LOOKUP(2,1/(A:A=TargetDate),B:B) to find the last occurrence. Alternatively, upgrading to WPS Office provides free access to modern functions like XLOOKUP and UNIQUE.

Why does XLOOKUP return an error for some dates?

This usually happens if there are trailing spaces or formatting mismatches between your unique lookup dates and the original source data. Ensure both columns are formatted properly as 'Date'.

Can I use a Pivot Table instead of formulas to get the last value?

A standard Pivot Table defaults to summing or counting values. To get the 'last' value, you would need to add the data to the Data Model and use a DAX measure (like LASTDATE), which is generally more complex than simply using the XLOOKUP function.

Excel & Data Problems