Trimrange Function

Purpose

Return value

Syntax

=TRIMRANGE(range,[trim_rows],[trim_columns])

• range - The range or array to be trimmed.
• trim_rows - [optional] How rows should be trimmed. 0 = none, 1 = trim leading, 2 = trim trailing, 3 = trim leading and trailing (default).
• trim_columns - [optional] How columns should be trimmed. 0 = none, 1 = trim leading, 2 = trim trailing, 3 = trim leading and trailing (default).

Using the TRIMRANGE function

The TRIMRANGE function removes empty rows and columns from the outer edges of a range of data. Given a range or array, it will exclude empty rows and/or columns and return a “trimmed” range that contains only data. The beauty of TRIMRANGE is that it will track the data in a worksheet as it changes. When data is added or removed, the range will automatically adjust, with no need to adjust cell references manually. This means you can feed the result from TRIMRANGE into other formulas, and they will always use the latest data to calculate results. For this reason, TRIMRANGE is a good option for creating a dynamic range, or a dynamic named range, with a formula. See below for details with examples.

TRIMRANGE and the dot operator are two different ways to create a trimmed range (sometimes called a “trim ref”). This article is focused on TRIMRANGE, but includes a section on the dot operator below .

• Basic syntax
• Alternative syntax with the dot operator
• How TRIMRANGE works
• What is a dynamic range?
• Example - Trimming a large range
• Example - Creating a dynamic range
• Example - Creating a dynamic named range
• Example - Creating a dropdown menu
• Example - Anchoring other formulas
• Key benefits of TRIMRANGE
• When to use TRIMRANGE
• Tips for using TRIMRANGE
• TRIMRANGE limitations
• Notes on TRIMRANGE

Basic syntax

The syntax for TRIMRANGE is simple; just give it a range, and it will automatically remove empty rows and columns:

=TRIMRANGE(range) // remove empty rows and columns

There are two optional arguments, trim_rows , and trim_columns , that let you fine-tune this behavior:

=TRIMRANGE(range,1,1) // remove leading rows and columns
=TRIMRANGE(range,2,2) // remove trailing rows and columns
=TRIMRANGE(range,1,2) // remove leading rows and trailing columns

In each case, TRIMRANGE will examine the data in the range and discard empty rows and/or columns outside the data. The result is a “trimmed range” that includes only the data in the original range. Like other Excel formulas, TRIMRANGE is dynamic, so the result will automatically update to match changes in the worksheet.

Alternative syntax with the dot operator

You can also trim a range using an alternative syntax based on a " dot operator “. When the Excel team added TRIMRANGE to Excel, they also extended the range operator, a colon (:), to handle “Trim references” (also called “Trim refs”). Essentially, trim refs use a dot (.) together with the colon (:) to define a range with trim behaviors. Here are some examples:

=A:F // normal range, not trimmed
=A:.F // trim trailing rows and columns
=A.:F // trim leading rows and columns
=A.:.F // trim leading and trailing rows and columns

Note: The dot operator is an alternative to TRIMRANGE — don’t use both together; use one or the other. I like the TRIMRANGE function because it makes the action clear and explicit. The dot syntax is tricky to read and might not be noticed or understood by many users. That said, the dot operator is a very concise . And who doesn’t like formulas that are short and to the point? 🙂

How TRIMRANGE works

TRIMRANGE removes empty rows and columns from the outer edges of a range. Starting from the outer boundary of the range, TRIMRANGE scans inward. When it finds a non-blank value, it uses it as a reference point and discards the empty rows or columns between the reference point and the outer boundary. Depending on how TRIMRANGE is configured, this process is repeated for the incoming array’s top, bottom, right, and left edges. The result is a rectangular range that includes all data contained by the original range, with the empty rows and columns around the data “trimmed off”. You can see the basic concept in the worksheet below, where we are using TRIMRANGE like this:

=TRIMRANGE(A:J)

