How to find and return the second to last value in a certain row or column in Excel?
When working with large Excel worksheets, it is often necessary to identify not just the last value in a row or column, but specifically the second-to-last value. For example, as shown in the screenshot below, given the table range A1:E16, you may need to retrieve the second-to-last value in the 6th row or in column B. This need commonly arises when tracking recent changes, monitoring time series data, or analyzing the most recent but not current entries in a dataset. Compared to simply finding the last value, finding the second-to-last value can be less straightforward, especially when the data includes empty cells or updates regularly. This article provides clear, step-by-step solutions for this task to make your workflow more efficient and help avoid common pitfalls.
- Find and return the second to last value in a certain row or column with formulas
- VBA Code – Use a macro to find the second-to-last value in a specified row or column
- Other Built-in Excel Methods – Filter all blanks and manually identify the second-to-last value
Find and return the second to last value in a certain row or column with formulas
As shown in the screenshot above, when you need to find and return the second-to-last value in either the 6th row or column B within the range A1:E16, Excel formulas can offer a dynamic and efficient solution. Using formulas is ideal in scenarios where your data updates frequently or the position of the second-to-last value might change due to additions or deletions. Unlike manual approaches, formulas ensure your result automatically adjusts to the latest data entry.
Find and return the second-to-last value in column B
1. Select a blank cell where you would like to display the second-to-last value. Enter the following array formula in the Formula Bar, and then press Ctrl + Shift + Enter (for legacy Excel) to confirm it as an array formula. In Excel 365 or Excel 2021, pressing Enter alone is sufficient as array formulas are handled automatically.
=INDEX(B:B,LARGE(IF(B:B<>"",ROW(B:B)),2))
Note: In this formula, B:B refers to column B. If you need the second-to-last value in a different column, simply adjust B:B to your desired column reference (for example, change to C:C for column C). This method works for data ranges with or without blank cells, but if there are hidden values or filtered-out data, ensure to account for potential discrepancies. Always double-check your data range if you do not see the expected value.
Find and return the second-to-last value in row 6
Select a blank cell to display the result for the 6th row. Then enter the following formula in the Formula Bar and press Enter to confirm.
=OFFSET($A$6,0, COUNTA(6:6)-2,1,1)
Note: In the formula above, $A$6 is the first cell of row 6, and 6:6 designates the entire row 6. Adjust these references to target different rows if needed. The formula dynamically counts the number of non-empty cells in row 6, then offsets from the starting cell to locate the second-to-last filled cell. If your row includes cells with formulas returning empty strings (""), COUNTA may still count these as non-blank, which could affect the result. For ranges with a mix of constants and formulas, double-check outcomes to avoid errors.
Tips for using formulas:
- For large datasets, using full-column references (e.g.,
B:B) may impact performance. If possible, limit the range to just the area you need (e.g.,B1:B100). - If your data includes hidden rows or is filtered, formulas will still count hidden and filtered-out cells. For filtered data, consider using special subtotal functions or helper columns.
- If all values in a target row or column are blank, these formulas may return an error or unexpected result; ensure your data contains at least two non-blank values.
VBA Code – Use a macro to find the second-to-last value in a specified row or column
For users who frequently need to find the second-to-last value in varying or large ranges, automating the process with a VBA macro can save significant time, especially when dealing with dynamic or complex tables. Macros are useful when your dataset changes size and you want to avoid repeatedly updating formulas, or when you need to extract values from multiple different positions using the same approach. Below is a VBA code solution that prompts for your target row or column, then automatically returns the second-to-last value, eliminating manual counting or adjustment.
1. Go to Developer Tools in the Excel ribbon, click Visual Basic to open the VBA editor. In the editor, click Insert > Module. Then, paste the following VBA code into the newly created module:
Sub GetSecondToLastValue()
Dim rng As Range
Dim arr As Variant
Dim values As Collection
Dim i As Long
Dim secondLast As Variant
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.InputBox("Select the row or column range to analyze", xTitleId, Selection.Address, Type:=8)
If rng Is Nothing Then Exit Sub
arr = rng.Value
Set values = New Collection
If rng.Rows.Count = 1 Then
For i = 1 To rng.Columns.Count
If arr(1, i) <> "" Then
values.Add arr(1, i)
End If
Next i
ElseIf rng.Columns.Count = 1 Then
For i = 1 To rng.Rows.Count
If arr(i, 1) <> "" Then
values.Add arr(i, 1)
End If
Next i
Else
MsgBox "Please select a single row or single column range.", vbExclamation
Exit Sub
End If
If values.Count < 2 Then
MsgBox "There are less than two non-blank values in the selected range.", vbInformation
Exit Sub
End If
secondLast = values(values.Count - 1)
MsgBox "The second-to-last value is: " & secondLast, vbInformation
End Sub2. After entering the code, return to Excel and run the macro using the 
Run button or by pressing Alt + F8 and selecting GetSecondToLastValue from the list. A prompt will ask you to select either a single row or column range (such as B1:B16 for a column, or A6:E6 for a row). After confirming your selection, the macro will display the second-to-last value in a dialog box.
Notes:
- This macro counts only non-blank cells, automatically skipping empty cells within the selected row or column.
- If fewer than two non-blank values exist in your selection, you'll receive a warning message and no value is returned.
- The macro is intended for single-row or single-column selections. If you select multiple rows and columns simultaneously, it will prompt you to adjust your selection.
- For automation, you can further modify the code to copy the result to a specific worksheet cell instead of displaying a message box.
This VBA solution is especially advantageous for users dealing with dynamic tables or frequently changing datasets, minimizing manual errors and repetitive tasks.
Other Built-in Excel Methods – Filter all blanks and manually identify the second-to-last value
While formulas and macros provide automated ways to find the second-to-last value, sometimes you may prefer a quick, visual approach, particularly with irregular or non-contiguous data, or when you only need to check a value occasionally. Using Excel's built-in filtering tools allows you to temporarily hide blanks or irrelevant cells, making it easier to spot the desired entry without editing or writing formulas.
Filtering Method:
- Select the data range for your column or row (for columns, select cells B1:B16; for rows, select A6:E6 in your worksheet).
- On the Excel ribbon, click Data > Filter to enable filter arrows. In the column header or beside your selected range, click the filter dropdown.
- Uncheck (Blanks) to hide empty cells. The data display now lists only non-blank values.
- For a column, scroll down to the bottom of the filtered list, and view the second-to-last value. For a row (after filtering, if possible), count from the right to identify the second-to-last non-blank entry.
Notes and Tips:
- Filtering is quickest for small to medium-size lists or when you visually need to confirm the position of values.
- If your data set is very large, filtering might slow performance, and it is not ideal for automation or repeated use.
- When filters are applied, make sure you're looking at the correct context (filtered lists may skip hidden rows or columns).
- To remove filters, click Clear in the Data tab.
This method is less suitable for ongoing dynamic analysis or large datasets, but it does provide a transparent, manual check for the second-to-last non-blank entry, which can be helpful for quick troubleshooting or verification.
Related articles:
- How to find position of first/last number in a text string in Excel?
- How to find first or last Friday of every month in Excel?
- How to vlookup find the first,2nd or nth match value in Excel?
- How to find value with highest frequency in a range in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in