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! 👍

Kasper Langmann, Co-founder of Spreadsheeto

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file
Download free exercise file

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.

Kasper Langmann, Co-founder of Spreadsheeto

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’.

selecting all cells for clear formatting

The ‘Clear Format’ tool is located in the ‘Home’ tab. So make sure you’re on the right tab.

home tab on tab list

The tool is on the ‘Editing’ group of the tab. Click the ‘Clear’ icon and choose ‘Clear Formats’ from the dropdown list.

clear formats icon on

You’ll immediately see the effects on the worksheet after you click the ‘Clear Formats’ tool.

using 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.

format painter icon on the clipboard group

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!

clearing formatting using the format painter tool

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.

Kasper Langmann, Co-founder of Spreadsheeto