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: 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) 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; 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: 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: 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)
