FIND LAST FILLED CELL IN EXCEL

Table of Contents

INTRODUCTION

There are many tricks which are really helpful when creating some of the useful techniques.

One of such technique is to FIND THE LAST FILLED CELL.

This article will give you the address of last filled cell in any row or column.

If you want to get the last filled cell value CLICK HERE.
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

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.

21kjhl;jhlouj
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 =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.

EXCEL: FIND THE LAST USED CELL IN A ROW

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
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 =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.

EXCEL:FIND LAST USED CELL IN A COLUMN

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.

Leave a Reply

Your email address will not be published. Required fields are marked *