# How to Find, Replace, and Highlight Data in Excel Using Python

Reports exported from several systems rarely use the same wording. One file says "North America", another says "America"; a department was renamed last quarter; a column header oscillates between "Total" and "Sum". Fixing this by hand has a second, quieter cost: after the edits nobody knows which cells were touched, so verification becomes a line-by-line comparison. A more practical approach is to let a script perform the replacement and color every changed cell at the same time, so the affected area is visible at a glance.

In Python this can be done with Spire.XLS for Python. Beyond plain string replacement, the library supports regular expression matching, numeric and formula lookup, and replacement that applies a new style in the same call, which covers most report-cleaning scenarios.

## Why Do Find and Replace in a Script

*   **Auditable**: the result is written to a new file while the original stays untouched, so you can always compare before and after.
    
*   **Annotated**: replacing and highlighting happen in the same loop, so there is no separate pass to work out what changed.
    
*   **Repeatable**: the same rules apply to dozens of worksheets or files by adding one more loop.
    

## Setting Up the Environment

Install the Spire.XLS library:

```bash
pip install Spire.XLS
```

Then import the required modules in your script:

```python
from spire.xls import *
from spire.xls.common import *
```

## Finding and Replacing Text While Highlighting It

The worksheet-level `FindAllString()` returns a collection of every matching cell, and iterating that collection is enough to change the text and style one cell at a time. The example below replaces every "Total" with "Sum" and fills the affected cells in yellow:

```python
from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.LoadFromFile("Report.xlsx")
worksheet = workbook.Worksheets[0]

# Find all matching cells
ranges = worksheet.FindAllString("Total", True, True)

for range in ranges:
    # Assigning the text performs the replacement
    range.Text = "Sum"
    # Fill the cell yellow to mark the change
    range.Style.Color = Color.get_Yellow()

workbook.SaveToFile("ReplaceAndHighlight.xlsx", ExcelVersion.Version2010)
workbook.Dispose()
```

The three arguments of `FindAllString()` are the text to look for, whether the search is case-sensitive, and whether it must match a whole word. Passing `True, True` here means only a standalone "Total" with exactly that casing is replaced, so `total` and `Subtotal` are left alone. `range.Text` is both a read and a write property — assigning to it performs the replacement — while `range.Style.Color` controls the cell fill and `Color.get_Yellow()` is a predefined color from the enum.

## Marking Matches Without Changing the Content

When reviewing data you often want to flag suspicious cells rather than modify them. In that case, skip the assignment inside the loop:

```python
from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.LoadFromFile("Data.xlsx")
worksheet = workbook.Worksheets[0]

ranges = worksheet.FindAllString("Area", False, False)

for range in ranges:
    # Keep the original text, mark it with color only
    range.Style.Color = Color.get_Yellow()

workbook.SaveToFile("Marked.xlsx", ExcelVersion.Version2013)
workbook.Dispose()
```

Both flags are `False` here, so the search ignores case and does not require a whole-word match. "Area", "area", and "Area Code" are all highlighted.

## Matching Complex Patterns with Regular Expressions

Regular expressions help when the data has no fixed literal form. `FindAllString()` has an overload with a fourth parameter; set it to `True` to enable regex matching:

```python
from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.LoadFromFile("FindTextByRegex.xlsx")
worksheet = workbook.Worksheets[0]

# Match every text that starts with North
ranges = worksheet.FindAllString(".*North.", False, False, True)

for range in ranges:
    range.Style.Color = Color.get_Yellow()

workbook.SaveToFile("RegexHighlighted.xlsx", ExcelVersion.Version2013)
workbook.Dispose()
```

The pattern follows .NET regular expression syntax, so `\d{4}` locates four-digit years and `^A.*` locates any text beginning with A. Combining a regex with highlighting is a quick way to circle the cells that break a naming convention in a sheet that mixes several formats.

## Finding Numbers and Formulas

Non-text data has its own lookup methods:

```python
# Find the number 100; the second argument means "match the exact value"
numberRanges = sheet.FindAllNumber(100, True)
for range in numberRanges:
    print(range.RangeAddress)

# Find cells that use a specific formula
formulaRanges = sheet.FindAll("=SUM(A11,A12)", FindType.Formula, ExcelFindOptions.none)
for range in formulaRanges:
    print(range.RangeAddress)
```

When the second argument of `FindAllNumber()` is `True`, only cells equal to that number match; with `False`, cells containing the number also match. `FindAll()` takes a `FindType` value — besides `Formula` it supports `Text`, `Number`, and `DateTime` — while `ExcelFindOptions.none` means no extra match options are applied. Unlike the earlier examples, these results are usually used for locating rather than editing: read `range.RangeAddress` inside the loop to get the cell reference, then act on it. A common follow-up is deleting the whole row:

```python
cr = sheet.FindString("Address", False, False)
sheet.DeleteRow(cr.Row)
```

`FindString()` returns only the first match, which suits "handle it once" cases. Read the `Row` property from the result and pass it to `DeleteRow()` to remove the row that carries the keyword.

## Applying a New Style During Replacement

When a replacement also has to update appearance, `ReplaceAll()` performs the content and style swap in a single call. It takes four arguments: the old text, the old style, the new text, and the new style.

```python
newStyle = workbook.Styles.Add("newStyle")
newStyle.Font.FontName = "Arial Black"
newStyle.Font.Size = 14

cellRange = sheet.Range["D9"]
oldStyle = cellRange.Style
sheet.ReplaceAll("North America", oldStyle, "America", newStyle)
```

This replaces "North America" with "America" only in cells whose style matches cell `D9`, and applies `newStyle` (Arial Black, size 14) at the same time. Styles created with `workbook.Styles.Add()` can be reused, so for consistently formatted reports you can take the style from one sample cell and let `ReplaceAll()` filter by it, which avoids rewriting identical text elsewhere in the sheet.

## Practical Tips

*   **Choose the match strength deliberately**: start with `False, False` to see the full set of matches, then tighten to a whole-word match once the data is confirmed clean.
    
*   **Log the addresses**: collect `range.RangeAddress` into a list or a log file during the loop to produce a change list after the replacement finishes.
    
*   **Keep a backup**: even though the script writes a new file, retain the original so the diff between the two versions stays available.
    
*   **Release resources**: call `workbook.Dispose()` after each workbook, and in batch jobs load, process, and release one file at a time.
    
*   **Work across worksheets**: `FindAllString()` operates on one worksheet, so for multi-sheet workbooks loop with `Worksheets.Count` and `get_Item(i)`.
    

## Conclusion

This article covered several ways to find and replace data in Excel with Python: replacing text through `FindAllString()` and marking the results with `Style.Color`, matching complex patterns through the regex overload, locating numbers and formulas with `FindAllNumber()` and `FindAll()`, and applying a new style during replacement with `ReplaceAll()`. Combined, these operations form an auditable report-cleaning routine in which replacement and annotation happen in one pass.