DEV Community

Jeremy K.
Jeremy K.

Posted on

Find and Highlight Data in Excel Using Python

#python #programming #api

In daily data processing, locating specific information—like outliers, keywords, or target values—in large Excel files can be tedious. Highlighting these entries makes them instantly recognizable, saving time and reducing errors. This guide shows you how to efficiently search for and highlight data in Excel using the free library Free Spire.XLS for Python, streamlining your workflow with minimal code.

⚙️ About the Library: Free Spire.XLS for Python

Free Spire.XLS for Python is a lightweight, powerful tool for Excel manipulation—no Microsoft Office installation required. It supports common formats (XLS, XLSX, CSV) and offers robust APIs for tasks like creating, editing, and converting spreadsheets. Its simplicity and versatility make it ideal for both basic and complex Excel operations.

Installation
Installing the library is quick with pip:

pip install Spire.XLS.Free
Enter fullscreen mode Exit fullscreen mode

Once installed, import the necessary modules to start coding:

from spire.xls import *
from spire.common import *
Enter fullscreen mode Exit fullscreen mode

🔍 Step-by-Step: Find and Highlight Specific Data

Let’s walk through a real-world example: Suppose you have a sales report (sales_data.xlsx) and need to flag all cells containing "East Region" with a yellow background and bold text for quick visibility.

Step 1: Load the Excel File and Select the Worksheet

First, load your Excel file using the Workbook class, then access the worksheet you want to process (we’ll use the first worksheet here):

# Initialize a Workbook object
workbook = Workbook()
# Load the Excel file
workbook.LoadFromFile("sales_data.xlsx")

# Get the first worksheet (indexes start at 0)
worksheet = workbook.Worksheets[0]
Enter fullscreen mode Exit fullscreen mode

Step 2: Search for Target Data

Use the FindAllString method to locate all cells containing your target keyword. This method simplifies searching by handling iterations internally:

# Find all cells with "East Region"
# Parameters: (search_text, case_sensitive, match_entire_cell)
cell_ranges = worksheet.FindAllString("East Region", False, True)
Enter fullscreen mode Exit fullscreen mode

Step 3: Apply Highlight Formatting

Loop through the found cells and customize their style. Here, we’ll set a yellow background and bold font to make them stand out:

# Highlight each matched cell
for cell in cell_ranges:
    # Set background to light yellow
    cell.Style.Color = Color.get_LightYellow()
    # Make font bold
    cell.Style.Font.IsBold = True
    # Optional: Adjust font color (e.g., dark red for stronger contrast)
    # cell.Style.Font.Color = Color.Red
Enter fullscreen mode Exit fullscreen mode

Step 4: Save the Modified File

Save your changes to a new file to avoid overwriting the original data:

# Save the updated file (supports XLSX, XLS, etc.)
workbook.SaveToFile("Highlighted_Sales_Data.xlsx")
# Release resources
workbook.Dispose()
Enter fullscreen mode Exit fullscreen mode

👉 Why Use FindAllString?

This method outperforms manual cell-by-cell checks in three key ways:

  • Simplicity: Replaces nested loops with a single line of code.
  • Speed: Optimized for large files, reducing processing time.
  • Flexibility: Supports case sensitivity and exact/fuzzy matching via parameters.

💡 Bonus: Highlight Multiple Keywords

To flag several terms (e.g., "East Region" and "Product A") in one go, reuse the logic above with a list of keywords:

# Define keywords to highlight
keywords = ["East Region", "Product A"]

# Highlight each keyword
for keyword in keywords:
    matches = worksheet.FindAllString(keyword, False, True)
    for cell in matches:
        cell.Style.Color = Color.get_LightYellow()  # Consistent formatting
Enter fullscreen mode Exit fullscreen mode

💬 Wrap-Up

With just a few lines of code, Free Spire.XLS for Python lets you automate Excel data searching and highlighting—no manual scrolling or formatting required. Whether you’re auditing reports, filtering records, or analyzing trends, this approach cuts down on repetitive work and ensures accuracy. Try it for your next data processing task!

Top comments (0)