It is not a matter of astonishment that we might face the requirement to find the last non blank cell in a row in Excel. Here, we will try to sort out some smooth ways to find the last non blank cell in Excel.
For more simplification, I am going to use a Dataset containing Employee Names and Working Hours.
How to Find Last Non Blank Cell in Row in Excel: 5 Quick Ways
1. Applying Excel LOOKUP Function to Find Last Non Blank Cell in Row
LOOKUP Function is an easy way to find last non blank cell in row in Excel.
Steps:
- Select a cell to apply the LOOKUP Function. Here, I have selected the H6 cell.
- Apply the formula.
=LOOKUP(2,1/(B6:G6<>""),B6:G6)Here, the LOOKUP Function searches through the given Range B6:G6 according to the lookup_value and returns the last non blank cell value.
- Press ENTER.
- Then, use Fill Handle to AutoFill the rests.
2. Combination of INDEX & COUNTA Functions to Find Last Non Blank Cell in Row
There is another easy way to find last non blank cell in a row in Excel using a formula that contains both INDEX Function & COUNTA Function.
Steps:
- Choose a cell to apply the formula. Here, I have selected cell H6.
- Input the Formula.
=INDEX(B6:G6,COUNTA(B6:G6))           Where, COUNTA Function counts the non blank cell values according to the range and INDEX Function returns the value given the range.
- Press ENTER.
- To AutoFill, use Fill Handle.
3. Using OFFSET Function to Find Last Non Blank Cell in Row
OFFSET Function is vastly used to find last non blank cell in row.
Steps:
- Pick a cell to apply the method. Here, I have picked cell H6.
- Apply the formula.
=OFFSET(B4,2,5,1,1)Here,
- B4 is the starting point which works as
- 2 is the number of rows to ignore from the reference cell.
- 5 is the number of columns to ignore to the right from the reference cell.
- 1 is the height in rows of the output cell.
- 1 is the height in columns of the output cell.
- Hit ENTER.
- AutoFill the rests.
4. Employing SUMPRODUCT Function to Find Last Non Blank Cell in Row
Application of SUMPRODUCT Function along with INDIRECT Function, ROW function and MAX function can easily find the last non blank cell in row.
Steps :
- Select a cell. Here, I have selected cell H6.
- Employ the formula on that cell.
=INDIRECT("G"&SUMPRODUCT(MAX((B6:G6<>"")*(ROW(B6:G6)))))Where SUMPRODUCT is used to find out the non blank cell total number in the selected range. INDEX Function here returns the value based on the location. Finally, We can have last non blank cell in row by employing the INDIRECT Function.
 Formula Breakdown
- ROW(B6:G6)—-> The ROWÂ function returns the value of the row number.
- Output: 6
- MAX((B6:G6<>””)*(ROW(B6:G6))—-> Here. the ROW & MAX is multiplied by each other.
- MAX(True,True,True,True,True,True*6)—-> Here, the boolean value of TRUE which is 1 is multiplied by 6.
- MAX(6,6,6,6,6,6)—-> The MAX function returns the largest number.
- Output: 6
- SUMPRODUCT(MAX((B6:G6<>””)*(ROW(B6:G6))))
- SUMPRODUCT(6)
- Output: 6
- INDIRECT(“G”&SUMPRODUCT(MAX((B6:G6<>””)*(ROW(B6:G6)))))
- INDIRECT(“G”& 6)—-> The Ampersand joins the texts.
- INDIRECT(“G6”)—-> The INDIRECT function returns the value of reference cell with text string.
- Output: 8.5
- Explanation: Here, ROW Function returns the row number and MAX Function returns the maximum value ignoring the blank cells. Multiplying them with SUMPRODUCT Function, we have got the maximum number with non blank cell. Connecting it with G through Ampersand, we have now declared the last non blank cell of the row. INDIRECT Function returns the value of that cell.
- Press ENTER.
- Then, AutoFill the next.
5. Applying XLOOKUP Function to Find Last Non Blank Cell in Row
The XLOOKUP Function is an advanced function in Excel. We can use it here to find last non blank cell in row.
Steps:
- Select a cell to apply the formula. Here, I have selected cell H6.
- Apply the formula.
=XLOOKUP(FALSE,ISBLANK(C6:G6),C6:G6,"Blanks",,-1)Here, I have selected the array C6:D6. ISBLANK Function works to find out if there is any blank cell. On that case, it will return True. Thus, the blank cells will be ignored. I have chosen the search option from last to first by giving -1 value. Finally, XLOOKUP Function finds last non blank cell in row considering all these values.
- Hit ENTER.
- Use Fill Handle to AutoFill the rests.
Practice Section
For further expertise, you can practice here.
Download Practice Workbook
Conclusion
Every method shown here is practically proven to find last non blank cell in row in Excel. For any kind of confusion, you can comment below.
Related Articles
- How to Find Last Cell with Value in Column in Excel
- How to Find Last Cell with Value in a Row in Excel
- Find Last Value in Column Greater than Zero in Excel
- How to Find Last Occurrence of a Value in a Column in Excel
<< Go Back To Excel Last Value in Range | Excel Find Value in Range | Excel Range | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Hi, how do you find the second last non blank value?
Sorry DIEGO for my late response.
There needs to make some changes in the formula in case of finding the second last non blank cell.
I have used the following formula using LOOKUP function in cells D5 to D15 that is the chemistry marks in the dataset to find the second last non-blank cell.
=LOOKUP(2,1/((D5:D15<>D15)*(D5:D15<>“”)),D5:D15)
My dataset is given below:
Second Last Non Blank Value
Name Physics Chemistry
Green 164 110 (D5)
Jack 185 165
Joey 178 132
Mark 183 137
Austin 165 112
Marvin 173 119
Mason 186 170
Mount 170
Martin 177 160
Freeman 164
Federer 163 111 (D15)
Second Last Non Blank Value 160
For me it worked perfectly. I hope it will work the same way for you too.
Hi, Thank you so much worked perfectly! Can you explain the vlookup arguments. Why is the first argument a “2”?
Hello MARIA, thanks for the comment and sorry for my late reply.
In the LOOKUP function, the first argument is Lookup_value. I have typed 2 just to signify the NUMBER format. You can type any number. It will work just fine.