To replace formatting in Excel for Microsoft 365 on Windows, press Ctrl+H, choose Options, set a format under Find what and a new format under Replace with, then select Replace All. To make the change more cautiously, use Find All to select matching cells first, then apply the new formatting from the Home tab; this lets you check which cells will change before you format them.
What Replace Formatting Does in Excel
Excel’s Find and Replace tool can search for cells with particular formatting and apply different formatting to matches. It can target features such as font, fill color, borders, number format, and alignment. For example, you could find cells with a yellow fill and change that fill to pale blue without searching for a particular word or number.
Formatting is separate from cell content. A cell might contain the number 0.25 but display it as 25%, or contain a date serial number but display it as a calendar date. Changing its number format changes how the value appears, not the underlying value. However, the Replace tab can also replace text or values if you enter search and replacement text. If the goal is strictly visual, avoid adding text criteria and verify a small test before applying a change across a large range.
There is an important limitation: a cell’s visible appearance may come from conditional formatting, a table style, or a theme rather than ordinary direct formatting. Find and Replace is most useful for replacing direct cell formatting, such as a fill or font manually applied from the ribbon.
Replace a Format with Find and Replace
- Select the worksheet or range you want to work with. Selecting a range limits the search to that area; with no range selected, Excel generally searches the active worksheet.
- Press Ctrl+H to open Find and Replace. You can also use Home > Find & Select > Replace.
- Select Options to display the search settings. If the dialog shows a Format button beside Find what, use it to specify the format to find. You can choose a format directly or use Choose Format From Cell, if available, and click a sample cell.
- Choose the Format button beside Replace with and specify the new appearance. For instance, set a different fill color while leaving font and number-format choices unchanged as appropriate.
- Check the other search options, including Within and Look in, if shown. Then use Find All to inspect matches or Replace All to apply the replacement.
When using Find All, Excel lists matching cells and selects the found results. You can review the addresses, close the dialog, and apply formatting using Home > Font, Home > Alignment, or the Number group. This approach is useful when you want to confirm the target cells or when you only need to change one part of the appearance. If you use Replace All, the selected replacement format can affect all matching cells at once, so review the scope and criteria before committing.
For an initial test, select a small range containing a few known examples. Confirm that the matching cells are the ones you intended and that their contents remain correct. If the results are unexpected, stop and adjust the search criteria rather than repeating Replace All across the whole workbook.
Choose the Search Scope and Format Carefully
Scope is a common source of surprises. A selection made before opening Find and Replace can limit the operation to those cells. Without a selection, the search usually applies to the active sheet. The Within option lets you choose between the sheet and the workbook when that option is available. Workbook-wide searches can change cells on multiple tabs, so use them only when the same formatting rule genuinely applies everywhere.
Excel can also retain search settings from earlier use. Before a large replacement, check the visible options instead of assuming the dialog has reset. Depending on the dialog and workbook, settings may include searching by rows or columns, looking in formulas or values, matching case, and matching entire cell contents. Those options mainly affect content searches, but they can still matter if you combine a text criterion with a format criterion.
When copying a format from an example cell, pick a cell that represents the exact style you want. Two cells can look similar while differing in number format or border details. If the replacement format is meant to change only the fill, make sure you do not unintentionally impose a different font, alignment, or number display. It can help to inspect the sample cell through Format Cells before using it as the replacement model.
Which Parts of a Cell’s Appearance Can Change?
Excel’s Format Cells dialog organizes many direct-formatting settings into tabs such as Number, Font, Border, Fill, and Alignment. The replacement you choose can affect one or several of those categories, depending on what you specify. A typical cleanup might replace an obsolete red font with a dark gray font, standardize a date display, or remove an inconsistent border.
- Number: Changes how values such as dates, percentages, currency, or decimals are displayed. It does not convert the stored value by itself.
- Font and fill: Changes text appearance or cell background, including chosen colors and emphasis.
- Border and alignment: Changes cell edges, text position, wrapping, and related display settings.
- Combined formatting: May affect multiple properties at once. Review the replacement settings to avoid changing more than intended.
Replacing a number format does not repair an incorrect value. For example, if a cell contains text that looks like a date, applying a date number format may not turn it into a real date. Likewise, changing a percentage display does not automatically determine whether the underlying value is correct. Treat display formatting and data correction as separate tasks.
When Find and Replace Is Not the Right Tool
If the target cells are easy to identify visually and there are only a few of them, the Format Painter may be faster. Select a correctly formatted cell, choose Home > Format Painter, and apply its formatting to the target. Double-click Format Painter to apply the copied format to several places; press Esc when finished. This copies a set of formatting properties, so use it carefully if the target cells need to keep their existing number formats.
For a reusable design, consider applying a cell style from Home > Cell Styles. Styles help keep headings, input cells, and totals consistent, and editing a style can update cells that use it. This is often more maintainable than repeatedly searching for a particular color or font. For a limited change, select the cells and use the relevant controls in the Home tab, or open Format Cells with Ctrl+1 on Windows.
Paste Special can also transfer formatting from a source cell: copy the source, select the destination, then use Home > Paste > Paste Special and choose Formats. This copies formatting rather than the source cell’s value. It is useful for a known destination, but it does not identify every cell matching an old format throughout a sheet.
Conditional Formatting, Tables, and Themes
A cell that appears red because it meets a conditional-formatting rule may not have a red fill applied as ordinary direct formatting. Changing its direct fill might not change what you see, because the conditional rule can continue to control the displayed appearance. To investigate, select the cell and choose Home > Conditional Formatting > Manage Rules. Check whether the rule applies to the cell and whether its formatting is responsible for the color or font.
Excel tables can also apply banded rows, header formatting, and other visual features through a table style. If the appearance belongs to a table style, changing the table style or its options may be more effective than replacing direct formatting cell by cell. Theme colors can behave similarly: a color chosen from the theme palette may change if the workbook theme changes. Identify the source of the appearance before trying to replace it.
These distinctions explain a frequent misconception: “I replaced the fill, but the cell still looks the same.” The operation may have changed direct formatting successfully while a conditional rule or table style continued to display another color. If you are unsure, inspect a single cell and temporarily review its conditional-formatting rules or table settings before making a broader change.
Common Problems and Safe Checks
- No cells are found: Clear the old format criteria and set them again. If you used a sample cell, make sure its direct formatting is what you want to search for.
- Too many cells match: Restrict the selected range or active sheet, and add a content criterion only if you need one. Use Find All to review addresses before changing anything.
- The appearance does not change: Check conditional formatting, table styles, and theme effects. The visible style may not be ordinary cell formatting.
- Numbers display unexpectedly: Review the Number format separately from the stored values. A format change can reveal extra decimal places or display a value as a date without changing the data itself.
- Old criteria keep reappearing: Use Clear Find Format or reset the format choices in the Find and Replace dialog, then set the criteria afresh.
Save a copy of an important workbook before a workbook-wide replacement. Excel normally allows a recent Find and Replace operation to be undone with Ctrl+Z, but relying on Undo alone is less prudent when the workbook is complex or other changes follow. After the replacement, inspect representative cells and, if relevant, check other sheets that share the same layout.
Excel on Mac and Excel for the Web
The instructions above are for Excel for Microsoft 365 on Windows. In Excel for Mac, Find and Replace is available from the Edit menu, and keyboard shortcuts can differ with the Mac keyboard and Excel settings. Use the menu command if Ctrl+H does not open the expected dialog. The Home tab and Format Cells provide similar ways to apply formatting, though dialog layout and labels can vary slightly by release.
Excel for the web has a more limited interface for some advanced Find and Replace formatting tasks. If the dialog does not offer the format criteria or controls you need, open the workbook in the desktop Excel app and make the change there. Before editing a shared workbook, also confirm that you are not applying a broad replacement that changes a teammate’s carefully customized worksheet.
Frequently Asked Questions
Can I replace formatting without changing cell values?
Yes. Use format criteria to identify cells and set the replacement formatting, without entering replacement text or values. For extra control, use Find All to select matches and then change their formatting from the Home tab. Check a small sample first, especially if the cells contain important data.
How do I change one color throughout a worksheet?
Open Ctrl+H, set the old color as the Find format and the new color as the Replace with format, then check the search scope and use Find All or Replace All. This targets matching direct formatting; it may not change colors supplied by conditional formatting or a table style.
Can Find and Replace change only the font or fill?
Yes. Set the relevant property in the Find format and replacement format choices. To avoid unintended changes to other properties, review the selected formatting settings and consider finding the cells first, then changing only the font or fill from the ribbon.
Why does Replace All change the wrong cells?
The search may be covering more sheets or cells than expected, or the format criterion may match more cells than you thought. Use a selected range, review the Within setting, and choose Find All to inspect the matched cell addresses before replacing.
Will replacing a number format change the actual number?
No. A number format changes how a value is displayed, not the stored value. If the underlying number or date is wrong, correct the data separately rather than expecting a formatting replacement to repair it.
Related
- Excel Slow? Causes, Fixes, and Ways to Speed It Up
- Cash Flow Excel Sheet: Build a Reliable Monthly Forecast
- Cash Flow Format in Excel: Build a Simple Statement
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- XLOOKUP in Google Sheets: How It Works and When to Use ItYes. XLOOKUP is a built-in Google Sheets function that searches a row or column for a value and…
- How to Freeze More Than the Top Row in ExcelTo freeze more than the top row in Excel, select the cell directly below the rows you want…
- Freeze Panes in Excel: How to Keep Rows and Columns VisibleTo insert a freeze pane in Excel for Microsoft 365 on Windows, select the cell immediately below the…
- How to Add a Checkbox in Excel for the Web and Use ItTo add a checkbox in Excel for the web, select the cell or range where you want it,…
- Remove Section Breaks in Word Without Losing FormattingTo remove a section break in Microsoft Word, show formatting marks with Home > ¶, select the section…
- How to Put a PowerPoint Slideshow on LoopTo make a PowerPoint presentation repeat, open it in the desktop app and choose Slide Show > Set…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.