Starting with the full column reference A:F, TRIMRANGE trims from the outside in. It removes empty leading and trailing rows, and empty leading and trailing columns. The final result is data in the range B3:H14.

Important: TRIMRANGE does not remove any empty rows or columns from the interior of a set of data, it only removes unused rows and columns outside the rectangle that contains the data.

What is a dynamic range?

To understand why TRIMRANGE can be useful, you should understand the concept of a dynamic range. A dynamic range in Excel automatically expands and contracts to track the data it contains. This kind of range is useful for things like reports, pivot tables, and charts that need to process data that is always changing. There are a variety of ways to create a dynamic range in Excel, including:

• Excel Tables - The easiest way to create a dynamic range in Excel. You can create an Excel Table with the shortcut Control + T, then give the table a name. Excel Tables are designed to expand as needed to contain all data automatically. They work well, but they do have a few limitations. They can’t contain dynamic array formulas, and headers in a table must be unique. For a full rundown, see our guide to Excel Tables .
• Spill range - Modern dynamic array formulas can create a spill range , which can be used as a simple dynamic range. For example, if you use the UNIQUE function in cell A1, you can refer to the values returned by UNIQUE with the syntax =A1#. This reference will track the data returned by UNIQUE as it changes. One key limitation of spill ranges is that they only contain values generated by a formula; they don’t contain data entered manually in a workbook.
• Regular Formula - The classic way to create a dynamic range is to use the name manager to create a name that is defined by a regular formula. Traditionally, this formula uses a function like OFFSET ( example ) or INDEX ( example ) to compute a range by counting values in a worksheet and using those counts to construct a range from a given starting point. Formulas like this predate Excel Tables and can work pretty well. However, they are complicated and non-intuitive to most users. The OFFSET version can also cause performance problems because OFFSET is a volatile function that automatically recalculates with every workbook change.
• TRIMRANGE - The new TRIMRANGE function provides a new way to create a dynamic range with a simple formula. The beauty of TRIMRANGE is that you can give it a simple reference like A:C, and it will automatically return only the used portion of the range. Even better, there is no restriction on the data contained by the range. You can use any combination of formulas and manually entered data, and TRIMRANGE will happily return the portion of the range that contains that data.

Example - Trimming a large range

The main function of TRIMRANGE, as the name suggests, is to “trim” a range, which means to remove empty rows and columns outside a block of data. You can see an example of this in the worksheet below, where we have data in columns G through J, and we are using TRIMRANGE to trim the range down to only the rows that contain data. The formula in B4 looks like this:

=TRIMRANGE(G:J)

This is an example of using full-column references to simplify range management. The beauty of this approach is simplicity. Instead of working out the address of the last row that contains data, we just provide columns. However, we must be careful because Excel worksheets are very large — each worksheet contains over 1 million rows. When we use a range like G:J, we are referencing over 4 million cells since 1,048,576 rows * 4 columns = 4,194,304 cells. References like this can cause serious performance problems in certain worksheets because they can cause Excel to do a lot of extra work. Essentially, TRIMRANGE allows us to have our cake and eat it, too. We can use simple references and let TRIMRANGE figure out which cells contain data.

In the worksheet, TRIMRANGE “trims” the range G:J by removing the empty rows above and below the data and returns the result as an array that lands in cell B4 and spills into the range B4:E15. Of course, this is a contrived example, so you can see how TRIMRANGE works on a small data set. More commonly, you will pipe the result of TRIMRANGE into another function. For example, to count rows of data in the range G:J, we can wrap TRIMRANGE in the ROWS function like this:

=ROWS(TRIMRANGE(G:J)) // returns 12

The result is 12, since there are 11 rows of data plus the header row. If we want to count rows of data only, we can use the DROP function to “drop” the first row like this:

=ROWS(DROP(TRIMRANGE(G:J),1)) // returns 11

The result from TRIMRANGE is dynamic. As data is added or removed from G:J, TRIMRANGE will continue to return the latest data in the used portion of the original range.

