Table of Contents
- INTRODUCTION
- FINDING LAST FILLED CELL IN A ROW
- SOLUTION: FIND LAST CELL IN A ROW USING LOOKUP
- WHICH VALUE IS TO BE LOOKED FOR?
- FINDING LAST FILLED CELL IN A COLUMN
- SOLUTION -FIND LAST FILLED CELL IN A CELL USING LOOKUP
INTRODUCTION
There are many tricks that are really helpful when creating some useful applications.
One such technique is to FIND THE LAST FILLED CELL. [The cell which contains any value. ]
This article will give you the address of the last filled cell in any row or column.
If you want to get the last filled cell value CLICK HERE.
We’ll discuss different types 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
There is no direct formula or function to get the last filled cell in a row or column but we’ll make use of other functions to get the last filled cell in a row.
THE LAST FILLED CELL IN A ROW IS THE CELL CONTAINING THE VALUE. THE VALUE CAN BE TEXT OR NUMBER OF ANY OTHER CHARACTER.
SOLUTION: 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 functions 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.
WE NEED TO FIND THE LAST FILLED CELL IN THE GIVEN DATA. THE DATA IS PUT IN THE ROW NUMBER 2.
| 21 | k | j | h | l | ; | j | h | l | o | uj |
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 =MATCH(LOOKUP(ANY VAGUE NUMBER,1/(ROW:ROW<>””),ROW:ROW),ROW:ROW,0)
- For our example we’ll use the formula as =MATCH(LOOKUP(3512,1/(2:2<>””),2:2),2:2,0) [ THIS FUNCTION WILL RETURN THE COLUMN NUMBER OF THE LAST CELL WHICH HAS DATA IN THE ROW NUMBER 2 ]
- Click enter.
- The result will appear as 14 which is COLUMN N , as it is the 14th letter of the ALPHABET.
The process is shown below in the animation.
WHICH VALUE IS TO BE LOOKED FOR?
Actually it is a random value which must not be present in our values .
It means LOOKUP FUNCTION should not be able to search for the value which we’d be asking it to search for.
If it found the value, it’ll return the result prior to the last value.
That explains the working of this method.
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 -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 again take the help of LOOKUP function in this as it 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.
| F |
| D |
| D |
| 54 |
| 654 |
| 6 |
| 21 |
| A |
| B |
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 =MATCH(LOOKUP(ANY VAGUE NUMBER,1/(COLUMN:COLUMN<>””),COLUMN:COLUMN),COLUMN:COLUMN,0)
- For our example we’ll use the formula as =MATCH(LOOKUP(3512,1/(C:C<>””),C:C),C:C,0) [ THIS FORMULA WILL FIND THE LAST FILLED CELL IN THE COLUMN C ]
- Click enter.
- The result will appear as 25 which is the row number of the last cell containing data.
The process is shown below in the animation.
In this way , we can find out the last filled cell value.
If you want to find the VALUE IN THE LAST FILLED CELL, CLICK HERE.