Apply or Remove Filters in Excel with Python (Comprehensive Guide)

A filter in Excel is a tool that enables you to display only the rows of data that meet certain conditions or criteria that you specify. When you apply a filter, Excel will hide the rows that do not match the filter criteria, allowing you to quickly identify and work with the data that is most important to you. In this article, we will explore how to apply different types of filters and remove filters in Excel using Python.

We will discuss the following topics:

• Apply Filter for Text in Excel with Python
• Apply Filter for Number or Number Range in Excel with Python
• Apply Filter for Font Color, Cell Color or Icon Sets in Excel with Python
• Apply Filter for Date or Date Range in Excel with Python
• Apply Filter for Dynamic Date in Excel with Python
• Filter Blank or Non-Blank Cells in Excel with Python
• Remove Filters from Excel with Python

Python Library to Apply or Remove Filters in Excel

To apply and remove filters in Excel with Python, we can use the Spire.XLS for Python library.

Spire.XLS for Python is an easy-to-use and feature-rich library for creating, reading, editing, and converting Excel files within Python applications. With this library, you can work with a lot of spreadsheet formats, such as XLS, XLSX, XLSB, XLSM, and ODS.

You can install Spire.XLS for Python from PyPI by running the following commands in your terminal:

pip install Spire.Xls

For more detailed information about the installation, you can check these official documentations:

• How to Install Spire.XLS for Python on Windows
• How to Install Spire.XLS for Python on Mac

Apply Filter for Text in Excel with Python

The text filter in Excel allows you to quickly filter your data to display only the rows containing specific words, phrases, or characters. This is particularly useful when you need to focus on a particular product, customer, or other text-based criteria within a larger data set.

Using the AddFilter() function provided by Spire.XLS for Python, you can easily add one or more text filters to an Excel worksheet. The code below shows how to apply a text filter to filter cells with text equal to a specific word in Excel using Python:

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Sales.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Specify the range of data to be filtered
sheet.AutoFilters.Range = sheet.Range["A1:I21"]

# Create a filter to filter cells with text equal to “Laptop” in a specific column within the range ("1" is the index (0-based) of the column to which the filter will be applied)
sheet.AutoFilters.AddFilter(1, "Laptop")

# Apply the filter
sheet.AutoFilters.Filter()

# Save the resulting workbook to a file
workbook.SaveToFile("TextFilter.xlsx", FileFormat.Version2016)
workbook.Dispose()

Apply Filter for Specific Text in Excel with Python

In some cases, you may want to filter text begins with specific letters. To achieve this, you can use the CustomFilter() function. The code below shows how to apply a text filter to filter cells with text beginning with “Mo” in Excel using Python:

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Sales.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Specify the range of data to be filtered
sheet.AutoFilters.Range = sheet.Range["A1:I21"]

# Add a custom filter to filter cells with text that begins with "Mo"
sheet.AutoFilters.CustomFilter(1, FilterOperatorType.Equal, String("Mo*"))

# Apply the filter
sheet.AutoFilters.Filter()

# Save the resulting workbook to a file
workbook.SaveToFile("CustomTextFilter.xlsx", FileFormat.Version2016)
workbook.Dispose()

Apply Filter for Text Begins with Specific Characters in Excel with Python

Apply Filter for Number or Number Range in Excel with Python

The number filter in Excel enables you to filter your data based on specific numerical values or a range of values. This can be helpful for tasks like analyzing sales figures, inventory levels, or any other numeric data where you need to isolate certain values or value ranges.

To add a number filter to an Excel worksheet, you can use the CustomFilter() function. The code below shows how to apply a number filter to filter cells with values greater than a specific number using Python:

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Sales.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Specify the range of data to be filtered
sheet.AutoFilters.Range = sheet.Range["A1:I21"]

# Add a custom filter to filter cells with values greater than 1800
sheet.AutoFilters.CustomFilter(5, FilterOperatorType.GreaterThan, Int32(1800))

# Apply the filter
sheet.AutoFilters.Filter()

# Save the resulting workbook to a file
workbook.SaveToFile("NumberFilter.xlsx", FileFormat.Version2016)
workbook.Dispose()

Apply Filter for Numbers Greater than Specific Value in Excel with Python

In addition to the “greater than” operator type, you are also able to filter numbers with other operator types, including less than, equal, less or equal and more. The code below shows how to filter cells with values between two specific numbers using Python:

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Sales.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Specify the range of data to be filtered
sheet.AutoFilters.Range = sheet.Range["A1:I21"]

# Add a custom filter to filter cells with values between 1000 and 1800
sheet.AutoFilters.CustomFilter(5, FilterOperatorType.GreaterOrEqual, Int32(1000), True, FilterOperatorType.LessOrEqual, Int32(1800))

# Apply the filter
sheet.AutoFilters.Filter()

# Save the resulting workbook to a file
workbook.SaveToFile("NumberFilter1.xlsx", FileFormat.Version2016)
workbook.Dispose()

Apply Filter for Numbers Between Specific Values in Excel with Python

Apply Filter for Font Color, Cell Color, or Icon Sets in Excel with Python

Applying filters based on font color, cell color, or icon sets can be a valuable way to visually distinguish and isolate data within a larger dataset. This functionality helps you quickly identify and work with subsets of information that share common formatting or visual characteristics.

To apply a cell color filter, use the AddFillColorFilter() function. The code below shows how to apply a filter to filter cells filled with a specific color in Excel using Python:

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Sales.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Specify the range of data to be filtered
sheet.AutoFilters.Range = sheet.Range["A1:I21"]

# Create a filter to filter cells filled with a specific color
sheet.AutoFilters.AddFillColorFilter(0, sheet.Range["B2"].Style.Color)

# Apply the filter
sheet.AutoFilters.Filter()