By default, TRIMRANGE trims empty rows and columns from a range. If we provided a larger range in the formulas above, like G:M, the result would be the same. TRIMRANGE would remove the unused rows above and below the data and also remove the three empty columns to the right.

Example - Creating a dynamic range

In this example, the goal is to create a simple dynamic range to track property listings, as seen in the worksheet below. The table that holds the property listings appears in the range B4:H14. The first row is a header row, so the actual data range is B5:H14. The formula in cell K4 uses the ROWS function to count the number of rows in the data:

=ROWS(B5:H14) // returns 10

Cell K5 is meant to hold a unique ID for a property, and cell K6 contains a VLOOKUP formula that uses the ID in K5 to look up the price for a given property:

=VLOOKUP(K5,B5:H14,3,0) // returns $388,774

The result is $388,774, the correct price for property 6. Note that these ranges are not dynamic. If we paste in an additional 90 rows of data, and change the ID to 12, the row count in cell K4 doesn’t change, and VLOOKUP returns #N/A since ID 12 does not exist in the range B5:B14, as you can see below:

The problem is that our original formula ranges are static and do not adjust to the new data. One way to make these ranges dynamic is to use the TRIMRANGE function with a range like B:H. Then, we replace the hardcoded range inside the ROWS function with TRIMRANGE like this:

=ROWS(TRIMRANGE(B:H)) // returns 101

Now, TRIMRANGE removes the empty rows from this (huge) range and returns a trimmed array to ROWS, which returns 101. This doesn’t quite work in this case because we are including the header row together with the data. We can solve that problem by adding the DROP function to drop the first row. Now, ROWS returns 100, which is correct:

=ROWS(DROP(TRIMRANGE(B:H),1)) // returns 100

To adapt the VLOOKUP formula, we can use exactly the same approach, replacing the static range B5:B14 with the DROP + TRIMRANGE combo above:

=VLOOKUP(K5,DROP(TRIMRANGE(B:H),1),3,0)

You can see the result of these new formulas in the worksheet below:

What have we accomplished here? Well, the key improvement is that our formulas will now track the data correctly when rows are added or removed because they use dynamic ranges. In addition, using TRIMRANGE like this ensures we aren’t processing more cells than needed, avoiding potential performance problems in large, complicated workbooks. It’s a bit annoying to clutter up our simple reference to B:H with TRIMRANGE and DROP, but it works nicely. The next logical step is to create a dynamic named range.

Example - Creating a dynamic named range

In the previous example, we looked at how to adapt formulas using a static range to formulas using a dynamic range using the TRIMRANGE function with some help from the DROP function. This worked well, but one issue is that we need to keep typing out the code that created the dynamic range:

=DROP(TRIMRANGE(B:H),1) // dynamic range

It would be nice if we could avoid this duplication. One way to do that is to create a named range . A named range is simply a human-readable name assigned to a range. Named ranges are cool because they give you a way to create names for different parts of a worksheet that you can refer to in formulas. While many named ranges point to a static range like A1:A100, a formula can also be used to define the range. Because formulas respond to changes in a worksheet, we call this a “dynamic named range”. To illustrate how this works, let’s build on the previous example and create a dynamic named range called “data”.

The first step is to define a name. Open the Name Manager with the keyboard shortcut Control + F3 (or navigate to Formulas > Name Manager in the ribbon), then click “New”. Next, type the name “data”, then enter the formula below:

=DROP(TRIMRANGE(Sheet3!$B:$H),1)

Note that we must use the absolute reference $B:$H when creating a named range. This is because we need the reference to work the same from any cell in the workbook. Excel will automatically add the sheet reference and create an absolute reference if you point and click on the worksheet to select the range.

Now that we have created a named range, we can adapt our two existing formulas to use the name “data”:

=ROWS(data)
=VLOOKUP(K5,data,3,0)

