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

With a few clicks in Excel, you can quickly erase cell formatting (or a keyboard shortcut).

Additionally, formatting may be copied and pasted from one cell or range to another. Alternatively, you can decide to remove all formatting from cells that have a certain value or format.

I’ll demonstrate how to quickly remove cell formatting in Excel in this brief tutorial (and some other cool things you can do).

then let’s get going!

This Guide Addresses the:

1. Remove the whole worksheet's cell formatting. 2. Remove Formatting from Blank/Empty Cells 3. Eliminate a Particular Cell Formatting from a Range or Worksheet 4. Copy from Another Cell and Remove the Current Formatting

1. Remove the whole worksheet’s cell formatting.

When I receive an Excel worksheet from a coworker or a database download, I always do this. Sometimes it’s simpler to make a simple formatting modification, and other times you need to totally delete the formatting from every cell in the sheet and start again.

The steps to eliminate cell formatting throughout the full sheet are listed below:

To remove the formatting from a worksheet, open it.
On the worksheet, click on the upper left corner (one with a grey triangle icon).

The worksheet’s cells will all be selected as a result.

On the Home tab, click

Click the Clear option drop-down in the Editing group.

Select “Clear Formats” from the menu that appears.

The aforementioned methods would instantaneously eliminate any formatting, leaving you merely with the data.

Additionally, you may remove all formatting from the whole sheet by using the keyboard shortcut below (use this after selecting all the cells)

ALT + H + E + F

Keep in mind:

This would eliminate all formatting, including color, borders, and number formatting. For instance, if you have dates presented in a certain format (such as 01-01-2020 or January 1, 2020), doing this would remove that format and show you the actual number, which is 43831.

Any conditional formatting rules that you have in the worksheet would likewise be eliminated by the aforementioned processes.

The same technique may be used to delete everything or only the comments, text, or hyperlinks in Excel.

2. Remove Formatting from Blank/Empty Cells

You may pick all of the empty cells at once in Excel by using the Go-To Special function.

You may remove the formatting from these blank cells after you have selected them (or delete these cells or change the formatting to something else).

The procedures to pick every blank cell using Go To Special and then remove all formatting from them are listed below;

Choose the range of cells you wish to choose, all the blank cells, and then eliminate the formatting.

Toggle the F5 key. The Go-To dialogue box will be shown.

Click the “Special” button in the Go To dialogue box. The Go-To Special dialogue box will then be shown.

Choose “Blank” from the Go To Special dialogue box’s options.

Select OK. This will choose every empty cell in the range that has been chosen.

On the Home tab, click

Click the Clear option drop-down in the Editing group.

Select Clear Formats from the list of choices that appears.

The same procedure may be applied to select the empty cells, remove all rows with empty cells, modify the value of these empty cells, or highlight these empty cells.

3. Eliminate a Particular Cell Formatting from a Range or Worksheet

You could occasionally receive a dataset with a few cells that have a particular formatting style applied. For instance, it may be the cells with a strong typeface and a bright background.

How can you in this situation only choose these cells and maintain the rest of the formatting in place?

While you may choose to choose and delete formatting from every cell, it wouldn’t be a solution if you wanted to maintain certain formatting while removing others.

Here’s an illustration of a prepared dataset with certain cells highlighted in yellow and red text.

Only the formatting in these yellow cells is to be removed.

With Find and Replace, you can perform that.

You may locate and choose cells that have certain formatting applied to them using Find and Replace. Once you’ve selected these cells, you can quickly remove the format from them.

The steps to select every cell with a particular formatting and then remove it are listed below:

Choosing the full collection (or the entire worksheet)

1. Choose the “Home” tab.
2. Go to the Editing group and select “Find and Select.”
3. Select Find. The Find and Replace dialogue box will then be shown.
4. Choose Options by clicking it.
5. Click the drop-down icon (a downward-pointing triangle) in the “Format” button in the “Find What” box. You will get a drop-down with a few extra possibilities as a result.
6. Select “Choose a format from cell” from the menu. The cursor will then change to a plus sign and a dropper.
7. 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.
8. Select Find All. This will identify every cell with the same formatting.
9. Press the A key while keeping the Control key depressed. This will pick every cell with the chosen formatting.
10. The Find and Replace dialogue box should be closed.
11. (Home -> Clear -> Clear Formats) Remove formatting

The formatting in the chosen cells would be removed using the aforementioned processes.

4. Copy from Another Cell and Remove the Current Formatting

Sometimes, copying the formatting from an existing cell in your worksheet and pasting it into the cells or range is the simplest approach to remove it.

Assume you have a dataset like the one below and want to rapidly make one cell’s color like the others by removing its yellow tint.

While one method might be to completely clear the formatting and then perform the formatting manually, copying and pasting it from already formatted cells is much simpler.

The steps to copy formatting from one cell to another are as follows:

1. Copy the cell from which the formatting should be copied. Any grey cell will do in this situation.
2. Choose the cell whose formatting you wish to erase and then paste the new one.
3. pick the cells or range with a right-click.
4. Select Paste Special.
5. Click Formats in the Paste Special dialogue box.
6. OK

By doing this, the selected cell(s) or range would immediately receive the copied cell formatting.

Although I’ve provided you with an example where the cell color is present, a more realistic application of this method would be when you want the data to be consistent.

By simply copying the format (which includes replicating the date/number format) from one cell and pasting it over the other, you may make inconsistent date data, for instance, consistent.

Additionally, Excel’s Format Painter feature allows you to easily copy formatting.

Note: Conditional formatting can also be copied using this method.

 

I sincerely hope you were able to utilize my lesson on “How to Remove Cell Formatting in Excel.”