FIND LAST FILLED CELL VALUE IN EXCEL

Table of Contents

INTRODUCTION

This situation comes around many times when we are programming any application in VBA or we are making any reports in Excel when we need to find out the last filled cell in a row or a column.
We’ll discuss different type of cases related to the issue and find out different ways to sort them out.The article will discuss the solutions with examples.

FINDING LAST FILLED CELL IN A ROW

This is very frequent situation when we need to find out the last cell in a row or column. We can get any value against the last cell. Let us find out the ways by which we can do this.

SOLUTION 1-FIND LAST CELL IN A ROW USING LOOKUP

Usually , it is hard to run a check of different cells using the functions in Excel but even then we have a few which help in this regard. One of such function is LOOKUP. It runs from one end to other and looks up the value needed and return the value from the column which we have specified.

Suppose We have the following data in a row and we need to find the last filled value.

12 1212233454563445
EXAMPLE DATA

The given data simply contains a few numbers and a few empty cells.

FOLLOW THE STEPS TO FIND OUT THE LAST FILLED CELL IN THE GIVEN ROW

  • Select the cell where you want to get the value in the last filled cell in any column by double clicking it.
  • Enter the formula as =LOOKUP(ANY VAGUE NUMBER,1/(ROW:ROW<>””),ROW:ROW)
  • For our example we’ll use the formula as =LOOKUP(2,1/(1:1<>””),1:1) [The picture below shows absolute references $1:$1 for rows but not necessarily needed in this case ]
  • Click enter.
  • The result will appear as 3445 which is the last filled cell value.

The process is shown below in the animation.

EXCEL: FIND THE LAST USED CELL IN A ROW

FINDING LAST FILLED CELL IN A COLUMN

As in the case of the row, sometimes we need to find out the value of the last filled cell in a column too. In this solution we’d find the way to find out the value of last filled cell in a column or any value corresponding to the last filled cell in a column.

SOLUTION 1-FIND LAST FILLED CELL IN A CELL USING LOOKUP

Let us try to check the cells of a column and find out the last filled cell in a column. Usually this kind of task is easily done in VBA but we will still try in Excel. We’ll take help of LOOKUP function in this as this is a very basic function for looking up a value in a given table and it runs throughout the range.

Suppose We have the following data in a column.

RANGE
1
 
3
23
4
54
5
 
 
 
 
6
EXAMPLE DATA

The given data simply contains a few numbers and a few empty cells.

FOLLOW THE STEPS TO FIND OUT THE LAST FILLED CELL IN THE GIVEN COLUMN

  • Select the cell where you want to get the value in the last filled cell in any column by double clicking it.
  • Enter the formula as =LOOKUP(ANY VAGUE NUMBER,1/(COLUMN:COLUMN<>””),COLUMN:COLUMN)
  • For our example we’ll use the formula as =LOOKUP(123,1/B:B<>””),B:B).
  • Click enter.
  • The result will appear as 6 which is the last filled cell value.

The process is shown below in the animation.

EXCEL:FIND LAST USED CELL IN A COLUMN

In this way , we can find out the last filled cell value.