How to get the second to last value in a column in Excel
Formula (Excel and Google Sheets)
=CHOOSEROWS(FILTER(B2:B11,B2:B11<>""),-2)Drop the blanks and count back from the end: =CHOOSEROWS(FILTER(B2:B11,B2:B11<>""),-2).
- Result on the example
- $3.35 in
E1 - Works in
- Excel 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
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.
=CHOOSEROWS(FILTER(B2:B11,B2:B11<>""),-2)Result3.35
B2:B1110 rows × 1 col: $3.19, $3.24, (empty), $3.31, …A range: 10 cells in one column.
B2:B11<>""{10 rows × 1 col} TRUE, TRUE, FALSE, TRUE, …The <> operator tests whether they differ.
FILTER(B2:B11,B2:B11<>""){6 rows × 1 col} 3.19, 3.24, 3.31, 3.28, …FILTER: Rows of a range that meet every condition. #N/A when none match.- range
B2:B1110 rows × 1 col: $3.19, $3.24, (empty), $3.31, … - condition1
B2:B11<>""{10 rows × 1 col} TRUE, TRUE, FALSE, TRUE, …
- range
-2-2Makes the value negative.
CHOOSEROWS(FILTER(B2:B11,B2:B11<>""),-2)3.35CHOOSEROWS: The rows you name from an array; negative numbers count from the end.- array
FILTER(B2:B11,B2:B11<>""){6 rows × 1 col} 3.19, 3.24, 3.31, 3.28, … - row_num1
-2-2
- array
In Google Sheets
The same formula works in Google Sheets: =CHOOSEROWS(FILTER(B2:B11,B2:B11<>""),-2). We ran it in Google Sheets mode on the same data and it returns $3.35.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(B2:B11,LARGE(IF(B2:B11<>"",ROW(B2:B11)-ROW(B2)+1),2)) | $3.35 | 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) | $3.35 | 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!.
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.
Get the second to last value in a column
E1, return the price before the latest one: the second to last filled cell in B2:B11.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).
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.