How to Clear Formatting in Excel

Changing each cell format is definitely not an easy thing. Whether you can change the whole sheet format or if you want to apply new formatting, you have to clear the previous one. 

The question is how to clear formatting in Excel while saving time. You can remove cell formatting easily by using different methods. Follow each step carefully and apply new settings as your need. Don’t worry cell formatting will never change your data in the cells. Clearing format will enable you to solve issues appearing with the cell content and formatting.

While working with larger Excel sheets, you most likely need to apply different formatting every time you add new data. Sometimes, you may have to highlight some content to make it relevant. For this, you always have to clear formatting done earlier.  

Nobody likes to waste time in changing borders of cells, font, color as well as alignment. These tasks are arduous and demand lots of time and patience. However, with Excel’s advanced features you can do tiresome functions effortlessly. We have better tricks and tips for you to apply. 

Steps to Clear Formatting in Excel

Here we go:

1. Select the data you want to format. 

2. Click on the Home button and you will see a white eraser on the right-hand side; click on it and choose the Clear Formats option. While using the keyboard, you will need to press shortcut keys Alt + E + F. 

3. Your data is format free. 

Tips for Clear Formatting 

Using tips and tricks, you can remove the format easily and quickly. Excel clear formatting feature lets you remove formatting from one cell to one row or column and even an entire sheet. 

• If you want to clear the formatting from the entire worksheet, select the whole sheet by using shortcut keys from the keyboard (Ctrl + A), or else you may press the Select All button given on the upper left side of the sheet. Now, choose Clear Formats. 
• If you want to clear formatting from one row or a column, select the entire row or column by clicking on the heading of that row or column. 
• In non-adjacent cells or ranges, you need to choose the first cell or range while clearing formats. Press and hold the CTRL along the selection of other cells or ranges.  

Clear All Cell Formatting 

Below is the step-by-step guide to clear formatting from the entire worksheet.

1. Go to the worksheet from which you want to clear the formats. 
2. On the upper left side of the sheet, you will be able to select the entire worksheet.

3. Open the Home tab. 

4. Click on the Clear option drop-down menu given in the Editing group. 

5. Click on the Clear Formats from the options given there. 

Practicing these steps would make your entire worksheet clear from the current formatting. You will only see data on the sheet. Now, you can apply formatting as per your choice.

Besides these steps, you can even try the below-mentioned shortcut key from the keyboard. This shortcut key is used for the formatting of the entire worksheet. You just need to select all the cells before pressing these keys.  

ALT + H + E + F

How You Can Make the Clear Formats Option Accessible in a Click 

Adding the Clear Formats option to the Excel ribbon or the Quick Access toolbar, you can simply remove the formatting with just one click. It is helpful particularly when you have numerous files to format.

Remove Formatting Using Format Painter 

Have you ever tried formatting the Excel worksheet with the help of Format Painter? 

I think every user knows this trick because Format Painter is a tool in Excel, which is familiar to everyone. However, more than half of the users must not know that it can be used to clear formatting. 

Let’s have a look at how this tool can help in Clearing Format: 

1. Firstly, select a cell that is unformatted and near the cell of which you want to clear formatting. 
2. Now, Click on the Format Painter tab given in the Home tab menu.
3. Select the cell from which you need the formatting done.

 All done! 

Clear a Specific Cell Formatting from a Range  

When you have a data set that consists of multiple formatting on cells, you need some different tricks. For instance, your different formatted cells could have different colors or fonts, etc. 

In that case, you must be wondering how someone could clear formatting from these specific cells only while keeping the entire data and formatting as it is. Find and Replace is a way better feature that will help you execute this function. 

Using Find and Replace lets you find the specific cells that you want to format. Once these cells are found, you can replace them with newer formatting. After finding the specific cells, first, you have to clear their current format before applying the newer format. 

Here are the steps to follow for the selection of the cells: 

1. Select the whole worksheet. 
2. Go to the Home tab. 

3. Click on the ‘Find and Replace’ option given in the Editing group menu 

4. Click on the Find option and a dialog box of Find and Replace will appear. 

5. Click on the Options tab. 

6. Click on the drop-down icon given in the ‘Find What’ field. In the ‘Format’ button, you will see all these options. 

7. Click on the ‘Choose format from cell’ option. Doing this will change the cursor into a dropper and a plus icon. 

8. Click on the cell that needs formatting and you want to clear. Once you do it, you will see the formatting appears in the ‘Find What’ area as a preview. 

9. Click on Find All option and all the cells with the same formatting will be selected. 

10. Press Ctrl + A to select all the cells with the selected formatting.
11. Close the Find and Replace dialog box. 
12. Remove the formatting (Home > Clear > Clear Formats)

To Sum Up 

Here is the end of formatting tricks. Now, you have enough information about how to clear formatting in Excel. Keep trying it!