Ravindra BagaleCourses & study guides Track your progress

Excel TRIMRANGE Function

By · Last updated:

Chala mitrano — today we learn TRIMRANGE. Read Why → What → How, then try the practice list.

Version note. Newer Microsoft 365 builds. If #NAME?, your channel is too old — use TAKE/DROP/FILTER workarounds.
Fictional sample data for learning. Demo columns with intentional blank edges.

Purpose

Trim unused edges from a range/array so formulas work on the compact used block (newer 365).

Return value

A trimmed array without empty edges (behaviour depends on options).

Syntax

TRIMRANGE(range, [trim_rows], [row_type], …) — confirm exact args on your Microsoft build docs.

Arguments

ArgumentRequired?Description
rangeRequiredThe range or array to trim.
optionsOptionalWhich edges / blank definitions to trim (build-specific).

Version

Newer Microsoft 365 builds. If #NAME?, your channel is too old — use TAKE/DROP/FILTER workarounds.

How to use — Why → What → How

Why: Copied ranges often include blank rows that break charts and averages.

What: TRIMRANGE tightens the data block automatically.

How — numbered steps:

  1. Check Microsoft’s current TRIMRANGE documentation for your Version.
  2. Try on a column with trailing blanks.
  3. If missing, workaround: FILTER(range, range<>"").
  4. Pair with charts.
  5. Document fallback for classmates on older builds.

Ravindra Bagale's Tip

Always verify the function exists: =TRIMRANGE( should autocomplete.

Ravindra Bagale's Tip

Fallback: FILTER or TAKE with COUNTA.

Worked examples

1) Trim trailing blanks

Column A1:A100 half empty.

Formula: =TRIMRANGE(A1:A100)

Result: Compact list

What it means: Exact args may vary — verify docs.

2) Chart-ready

Feed chart to trimmed data.

Formula: (trim then chart)

Result: No blank category gaps

What it means: Cleaner axis.

3) Fallback FILTER

Older Excel.

Formula: =FILTER(A1:A100, A1:A100<>"")

Result: Non-blank list

What it means: Portable pattern.

4) With UNIQUE

Unique trimmed cities.

Formula: =UNIQUE(TRIMRANGE(A1:A100))

Result: Unique cities

What it means: Compose modern functions.

5) #NAME? handling

Old build.

Formula: =IFERROR(TRIMRANGE(A1:A10), FILTER(A1:A10,A1:A10<>""))

Result: Fallback path

What it means: Class-friendly.

Common errors

#NAME?: Function not in this build.
#SPILL!: Output blocked.

Practice tasks (10+)

  1. Task 1. Test whether TRIMRANGE autocomplete exists.
    Hint: try on a tiny range first
  2. Task 2. Trim a messy column.
    Hint: try on a tiny range first
  3. Task 3. Write FILTER fallback.
    Hint: try on a tiny range first
  4. Task 4. Chart before vs after.
    Hint: try on a tiny range first
  5. Task 5. Document build version.
    Hint: try on a tiny range first
  6. Task 6. Combine with UNIQUE/SORT.
    Hint: try on a tiny range first
  7. Task 7. Interview: why trailing blanks hurt AVERAGE.
    Hint: try on a tiny range first
  8. Task 8. Explain #NAME? to a classmate.
    Hint: try on a tiny range first
  9. Task 9. Use LET to name the trimmed block.
    Hint: try on a tiny range first
  10. Task 10. Bonus: trim a 2D import block if supported.
    Hint: try on a tiny range first

Quick quiz

Q1. TRIMRANGE is brand-new on some 365 channels.
Q2. #NAME? can mean the build lacks TRIMRANGE.
Q3. FILTER can be a fallback for blanks.
Q4. Trailing blanks never affect charts.

Answers: Q1=True, Q2=True, Q3=True, Q4=False

Related: LAMBDA · LET · GROUPBY · Function hub · Learning path