Skip to main content

Clear formatting in Excel (Quick and simple methods)

Author: Amanda Li Last Modified: 2023-07-04

Formatting plays a crucial role in presenting data effectively in Excel. However, there are times when you need to remove formatting to start with a clean slate or make your data consistent. This tutorial will guide you through different techniques to efficiently clear formatting in Excel, whether it involves removing bold or italic formatting, resetting font styles, or clearing cell colors, borders, number formatting and conditional formatting.


Video: Clear formatting in Excel


Remove formatting with Clear Formats option

First, let’s learn the most straightforward approach to eliminate various formatting elements, including conditional formatting, from your data.

Step 1: Select the range from where to clear formatting

Tips:
  • To select all cells on a worksheet, press Ctrl + A.
  • To select a whole column or row, click on the heading of the column or row.
  • To select non-adjacent cells or ranges, select the first cell or range, then hold Ctrl while selecting the other cells or ranges.

Step 2: Select the Clear Formats option

On the Home tab, in the Editing group, click Clear > Clear Formats.

Tip: Press ATL + H + E + F to select the Clear Formats option if you prefer a keyboard command.

Result

Excel removes all formatting from your selected cells right away.

Notes:

  • If you find yourself constantly needing to clear formatting in Excel, you can add the Clear Formats command to the Quick Access Toolbar. This allows you to quickly access the Clear Formats option with just a single click.
  • To do this, right-click on the Clear Formats option and select Add to Quick Access Toolbar.
  • The Clear Formats option cannot clear formatting that is exclusively applied to individual characters or words within a cell as below screenshot shown. If your data falls into this category, please use the Clear All Formatting feature of Kutools for Excel.

Clear formatting (including formatting applied to individual characters within a cell) with Kutools

In Excel, individual characters within a cell can have different formatting, which cannot be cleared using the standard clear formatting option. This is where Kutools for Excel's Clear All Formatting feature shines. Not only can it clear formatting applied to individual characters or words within a cell, but it can also clear regular formatting, including cell colors, borders, conditional formatting, just like Excel's built-in clear formatting option.

After selecting the range from where to clear formatting, on Kutools tab, click Format > Clear All Formatting.

Note: To enable this feature, please download and install Kutools for Excel. The professional Excel add-in offers a 30-day free trial with no limitations.


Wipe formatting with Format Painter

The Format Painter feature in Excel is a handy tool that allows you to quickly apply formatting from one cell to another. This means you can effortlessly copy the formatting of an unformatted cell and apply it to another cell, effectively wiping out any existing formatting, including conditional formatting.

  1. Select an unformatted cell and click Format Painter.
  2. Select the cells from which you want to remove the formatting.

Notes:

  • To clear formatting from multiple non-adjacent cells or ranges, double-click the Format Painter button after selecting an unformatted cell, then individually select and wipe the formatting from each desired cell or range. When finished, click the Format Painter button again to deselect it and return to normal editing mode.
  • The Format Painter method cannot clear formatting that is exclusively applied to individual characters or words within a cell as below screenshot shown. If your data falls into this category, please use the Clear All Formatting feature of Kutools for Excel.

Remove only conditional formatting

In the provided example, we have applied a conditional formatting rule (if cell value > 10 then format the cell with a light red fill and dark red text), cell borders and an orange font color. To clear only the conditional formatting while preserving other formatting elements, you can achieve this by following these steps:

Step 1: Select the range from where to clear conditional formatting

Step 2: Select the Clear Rules from Selected Cells option

On the Home tab, in the Styles group, click Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.

Result

Only the conditional formatting is removed while other formatting elements are retained.

Above is all the relevant content related to clearing formatting in Excel. I hope you find the tutorial helpful. If you're looking to explore more Excel tips and tricks, please click here to access our extensive collection of over thousands of tutorials.

Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations