FILTER to remove columns in Excel
One of the most versatile functions in Excel is the FILTER function, which allows users to extract specific data based on defined criteria. While commonly used to filter rows, it can also be employed to effectively remove columns from a dataset.
??Purchase our book to improve your Excel productivity
Benefits
- Customizable Data Views: Easily create tailored views of your data by excluding unnecessary columns.
- Simplified Data Analysis: Focus on relevant data by filtering out extraneous information.
- Improved Performance: Reduce the dataset size for better performance in large spreadsheets.
- Dynamic Filtering: Automatically update filtered data when the original dataset changes.
Step-by-Step Guide
Step 1: Understanding the FILTER Function
The basic syntax of the FILTER function is:
=FILTER(array, include, [if_empty])
- array: The range of cells to filter.
- include: A Boolean array (TRUE/FALSE) that determines which rows to include.
- if_empty: Optional argument to specify the value to return if no entries meet the criteria.
Step 2: Preparing Your Data
Ensure your data is well-organized in a tabular format with headers. For example:
| A | B | C | D |
|---|----|-----|----|
| 1 | 10 | Cat | 20 |
| 2 | 15 | Dog | 25 |
| 3 | 20 | Cow | 30 |
| 4 | 25 | Pig | 35 |
Step 3: Creating Criteria for Filtering Columns
Since the FILTER function primarily filters rows, we need to create a logical test that works for columns. One way to achieve this is to transpose the data and apply the filter.
Step 4: Transposing the Data
Use the TRANSPOSE function to switch rows and columns:
=TRANSPOSE(A1:D4)
This will convert your data into:
| A | 1 | 2 | 3 | 4 |
|----|----|----|----|----|
| 10 | Cat| 20 | 10 | 25 |
| 15 | Dog| 25 | 15 | 30 |
| 20 | Cow| 30 | 20 | 35 |
| 25 | Pig| 35 | 25 | 40 |
Step 5: Applying the FILTER Function
Now, apply the FILTER function to remove unwanted columns. Suppose you want to keep only columns with numerical data (A and D in the original dataset):
=FILTER(TRANSPOSE(A1:D4), {TRUE,FALSE,FALSE,TRUE})
This will filter the data to:
| A | D |
|----|----|
| 1 | 20 |
| 2 | 25 |
| 3 | 30 |
| 4 | 35 |
Step 6: Transposing Back
Finally, transpose the filtered data back to the original orientation:
=TRANSPOSE(FILTER(TRANSPOSE(A1:D4), {TRUE,FALSE,FALSE,TRUE}))
This results in:
| A | D |
|---|----|
| 1 | 20 |
| 2 | 25 |
| 3 | 30 |
| 4 | 35 |
??Purchase our book to improve your Excel productivity
Example
Let's consider a dataset of sales information with the following columns: Product ID, Product Name, Category, Quantity Sold, and Total Revenue.
| A | B | C | D | E |
|-------------|--------------|-----------|--------------|-------------|
| Product ID | Product Name | Category | Quantity Sold| Total Revenue|
| 101 | Widget A | Gadgets | 150 | 3000 |
| 102 | Widget B | Gadgets | 120 | 2400 |
| 103 | Gizmo A | Tools | 200 | 5000 |
| 104 | Gizmo B | Tools | 80 | 1600 |
领英推荐
Goal: Remove Product Name and Category Columns
Step 1: Transpose the Data
=TRANSPOSE(A1:E5)
| A | 1 | 2 | 3 | 4 | 5 |
|-------------|-------|-------|-------|-------|-------|
| Product ID | 101 | 102 | 103 | 104 |
| Product Name| Widget A| Widget B| Gizmo A| Gizmo B|
| Category | Gadgets | Gadgets | Tools | Tools |
| Quantity Sold| 150 | 120 | 200 | 80 |
| Total Revenue| 3000 | 2400 | 5000 | 1600 |
Step 2: Apply the FILTER Function
=FILTER(TRANSPOSE(A1:E5), {TRUE, FALSE, FALSE, TRUE, TRUE})
Result:
| A | 1 | 4 | 5 |
|-------------|-------|-------|-------|
| Product ID | 101 | 104 |
| Quantity Sold| 150 | 80 |
| Total Revenue| 3000 | 1600 |
Step 3: Transpose Back
=TRANSPOSE(FILTER(TRANSPOSE(A1:E5), {TRUE, FALSE, FALSE, TRUE, TRUE}))
Final Result:
| A | D | E |
|-------------|--------------|-------------|
| Product ID | Quantity Sold| Total Revenue|
| 101 | 150 | 3000 |
| 102 | 120 | 2400 |
| 103 | 200 | 5000 |
| 104 | 80 | 1600 |
??Purchase our book to improve your Excel productivity
Advanced Tips
1. Dynamic Column Removal: Use named ranges or dynamic arrays to create more flexible and maintainable filters.
=FILTER(TRANSPOSE(A1:E5), TRANSPOSE(ISNUMBER(MATCH(A1:E1, {"Product ID","Quantity Sold","Total Revenue"},0))))
2. Conditional Logic: Combine the FILTER function with IF statements for more complex criteria.
=FILTER(TRANSPOSE(A1:E5), {TRUE, IF(ISNUMBER(SEARCH("Widget", B1:B4)), FALSE, TRUE), TRUE, TRUE})
3. Helper Columns: Create helper columns that use logical tests to define which columns to keep or remove.
=IF(ISNUMBER(SEARCH("Revenue", A1:E1)), TRUE, FALSE)
4. Automation with VBA: For repetitive tasks, consider automating the filtering process with a VBA macro.
```vba
Sub RemoveColumns()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
ws.Range("A:E").AutoFilter Field:=2, Criteria1:="<>Widget*"
End Sub
??Purchase our book to improve your Excel productivity
??102 Most Useful Excel Functions with Examples: The Ultimate Guide
???? Order it here : https://lnkd.in/enmdA8hq
?? Transform from novice to pro with:
?? Step-by-Step Guides
??? Clear Screenshots
?? Real-World Examples
?? Downloadable Practice Workbooks
?? Advanced Tips
OK Bo?tjan Dolin?ek