Clear formatting in Excel (Quick and simple methods)
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.
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
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 Hometab, 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.
Excel removes all formatting from your selected cells right away.
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.
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.
Select an unformatted cell and click Format Painter.
Select the cells from which you want to remove the formatting.
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 Hometab, in the Stylesgroup, click Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.
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.
Supposing you have created multiple filters across different worksheets in an Excel workbook, and now you want to clear all these filters at once. Normally, you need to shift to the worksheet one by one to check the filter and then clear it manually. Is there any convenient method to clear filters from all worksheets in an active workbook? Please try the method in this article.
Suppose you have multiple worksheets containing filtered data in your workbook. To clear all the filters, you need to check the filtered list in different worksheets and then clear them manually one by one. That’s annoying! In this article, we will show you several methods of clearing filters when opening, saving or closing a workbook in Excel.
Best Office Productivity Tools
Supports Office/Excel 2007-2021 and 365 | Available in 44 Languages | Easy to Uninstall Completely