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?

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.

3. Click on the Clear command in the Editing section.
4. 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.

Sub ClearFormat()
    Range("B2:D6").ClearFormats
End Sub

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.

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	selectedSheet.getRange("B2:D6").clear(ExcelScript.ClearApplyTo.formats);
}

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.

4. Select the Clear option.

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.

4. 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!