How to Clear Excel Formatting
Written by co-founder Kasper Langmann, Microsoft Office Specialist.
Knowing how to clear Excel formatting is a time-saver.
As you work with large worksheets, there are times when you need to remove the current formatting to highlight the data, change them into another, or to correct display issues.
Usually, when the formatting isn’t too many, we simply change it to another manually.
But what if there are hundreds of cells you have to shed with lots of formatting?
In this article, we’ll show you 2 methods you can use to remove formatting in Excel easily. Let’s get started! 👍
Table of Content
How to remove all formatting in Excel
There’s a tool in Excel which lets you remove all formatting immediately.
For this tutorial, let’s use this data set below and remove all formatting in the worksheet.
To use the ‘Clear Formats’ tool, you have to select first which cells you would like to clear.
That means, you can’t only clear one cell at a time. You can clear whole rows, columns, cell ranges, and whole worksheets.
For this example, we’ll be clearing the whole worksheet of all formatting.
To do so, let’s select all cells in the worksheet by pressing ‘Ctrl’ + ‘A’.
The ‘Clear Format’ tool is located in the ‘Home’ tab. So make sure you’re on the right tab.
The tool is on the ‘Editing’ group of the tab. Click the ‘Clear’ icon and choose ‘Clear Formats’ from the dropdown list.
You’ll immediately see the effects on the worksheet after you click the ‘Clear Formats’ tool.
Clear Formatting with ‘Format Painter’
As the tool’s name suggests, it paints the format of the selected cell.
To use this, you have to select a cell first by clicking on it.
Since the purpose we’re using the ‘Format Painter’ is to clear formatting on the cells, make sure to select a cell without any formatting done on it.
After selecting a cell, click the ‘Format Painter’ icon on the ‘Clipboard’ group. Make sure you’re on the ‘Home’ tab.
You’ll know if the tool is active since the cursor will have the tool’s icon on it.
With the tool activated, select the cells you’d like to remove formatting and voila!
Wrapping things up…
Clearing formatting in Excel is especially useful when you’re trying to determine what’s causing a display error.
If you’d like to practice the 2 methods we shared with you, feel free to download and use the exercise file we included in this tutorial.