You can use the following formulas in Excel to find the last column with data in a particular sheet:
Formula 1: Return Number of Last Column with Data
=MIN(COLUMN(team_data))+COLUMNS(team_data)-1
This particular formula returns the number of the last column with data in the named range team_data.
For example, this formula might return column number 5.
Formula 2: Return Letter of Last Column with Data
=CHAR(64+(MIN(COLUMN(team_data))+COLUMNS(team_data)-1))
This particular formula returns the letter of the last column with data in the named range team_data.
For example, this formula might return column letter E.
The following example shows how to use this formula in practice with the following Excel sheet in which we have created a named range called team_data to represent the range B1:E11:
Example 1: Return Number of Last Column with Data
We can type the following formula into cell A14 to return the number of the last column with data for the named range team_data:
=MIN(COLUMN(team_data))+COLUMNS(team_data)-1
The following screenshot shows how to use this formula in practice:
The formula returns the number 5.
This tells us that the last column with data in the named range team_data can be found in column 5 of our worksheet.
Example 2: Return Letter of Last Column with Data
We can type the following formula into cell A14 to return the letter of the last column with data for the named range team_data:
=CHAR(64+(MIN(COLUMN(team_data))+COLUMNS(team_data)-1))
The following screenshot shows how to use this formula in practice:
The formula returns the letter E.
This tells us that the last column with data in the named range team_data can be found in column E of our worksheet.
Note that the CHAR function in Excel returns a character based on the ASCII character code list.
The numbers 65 (A) through 90 (Z) correspond to the uppercase letters in the English alphabet.
Thus, in our formula we use CHAR(64 + Cell Reference) to return the uppercase letter that corresponds to the last row with data in our named range.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: Find First Occurrence of a Value in Column
Excel: Find First Value Greater than Specific Number
Excel: How to Find First Non-Zero Value in Row
