Quick Guide to Removing Formatting in Excel

[nerdslife_team]

Formatting in Excel can really help make your data pop, but sometimes you just need to start fresh. Whether you’ve got a spreadsheet with tons of different formatting or you’re working with a file someone else formatted, clearing it all out can be a real time-saver. Deleting cell content doesn’t touch formatting, so we need specific methods to clear the look without losing the data. Here’s a breakdown of how to do it.

Clear formatting from specific cells

If you only need to clear formatting from a few specific cells, here’s how you can do it:

1. First, open your Excel sheet and select the cell(s) you want to clear the formatting from.

   

2. Next, navigate to the “Home” tab. In the “Editing” section at the far right of the ribbon, click the “Clear” button (it’s the one with the pink eraser icon). From the dropdown menu, select “Clear Formats”.

   

This will instantly remove all formatting from the selected cells, leaving just the raw data behind.

Shortcut for clearing cell formatting

Alternatively, you can use a handy keyboard shortcut for the same effect. Select the cells, and press ALT + H + E + F and formatting will be cleared.

Clear formatting from the entire worksheet

Got a whole sheet you want to de-format? Doing it cell by cell would be a nightmare! Here’s how to quickly clear all formatting:

1. Open your worksheet. To select every cell in the sheet, click the grey triangle at the top-left of the sheet or simply press Ctrl + A.

   

2. Go to the “Home” tab, find the “Editing” section, click “Clear” and then choose “Clear Formats”.

   

Keep in mind that clearing formats will remove any custom formatting, including things like date formats. For example, a date displayed as “26-May-19” will revert to its underlying serial number, like 43611.

Clear only cell contents while keeping formatting

Sometimes you want to keep the formatting but remove the data. Here’s how to clear the contents while keeping the formatting:

1. Open your worksheet, select the cell(s) or the entire sheet.

   

2. Go to the “Home” tab, find the “Editing” section, click the “Clear” button, then select “Clear Contents”.

   

This action will only remove the content, without modifying any of the cell’s formatting.

Remove formatting from empty/blank cells

It’s tough to spot formatting on blank cells, but it can be there! Here’s a trick to find and clear it:

1. Select the range or entire sheet where you want to clear formatting from empty cells. Click “Find & Select” in the “Editing” section of the “Home” tab.

   

2. Or you can select the range with the blank cells, and then press the F5 key and then select “Special” button in the “Go To” Dialog box.

   

3. In the “Go To Special” dialog, select “Blanks” and click “OK”.

4. Excel will now highlight all the empty cells in your selection.

5. Go to the “Home” tab, click the “Clear” drop-down and select “Clear Formats”.

   

You can also use the same method to find blank cells and apply formatting to them.

Remove a specific cell formatting

Sometimes, you only want to remove one specific type of formatting. For instance, cells that are blue with red text. Here’s how:

1. First, select the data or entire sheet that contains the formatting you want to remove.

   

2. Go to “Home” tab, click “Find & Select” and choose “Find”.

   

3. Click “Options” to expand the find and replace window.

   

4. Click the drop-down button in the “Format” button and choose “Choose Format From Cell…”.

   

5. Click on the cell with the formatting you want to remove, the format you picked will show up in the preview box.

6. Click “Find All”.

7. In the find and replace dialog box, press Ctrl+A to select all the listed results.

8. Then, click “Close” in the dialog.

9. All the cells with specific formatting are selected.

10. Now click the “Clear” button in the “Home” tab, and select “Clear Formats”.

This will remove the specific formatting you targeted and leave the rest of the formatting intact.

Remove formatting by copying another cell’s format

You can overwrite existing formatting by copying the format of another cell or a blank cell.

1. Copy the cell from which you want to copy the formatting. Right click and select “Copy” or press Ctrl + C.

   

2. Select the cell or range you want to clear formatting from. Right-click and select “Paste Special…”.

   

3. In the “Paste Special” dialog, choose “Formats” under “Paste” and click “OK”.

   

This will apply the formatting from the copied cell to the selected cells.

