Removing All Formatting in a Worksheet

Dennis has a workbook that has been in use in his company for years, and now he's inherited it. Dennis doesn't like the formatting that has been applied in the worksheets, so he wonders if there is a quick way to remove all cell formatting from a worksheet, without affecting cell content, so that he can make the data look decent.

The easiest way to remove all formatting is to follow these steps:

1. Select all the cells. (You can either press Ctrl+A or click the triangle above the row numbers and to the left of the column letters.)
2. Display the Home tab of the ribbon.
3. In the Editing group, click the Clear tool. (It looks like an eraser.) Excel displays some clearing options.
4. Choose Clear Formats.

When you follow these steps, Excel removes almost all formatting. For instance, it will remove fonts, colors, borders, number formats, conditional formatting, cell merging, etc. It does not change column width, row height, or any formatting applied to individual characters within a cell. (More on how to change these in a moment.)

There is a drawback to using Clear Formats—you will no doubt end up seeing things change that you might not have associated with formatting. For instance, all cells are set back to a General format, which means that all your dates and times may seem to change to numbers. This is because dates and times are displayed using cell formats. Remove the format (as Clear Formats does), and you end up seeing the underlying serial number for the date/time.

If you don't want to get rid of conditional formatting or merged cells, you may get the results you want by following these steps, instead:

1. Select all the cells.
2. Display the Home tab of the ribbon.
3. Click Cell Styles. Excel displays a bunch of different styles you can apply.
4. Choose Normal.

The other thing that hasn't been addressed when it comes to formatting is hyperlinks. When you use Clear Formats or apply the Normal style, the hyperlinks in a cell remain active, but the formatting used to denote the presence of the hyperlink is removed. Chances are good, though, that if you want to get rid of all formatting, you also want to get rid of the hyperlinks along with their formatting.

The only way to address all of these unwanted formatting changes or formatting you want changed that isn't changed is to use a macro. Here is a good start to a macro that will, in a custom manner, remove formatting from cells in the worksheet:

Sub ClearWorksheetFormatting()
    Dim ws As Worksheet
    Dim c As Range
    Dim sTemp As String

    Set ws = ActiveSheet

    For Each c In ws.UsedRange
        ' Check to see if cell is part of merged area
        If c.MergeCells Then
            c.MergeArea.UnMerge
        End If

        ' Delete any hyperlinks
        c.Hyperlinks.Delete

        ' Clear cell-level formatting if not a date
        If IsDate(c) Then
            sTemp = c.NumberFormat
            c.ClearFormats
            c.NumberFormat = sTemp
        Else
            c.ClearFormats
        End If

        ' Re-enter information to get rid of in-cell formatting
        If Not IsEmpty(c) Then
            If c.HasFormula = True Then
                c = c.Formula
            Else
                c = c.Value
            End If
        End If
    Next c

    ' Reset column widths and row heights
    ws.Cells.ColumnWidth = 8.43   ' default width
    ws.Cells.RowHeight = 15       ' default height
End Sub

The macro steps through each cell in the .UsedRange of a worksheet. It checks if the cell is merged and, if so, undoes the merging. Then it deletes any hyperlinks in the cell and next checks to see if the cell contains a date/time. If it does, then the date formatting is saved, all cell formatting is removed, and the date formatting is reapplied. If the cell does not contain a date/time, then its formatting is simply cleared.

The next step is for the macro to re-enter information in the cell in order to remove any in-cell formatting. (For instance, if part of the text in a cell is bold or italic, but not all of it is.) Finally, the column width and row height of all of the cells is reset to default.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13956) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.