sodcalculator

10 Tech How-To

Find and Replace Formatted Values in Excel

Guide 10-52823 · Updated · Step by step

This guide shows you how to use Excel’s Find and Replace to locate cells with specific formatting (like bold, fill color, or font) and replace that formatting with something else. You’ll also learn how to replace the value itself while matching formatting, and how to avoid common pitfalls. No special tools are needed—just a spreadsheet with some formatting already applied.

What You’ll Need

  • Microsoft Excel (2016 or later; the steps are similar in older versions)
  • A workbook with cells that have formatting you want to find (e.g., bold text, yellow fill, specific font color)
  • Basic familiarity with Excel’s ribbon interface

Step-by-Step

  1. Open the Find and Replace dialog. Press Ctrl+H (Windows) or Cmd+Shift+H (Mac) to open the Replace tab. If you only need to find, you can use Ctrl+F and then switch to the Replace tab.
  2. Expand the options. Click the Options button in the bottom-left corner of the dialog. This reveals additional fields, including the Format buttons.
  3. Set the formatting to find. Click the Format button next to the Find what field. In the Find Format dialog, choose the formatting you want to locate. You can specify font style, size, color, fill color, borders, and more. For example, to find all bold cells, go to the Font tab and select Bold in the Font style list. Click OK.
  4. Set the replacement formatting. Click the Format button next to the Replace with field. In the Replace Format dialog, choose the new formatting. For example, to change bold to italic, go to the Font tab, select Italic, and clear Bold (set it to Regular). Click OK.
  5. Leave the value fields blank (for formatting-only replacement). If you only want to change the formatting and not the cell values, leave both Find what and Replace with empty. This tells Excel to match any cell with the specified formatting.
  6. Choose the search scope. In the Within drop-down, select Sheet to search the current worksheet, or Workbook to search all sheets. Use Search to choose by rows or columns, and Look in to specify whether to search formulas, values, or comments.
  7. Preview and replace. Click Find All to see a list of matching cells at the bottom of the dialog. Click Replace All to change all matches at once, or use Find Next and Replace to go one by one. If you used Replace All, Excel will show a confirmation message with the number of replacements made.
  8. Clear the formatting criteria when done. After the operation, click the Format button next to Find what, then click Clear in the Find Format dialog. This prevents accidental matches in future searches.

Common Mistakes

  • Forgetting to clear the format after use. If you don’t clear the format, subsequent Find and Replace operations will still be limited to that formatting, which can cause confusion.
  • Mistaking ‘Find what’ for a value. When replacing formatting only, you must leave the Find what field empty. If you type a value, Excel will only match cells with that value AND the specified formatting.
  • Not specifying the exact format. If you only set bold in the Find Format, Excel will match cells that are bold, regardless of other formatting. To match a specific combination (e.g., bold AND red font), you must set all those attributes in the Find Format dialog.
  • Using ‘Replace All’ without previewing. Always click Find All first to verify the matches. Replacing all without checking can lead to unintended changes across the sheet.

Tips

  • Use ‘Choose Format From Cell’ – In the Find Format dialog, click the drop-down arrow on the Format button and select Choose Format From Cell. Then click a cell that has the formatting you want to find. This is faster and more accurate than manually selecting attributes.
  • Combine value and format replacement. You can replace both the value and the formatting in one go. For example, to change all cells with the value “N/A” that are red to “Not Available” with a yellow fill, type “N/A” in Find what, set the red format, type “Not Available” in Replace with, and set the yellow format.
  • Use wildcards for partial matches. In the Find what field, you can use asterisks (*) to match any sequence of characters. For example, “*error*” will match any cell containing the word “error”. This works with formatting criteria too.
  • Test on a small range first. If you’re unsure, select a small range of cells before opening Find and Replace. Excel will only search within that selection.

Troubleshooting

  • No matches found. If Excel says it can’t find any data, double-check that the formatting criteria are correct. Sometimes the format you set includes a font color or fill that isn’t actually present. Use Choose Format From Cell to pick a real cell.
  • Replacement doesn’t change the formatting. This usually happens when you left the Replace with format empty. If you only want to change formatting, you must set the new format in the Replace with Format dialog. If you leave it blank, Excel will remove the formatting entirely (set to default).
  • Find and Replace is disabled. This can occur if the sheet is protected. Go to the Review tab and click Unprotect Sheet (you may need the password). Also, ensure the workbook is not in shared mode.
  • Replacement affects cells you didn’t intend. If you used Replace All and it changed too many cells, press Ctrl+Z to undo. Then redo the operation with more specific criteria, such as adding a value to the Find what field.

Frequently Asked Questions

Can I find and replace formatting without changing the cell values?

Yes. Leave both the Find what and Replace with fields empty, and only set the formatting in the respective Format dialogs. Excel will then match cells based solely on formatting.

How do I find cells with a specific fill color?

In the Find Format dialog, go to the Fill tab, choose the color, and click OK. Then use Find All to see matching cells.

Can I replace a fill color with a different fill color?

Yes. Set the find format to the original fill color, and set the replace format to the new fill color. Leave the value fields empty.

Does Find and Replace consider conditional formatting?

No. Find and Replace only looks at static formatting applied directly to cells. Conditional formatting is dynamic and not included in the search.

Can I use Find and Replace to remove formatting?

Yes. Set the find format to the formatting you want to remove, and in the Replace Format dialog, choose the default format (e.g., no fill, regular font). Then replace all.

Why does my search not find cells that appear to have the formatting?

This often happens when the formatting is applied via a table style or conditional formatting. Check if the cell is part of an Excel Table—table styles override direct formatting. Also, ensure you haven’t accidentally set a font color or other attribute that doesn’t match.

Related

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →