Shortcut key for clear formatting in Excel

Adding colors and number formatting can improve the readability of your Excel reports, but sometimes you will want to remove all the formatting and start over. This is why it is essential to learn how to clear formatting in Excel as quickly as possible (we also want to be efficient, right?).

How to clear all formatting in Excel

The most common way to remove all formatting in Excel is via the Clear Formatting option. To achieve this, simply follow these steps:

1. Select a cell or range of cells from which you want to clear formatting.
2. Navigate to the Home tab and locate the Editing group.
3. Click on the Clear button to open the Clear menu, and select Clear Formats.

How to clear formatting using Format Painter

An alternative option is to remove formatting using Format Painter:

1. Select a blank cell that contains no formatting.
2. Navigate to the Home tab.
3. From the Clipboard group, select Format Painter. You will see a dotted line around the cell once the Format Painter option is activated.
4. Select all the cells for which you want to clear formatting. This will revert them to the default Excel format.

Keyboard shortcut to Clear Formatting

Clearing formatting using a keyboard shortcut is very fast. All you need to do is:

1. Select the cells from which you want to remove the formatting.
2. Press ATL + H + E + F.

Quick Tip: You need to press the keys in successive order, not all at once.

Remove all Cell Formatting from the entire worksheet

Removing cell formatting from the entire worksheet is just as easy.

1. Press CTRL + A to select the entire cells from your worksheet. Alternatively, you can click on the green triangle from the upper left part of your worksheet (above row 1 and to the left of column A).
2. Navigate to the Home tab > Editing group.
3. Open the Clear menu and select Clear Formats.

Quick Tip: If you have manually applied formatting only to some part of the cell contents, then you will have to change the color of the cell back to black manually. Using either Clear Formats or Format Painter will not remove such formatting. In the screenshot below, you can see that the word 'increased' has been formatted with green.

How to clear Conditional Formatting in Excel

Conditional Formatting is a very powerful tool and can be used to achieve so many things. In fact, I've written a comprehensive article on How to find duplicates in Excel using Conditional Formatting.

However, if you apply too much Conditional Formatting, this will significantly slow down your Excel file. If this is the case, then it's really important to know how to clear Conditional Formatting in Excel using just a few easy steps:

1. Select the cells from which you want to remove the Conditional Formatting.
2. Navigate to the Home tab and locate the Styles group.
3. Click on the Conditional Formatting button to open the menu.
4. Click on the Clear Rules button and select the Clear Rules from Selected Sheet.

Alternatively, you can use the keyboard shortcut ALT + H + L + C + S to achieve the same result.

Quick Tip: You can also remove the Conditional Formatting from the entire worksheet by selecting Clear Rules from Entire Sheet.

If you still don't understand how to clear formatting in Excel after reading this article, please leave a comment, and I'll help you out. I reply to every single comment.

Do you need to clear all the formatting from your data? This post will show you how to remove all formats from a selected range in Excel.

Formatting is key to making your Excel workbooks look good!

However, too much formatting can become a distraction. When this occurs, you may need to reset all of your format options on the sheet and begin again.

Any format that has been applied such as bold font, increased font size, borders, strikethrough, number formats, date formats, and cell fill color will need to be removed before you start over!

How can you reset all the format options to their original defaults?

Clear Formatting

Select the range to clear formatting from then press Alt, H, E, and F on your keyboard.

This post will show you all the ways you can use to restore the original format settings to any range quickly. Follow along to find out how!

Clear Format from the Home Tab

The command to clear all formats can be found in the Home tab of the Excel ribbon. This is the most straightforward method to clear the format from any range in your worksheet.

Follow these steps to clear all formats from the Home tab.

1. Select the range from which you want to clear formats.
2. Go to the Home tab.

1. Click on the Clear command in the Editing section.
2. Select Clear Formats from the menu options.

All the formatting will be gone!

📝 Note: This option won’t delete any values or formulas in the cells. It will only remove any additional format that has been applied and restore the default cell format.

Clear Format with a Keyboard Shortcut

It is possible to access the Clear Format command with your keyboard.

You can use the Alt hotkeys to execute this command. These allow you to navigate the ribbon commands with your keyboard.

Press the sequence Alt, H, E, and F on your keyboard to clear the format in the selected range of your Excel sheet.

Clear Format with Paste Special

Any unused cells in your workbook will have the default format applied and you can copy and paste this as a way to clear formatting in other cells.