You can see these formulas in action in the worksheet below. Both formulas are now using the named range “data”:

<img loading=“lazy” src=“https://exceljet.net/sites/default/files/styles/original_with_watermark/public/images/functions/inline/trimrange_example_-_dynamic_named_range_2.png" onerror=“this.onerror=null;this.src=‘https://blogger.googleusercontent.com/img/a/AVvXsEhe7F7TRXHtjiKvHb5vS7DmnxvpHiDyoYyYvm1nHB3Qp2_w3BnM6A2eq4v7FYxCC9bfZt3a9vIMtAYEKUiaDQbHMg-ViyGmRIj39MLp0bGFfgfYw1Dc9q_H-T0wiTm3l0Uq42dETrN9eC8aGJ9_IORZsxST1AcLR7np1koOfcc7tnHa4S8Mwz_xD9d0=s16000';" alt=“TRIMRANGE Example - dynamic named range called “data” - 7”>

There are two key benefits to this approach. First, we have reduced redundant code in our formulas. Instead of two formulas that use TRIMRANGE + DROP in the same way, we have just one formula that defines the dynamic range in one location. Second, we have created a simple human-readable name, “data”, that can be used by other formulas that need to refer to the same data. As before, the range is fully dynamic and will adapt as the worksheet is changed.

Example - Creating a dropdown menu

One interesting use of TRIMRANGE is to create a dropdown menu of preselected values using Data Validation with a formula to provide the list. You can see an example of this in the worksheet below, where both Products and Colors are set up as dropdown menus.

In this worksheet, we use Data Validation set to allow a List, and we use a TRIMRANGE formula to define the values in Source. The formula that creates values for the “products” list looks like this:

=DROP(TRIMRANGE($E:$E),1)

The formula that creates values for the “colors” list looks like this:

=DROP(TRIMRANGE($G:$G),1)

Both formulas use the DROP function to remove the header for each list that appears in row 4. Because we are using TRIMRANGE, these formulas are dynamic and will instantly update when products or colors change.

Note we are not using TRIMRANGE to create a dynamic named range in this case, but we could. The exact same formulas above can be used to create the names “products” and “colors” and then these names can be used directly in the Data Validation rule instead of the formulas. In addition to being a bit more friendly, creating named ranges would make sense if there was a need to use the same lists elsewhere in the workbook. For more details on Data Validation, see this page .

Example - Anchoring other formulas

Another use of TRIMRANGE is to “anchor” other formulas. What does this mean, exactly? In the current version of Excel, formulas that return multiple values will spill onto the worksheet, creating a spill range. For example, in the worksheet below, we can use the LEN function to calculate the length of each text string in column B with one formula in cell D5, like this:

=LEN(B5:B15)

Because there are 11 values in the range B5:B16, LEN will return 11 results in an array that spills onto the worksheet. This is convenient because we get all 11 results with a single formula. However, if we add another value to column B, the formula above will not expand because the range B5:B15 will not change:

This takes the fun out of using a formula like this. So, how can we get a simple formula like this to expand when new data is added to column B? With TRIMRANGE, naturally 🙂. In the worksheet below, we have the original formula in cell D5. In cell E5, we have a new formula that incorporates TRIMRANGE like this:

=LEN(DROP(TRIMRANGE(B:B),3))

In this formula, TRIMRANGE first removes the empty rows above and below the data in column B, so that we are only working with the used portion of the range. Next, TRIMRANGE returns the trimmed range to the DROP function, which is configured to remove the first 3 rows. The result is a truncated array that contains the 12 text strings in B5:B16, which DROP returns to LEN. Finally, the LEN function counts the characters in each value and returns 12 results, which spill into the range E5:E16. The difference is that the LEN formula is “anchored” to the data in column B because we are using TRIMRANGE. As values are added and removed to column B, the LEN formula will automatically expand or contract as needed.

