How to Clear All Formatting on Excel?

Formatting in excel plays a crucial role to highlight your data, make it unique and add a visual appeal. But, sometimes, you could go overboard with this feature and kill the whole vibe of your spreadsheet. In such case, you may need to strip away all formatting and focus just on the values.

You can revert back by undoing changes to formats like underline, font color, font size, cell-width, border, fill and so on. This might not always be feasible specially when dealing with big data and it could end up taking more of your time.

Clear All Format

One of the quickest ways to clear all formats of your data is by using the keyboard shortcut. Select the data and press Alt + H + E + F keys together. Within a second, it will remove all text formatting, table formatting, cell styles, comments, hyperlinks, conditional formatting, etc from your worksheet.

There are also other ways to clear out the format of your document. You can choose from one of the methods mentioned below.

Using Clear Format button

If you want to have more options to remove formats, you can find a default Clear format button in the Home Tab. You can choose to clear the entire or specific format.

1. Launch Excel and go to your worksheet.
2. Select the Cell ranges or Areas you wish to clear format.
   
3. Navigate to Home tab.
   
4. On the Editing Menu, click on Clear. Then, choose one of the options to Clear the format.
   

• Clear All: Deletes everything from the selected area. It includes contents, comments, formats, etc.
• Clear Formats: Removes only formats from the selected range while keeping contents.
• Clear Contents: Clears only contents within the selection. You will still have formats or comments. 
• Clear Comments: Deletes comments from the selected range or data. 
• Clear Hyperlinks: Removes attached hyperlinks in data. 

Note: If you are looking to clear format to solve a formatting error, check out our article on "too many different cell formats".

Using Paste Special

Most of the time when you import data, the information gets pasted in the original format. This happens when you use Ctrl + V to paste any data. 

To avoid pasting the format, we will refer to the Paste Special menu. In this approach, we will choose to paste as Values such that you get only texts and numbers in your worksheet. 

1. Firstly, select the Data on your worksheet and enter Ctrl + C to copy it.
   
2. Right-click on a cell. Then, click on Paste Special menu from the Paste options. 
   
3. Under Paste, choose Values. Click OK to confirm.
   
4. It will remove the original formatting.
   

Using Format Painter

Format Painter tool of Excel does a pretty good job to apply the formatting of the specific cell to other ranges quickly. So, we will use this tool to copy the format of an empty cell and paste it into a formatted cell. This way, you won’t have any formats in your selected area.

1. On your worksheet, click on any Empty cell.
2. Go to Home tab.
   
3. On the Clipboard menu, click on Format Painter or Paintbrush icon. 
   
4. Using the Paintbrush tool, highlight the Formatted cell. 
   
5. Now, your data will not have any format.
   

Clear Specific Format 

While you can clear specific formats from the Clear format button, you may not find many options for it. For Instance, there is no option to clear the table format. During such cases, you can go through the default menu of each format to remove them. 

From Styles Menu

Use this method if you have to clear the Cell styles format from your worksheet.

1. Select Cell ranges with Cell Styles format.
2. Go to Home Tab.
   
3. On the Styles menu, click on More Icon.
   
4. Now, Right-click on the applied format and choose Delete.
   

From General Format

Setting the number to the general format will remove all formatting from the numerical data set.  

1. Select all Number data ranges.
2. On the number tab, set the format to General. 
   

From Table Design 

Go to the Table Design menu to delete the Table format from your spreadsheet.

1. Highlight the Table data.
2. Go to Table Design Tab and expand More icon. 
   
3. Pick the First option under Light.
   

From AutoCorrect Options

AutoCorrect Option will allow you to delete a Hyperlink from the text.

1. Hover your mouse cursor over the Bottom-left of the hyperlink text.
   
2. Select the AutoCorrect Options > Undo Hyperlink. 
   
3. Choose Stop Automatically Creating Hyperlinks to avoid this format in future. 
   

Clear Conditional Formatting

If you want to delete only the rules for conditional formatting while keeping other formats in place, you can go through these steps.

1. On your worksheet, select the Ranges with conditional formatting applied.
2. From Home Tab, click on Conditional Formatting > Clear Rules.
   
3. Now, pick one of the options to Clear format.
   

• Clear Rules from Selected Cells
• Clear Rules from Entire Sheet
• Clear Rules from This Table