Applying formatting options when working with large Excel Worksheets, helps users organize and make their data stand out. Sometimes users might want to highlight or change formatting while inputting data in an Excel sheet, and for this they will need to remove the current format before adding a new format.
Removing formatting manually like changing cell color, changing font, changing borders, alignment and other formats is very tiresome and time-consuming. But Microsoft Excel has quick and simple ways to remove formatting in an Excel Worksheet, follow this article to know the techniques involved.
How to remove formatting in Excel
There are few ways to quickly remove formatting in Excel:
- Select the range of cells from which you want to remove its formatting.
- Navigate to the Home tab.
- Go to the Editing group, and click the arrow next to the Clear button.
- Click the Clear Formats option to remove all formatting from the selected cells.
This erases all cell formatting like; conditional formatting, number formats, fonts, colors, borders, etc. however, the cell contents still remains in your worksheet.
How to remove formatting in Excel from an entire column, row, or worksheet.
Also, one can easily remove formats not only from a single cell, also from an entire row, column or worksheet.
To remove formatting from an entire worksheet:
- Press Ctrl+A to select the entire sheet or click the Select All button at the top-left corner of the worksheet
- Then click Clear Formats.
To remove formatting from a whole column or row:
- Click the heading of the column or row to select it.
- Then click Clear Formats.
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
To make it easier, one can add the Clear Formats option to the Quick Access toolbar in the top-left corner of your Excel window or Excel Ribbon in order to have a one-click tool to remove formatting in Excel. This will help you handle many Excel files.
Follow the following steps to do so:
- Open your Excel Worksheet.
- Click on File.
- Then select Options.
- Pick the Quick Access Toolbar on the left-side pane.
- Select All Commands, under ‘Choose commands from‘.
- When you see the list of commands, scroll down to Clear Formats and select it.
- Then click the Add button to move it to the right-hand section.
- Click OK.
Add the Clear Formats button to Excel Ribbon
If the Quick Access Toolbar is already cluttered with too many buttons, you have the option of creating a custom group on the Excel Ribbon and placing the Clear Formats button there.
Follow these steps to add the Clear Formats button to the Excel Ribbon:
- Using the mouse right-click anywhere on the Ribbon.
- Then select Customize the Ribbon.
- New commands can only be added to custom groups, therefore click the New Group button.
- When the New Group is selected, click the Rename button, type in your preferred name, and click OK.
- Select All Commands under Choose commands from.
- When you see the list of commands, scroll down to Clear Formats and select it
- Select the newly created group then click Add.
- Then click OK to close the Excel Options dialog and apply the changes.
How to remove formatting in Excel using Format Painter
To remove all formatting in Excel using a Format Painter, do the following:
- Select an unformatted cell close to the cell you want to remove formatting from.
- Navigate to the Home tab and click on the Format Painter button, in the Clipboard group.
- Then select the cell(s) from which you want the formatting cleared.
How to remove formatting in Excel with a keyboard shortcut
The Alt hotkeys helps you to execute this command of removing formatting in Excel. These keys allow you to navigate the Ribbon commands with your keyboard.
- First select the cell range.
- Then press the sequence Alt, H, E, and F on your keyboard to clear the format.
How to remove formatting in Excel with Paste Special
Any unused cells in your workbook have the default format applied. You can copy and paste this as a way to remove the formatting in other cells.
Follow these steps to use Paste Special to remove format:
- Select an unused cell.
- Press Ctrl + C to copy the cell.
- Then choose the area from which you want to clear the format.
- Press Ctrl + Alt + V keys on the keyboard to open the Paste Special menu.
- Select Formats option.
- Press the OK button.
This pastes only the format from the unused cell with the default format.
Final thoughts
Following the steps highlighted above will help you know how to remove formatting in Excel easily and keep your data organized.
If you liked this tutorial, then kindly subscribe to our YouTube Channel for more Microsoft Excel video tutorials.