Note that our use of DROP in this case is different from the other formulas explained above because we need to remove three rows and not just one. This may seem a bit confusing since there are actually 4 rows above cell B5 that we want to discard. However, remember that TRIMRANGE trims the range first , before DROP gets involved. That means the first row is already gone by the time the range gets to DROP, so we only need to remove three rows. There are different ways to handle this kind of problem. We could, for example, use TRIMRANGE by itself with a smaller range like B5:B1000 that starts at the first value. However, in a “busy” worksheet, you will start to see this reference change if rows are inserted or deleted. What makes a reference like B:B nice is that it is stable and unaffected by common editing tasks.

Key benefits of TRIMRANGE

TRIMRANGE is a technical function designed for more advanced users. Potential benefits include:

• The ability to use very simple references like A:F without performance problems.
• A simple way to create a dynamic range in Excel.
• A way to speed up slow workbooks that use large, inefficient references.
• A way to provide trimmed ranges to other functions.
• A way to clean up messy ranges before handing off to other tools.

When to use TRIMRANGE

Here are some situations where you may want to use TRIMRANGE:

• You want to trim off extra (unused) rows or columns from a range.
• You want to use a reference like =A:Z because you don’t know how many rows or columns to expect.
• You want a simple dynamic range, but you don’t want to use an Excel Table.
• You want a simple named range that dynamically expands to include new data.
• You want to fix a performance problem caused by large references that trigger too much calculation.

Tips for using TRIMRANGE

• When you use full-column references like A:F with trim range, make sure that there is no other data below the data you are targeting. If there is, TRIMRANGE will include this extra data in your range. For example, if you have 200 rows of data starting at row 1, and there’s a cell in A9000 that contains a single character like “x”, TRIMRANGE will see this content and expand the range to include 9000 rows. Even a cell with a single (invisible) space can cause this problem.
• If you are working with a messy spreadsheet that has been used by many people, a good way to clean things up fast is to simply delete all rows below the data before you use TRIMRANGE. This will clear out any wayward junk that might be lurking in the cells below. First, make sure there is nothing important below the data, then select an entire row below the data, extend the selection to the bottom of the worksheet, and delete the rows. You can do the same thing with columns to the right. Select an entire column to the right of the data, extend the selection to the right edge of the worksheet, and delete all columns. When you delete rows and columns like this, Excel simply replaces them with fresh copies, so your worksheet stays the same size.

TRIMRANGE limitations

• TRIMRANGE does not currently work with 3D references like =Sheet1:Sheet3!A:A.
• TRIMRANGE cannot be used directly to create a dynamic Pivot Table. However, you can use TRIMRANGE to create a named range like “data” (explained above ) and then use the named range as the Pivot Table source.

Notes on TRIMRANGE

• If the target range contains no data, TRIMRANGE returns a #REF! error.
• TRIMRANGE trims empty rows and columns dynamically based on the data in the range.
• Useful for creating dynamic ranges without Excel Tables.
• Can be used with other functions to handle data dynamically and efficiently.
• Does not work with 3D references.
• Offers a simple solution for managing large data sets without performance issues.

Purpose

Return value

Syntax

=UNIQUE(array,[by_col],[exactly_once])

• array - Range or array from which to extract unique values.
• by_col - [optional] How to compare and extract. FALSE = by row (default); TRUE = by column.
• exactly_once - [optional] TRUE = values that occur once, FALSE= all unique values (default).

Using the UNIQUE function

The UNIQUE function extracts a list of unique values from a range or array . The result is a dynamic array that spills onto the worksheet, automatically updating when source data changes.

UNIQUE is often combined with other dynamic array functions like FILTER and SORT . For example, you can filter data before extracting unique values, or sort the results alphabetically. Use the ROWS function or COUNTA function to count the unique values returned by UNIQUE.

Note that UNIQUE won’t automatically adjust the source range if data is added or deleted. To use UNIQUE with a range that automatically resizes to fit the data, use an Excel Table or a dynamic range created with TRIMRANGE or the dot operator .

