Find the Last Column with Data in Excel (Not Just Column Count)

Finding the last used column in Excel is a common need, but most available methods simply count the number of columns in a range. This can be misleading when the rightmost columns are empty.

For example, if your data expands across columns, and your range is A1:Z100, but the actual data only extends to column E, counting columns would return 26 instead of the correct value, 5.

In this tutorial, we’ll use a dynamic Excel formula that accurately identifies the last non-empty column in a given range. Instead of returning the total column count, this method ensures that only columns containing data are considered.

This formula does not require every row in a column to be filled. Even a single non-empty cell in a column will count.

How It Works

Our approach leverages the TRIMRANGE function, which is currently available in Excel 365. This makes the formula both efficient and adaptable, ensuring accurate results even as your data structure changes.

Formula to Find the Last Column with Data in Excel

1. Get the Last Column Number with Data

=LET(
   data, TRIMRANGE(range, 3, 2),
   MIN(COLUMN(data))+COLUMNS(data)-1
)

Replace range with the actual data range, e.g., Sheet1!A1:Z100.

Example Output: If column G is the last non-empty column, the formula will return 7.

2. Get the Column Letter of the Last Used Column

=LET(
   data, TRIMRANGE(range, 3, 2),
   col, MIN(COLUMN(data))+COLUMNS(data)-1,
   colL, ADDRESS(1, col),
   REGEXEXTRACT(colL, "[A-Z]+")
)

Again, replace range with the actual data range.

Example Output: If the last used column is G, the formula will return "G".

Sample Data

The sample data is currently in B2:E8, but it will gradually expand up to column Z. So, we will use B2:Z8 as the range, and the sheet name is Sheet1.

Here, the last column with data is column E, so the formulas will return:

Column number: 5
Column letter: "E"

Example Usage

1. Get the Last Column Number with Data in a Dynamic Range

=LET(
   data, TRIMRANGE(Sheet1!B2:Z8, 3, 2),
   MIN(COLUMN(data))+COLUMNS(data)-1
)

Where to enter the formula?

Place this formula in any cell outside the range (or in another worksheet). It will return the correct last used column number.

The formula is dynamic – if you enter new data, the column number will update automatically.

2. Get the Column Letter of the Last Used Column

=LET(
   data, TRIMRANGE(Sheet1!B2:Z8, 3, 2),
   col, MIN(COLUMN(data))+COLUMNS(data)-1,
   colL, ADDRESS(1, col),
   REGEXEXTRACT(colL, "[A-Z]+")
)

Example Output: "E" if column E is the last non-empty column.

Formula Breakdown

Step 1: Trim Empty Columns

TRIMRANGE(Sheet1!B2:Z100, 3, 2)

This removes empty columns from the range dynamically.

Step 2: Find the Last Column Number

MIN(COLUMN(data))+COLUMNS(data)-1

MIN(COLUMN(data)) -> Finds the starting column number of the trimmed range.
COLUMNS(data) -> Returns the total number of columns after trimming.
Adding them and subtracting 1 gives the last used column number in the specified range.

Step 3: Convert the Column Number to a Letter

ADDRESS(1, col)

This converts the column number to an Excel cell reference, such as "E1".

REGEXEXTRACT(colL, "[A-Z]+")

Then, REGEXEXTRACT extracts only the column letter.

FAQs

What happens if the cell range is empty?

If the range has no data, the formula returns #REF!.

Fix: Wrap the formula with IFERROR:

=IFERROR(formula_here, "")

Does this formula work in older Excel versions?

No. This formula requires Excel 365, as it relies on TRIMRANGE, LET, and REGEXEXTRACT.

Final Thoughts

This method ensures that only columns with data are considered when finding the last used column in Excel. Unlike traditional methods that just count columns, this approach dynamically adapts to changes in your data.

Try the formulas above and let me know if you have any questions!

More Excel & Google Sheets Tutorials: