# How to calculate percent change from the first to the last value in Excel

Find the last filled value with XLOOKUP from the bottom and compare it with the first: `=XLOOKUP(TRUE,B2:B13<>"",B2:B13,,0,-1)/B2-1`.

**Excel:** `=XLOOKUP(TRUE,B2:B13<>"",B2:B13,,0,-1)/B2-1` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Month | Revenue |  | Change so far |
| Jan | 42000 |  |  |
| Feb | 39500 |  |  |
| Mar | 44100 |  |  |
| Apr | 46800 |  |  |
| May | 45200 |  |  |
| Jun | 48900 |  |  |
| Jul | 50300 |  |  |
| Aug | 49700 |  |  |
| Sep | 51660 |  |  |
| Oct |  |  |  |
| Nov |  |  |  |
| Dec |  |  |  |

Formula in E1: `=XLOOKUP(TRUE,B2:B13<>"",B2:B13,,0,-1)/B2-1`

Result: 23.0%

## How it works

`B2:B13<>""` marks the months that have a figure. XLOOKUP looks for TRUE, and its last argument, -1, makes it search from the bottom up, so it returns the latest filled month: September's 51,660. Divided by January's 42,000, minus 1, that is a 23% rise.

When October is filled in, the formula picks it up without editing.

## Other ways

- `=INDEX(B2:B13,COUNT(B2:B13))/B2-1`: Works in every version when the filled months have no gaps: COUNT gives the position of the last one.

## Common mistakes

- Pointing at a fixed cell like B10 stops working as months are added.
- A zero in a future month counts as filled. Leave future months blank, not 0.

## Questions

**How do I get the average monthly growth instead?**

That is a compound rate: `=(last/first)^(1/(months-1))-1`.

**How do I compare the last two filled months?**

Use XLOOKUP for the last and the value above it, or in Excel 365 `=TAKE(FILTER(B2:B13,B2:B13<>""),-2)` to get both.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/percent-change-first-to-last-value