Video: The UNIQUE function

Key features

• Extracts unique values from a range or array automatically.

• Returns a dynamic array that updates when source data changes.

• Case-insensitive: “APPLE”, “Apple”, and “apple” are treated as the same value.

• Set exactly_once to TRUE to return only values that appear once (distinct values).

• Set by_col to TRUE to extract unique values from horizontal data.

• Only works with adjacent columns; use CHOOSECOLS or FILTER for non-adjacent columns.

• Basic usage

• Unique values

• Unique values by column

• Sort unique values

• Unique rows

• Distinct values

• Unique values ignore blanks

• Unique values with criteria

• Count unique values

• Unique values by count

• Unique with non-adjacent columns

• Notes

Basic usage

Using the UNIQUE function is straightforward. Just provide a range or array:

=UNIQUE(A1:A10) // unique values from A1:A10

Here are a few variations, which are explained in more detail below:

=UNIQUE(A1:B10) // unique rows from two columns
=UNIQUE(A1:E1,TRUE) // unique values from horizontal range
=UNIQUE(A1:A10,,TRUE) // unique values that appear exactly once
=SORT(UNIQUE(A1:A10)) // unique values, sorted

Unique values

In the worksheet below, the goal is to extract a list of unique colors from the range B5:B16. The formula in D5 is:

=UNIQUE(B5:B16)

The UNIQUE function evaluates the 12 values in B5:B16 and returns the 7 unique colors. The result spills into the range D5:D11 automatically. If any data in B5:B16 changes, the output from UNIQUE updates immediately.

For more details, see Unique values .

Unique values by column

By default, UNIQUE compares values by row. To extract unique values from horizontal data (arranged in columns), set by_col to TRUE. In the worksheet below, the goal is to extract unique colors from the range C4:I4. The formula in C6 is:

=UNIQUE(C4:I4,TRUE)

With by_col set to TRUE, UNIQUE compares values across columns instead of down rows. The result spills horizontally, returning the 5 unique colors: red, blue, green, purple, and gray. To convert the horizontal result to a vertical list, wrap the formula with TRANSPOSE :

=TRANSPOSE(UNIQUE(C4:I4,TRUE))

Sort unique values

A common pattern is to combine UNIQUE with SORT to return unique values in alphabetical or numerical order. In the worksheet below, the goal is to extract unique colors and sort them alphabetically. The formula in D5 is:

=SORT(UNIQUE(B5:B16))

Working from the inside out, UNIQUE extracts the 7 unique colors, then SORT arranges them in ascending order (A to Z). To sort in descending order (Z to A), add -1 as the third argument to SORT:

=SORT(UNIQUE(B5:B16),,-1)

Unique rows

UNIQUE can extract unique rows from multi-column data. In the worksheet below, the goal is to extract unique rows from the range B5:C15, which contains Group and Color columns. The formula in E5 is:

=SORT(UNIQUE(B5:C15))

By default, UNIQUE compares values by row, so no special configuration is needed. The UNIQUE function evaluates all 11 rows and returns the 7 unique combinations of Group and Color. The SORT function then sorts the result by the first column (Group). SORT is optional and can be removed if sorting isn’t needed.

For more details, see Unique rows .

Distinct values

The exactly_once argument controls how UNIQUE handles repeating values. By default, UNIQUE returns all unique values regardless of how many times they appear. Set exactly_once to TRUE to return only values that appear exactly once in the data. In the worksheet below, the goal is to extract colors that appear only once. The formula in D5 is:

=UNIQUE(B5:B16,FALSE,TRUE)

Because exactly_once is TRUE, UNIQUE returns only the 3 values that appear once: “purple”, “pink”, and “gray”. Notice by_col is set to FALSE, which is the default. You can also omit by_col entirely:

=UNIQUE(B5:B16,,TRUE)

For more details, see Distinct values .

Unique values ignore blanks

