Google sheets exclude cells from range

While working with spreadsheets we come across many simple tasks which look complex to perform but once you know how to solve them, it feels super easy.

In this tutorial, we will see how to exclude cells from range in google sheets. You will also learn how to exclude multiple cells (sub-range), exclude when certain criteria are met, and so on. Let’s jump in.


Google sheets exclude cells from range

You have a range say from A1 to A10 and you want to select all of the ranges except A5. Let’s see how to do it?

The formula to exclude cells from range is:

=FILTER(Range, range<>cell_to_exclude)

Step-1: Open google sheets on your computer and in a new cell write FILTER()

Step-2: For the first parameter, select the source range

Step-3: Add a comma and then write source_range<> 

Step-4: After the <> operator write the cell to exclude

Step-5: Press enter to output all cells except the excluded


Here is an example of google sheets exclude cells from range:

You have 10 cells in column A. In column B you want to print all cells except A5.

Google sheets exclude cells from range

As you can see from above, in Column B, you have all the cells from column A except A5. 

Lets explore different use cases.


Table of content



Exclude multiple cells from range


To exclude multiple cells instead of just one, you can use the FILTER formula with multiple conditions.

The formula is:

=FILTER(range, criteria_1, criteria_2 ….)

In the same example above, if you want to exclude cells A5 and A6 from the range, you can do this with the formula:

​​=FILTER(A1:A10,A1:A10<>A5,A1:A10<>A6) 

Here is a quick animation showing this in action:

Google sheets exclude cells from range



Exclude sub-range of cells from range


You learned how to exclude multiple cells from a range in google sheets. If you are working on a bigger range of data and you want to exclude a certain range of data then writing multiple criteria in the FILTER formula is not efficient.

Instead, you can use the QUERY formula. Here is an example:

Let’s say you have column A with a range of A1 to A20 and you want to exclude data from the range 10 to 15. 

Here is the QUERY formula in google sheets to exclude cells from the range.

=ARRAYFORMULA(QUERY({ROW(A1:A20), A1:A20}, "SELECT Col2 WHERE Col1<10 OR Col1>15"))

Exclude sub-range of cells from range

This will filter and output the range excluding ranges from 10 to 15.

Note that the SELECT statement is excluding cells based on the value and not based on the exact cell number.


Exclude the highest and lowest value cells in the range


The problem statement here is to select all cells in a range but exclude the highest value and the lowest value in the range.

You can do this by using the same FILTER formula but by checking the max and min of the range.

=FILTER(range,range<>MAX(range),range<>MIN(range))


Here is a demo:

In the below data set, we want to exclude the highest and lowest value in column A.

Exclude the highest and lowest value cells in the range


Exclude a cell from range that contains text


Let’s say you have a range of data in column A that you have imported from an external source. In this data range, there are a few rows with the text #NA in them. Also, there are blank rows in between. You want to exclude these rows and select all others.


Exclude #NA and Blank cells


You can use the FILTER formula with 2 conditions. One for checking #N/A and another for Blank.

Here is how:

In the below data set, we have a few #NA and blank cells in column A. Using the below formula, we can exclude these cells from the range in google sheets.

=FILTER(A1:A9,A1:A9<>"#N/A",A1:A9<>"")
exclude blank cells



Exclude a list value using a filter


Here you want to exclude cells from a range that contains a certain value from another list.

Here is an example:


Let’s say we have the below data

idusernamestatusexclude
1user 1ActivePending
2user 2ActiveBlocked
3user 3Pending
4user 4Active
5user 5Pending
6user 6Pending


We want to exclude cells from the first table where user status is one from the “exclude” list.


Here is the formula:

=FILTER(A2:C7,isna(MATCH(C2:C7,F1:F2,0)))


The MATCH formula will match the list “exclude” with column C and the ISNA() formula will select those that are not a match. 


Here is the result:

Exclude a list value using a filter


Exclude certain data types of cells from a range


Here we want to exclude text cells and show only those cells that contain a number.

The formula is:

=FILTER(A1:A5,ISNUMBER(A1:A5))
Exclude certain data types of cells from a range


Next, we want to Filter cells to show only cells with a text value.

The formula is:

=FILTER(A1:A5,ISTEXT(A1:A5))

Google sheets exclude cells from range example



FAQ

How do I exclude the first row in google sheets?

The easiest way to exclude the first row is not to include it in the formula. For example Sheet1!A2:A will select all cells in Sheet1, column A excluding the first row.

How do I exclude cells in autosum?

To exclude certain cells from the auto-sum, you can subtract them from the auto-sum. For example, if you are calculating the sum of A1:A10 and want to exclude A4:A6, then you can write =SUM(A1:A10)-SUM(A4:A6)

How to blackout row in google sheets?

If you want to hide certain rows in google sheets, you can do so visually without any formula. Select the rows which you want to hide, right-click on them and choose Hide from the menu.


Wrapping up

In this tutorial, we learned about Google sheets exclude cells from range formula. You saw examples of how to do it for one cell, a sub-range of cells, and using certain criteria. Go ahead and try these methods on your worksheet. 

Appendix

[1] FILTER formula in google sheets – Link

Further Reading

New to google sheets ? Start here

More about Data analysis in google sheets:

Tables
Sort & Filter
Charts

30+ smart tools to supercharge your sheets