# How to get the second to last value in a column in Excel

Drop the blanks and count back from the end: `=CHOOSEROWS(FILTER(B2:B11,B2:B11<>""),-2)`.

**Excel:** `=CHOOSEROWS(FILTER(B2:B11,B2:B11<>""),-2)` (Excel 2024 and Microsoft 365)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Week | Price per gallon |  | Previous price |
| 2026-02-02 | 3.19 |  |  |
| 2026-02-09 | 3.24 |  |  |
| 2026-02-16 |  |  |  |
| 2026-02-23 | 3.31 |  |  |
| 2026-03-02 | 3.28 |  |  |
| 2026-03-09 | 3.35 |  |  |
| 2026-03-16 |  |  |  |
| 2026-03-23 | 3.42 |  |  |
| 2026-03-30 |  |  |  |
| 2026-04-06 |  |  |  |

Formula in E1: `=CHOOSEROWS(FILTER(B2:B11,B2:B11<>""),-2)`

Result: $3.35

## How it works

FILTER keeps only the filled prices, in order: 3.19, 3.24, 3.31, 3.28, 3.35, 3.42. CHOOSEROWS with a negative number counts from the end, so `-2` returns the one before last, $3.35. Use `-1` for the latest price.

## Other ways

- `=INDEX(B2:B11,LARGE(IF(B2:B11<>"",ROW(B2:B11)-ROW(B2)+1),2))`: Every version (Ctrl+Shift+Enter in Excel 2019 and earlier): LARGE takes the second-highest position of a filled cell.
- `=INDEX(FILTER(B2:B11,B2:B11<>""),COUNT(B2:B11)-1)`: Excel 2021 and Google Sheets: the position before the count of prices.

## Common mistakes

- Counting filled cells and subtracting one, `INDEX(B2:B11,COUNT(B2:B11)-1)`, breaks when there are gaps.
- With fewer than two prices, CHOOSEROWS returns #VALUE!.

## Questions

**How do I calculate the change from the previous price?**

Subtract the two: `=CHOOSEROWS(FILTER(B2:B11,B2:B11<>""),-1)-E1`.

**How do I return the date of that price?**

Filter the dates with the same condition: `=CHOOSEROWS(FILTER(A2:A11,B2:B11<>""),-2)`.

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/second-to-last-value-in-column