Written by Allen Wyatt (last updated February 1, 2025)
This tip applies to Excel 97, 2000, 2002, and 2003
David needs to find and change every occurrence of a specific cell format in a multi-worksheet workbook. For example, he may need to find all cells that are formatted as Currency and change that format to General. He wonders how to accomplish the task.
The best way to go about this task depends on the version of Excel you are using. If you are using Excel 2003 you can simply use Excel's Find and Replace tool to make the change. Follow these steps:
Figure 1. The Replace tab of the Find and Replace dialog box.
Figure 2. The Number tab of the Find Format dialog box.
If you are using an older version of Excel, then the Find and Replace tool doesn't allow you to search or replace formatting. Instead, you must use a macro to do the changes. Here's an example of a macro that simply goes through all the used cells in the workbook and sets all the formats to General.
Sub FormatGeneral() Dim iSht As Integer Dim rng As Range For iSht = 1 To Sheets.Count Set rng = Worksheets(iSht).UsedRange With rng .NumberFormat = "General" End With Next End Sub
If you wanted to get a bit more selective in which formats were replaced, then you'll need to check the existing format of the cells as you go through them. For instance, the following macro checks for any cells formatted as Currency and then changes only those cells to a General format.
Sub CurrencyToGeneral() Dim iSht As Integer Dim rng As Range Dim c As Range For iSht = 1 To Sheets.Count For Each c In Worksheets(iSht).UsedRange.Cells If c.NumberFormat = "$#,##0.00" Then c.NumberFormat = "General" End If Next c Next End Sub
If you want to make the macro even more flexible, you can have it ask you to click on a cell that uses the format you want to find and then click on a cell that uses the format you want to change those cells to.
Public Sub UpdateFormats() Dim rFind As Range Dim rReplace As Range Dim rNextCell As Range Dim sNewFormat As String Dim sOldFormat As String Dim ws As Worksheet On Error Resume Next ' Determine the old format Do Set rFind = Application.InputBox( _ prompt:="Select a cell that uses the format " & _ "for which you want to search", _ Type:=8) If rFind Is Nothing Then If MsgBox("Do you want to quit?", vbYesNo) = vbYes Then Exit Sub ElseIf InStr(1, rFind.Address, ":", vbTextCompare) > 0 Then MsgBox "Please select only one cell." Set rFind = Nothing End If End If Loop Until Not rFind Is Nothing sOldFormat = rFind.NumberFormat ' Determine the new format Do Set rReplace = Application.InputBox( _ prompt:="Select a cell using the new format", _ Type:=8) If rReplace Is Nothing Then If MsgBox("Do you want to quit?", vbYesNo) = vbYes Then Exit Sub ElseIf InStr(1, rReplace.Address, ":", vbTextCompare) > 0 Then MsgBox "Please select only one cell." Set rReplace = Nothing End If End If Loop Until Not rReplace Is Nothing sNewFormat = rReplace.NumberFormat ' Do the replacing For Each ws In ActiveWorkbook.Worksheets For Each rNextCell In ws.UsedRange If rNextCell.NumberFormat = sOldFormat Then rNextCell.NumberFormat = sNewFormat End If Next rNextCell Next ws MsgBox "The selected format has been changed." End Sub
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9865) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Replacing Cell Formats.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Need to protect a series of Social Security Numbers in a worksheet? The techniques provided in this tip might be a good ...
Discover MoreWhen you create custom formats for your data, Excel provides quite a few ways you can make that data look just as you ...
Discover MoreWhen you add a combo box to a worksheet, Excel makes some assumptions about the best font size to use in the control. ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments