How to clear formatting in Excel - remove all or only specific formats

This short tutorial shows a couple of quick ways to clear formatting in Excel, and explains how to remove only specific formats in selected cells.

When working with large Excel worksheets, it's a common practice to apply different formatting options to make data relevant to a particular situation stand out. In other situations, however, you may want to highlight other data, and for this, you will need to remove the current format first.

Manually changing cell color, font, borders, alignment and other formats would be tiresome and time-consuming. Luckily, Microsoft Excel provides a couple of quick and simple ways to clear formatting in a worksheet. Moreover, there is a way to remove only specific formatting, and I you will learn all these techniques in a moment.

How to clear all formatting in Excel

The most obvious way to make a piece of information more noticeable is to change the way it looks. Excessive or improper formatting, however, may have an opposite effect, which makes your Excel worksheet difficult to read. The easiest way to fix this is to remove all current formatting and start embellishing the worksheet from scratch.

To remove all formatting in Excel, just do the following:

  1. Select the cell or range of cells from which you want to clear formatting.
  2. On the Home tab, in the Editing group, click the arrow next to the Clear button button.
  3. Select the Clear Formats option.
    Click Clear Formats to remove all formatting from the selected cells.

This will erase all cell formatting (including conditional formatting, number formats, fonts, colors, borders, etc.) but keep the cell contents.

Clear Format tips

With this Excel Clear Formatting feature, you can easily remove formats not only from a single cell, but also from an entire row, column or worksheet.

  • To clear out formatting from all cells on a worksheet, select the entire sheet by pressing Ctrl+A or by clicking the Select All button The Select All button at the top-left corner of the worksheet, and then click Clear Formats.
  • To remove formatting from a whole column or row, click the column or row heading to select it.
  • To clear formats in non-adjacent cells or ranges, select the first cell or range, press and hold the CTRL key while selecting other cells or ranges.

How to make the Clear Formats option accessible in a click

If you want to have a one-click tool to remove formatting in Excel, you can add the Clear Formats option to the Quick Access toolbar or Excel ribbon. This might be especially useful if you receive many Excel files from your colleagues or clients and their formatting prevents you from making the data look the way you want.

Add the Clear Formats option to Quick Access toolbar

If Clear Formats is one of the most used features in your Excel, you can have it added to the Quick Access toolbar in the top-left corner of your Excel window:
The Clear Formats option added to the Quick Access toolbar

To do this, perform the following steps:

  1. In your Excel worksheet, click File > Options, and then select Quick Access Toolbar on the left-side pane.
  2. Under Choose commands from, select All Commands.
  3. In the list of commands, scroll down to Clear Formats, select it and click the Add button to move it to the right-hand section.
  4. Click OK.
    Adding the Clear Formats option to the Quick Access toolbar

Add the Clear Formats button to the ribbon

If you'd rather not clutter your Quick Access toolbar with too many buttons, you can create a custom group on the Excel ribbon and place the Clear Formats button there.

To add the Clear Formats button to the Excel ribbon, follow these steps:

  1. Right-click anywhere on the ribbon, and select Customize the Ribbon…
  2. Because new commands can only be added to custom groups, click the New Group button:
    Creating a new ribbon group
  3. With the New Group selected, click the Rename button, type the name you want, and click OK.
  4. Under Choose commands from, select All Commands.
  5. In the list of commands, scroll down to Clear Formats, and select it.
  6. Select the newly created group and click Add.
    Adding the Clear Formats button to the custom ribbon group
  7. Finally, click OK to close the Excel Options dialog and apply the changes you've just made.

And now, with the new button in place, you can remove formatting in Excel in a single click!
The Clear Formats button appears on the ribbon and lets you remove formatting with a single click.

How to remove formatting in Excel using Format Painter

I guess everyone knows how to use Format Painter to copy formatting in Excel. But have you ever thought that it can also be used to clear format? All it takes is these 3 quick steps:

  1. Select any unformatted cell close to the cell from which you want to remove formatting.
  2. Click on the Format Painter button on the Home tab, in the Clipboard group.
  3. Select the cell(s) from which you want the formatting cleared.

That's all there is to it!
Remove formatting in Excel using Format Painter

Note. Neither Clear Formats nor Format Painter can clear formatting applied to only some part of the cell contents. For example, if you highlighted just one word in a cell with some color, like shown in the screenshot below, such formatting won't be removed:
Neither Clear Formats nor Format Painter can remove formatting that is applied to only some part of the cell contents.

How to clear only specific formats

Sometimes, when creating a sophisticated worksheet design, you might apply various formatting to certain cells, and at a later point you may want to remove some but not all of the cell formats.

For example, in the following table, you may want to remove borders, font and fill colors, but keep the number formats such as currency and percentage:
The source table from which all formatting except for number formats should be removed.

With Excel's clear format tools, you have the following choices:

  • Remove all formatting, and then apply the required number formats anew, or
  • Remove excessive formats manually one by one.

Either way is cumbersome and frustrating… unless you have the Cell Cleaner add-in installed in your Excel.

With Cell Cleaner, you can choose which formats to remove and which ones to keep:

  1. Select a cell or range of cells from which you want the formatting removed, and click the Clear Formatting button on the Ablebits Data tab:
    Select the cells from which you want to remove formatting, and click the Clear Formatting button.
  2. On the Clear Formatting pane, uncheck the boxes for the formats you want to retain. By default, all of the checkboxes are selected including number format, alignment, font, background color, text color, conditional formatting, and borders.

    In this example, we want to keep the currency and percentage formats, so uncheck the Number format box:
    Select the formatting options you want to remove, and uncheck the formats you want to keep.

  3. Click the Clean button, and you will immediately get the desired result - all formatting except for the Number format is removed:
    All formatting except for the Number format is removed

I hope you liked this quick and straightforward way to remove formatting in Excel. If you want to give it a try, you are welcome to download the 15-day evaluation version.

And if the Cell Cleaner lives up to your expectations, you can buy it individually or as part of our Ultimate Suite for Excel.  Either way, be sure to benefit from the 15% off promotion code that we provide exclusively to our blog readers: AB14-BlogSpo

I thank you for reading and hope to see you on our blog next week!

You may also be interested in:

4 Responses to "How to clear formatting in Excel - remove all or only specific formats"

  1. Rohan Singh says:

    wow

  2. IvoryDuck says:

    Svetlana, I just recently came across your blog postings. I frequently come across situations where I know Excel can do something I need, but am just not sure which formula to use or how to make it work. I use google to find the answer from various sites, but the information can be difficult to follow, buried in a conversation, incomplete, etc. Your blog postings are excellent, well put together, simple language, thorough, easy to follow. This is fantastic. You should put these into a book.

  3. John Johnson says:

    Having an issue with Clean Cell Add In. Running Excel 2016 and Ultimate Suite with this plug-in. The add-ins are enabled, but this feature does not appear in the ribbon. Sent an e-mail with all the specifics of OS Ver etc. No reply yet and need this function for something I have to complete today. Any help is greatly appreciated.

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
33
Ultimate Suite 2018.5 for Excel
33
60+ tools for Excel
December offer: Dec. 8 – Dec. 17