Excel Find Last Column With Data (4 Quick Ways)

In a large worksheet, finding the last column with data manually is time-consuming and not friendly. There are some functions by using which you can quickly find out the last column with data. In this article, I’m going to explain how you can use the functions and VBA to find last column with data in Excel.

To make the explanation lively, I’m going to use a sample dataset that represents order information. The dataset has 4 columns these are Order Date, Order ID, and Amount.

Sample dataset

Download Workbook to Practice

4 Ways to Find Last Column With Data

1. Using LOOKUP Function to Find Last Column With Data

To find the last column with data in Excel you can use the LOOKUP function where you can look up the last value of any particular column, not only the last column.

Lets, start the procedure,
First, select any cell to place your resultant value.
➤I selected the cell F4
Then, type the following formula in the Formula Bar or into the selected cell.

=LOOKUP(2,1/(D:D<>""),D:D)

Using LOOKUP Function to Find Last Column With Data

Here in the LOOKUP function, I want to get the Last Column Data depending on the OrderID column as lookup_value value.
I selected the range of the D:D column as lookup_vector where I used a not equal operator (<>) to find find the non-empty cells. Later, divided it by 1 to get to know which of the cells contain data. Then, as a result_vector used the range D:D of Amount column.

Now, press the ENTER key.
Hence, you will see the last column data of the dataset.

2. Using INDEX & COUNT Function 

By using the INDEX function and the COUNT function together you can find the last column with data.

Let’s jump to the procedure,
To begin with, select any cell to place your resultant value.
➤I selected the cell F4
Then, type the following formula in the Formula Bar or into the selected cell.

=INDEX(D4:D9,COUNT(D4:D9))

Using INDEX & COUNT Function to Find Last Column With Data

Here in the INDEX function, I’ve selected the range D4:D9 as an array. Next, as the row_number of the INDEX function used the COUNT function to get the position of the last value of the selected range D4:D9. The COUNT function will return the last position then the INDEX function will return the value of that position.

Now, press the ENTER key, and you will see the last column data of the dataset.


Similar Readings:


3. Using MIN & COLUMN then INDEX Function

You can also use the MIN function with the COLUMN and COLUMNS function to get the last column number.
Depending on the last column number you can find the last column data using the INDEX function.

First, start the procedure to get the last column number.
➤I selected the cell F3
Then, type the following formula in the Formula Bar or into the selected cell.

=MIN(COLUMN(A4:C9))+COLUMNS(A4:C9)-1

Using MIN & COLUMN then INDEX Function to Find Last Column With Data

Here, in the COLUMN function selected the range A4:C9 as a reference, it will return an array that contains all column numbers for the given range.
Now to get the first column number used the MIN function.
Then the COLUMNS function will give the total column number of the selected range A4:C9. Now you can add the total column number with the first column and then subtract 1 to ensure the number of the last column of the range.

In the end, press the ENTER key. Then, you will get the last column number.

Now, let’s continue the procedure to get the last column data.

Select any cell to place your resultant value.
➤I selected the cell F4
Then, type the following formula in the Formula Bar or in the selected cell.

=INDEX(A4:C9,6,F3)

Using MIN & COLUMN then INDEX Function to Find Last Column With Data

Here, in the INDEX function, selected the range A4:C9 as an array then selected the last non-empty row which is 6 as row_number (need to remember row number should be according to the dataset, and you can find the value of any row providing the number).
Next, used F3 cell value as column_number, the formula will return the value of the corresponding row and column number which will be the last column data.

Press ENTER, and you will get the last column data.

4. Using VBA to Find Last Column With Data

In case you want to show the last column with data you also can use the VBA.

Let’s begin the procedure,
First, open the Developer tab >> select Visual Basic (Keyboard Shortcut ALT + F11 )

Using VBA to Find Last Column With Data

Next, it will open a new window of Microsoft Visual Basic for Applications.
From there, open Insert >> select Module

A Module will open then type the following code in the opened Module.

Sub Find_Last_Column_with_Data()
cell_value = Cells(9, Columns.Count).End(xlToLeft).Value
MsgBox "Last Column With Data is " & cell_value
End Sub

Using VBA to Find Last Column With Data

Here, I declared the Sub procedure Find_Last_Column_with_Data
I used the COUNT method to count the last column then I used the VALUE method to get the last column value where I provided the row number 9. Here, I stored the value in the cell_value variable.
To show the value I used MsgBox.

Finally, Save the code and go back to the worksheet.
Next, open the View tab >> from Macros >> select View Macros

➤ A dialog box will pop up.

Using VBA to Find Last Column With Data

Now, from the Macro name select the Find_Last_Column_with_Data also select the workbook within Macros in.
Then, Run the selected Macro.

As a result, it will show the last column value in a message box.

Practice Section

I’ve provided a practice sheet in the workbook to practice these explained ways. You can download it from the above link.

Practice Sheet to Find Last Column with Data

Conclusion

In this article, I’ve explained 4 quick ways of finding the last column with data in Excel. Hope these different ways will help you to find the last column with data. Lastly, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.


Further Readings

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo