This post discusses different methods to clear the formatting from cells in Microsoft Excel.
How to remove formatting in Excel
Here are multiple methods using which you can remove formatting in your Excel workbooks on Windows PC:
- Use the Clear Formats option to remove formatting in Excel.
- Use keyboard shortcuts to remove formatting in Excel.
- Clear all formatting using the Format Painter tool.
- Run a VBA code to remove formatting.
- Remove formatting from a picture.
- Clear Conditional Formatting.
1] Use the Clear Formats option to remove formatting in Excel
The first and easiest method to clear formatting in your Excel workbook is to use the Clear Formats option. It is available on your ribbon and can quickly be accessed from the Home tab.
Open your Excel workbook and then select the cells from where you want to remove formatting. If you want to select all cells, press the Ctrl+A hotkey.
Now, go to the Home tab from your ribbon, and from the Editing group, click on the Clear drop-down option. After that, select the Clear Formats option from appeared menu options.
As soon as you do that, all the formatting (color, alignment, etc.) from the selected cells will be removed.
See: How to format or edit an Image in Excel?
2] Use keyboard shortcuts to remove formatting in Excel
Another easy method to quickly remove formatting in Excel is to use keyboard shortcuts. Here’s how you can use the shortcut keys you can use in a sequence to clear formatting from selected cells in MS Excel:
First, you can select the cells from which you want to remove formatting and press the Alt key on your keyboard. It will show the keys you need to press to select a particular menu and other options. Now, for selecting the Home menu, press the H key.
To choose the Clear option, select the key that is being shown for the option. It is usually the E key that you need to press.
After that, press the F key to use the Clear Formats option to remove formatting from the cells that are currently selected.
So, here is the sequence of the shortcut keys you need to press to quickly clear formatting in Excel: ALT → H → E → F
Read: How to locate and reset the last cell on an Excel worksheet?
3] Clear all formatting using the Format Painter tool
The next method you can use to remove formatting in your Excel workbook is using the Format Painter tool. It is a handy tool in Excel that lets you apply the same formatting to selected cells as some other cells. You can apply the same color, font style, font size, border style, etc., to cells by copying the formatting from other cells. It can also be used in a way to remove all formatting. Let us check out how.
Open the desired Excel workbook and choose a cell without any formatting.
Next, navigate to the Home tab, and from the Clipboard group, select the Format Painter option.
Once you have chosen Format Painter, select all the cells from where you want to remove formatting. You can choose multiple cells by clicking on cells while holding the Shift key.
Finally, press the Enter button and it will remove the formatting from the selected cells.
See: How to reset Excel settings to default?
4] Run a VBA code to remove formatting
You can also use VBA code to remove formatting from Excel. Once you have created the required VBA script, you just need to run it to quickly clear all the formatting from the defined range of cells. Here’s the simple VBA script you can use for that:
First, move to the Developer tab and click on the Visual Basic option.
After that, in the opened window, go to the Insert menu and select the Module option.
Next, enter the following script:
Sub VBA_Script_to_Clear_Formats()
Range("A1:E10").ClearFormats
End Sub
In the above script, A1:E10 is the range of the cells from which you want to remove the formatting.
Once done, save and exit the Microsoft Visual Basic for Applications window.
Now, click on the Developer > Macros option, select the above-created script, and press the Run button. All the formatting will be cleared quickly.
Read: How to highlight Cell or Row with Checkbox in Excel?
5] Remove formatting from a picture
You can also remove all the formatting from a picture that you have added to your Excel workbook. Excel provides you with a Reset Picture option that discards all formatting done to pictures present in a workbook. Here’s how you can use it.
First, open the target workbook containing images and select all the images from where you want to remove formatting.
Now, go to the Picture Format tab and click on the Reset Picture drop-down option. You can find this option under the Adjust group. Next, click on the Reset Picture option. If you want to reset the size of the picture, select the Reset Picture & Size option.
See: How to remove dashes in Excel?
6] Clear Conditional Formatting
If you want to remove conditional formatting in Excel, you can use the following steps:
First, open an Excel workbook and select the part of the sheet from which you want to clear the formatting.
Now, go to the Home tab and click on the Conditional Formatting drop-down menu option.
Next, click on the Clear Rules option and then choose the Clear Rules from Selected Cells or Clear Rules from Entire Sheet option.
As soon as you do that, the conditional formatting from chosen cells or whole sheet will be cleared/
Now read: How to reduce Size of Excel file?
What is the shortcut to remove formatting in Excel?
You can remove formatting in Excel using shortcut keys. Simply press the ALT → H → E → F keys in the given sequence and the formatting will be removed from the selected cells in your workbook.