Clear formatting using the format painter

The format painter tool is another method to quickly remove formatting.

1. Select an empty cell that does not have any formatting. Then, navigate to the “Home” tab and click on “Format Painter” button in the “Clipboard” group.

   

2. Then, select all the cells from which you want to clear formatting. That’s it.

   

Clearing all formats and contents from cells

If you need to completely wipe cells clean, here is how:

1. Select the cells or the range you want to clear.

   

2. Go to “Home” tab, find the “Editing” section, click the “Clear” button, and select “Clear All”.

   

This removes everything, leaving completely blank cells behind.

Clear formatting from excel tables

When you convert a range into a table in Excel, it gets a special table-specific style applied. The regular “Clear Formats” button won’t remove table styles, so you’ll need to follow these steps.

1. If you try to remove cell color formatting using the Clear formats button, you will see that the table style remains.

   

2. Select the range of cells you want to remove the formatting from.

   

3. Go to the “Design” or “Table Design” tab and click “Convert to Range” from the Tools group.

   

4. Click “Yes” in the confirmation dialog.

   

5. Now, you can go to the “Home” tab and select “Clear Formats.”

   

Remove table styles

Alternatively, you can remove table formatting by clearing the table styles.

1. Select the table or the range of cells you want to remove the formatting from.

   

2. Go to the “Design” tab and click the “More” button in the “Table Styles” section.

   

3. At the bottom of the table styles window, click “Clear”.

   

Remove conditional formatting in excel

Conditional formatting applies specific formatting rules based on set criteria. Here’s how to remove them.

Remove conditional formatting from selected cells

1. Select the range of cells that has conditional formatting applied.

2. Go to the “Home” tab, click “Conditional Formatting,” select “Clear Rules” then choose “Clear Rules from Selected Cells.”

   

This will only remove conditional formatting from your selected range.

Manage conditional formatting rules

Alternatively, you can manage the rules individually:

1. Select any cell in the sheet. Go to the “Home” tab, click “Conditional Formatting” and choose “Manage Rules.”

   

2. In the “Conditional Formatting Rules Manager” dialog, choose “This Worksheet” in the “Show formatting rules for:” dropdown.

   

3. Select the rule you want to delete and click “Delete Rule”.

This will remove only the selected rule.

Remove conditional formatting from the entire sheet

1. To remove all conditional formatting, click “Conditional Formatting” in the “Home” tab, select “Clear Rules”, and choose “Clear Rules from Entire Sheet”.

   

This will clear all conditional formatting from the current worksheet.

Adding the “Clear Formats” Option to the quick access toolbar

If you find yourself using the “Clear Formats” button frequently, you can add it to the Quick Access Toolbar for faster access. Here’s how:

1. Open any Excel spreadsheet, click the “File” tab and then click “Options”.

   

2. In the “Excel Options” window, click “Quick Access Toolbar”. In the dropdown “Choose commands from:” select “All Commands”.

   

3. Scroll down to find “Clear Formats”, select it, and click “Add” to move it to the right-hand box.

   

4. You can add more commands, then click “OK”.

   

Now, the “Clear Formats” button will be right at the top of your Excel window.

Add a separate “Clear Formats” button to the ribbon

If the Quick Access toolbar feels too cluttered, you can create a custom group on the ribbon.

1. Open any Excel sheet, click the “File” tab then “Options” to open Excel options.

   

2. In the “Excel Options” window, select “Customize the Ribbon.” Or, right-click on the ribbon and choose “Customize the Ribbon”.

   

3. In the dropdown “Choose commands from:”, select “All Commands”.

   

4. Select the tab (e.g., Home) where you want to add the new group, and click “New Group”.

   

5. Select “New Group (Custom)” and click the “Rename” button.

   

6. Give the group a name, choose a symbol if you want to, then click “OK”.

   

7. Select the new group, scroll down to the “Clear Formats” in the list, and click “Add”.

   

8. Click “OK” to apply the changes.

   

Now the “Clear Formats” button is available on your ribbon.