Clear formatting in Excel to reset your cells to their default style, ensuring a clean slate for your data and enhancing the consistency and professionalism of your spreadsheets. Whether you’re dealing with imported data that carries over unwanted formats, preparing a document for a new project, or simply aiming to standardize the appearance of your spreadsheet, understanding how to clear formatting is crucial. By mastering this function, you’ll maintain a polished, uniform look across your Microsoft Excel projects, making your spreadsheets not only more visually appealing but also easier to navigate and analyze.
This Tutorial Covers:
- Remove All Cell Formatting from the Entire worksheet
- Clear Formatting in Excel from the Entire worksheet with a Keyboard Shortcut
- Remove Formatting from Empty/Blank cells
- Remove a Specific Cell Formatting from a Range/Worksheet
- Clear Formatting with Format Painter
- Clear Format from a Table
- Clear Format from a Pivot Table
- Conclusions
1. Remove All Cell Formatting from the Entire worksheet
Sometimes it’s simpler to make a simple formatting modification, and other times you need to totally remove the formatting from every cell in the sheet and start again.
The procedures for clearing the entire sheet of cell formatting are listed below:
Step 1: Open the worksheet whose formatting you want to delete.
On the worksheet, click in the upper left corner (one with a gray triangle icon). This will pick each and every worksheet cell and show a different color, like gray.
Step 2: Go to the Home tab and click “Clear” option from the drop-down menu under the “Editing” group in the ribbon.
Step 3: Select “Clear Formats” button from the menu that appears.
By following the procedures above, all formatting will be instantly eliminated, leaving you with the data.
2. Clear Formatting in Excel from the Entire worksheet with a Keyboard Shortcut
Additionally, you can clear formatting using shortcut key, from the entire sheet. (use this after selecting all the cells)
Alt + H + E + F (press the ALT key followed by H and E and F) shortcut to clear formatting.
Note:
- By doing this, all formatting, including color, borders, and number formatting, would be removed. For instance, if a date is displayed using a certain format (such as 25-12-2022 or 25 December 2022), doing this will remove that format and reveal the actual number, which is 44920.
- The same technique can be used to delete everything or only the comments, content, or hyperlinks in Excel.
Clear all conditional formatting through a shortcut
Any conditional formatting rules that you have in the worksheet would likewise be eliminated by pressing ALT+H+E+F (press the ALT key followed by H and E and F) – use this by selecting all the cells
3. Remove Formatting from Empty/Blank cell in Excel
You can pick all of the empty cells at once in Excel by using the “Go To Special” feature.
You can clear formatting from these blank cells once you have selected them (or delete these cells or change the formatting to something else).
The procedures to select every blank cell using “Go To Special” and then remove all formatting from them are described below:
Step 1: Select cells from the range you want to choose including all the blank cells.
Step 2: Press the Function (Fn) + F5 key. It will launch the Go To dialog box and select “Special” from the list of options in the Go To dialog box.
Step 3: The “Go To Special” dialog box will appear as a result. Choose “Blank” from the “Go To Special” dialog box’s options and click OK.
Step 4: After clicking OK, this will select all the blank cells in the selected range.
Step 5: Go to the Home tab and click “Clear” option from the drop-down menu under the “Editing” group.
Step 6: Select “Clear Formats” from the menu that appears.
By following the procedures above, all formatting will be instantly eliminated from blank cells.
The same procedure can be applied to select the empty cells, delete all rows with empty cells, change the value of these empty cells, or highlight these empty cells.
4. Remove a Specific Cell Formatting from a Range/Worksheet
You might occasionally receive a dataset with a few cells that have a particular formatting style applied. For instance, it can be the cells that have been given a red color and a bold typeface from which you want to remove the formatting.
How can you in this situation only choose these cells and maintain the rest of the formatting in place?
While you could choose to clear all formatting from every cell, that wouldn’t be a solution if you wanted to maintain certain formatting while removing others.
An example of a prepared dataset with some cells highlighted in red and with bold text is shown below.
The intent is to only remove the formatting from these red cells.
With Find and Replace, you can accomplish that.
You can locate and choose cells that have certain formatting applied to them using Find and Replace. Once you’ve selected these cells, you may quickly remove the format from them.
The steps to pick every cell with a particular formatting and subsequently remove it are listed below:
Step 1: Choose the whole collection of data (or the entire worksheet)
Step 2: After selecting “Find and Select” from the “Editing” group on the “Home” page, click “Find”.
Step 3: The “Find and Replace” dialog box will then appear. Select “Options>>” from the menu.
Step 4: select the “Format” button’s drop-down icon (a downward-pointing triangle). You will get a drop-down with a few extra possibilities as a result and then select “Choose format from cell” from the menu. The cursor will then change to a plus sign and a dropper.
Step 5: To remove the formatting from a cell, simply click on it.
Step 6: The formatting is displayed as a preview in the “Find what” box as soon as you complete it. Select “Find All.” This will identify every cell with the same formatting.
Step 7: Press the A key while keeping the Control key depressed (Ctrl + A). This will select every cell with the chosen formatting. To close the “Find and Replace” dialog window, click “Close.”
Step 8: Clear the formatting by going to Home > Clear > Clear Formats. The result looks like this.
5. Clear Formatting with Format Painter
The Home tab’s Format Painter tool lets you copy format from a range and then apply it to another spot with only one click.
The format can be copied from any empty cell with the default format and applied to the region you want to clear using this method.
An example of a prepared dataset with some cells formatted is shown below.
Use Format Painter to clean the format by following these procedures:
Step 1: Choose any blank cell.
Step 2: Select the “Home” tab and in the “Clipboard” area, click the “Format Painter” command.
Step 3: The range from which you want to clear the format can be chosen by clicking and dragging.
As a result, the blank cell will be painted in your chosen range using the default format!
The format painter, sadly, is a one-time use action. If you want to paint more than one range, you must go through each step again.
Note:
To use the Format Painter command as many times as you like, double-click on it! You’ll be able to paint different ranges as a result. The limitless format painter will remain active once enabled until the Esc key is used to turn it off.
6. Clear Format from a Table
Excel automatically applies styling to tables you design, such as borders, alternating row colors, and fill colors for column headings.
You could want to preserve the fantastic table features while removing the format that is applied automatically.
It is possible to maintain the table while changing the format to one that is more understated.
To remove the formatting from a table, follow the following steps:
Step 1: In the table, choose any cell.
Step 2: Go to the Tab for “Table Design”. To view more options, click the arrow symbol in the “Table Styles” section.
Step 3: Choose “Clear” from the menu.
By doing this, your table’s format style will be cleared.
7. Clear Format from a Pivot Table
Similarly to that, when you build a pivot table, the default pivot table format is automatically used.
Remove the format from a pivot table is shown below:
Step 1: In the Pivot Table, choose any cell.
Step 2: Select the “Design” tab. To open the “PivotTable Styles” section, click the Arrow symbol.
Step 3: At the very bottom of the menu, choose “Clear”.
By doing this, your pivot table’s present format is removed and the minimal style format is applied.
8. Conclusions
Any spreadsheet’s format is an essential component. However, given the variety of formatting possibilities available, you might need to delete the existing formats and begin again.
The format can be easily removed from any chosen range. The Home tab contains the Clear Format option. You should always use this approach!
But there are alternative approaches that are worthwhile learning, such using the Format Painter.
When you create tables or pivot tables in Excel, an automatic format is employed. These can also be eliminated by giving them a more subdued format.
You may be interested:
