Table of Contents
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.
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 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.