By default, UNIQUE will include blank cells in the results, which will appear as a zero (0) in the output. To exclude blanks, use the FILTER function to remove them first. In the worksheet below, the goal is to extract unique colors while ignoring blank cells. The formula in D5 is:

=UNIQUE(FILTER(B5:B16,B5:B16<>""))

Working from the inside out, FILTER removes blank cells using the criterion <>”” (not equal to empty string). The filtered array is then passed to UNIQUE, which extracts the 5 unique colors.

For more details, see Unique values ignore blanks .

Unique values with criteria

To extract unique values that meet specific criteria, combine UNIQUE with FILTER . In the worksheet below, the goal is to extract unique colors for each group (A and B). The formula in E5 is:

=UNIQUE(FILTER(B5:B16,C5:C16=E4))

Working from the inside out, FILTER returns only colors where the corresponding group matches E4 (“A”). UNIQUE then extracts the unique colors from that filtered list. The formula in F5 works the same way, filtering for group “B” in F4.

For more details, see Unique values with criteria .

Count unique values

To count unique values, wrap UNIQUE with COUNTA or ROWS . In the worksheet below, the goal is to count the unique colors in B5:B16. The formula in F5 is:

=COUNTA(UNIQUE(B5:B16))

UNIQUE returns an array of 7 unique colors, which COUNTA counts. You can also use =ROWS(UNIQUE(B5:B16)) for the same result. If UNIQUE has already spilled results to the worksheet (as in D5), you can count them with a spill range reference :

=COUNTA(D5#)

The hash character (#) tells Excel to reference the entire spill range.

For more details, see Count unique values .

The GROUPBY function can also be used to count unique values. See GroupBy function .

Unique values by count

To extract unique values that appear a certain number of times, combine UNIQUE with FILTER and COUNTIF . In the worksheet below, the goal is to extract colors that appear more than once (duplicates). The formula in D5 is:

=UNIQUE(FILTER(B5:B16,COUNTIF(B5:B16,B5:B16)>1))

Working from the inside out, COUNTIF counts how many times each value appears in the data. The expression COUNTIF(data,data)>1 returns TRUE for values that appear more than once. FILTER keeps only those values, and UNIQUE extracts the unique ones. The result shows the 4 colors that appear more than once: red, green, blue, and gray. In cell E5, the formula has been adjusted to extract unique values that appear more than twice:

=UNIQUE(FILTER(B5:B16,COUNTIF(B5:B16,B5:B16)>2))

For more details, see Unique values by count .

Unique with non-adjacent columns

UNIQUE only works with adjacent columns. To extract unique values from non-adjacent columns, use FILTER or CHOOSECOLS to select the columns first. In the worksheet below, the goal is to extract unique combinations of Color (column B) and Region (column D), skipping Qty (column C). The formula in F5 is:

=SORT(UNIQUE(FILTER(B5:D15,{1,0,1})))

The array constant {1,0,1} tells FILTER to include the first and third columns while excluding the middle column. UNIQUE then extracts unique rows from the two remaining columns, and SORT arranges the results alphabetically. As an alternative, you can use the CHOOSECOLS function instead of an array constant like this:

=SORT(UNIQUE(CHOOSECOLS(B5:D15,1,3)))

For more details, see UNIQUE with non-adjacent columns .

Notes

• UNIQUE is case-insensitive. “APPLE”, “Apple”, and “apple” are treated as identical.
• UNIQUE treats text and numbers as different types. The text “10” and the number 10 are considered different values.
• Empty cells appear as zeros (0) in results. Use FILTER to exclude blanks before UNIQUE runs.
• The empty string "” and truly empty cells may be treated differently. Use <>"" to filter both.
• UNIQUE returns #SPILL! if the destination range isn’t empty or doesn’t have room for results.
• Cross-workbook references require both workbooks to be open. Closed workbooks return #REF!.
• UNIQUE won’t automatically adjust the source range if data is added or deleted. Use an Excel Table or dynamic range if needed.