How to Find and Replace Data in Excel with Python and Highlight Matches

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: pip install Spire.XLS Then import the required modules in your script: 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: 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: 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: 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: # 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: 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. 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.