Spreadsheet Practice

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

Formula (Excel and Google Sheets)

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

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.

Result on the example
23.0% in E1
Works in
Excel 2021, 2024 and Microsoft 365
Google Sheets
Same formula

See it work

The formula is already in E1. Change any input and the result updates, or edit the formula to experiment.

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.

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

Result0.23

  1. B2:B1312 rows × 1 col: $42,000, $39,500, $44,100, $46,800, …

    A range: 12 cells in one column.

  2. B2:B13<>""{12 rows × 1 col} TRUE, TRUE, TRUE, TRUE, …

    The <> operator tests whether they differ.

  3. -1-1

    Makes the value negative.

  4. XLOOKUP(TRUE,B2:B13<>"",B2:B13,,0,-1)51660

    XLOOKUP: Searches one range and returns the matching item from another.

    • lookup_valueTRUE
    • lookup_arrayB2:B13<>""{12 rows × 1 col} TRUE, TRUE, TRUE, TRUE, …
    • return_arrayB2:B1312 rows × 1 col: $42,000, $39,500, $44,100, $46,800, …
    • if_not_found(omitted)
    • match_mode0
    • search_mode-1-1
  5. B2$42,000

    The value in B2.

  6. XLOOKUP(TRUE,B2:B13<>"",B2:B13,,0,-1)/B21.23

    The / operator divides.

  7. XLOOKUP(TRUE,B2:B13<>"",B2:B13,,0,-1)/B2-10.23

    The - operator subtracts.

In Google Sheets

The same formula works in Google Sheets: =XLOOKUP(TRUE,B2:B13<>"",B2:B13,,0,-1)/B2-1. We ran it in Google Sheets mode on the same data and it returns 23.0%.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=INDEX(B2:B13,COUNT(B2:B13))/B2-123.0%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.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Calculate percent change from the first to the last value

In E1, calculate the percentage change from January to the latest month that has a value.

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.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.