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.
Program Successfully in Excel! This guide will provide you with all the information you need to automate any task in Excel and save time and effort. Learn how to extend Excel's functionality with VBA to create solutions not possible with the standard features. Includes latest information for Excel 2024 and Microsoft 365. Check out Mastering Excel VBA Programming today!
There are times when displaying zero values in a worksheet (especially if there are lots of them) can be distracting from ...
Discover MoreThe error message "too many cell formats" can be difficult to fix. This tip describes ways you can attempt to get rid of ...
Discover MoreWant to adjust the font size used in a cell or range of cells? It's easy to do by using the shortcut described in this tip.
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