Follow these steps to use Paste Special to clear format.

1. Select any unused cell.
2. Press Ctrl + C to copy the cell.

1. Select the area from which you want to clear the format.
2. Press Ctrl + Alt + V to open the Paste Special menu.

This will open up the Paste Special menu.

1. Select the Formats option.
2. Press the OK button.

This will paste only the format from the unused cell with the default format. It acts the same as clearing out the format.

This is actually the method I first used before I discovered there is a dedicated command for clearing the formats.

You can also use a very similar tactic with Paste Special. You can copy the contents which you want to clear format then paste them into an area of the unused cells. This time use the Formula option to only paste any formulas and values with no formatting.

Clear Format with Format Painter

This is essentially the same method as using the paste special format method to clear any formatting.

Format Painter is a feature found in the Home tab that allows you to copy format from a range and then apply it to another location with a single click.

You can use this to copy the format from any unused cell with the default format and apply it to the area you want to clear.

Follow these steps to use Format Painter to clear the format.

1. Select any unused cell.
2. Go to the Home tab.
3. Click on the Format Painter command in the Clipboard section.
4. Click and drag to select the range from which you want to clear the format.

This will paint the default format from the unused cell to your selected range!

Unfortunately, the format painter is a single use action. You will need to repeat all the steps again if you want to paint multiple ranges.

💡 Tip: Double-click on the Format Painter command to use it an unlimited number of times! This will allow you to paint multiple ranges.

When you enable the unlimited format painter, it will persist until you disable it by pressing the Esc key.

Clear Format with VBA

Clearing the format from a select range can also be done with VBA.

Perhaps you have a range in your spreadsheet that you will be marking with format and want an easy way to clear it out every now and then.

You could create a button to run the code and this way it’s an easily accessible option to clear the format.

Go to the Developer tab and select the Visual Basic option to open the visual basic editor. Go to the Insert tab in the editor and select the Module option. This is where you will be able to write your VBA code.

💡 Tip: You can also open the visual basic editor using the Alt + F11 keyboard shortcut.

Copy and paste the above code into the new module. You can replace the range B2:D6 with whatever range you want to clear formatting from.

When you run this code it will remove all formats from the range B2:D6.

Clear Format with Office Scripts

If you are an Excel online user, then VBA won’t be an option!

But you can use Office Scripts instead. This is a JavaScript based language available in Excel online with Microsoft 365 business plans.

Go to the Automate tab in Excel online and press the New Script button to open up the Office Scripts Code Editor.

Copy and paste the above code into the Code Editor and press the Save script button.

Press the Run button and this script will clear out any format in the range B2:D6.

Clear Format from a Table

Tables are a great feature in Excel to contain your data.

When you create a table, Excel will automatically apply formatting to the table such as column headings fill colors, alternating row colors, and borders.

You might want to keep the awesome table features but ditch the format that’s automatically applied.

This is possible to change the format to a more subtle style while keeping the table.

Follow these steps to clear the format from a Table.

1. Select any cell in the table.
2. Go to the Table Design tab.
3. Click on the Arrow icon in the Table Styles section to reveal more options.

This will clear the format style from your table.

Clear Format from a Pivot Table

Similarly, when you create a pivot table it will automatically apply the default pivot table format.

This can also be changed to a more subtle style.

Follow these steps to clear the format from a Pivot Table.

1. Select any cell in the Pivot Table.
2. Go to the Design tab.
3. Click on the Arrow icon in the PivotTable Styles section.

1. Select the Clear option at the bottom of the menu.

This clears out the current format and applies the minimal style format to your pivot table.

Conclusions

The format is a key part of any spreadsheet. But with so many formatting options available, you might need to clear out the current formats and start over.

There is an easy way to remove the format from any selected range. The Clear Format option can be found in the Home tab. This should be your go-to method!

But there are other methods worth knowing such as using the Paste Special menu or the Format Painter.

When you constantly need to clear out the format in a select area of the spreadsheet, an automated option such as VBA or Office scripts would be preferred. Both can be run from a button which makes the solution ideal for improved user experience.

An automatic format is used when you create tables or pivot tables in Excel. These can also be removed by applying a more subtle format to them.

Do you have any spreadsheets that have too much format and you need to remove it all and start over? Do you have any other tips for getting rid of unwanted formats? Let me know in the comments below!