Cell formatting is something all Excel users need to do from time to time. When you use Excel, you can easily do it with the help of just a few clicks or a simple keyboard shortcut. You also have the option to copy the formatting from one cell/range to another. You can also decide to clear the formatting from cells that contain a specific value or a specific format if you want.

In this post, we will cover all you need to know about clearing formatting in excel. Let’s get started.

Clear Formatting in Excel: Steps to Take

How to Remove All Cell Formatting from the Entire worksheet?

Whenever you acquire your worksheet via a database download or from someone else, you will most likely need to change the cell formatting. On quite a few occasions, it’s just easier to change the formatting while sometimes you might need to remove the formatting from the entire sheet and start from scratch.

Here’s what you need to do for removing 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
  1. Click the Home tab
  1. In the Editing group, click on the Clear option drop-down

In the options that show up, click on ‘Clear Formats’

Once you do this, your worksheet’s formatting will be removed and all you will have left is the data. You can also select all the cells and use the “Alt + H+ E+ F” to clear formatting from all the cells. This process removes all kinds of formatting such as color, borders as well as number formatting. Conditional formatting will also be removed as a result of this process.

How to Remove Formatting From Empty/Blank Cells?

In case you want to clear the formatting from empty/blank cells, here’s what you need to do:

  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
  1. In the Go To Special dialog box, select the ‘Blank’ option
  1.  Click OK. This will select all the blank cells in the selected range.
  2. Click the Home tab
  1. In the Editing group, click on the Clear option drop-down
  1. In the options that show up, click on Clear Formats

You can utilize the same method for selecting the blank cells and deleting all the rows with blank cells or changing the value or highlighting these blank cells.

How to Remove a Specific Cell Formatting From a Range/Worksheet

In case you want to clear the formatting from a certain part of your worksheet, these are the steps you need to take:

  1. Select the entire dataset (or the entire worksheet)
  2. Click the ‘Home’ tab
  1. In the Editing group, click on ‘Find and Select’
  1. Click on Find. This will open the Find and Replace dialog box.
  1. Click on the Options button
  1. 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.
  1. Click on ‘Choose format from cell’ option. This will change the cursor to a plus icon and a dropper.
  1. 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.
  1. Click on Find All. This will find all the cells that have the same formatting
  1. Hold the Control key and then press the A key. This will select all the cells that have the selected formating.
  2. Close the Find and Replace dialog box.
  3. Clear the formatting (Home –> Clear –> Clear Formats)

Once you are done, formatting will be cleared from the cells you selected.

How to Remove Current Formatting and Copy from Another Cell?

Copying formatting from another cell is a great way to get rid of the formatting in a particular cell. Here’s how you can do it with ease:

  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
  1. In the Paste Special dialog box, click on Formats
  1. Click OK

Once you are done, the formatting from the copied cell will be applied to the selected cell.

So those were the different ways in which you can clear formatting in Excel. Hopefully, the information provided in this post will be enough for you to understand the process and make your experience with Excel more smooth.