How to Remove Cell Formatting in Excel (from All, Blank, Specific Cells)

In Excel, you can easily remove cell formatting with a few clicks (or a keyboard shortcut).

You can also copy-paste formatting from one cell/range to the other. Or, you can choose to clear the formatting from cells that contain a specific value or a specific format.

In this short Excel tutorial, I will show you how to quickly remove cell formatting in Excel (and some other cool things you can do).

So let’s get started!

Remove All Cell Formatting from the Entire worksheet

This is something I do when I get the Excel workbook from a database download or a colleague. Sometimes, it’s easier to simply change the formatting and sometimes, you need to remove the cell formatting (from the entire sheet) completely and start over.

Below are the steps to remove the cell formatting from the entire sheet:

1. Open the worksheet from which you want to remove the formatting
2. Click on the top left corner of the worksheet (one with a gray triangle icon). This will select all the cells in the worksheet
3. Click the Home tab
4. In the Editing group, click on the Clear option drop-down
5. In the options that show up, click on ‘Clear Formats’

The above steps would instantly remove all the formatting and you will be left with the data only.

You can also use the below keyboard short to clear formatting from the entire sheet (use this after selecting all the cells)

ALT + H + E + F

The above steps would also remove any conditional formatting rules that you have in the worksheet.

You can also use the same method to remove everything, remove only the comments or content or hyperlinks in Excel.

Remove Formatting From Empty/Blank Cells

The Go-To Special feature in Excel allows you to select all the blank cells (empty cells) at one go.

Once you have these blank cells selected, you can clear the formatting from them (or delete these cells or change the formatting to something else).

Below are the steps to select all blank cells using Go To Special and then clear formatting from them;

1. Select the range of cells within which you want to select all the blank cells and remove the formatting
2. Hit the F5 key. This will open the Go To dialog box
3. In the Go To dialog box, click on the ‘Special’ button. This will open the Go To Special dialog box
4. In the Go To Special dialog box, select the ‘Blank’ option
5.  Click OK. This will select all the blank cells in the selected range.
6. Click the Home tab
7. In the Editing group, click on the Clear option drop-down
8. In the options that show up, click on Clear Formats

The same methodology can be used to select the blank cells and then delete all rows with blank cells or change the value or highlight these blank cells.

Remove a Specific Cell Formatting From a Range/Worksheet

Sometimes, you may get a dataset where there is a specific type of formatting applied or a few cells. For example, it could be those cells that are colored yellow and have a bold font applied to it.

In such a case, how do you only select these cells and remove the formatting from it (while keeping everything else as is)?

While you can select and clear formatting from all the cells, it wouldn’t be a solution if you want to remove specific formatting only and keep the rest.

Below is an example where I have a formatted dataset where some cells are highlighted in yellow with red text.

The intent is to only remove the formatting from these yellow cell.

You can do that with Find and Replace.

Find and Replace allows you to find and select cells that have specific formatting applied to it. Once you have these cells selected, you can easily clear the format from them.

Below are the steps to select all the cells with specific formatting and then clear the formatting:

1. Select the entire dataset (or the entire worksheet)
2. Click the ‘Home’ tab
3. In the Editing group, click on ‘Find and Select’
4. Click on Find. This will open the Find and Replace dialog box.
5. Click on the Options button
6. In the ‘Find What’ field, click on the drop-down icon (a downward pointing triangle) in the ‘Format’ button. This will show you a few more options in a drop-down.
7. Click on ‘Choose format from cell’ option. This will change the cursor to a plus icon and a dropper.
8. Click on the cell that has the formatting that you want to remove. As soon as you do it, you will notice that the formatting is shown as a preview in the Find what field.
9. Click on Find All. This will find all the cells that have the same formatting
10. Hold the Control key and then press the A key. This will select all the cells that have the selected formating.
11. Close the Find and Replace dialog box.
12. Clear the formatting (Home –> Clear –> Clear Formats)

The above steps would remove the formatting from the selected cells.

Remove Current Formatting and Copy from Another Cell

Sometimes, the easiest way to remove the formatting is to simply copy the format from one of the existing cells in your worksheet and paste it on the cells/range.

Suppose you have a dataset as shown below where you want to quickly remove the yellow color from the cell and make it consistent with the other cells.

While one way could be to clear the formatting completely and then do the formatting manually, copy-pasting it from existing cells is way easier.

Below are the steps to copy formatting from a cell to another:

1. Copy the cell from which you want to copy the formatting. In this case, I will choose any gray cell
2. Select the cell from which you want to remove the existing formatting and paste the new one
3. Right-click on the selected cells/range
4. Click on Paste Special
5. In the Paste Special dialog box, click on Formats
6. Click OK

This would instantly apply the copied cell formatting to the selected cell(s)/range.

While in this case, I have shown you an example where there is cell color, more practical use of this technique would be when you want to make the data consistent.

For example, you can have the date data in different formats and you can make it consistent by simply copying the format (which also copies the date/number format) from one cell and paste it over the other.

You can also use the Format Painter option in Excel to quickly copy formatting.

Note: You can also use this technique to copy conditional formatting.

I hope you found this tutorial about ‘How to Remove Cell Formatting in Excel’ useful.

You may also like the following Excel tutorials:

• How to Copy and Paste Formulas in Excel without Changing Cell References
• How to Remove Table Formatting in Excel
• Excel AutoFormat
• How to Multiply in Excel Using Paste Special
• Excel Paste Special Shortcuts
• How to Stop Excel from Changing Numbers to Dates
• How to Indent in Excel (3 Easy Ways + Keyboard Shortcut)
• How to Remove Conditional Formatting in Excel (Shortcut + VBA)