# Save the resulting workbook to a file
workbook.SaveToFile("CellColorFilter.xlsx", FileFormat.Version2016)
workbook.Dispose()

Apply Cell Color Filter in Excel with Python

To apply a font color filter, use the AddFontColorFilter() function. The code below shows how to apply a filter to filter cells with a specific font/text color in Excel using Python:

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Test.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Specify the range of data to be filtered
sheet.AutoFilters.Range = sheet.Range["A1:D5"]

# Create a filter to filter cells with a specific font color
sheet.AutoFilters.AddFontColorFilter(0, Color.get_Red())

# Apply the filter
sheet.AutoFilters.Filter()

# Save the resulting workbook to a file
workbook.SaveToFile("FontColorFilter.xlsx", FileFormat.Version2016)
workbook.Dispose()

To apply an icon set filter, use the AddIconFilter() function. The code below shows how to apply a filter to filter cells with a specific icon set in Excel using Python:

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Icon.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Specify the range of data to be filtered
sheet.AutoFilters.Range = sheet.Range["A1:C5"]

# Create a filter to filter cells with a specific icon set
sheet.AutoFilters.AddIconFilter(2, IconSetType.ThreeArrows, 2)

# Apply the filter
sheet.AutoFilters.Filter()

# Save the resulting workbook to a file
workbook.SaveToFile("IconSetFilter.xlsx", FileFormat.Version2016)
workbook.Dispose()

Apply Filter for Date or Date Range in Excel with Python

Filtering data by a specific date or date range is a common and useful data analysis technique. This allows you to quickly isolate and work with only the information relevant to a particular time period, which can be essential for tasks like tracking trends, reviewing historical performance, or preparing reports.

You can use the AddDateFilter() function to add different types of date filters to Excel. The code below shows how to apply a filter to filter cells with dates within a specific month in Excel using Python:

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Sales.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Specify the range of data to be filtered
sheet.AutoFilters.Range = sheet.Range["A1:I21"]

# Add a date filter to filter cells with dates within a specific month (June, 2024)
sheet.AutoFilters.AddDateFilter(6, DateTimeGroupingType.Month, 2024, 6, 0, 0, 0, 0)

# Apply the filter
sheet.AutoFilters.Filter()

# Save the resulting workbook to a file
workbook.SaveToFile("DateFilter.xlsx", FileFormat.Version2016)
workbook.Dispose()

Apply Date Filter in Excel with Python

Apply Filter for Dynamic Date in Excel with Python

Dynamic date filtering allows users to filter data based on dynamic criteria, such as today’s date, the current week, month, or year. Instead of manually updating filters each time, dynamic date filters automatically adjust based on the current date, ensuring that the analysis is always up to date.

To apply a dynamic date filter, use the DynamicFilter() function. The code below shows how to apply a dynamic filter for filtering cells with dates within the current year in Excel using Python:

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Sales.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Specify the range of data to be filtered
sheet.AutoFilters.Range = sheet.Range["A1:I21"]

# Add a dynamic filter 
sheet.AutoFilters.DynamicFilter(6, DynamicFilterType.ThisYear)

# Apply the filter
sheet.AutoFilters.Filter()

# Save the resulting workbook to a file
workbook.SaveToFile("DynamicDateFilter.xlsx", FileFormat.Version2016)
workbook.Dispose()

Apply Dynamic Date Filter in Excel with Python

Filter Blank or Non-Blank Cells in Excel with Python

Blank cells in an Excel dataset can have a significant impact on data analysis. They can distort calculations, affect statistical measures, or lead to incorrect interpretations. Filtering out blank cells allows for more accurate analysis by focusing only on cells that contain meaningful data.

Non-blank cells can be filtered using the MatchNonBlanks() function. The code below shows how to filter non-blank cells in Excel using Python:

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Input2.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Specify the range of data to be filtered
sheet.AutoFilters.Range = sheet.Range["A1:E7"]

# Create a filter to filter the non-blank cells for the first column in the range
sheet.AutoFilters.MatchNonBlanks(0)

# Apply the filter
sheet.AutoFilters.Filter()

# Save the resulting workbook to a file
workbook.SaveToFile("FilterNonBlankCells.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

Filter Non-Blank Cells in Excel with Python

Similarly, blank cells can be filtered using the MatchBlanks() function. The code below shows how to filter blank cells in Excel using Python:

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Input2.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Specify the range of data to be filtered
sheet.AutoFilters.Range = sheet.Range["A1:E7"]

# Create a filter to filter the blank cells
sheet.AutoFilters.MatchBlanks(0)

# Apply the filter
sheet.AutoFilters.Filter()

# Save the resulting workbook to a file
workbook.SaveToFile("FilterBlankCells.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

Filter Blank Cells in Excel with Python

Remove Filters from Excel with Python

If you no longer need the filters, you can remove them from your worksheet using the Clear() function.

The code below shows how to remove all filters from an Excel worksheet using Python:

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("DynamicFilter.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Remove all filters from the worksheet
sheet.AutoFilters.Clear()

# Save the resulting workbook to a file
workbook.SaveToFile("RemoveFilter.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

Remove Filters from Excel with Python

Conclusion

This blog post demonstrated how to apply various types of filters, such as text filter, number filter, date filter, color filter, dynamic filter, blank filter, and non-blank filter in Excel using Python. Furthermore, it also explained how to remove filters from Excel using Python. We hope you find it helpful.

Related Topics

• Master Excel Number Formats with Python: Apply Number Formats and Retrieve the Results
• Add, Update, Extract or Delete Hyperlinks in Excel with Python
• Add or Remove Data Validation in Excel with Python
• Split an Excel File into Multiple Files with Python
• Python — How to Convert Excel XLS or XLSX to PDF