Md. Ahaduzzaman
Sometimes we work with a very big dataset. They can contain different columns. Sometimes the columns are so many in number that we can not see them on our screen. We need to scroll with our mouse. So we may need some ways to find the last column with data. Hence in this article, we are going to discuss four easy and user-friendly methods to find the last column with data in Excel.
📁 Download Excel File
Download the Excel file from here.
Learn to Find Last Column with Data in Excel Through These 4 Methods
In this article, we are going to learn 4 easy methods to find the last column with data. Here we have a sample dataset of product order sheets. in the last column, we have the Total Cost column. So we are going to demonstrate our last column with data using Excel which is the Total Cost of the product Bed. The sample dataset for our problem is as follows.
1. Applying LOOKUP Function
Here we are going to use the LOOKUP function to find the last column with data in Excel. Therefore the step-by-step procedures to do that are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly in cell C13, we write this formula in the formula box.
=LOOKUP(2,1/(E:E<>””),E:E)
🔨 Formula Breakdown
👉 Firstly, here 2 is our lookup value.
👉 Secondly, 1/(E:E<>””) is our lookup vector.Here <> operator finds the nonempty cells. Dividing it by 1 gives us the cells that contain data.
👉 Finally, E:E is our result vector array.
Read More: 5 Ways to Find First Occurrence of a Value in a Range in Excel
2. Utilizing INDEX and COUNT Functions
In this section, we will learn how to use both INDEX & COUNT functions to find the last column with data in Excel properly. So the step-by-step procedures are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- At first, we write this formula in the formula box of cell C13.
=INDEX(E6:E11,COUNT(E6:E11))
🔨 Formula Breakdown
👉 Firstly, here E6:E11 is our array.
👉 COUNT(E6:E11) is the row number for the INDEX Function.
👉 COUNT function gives us the position of the last value within the range.
3. Implementing INDEX After MIN & COLUMN Functions
Here in this section, we will see how to apply the INDEX function after using MIN & COLUMN functions to find the last column with data in Excel. So the step-by-step procedures to perform that are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, in the formula box of cell C13, we write this formula.
=MIN(COLUMN(B6:E11))+COLUMNS(B6:E11)-2
🔨 Formula Breakdown
👉 Firstly, here COLUMN(B6:E11) gives us an array containing all column numbers for the selected range.
👉 Following that MIN(COLUMN(B6:E11)) gives us the minimum value for the columns.
👉 COLUMNS(B6:E11) gives us the total number of columns for the selected range.
👉 Finally we subtract 2 from the whole value to get the index value of the last column for the selection of our range.
- Following that now we write this for cell C14 in the formula box.
=INDEX(B6:E11,6,C13)
🔨 Formula Breakdown
👉 Firstly here B6:E11 is the array.
👉 Following that we put 6 which is the row number for the data range.
👉 Finally C13 is our column number.
Read More: 3 Ways to Check If Range of Cells Contains Specific Text in Excel
4. Applying VBA Macro
Here we are going to see how we can apply VBA properly to find the last column with data in Excel. Therefore the step-by-step procedures to do that are shown below.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we go to the Developer option.
- After that, we select the Visual Basic option.
- Now we go to the Insert option.
- Following that we select the Module option.
- Now we write this script in the module.
Sub Finding_last_Column_containing_Data()
cell = Cells(11, Columns.Count).End(xlToLeft).Value
MsgBox "Your last column containing Data is " & cell
End Sub- Finally, we save our code and get out of the module.
- Firstly In this step, we go to the View option.
- Then we go to the Macros option.
- We select the View Macros option.
- We will find a dialog box.
- We select our Macro name.
- After that, we fill up the Macros in the description.
- Finally, we select the Run option.
- After doing all that we will get our results in a dialog box.
How to Find Last Row with Data in Excel
Here we are going to discuss how to find the last row with data. So the step-by-step procedures for doing that are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly in the formula box of cell C13, we write this formula.
=MAX((B5:E11<>””)*(ROW(B5:E11)))
🔨 Formula Breakdown
👉 Here ROW(B5:E11) gives us the row number for the given reference.
👉 MAX function returns us the maximum value in the set of values.
Read More: Excel Formula: Find Last Row Number with Data
How to Find Last Column Name in Excel
Here we will see how to know the last column name in Excel for a data range. The steps for that are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we select the first header B5 of our data range which is the header.
- Then we press these 2 keys.
Ctrl+Right Arrow
- Finally, we will see that the last column name or header name is selected and shown in the formula box.
📄 Important Notes
🖊️ While using the functions we have to be careful about the function argument.
🖊️ Also we have to save the excel file as Macro enabled workbook to use the VBA.
📝 Takeaways from This Article
📌 We can use the LOOKUP Function to find the last column with data.
📌 We can apply INDEX & COUNT Functions together to do the same job.
📌 We can also use INDEX, MIN & COLUMN Functions for this purpose.
📌 apart from all of that we can use the VBA to find the last column with data.
Conclusion
In this article, we have learned four different methods to find the last column with data using Excel. We can use different types of Excel functions like LOOKUP, INDEX, COUNT, MIN, and COLUMN to do that. Apart from that, we can also apply VBA to do the same thing. Users can use any of the methods. You can also cement in the comment section if you have any queries. You can also visit our website Excelden for further excel related problems and queries.
