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.

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.

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.

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:

• Find Last Value in Column Greater than Zero in Excel (2 Easy Formulas)
• Find the Last Cell With Value in Row in Excel (6 Methods)
• Find Multiple Values in Excel (8 Quick Methods)
• How to Find from Right in Excel (6 Methods)

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.

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.

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 )

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

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.

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.

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

• How to Find a Character in String in Excel
• Find External Links in Excel (6 Quick Methods)
• How to Find If A Range of Cells Contains Specific Text in Excel (4 Methods)
• Excel Search for Text in Range (11 Quick Methods)