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.
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
- Exclude sub-range of cells
- Exclude the highest and lowest value only
- Exclude cell that contains certain text
- Exclude #NA and Blank cells
- Exclude a list of values using filter
- Exclude certain data types
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:
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"))
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 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 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
| id | username | status | exclude | ||
| 1 | user 1 | Active | Pending | ||
| 2 | user 2 | Active | Blocked | ||
| 3 | user 3 | Pending | |||
| 4 | user 4 | Active | |||
| 5 | user 5 | Pending | |||
| 6 | user 6 | Pending |
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 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))
Next, we want to Filter cells to show only cells with a text value.
The formula is:
=FILTER(A1:A5,ISTEXT(A1:A5))
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:
