How to Clear Formatting from Cells in Excel

How to Clear Formatting from Cells in Excel

Excel is a powerful and versatile spreadsheet application that allows you to create, manipulate, and analyze data. One of the features of Excel is that you can apply various formatting options to your cells, such as fonts, colors, borders, alignment, and number formats. Formatting can help you make your data more readable, attractive, and meaningful.

However, sometimes you may want to remove the formatting from your cells and restore them to their default appearance. For example, you may want to clear the formatting from a copied range of cells before pasting it into another worksheet, or you may want to remove the formatting from a cell that contains an error or a formula.

There are different ways to clear formatting from cells in Excel, depending on what kind of formatting you want to remove and how many cells you want to affect. In this article, we will show you how to clear formatting from all cells, select cells, blank cells, and specific cells in Excel.

Clear Formatting from All Cells in Excel

If you want to remove all the formatting from your entire worksheet, you can use the following steps:

Click on the top left corner of the worksheet (one with a gray triangle icon). This will select all the cells in the worksheet.
Click the Home tab in the ribbon.
In the Editing group, click on the Clear option drop-down.
In the options that show up, click on Clear Formats.

This will clear all the contents, formats, and comments that are applied to the cells in the worksheet. The cleared cells will remain as blank or unformatted cells on the worksheet¹.

Clear Formatting from Select Cells in Excel

If you only want to remove the formatting from a single or multiple select cells, you can use the following steps:

Select the cell or cells from which you want to remove formatting. To select a specific row or column, click on the row or column header instead of individual cells.
Click the Home tab in the ribbon.
In the Editing group, click on the Clear option drop-down.
In the options that show up, choose one of the following:
- To clear all contents, formats, and comments that are contained in the selected cells, click Clear All.
- To clear only the formats that are applied to the selected cells, click Clear Formats.
- To clear only the contents in the selected cells, leaving any formats and comments in place, click Clear Contents.
- To clear any comments or notes that are attached to the selected cells, click Clear Comments and Notes.
- To clear any hyperlinks that are attached to the selected cells, select Clear Hyperlinks².

This will clear only the specified elements from your selected cells. The cleared cells will remain as blank or unformatted cells on the worksheet.

Clear Formatting from Blank Cells in Excel

If you want to remove the formatting from blank cells in your worksheet, you can use a simple trick with Find and Replace. Here’s how:

Press Ctrl+A (Windows) or Command+A (Mac) to select your entire worksheet.
Press Ctrl+F (Windows) or Command+F (Mac) to open the Find and Replace dialog box.
In the Find what box, enter =.
In the Replace with box, leave it blank.
Click Options to expand more settings.
Under Look in, select Values.
Under Within, select Sheet.
Click Find All to find all the blank cells in your worksheet.
Press Ctrl+A (Windows) or Command+A (Mac) to select all the found cells.
Close the Find and Replace dialog box.
Click the Home tab in the ribbon.
In the Editing group, click on the Clear option drop-down.
In the options that show up, click on Clear Formats.

This will clear all the formats from your blank cells in your worksheet ³.

Clear Formatting from Specific Cells in Excel

If you want to remove only certain types of formatting from specific cells in your worksheet, you can use a more advanced method with Paste Special. Here’s how:

Select a blank cell somewhere on your worksheet.
Press Ctrl+C (Windows) or Command+C (Mac) to copy it.
Select the cell or range of cells from which you want to remove specific formatting.
Right-click on your selection and choose Paste Special from the context menu.
In the Paste Special dialog box, under Paste, select Formats.
Under Operation, select Subtract.
Click OK.

This will subtract all the formats from your selected cells that match with the blank cell that you copied earlier. For example, if your blank cell has no fill color and no border, this method will remove any fill color and border from your selected cells.

You can also use this method to remove other types of formatting such as number formats, alignment, font size, etc., by adjusting the format of the blank cell that you copy.

Conclusion

In this article, we have shown you how to clear formatting from cells in Excel using different methods. You can use these methods to remove unwanted or unnecessary formatting from your cells and make your data more consistent and clear.

We hope you found this article helpful and informative. If you have any questions or feedback, please feel free to leave a comment below.

¹: Clear cells of contents or formats - Microsoft Support ²: How to Clear Formatting in Microsoft Excel - How-To Geek ³: How to Remove Cell Formatting in Excel (from All, Blank, Specific Cells) : [How to Remove Formatting in Excel (5 Easy Ways) - Spreadsheet Planet]