Get rid of formatting on particular cell, empty cells, as well as the whole worksheet of Excel using this tutorial.
Formatting is an excellent method to make the content of Excel cells stand out and distinguish them from other information. If you’re working with huge Excel sheets, you can apply different formatting options to the cells, such as changing the cell color border color, font color alignment numbers, date formatting, number format, conditional formatting, and many more to alter the appearance of your spreadsheet.
Additionally, if someone has shared a worksheet with you or purchased the worksheet from an online source the sheet is more likely to be prepared. In these instances you might want to change the formatting or eliminate any formatting that is present on the worksheet. But, simply deleting the content of a cell won’t erase the formatting, and so cleaning formatting can be an overwhelming job.
In this article we will demonstrate various ways to swiftly get rid of formatting on specific worksheet cells or to apply formatting to blank cells or to the entire worksheet.
Table of Contents
Clear Formatting for Select Cells in Excel
If you want to clear the formatting for just one or multiple select cell it is possible to do so without impacting other worksheet cells. This is how to do it:
Open the worksheet that you wish to eliminate the formatting. Then mark the cell or cells where you wish to eliminate all formatting.
Then, click on the tab ‘Home’, and then click the “Clear” (pink eraser symbol) option under the Editing section on the top on the ribbon. Select the option ‘Clear Formats’ in the dropdown menu.
This will eliminate all formatting of the cells selected leaving just the data.
Alternately, you could choose the cells that you wish to eliminate the formatting, and then press this keyboard shortcut such as Alt+H+E+F.
Clear Formatting of the entire Excel Worksheet Excel
If you’re working on an enormous spreadsheet that has too numerous cells that have been formatted removing each one separately will take many hours. In that situation you can eliminate all formatting for cells from the entire spreadsheet or the entire spreadsheet by using Clear. Clear feature.
Open your spreadsheet using the formatting. Then, click in the upper right corner (one that has an icon of a gray triangle) or use Ctrl+A to select all cells on the worksheet.
Then, click on the tab ‘Home’ and select the ‘Clear’ button within the Editing group. In the menu ‘Clear’, select the option ‘Clear Formats.
Be aware that removing any formatting in cells that have formatting cell(s) will eliminate any type of formatting. For example, if you eliminate formatting from cells with dates that are formatted in a specific format for dates (26-May-19) It will eliminate the date format, but keep the serial number in the format of the date: 43611.
RemoveOnly Contents are removed from Cells, while maintaining the formatting of the cells
You may also erase only the data or contents of the cells, while preserving the cell’s formatting.
To do this, open the worksheet using the cells formatted. Select either the cell or entire worksheet from which you wish to delete the contents.
Go to the tab ‘Home’, in the section for editing,, open the menu ‘Clear’ (eraser option) and choose the option ‘Clear Contents’..
The data in the cell is deleted from the range selected, without any formatting changes.
Remove Formatting From Empty/Blank Cells
It can be difficult to tell when a blank cell is missing any formatting that has been applied to it. Therefore, if you’ve got an extensive spreadsheet that has a lot of blank cells, it can be difficult to locate and eliminate formatting from all the blank or empty cells.
But, using Excel’s Go-To Special feature you can locate the blank cells at once and remove the formatting off them. You can follow these instructions to accomplish this:
Then, choose the cells in the range as well as the whole spreadsheet in which you wish to select all blank cells. Then, click the ‘Find and Select menu option in the Editing section of the tab ‘Home.
Alternately, you could choose the cells that have blank cells by pressing F5, and then click the ‘Special button’ in the Go To dialog box.
This will bring up this Go To Special dialog box. Select the option ‘Blanks’ and select ‘OK’.
This will be a way to select all blank cells (highlighted in gray) within the range you have selected. Then, you can take out the formatting from these cells.
To do this you need to go to the tab ‘Home’ and then click the ‘Clear’ drop-down within the Editing group. Select the option ‘Clear Formats’ in the dropdown menu.
Utilizing the same technique using the same method, you can highlight the empty cells and alter the formatting too.
Remove the Formatting of a Specific Cell from a Worksheet/Range
Sometimes, you might be working with a spreadsheet and have to remove only one particular type of formatting on a cell sheet or range. In this case, for instance, you might need to eliminate formatting from all blue-colored cells, or those with negative numbers.
With Excel’s Find and Replace feature, you can locate and select cells with particular formatting that is applied to them and then remove them, while conserving the remainder.
In the following dataset, there are some cells that have been formatted with a blue color, and have a red-colored font. We would like to remove the specific formatting, while keeping the other formatting of the spreadsheet. To accomplish this, follow these steps:
Then, choose the worksheet with formatting or the whole worksheet.
Go to the ‘Home tab, then click “Find and Select” in the Editing group, and then click the ‘Find’.
This will bring up this Find and Replace dialog box. Then, click the button ‘Options’ to open the options that are more advanced.
Click at the icon for dropdown (a downward-pointing button) under the ‘Format’ button, and then select the ‘Choose’ Format From Cell’ …’ dropdown menu.
This will transform the cursor into an plus icon as well as dropper. Then, you can choose the cell with the formatting you wish to eliminate. After this, you’ll notice the formatting you have selected will be displayed in the preview box in the ‘Find What field:’ (as as shown below).
Make sure that the formatting is what you wish to eliminate and then click the ‘Find All’ button.
Once you have done this, you’ll notice that every cell with chosen formatting will be displayed beneath in the Find and Replace dialog box. Then, press the shortcut Ctrl+A to select all of the results.
Then, click the “Close” option to end the dialogue.
This will pick all cells that have this particular formatting.
Then, click the ‘Clear’ icon and then select “Clear Formats” from the tab Home.
After this, it will eliminate the format (blue cell color and the red font) from the range you have selected.
Replace the Formatting of Current Formatting with Formatting from a Different Cell
Another method to get rid of formatting in the specific cell(s) is to copy the specific format (or any format) from a different cell on your worksheet, and then paste it into your cell’s range. Here’s how to accomplish this:
Then, copy the cell in which you wish to duplicate the format. Do this by clicking on the right and choosing ‘Copy’, or pressing Ctrl+C.
Then, choose the cell(s) in where you wish to eliminate any formatting, and then replace the format with copied. After that, right-click on the cells you want to copy and choose “Paste Special” …’
Within the Paste Special dialog box, choose the option ‘Formats’ under Paste and then click ‘OK’.
This will apply the formatting of copying the cell onto the chosen cell(s) or the entire range.
Clear Formatting using Format Painter
Format Painter is a program in Excel that lets you transfer formatting from one cell(s) and then apply it to a set of cells. It is also possible to make use of Format Painter to remove formatting from Excel. Excel.
Choose a blank cell that has no formatting applied. Go to the tab ‘Home’ and click the ‘Format Painter’ (brush icon) button within the clipboard section.
Then, you can select all the cells that you wish to remove formatting. This is it, all formats will be eliminated.
Clearing All Formats and Contents from Cells/Range/Worksheet
If you want to eliminate all formatting, comments, content and hyperlinks from cells, follow these steps:
Choose the range of cells that you wish to erase everything, including formatting.
Click on the ‘Home’ tab, then click the ‘Clear’ icon in the Editing section then select ‘Clear All’..
The process will eliminate everything and transform the cells into blank cells.
Clear Formatting of an Excel Table
When you convert a set of cell into tables, it’s formatted using certain colors. If you attempt to remove formatting by using the Clear Formats button, it will not work. If you’re looking to eliminate the table style (format) from particular cells, while maintaining the remainder of the table Follow these steps:
For example If you take out the color formatting of cells in the following example by clicking the Clear Formats button however, you’ll still see the table style as illustrated below.
To eliminate formatting on tables from a set of cells, you must first choose the range from which you wish to eliminate the formatting.
Then, click on the ‘Design’ or “Table Design Tab and then select the ‘Convert to Range’ button in the Tools section.
After that, you can click “Yes” to confirm the box.
Then, return to the tab ‘Home’ and click the option to clear formats.
You can also eliminate the formatting of tables through clearing Table Styles from the Design tab.
To do this, click on the table or the range of cells that you wish to eliminate the formatting.
Then, go to the tab ‘Design’, then, in the tab ‘Table Styles click the ‘More’ option.
In near the end of the expanded Table Style window, click the ‘Clear’ icon.
Remove Conditional Formatting in Excel
In Excel conditional formatting, you to apply certain styles to cell or groups of cells in accordance with the conditions. If you wish to eliminate conditional formatting from Excel then take these actions:
To eliminate the conditional formatting of a set of cells formatted, first, select the range. Next, click the Home tab and click on the option ‘Conditional Formatting’ in the Style group, and then click “Clear Rules”. Select option to ‘Clear Rules from selected cells Option.
This will eliminate conditional formatting only from the range you have selected.
Alternately, choose any cell on the spreadsheet and then, from the tab ‘Home’, select the option ‘Conditional Formatting’ in the Style group and then select the option to ‘Manage Rules’.
Within the Conditional Formatting Rules Manager dialog box, choose ‘This Worksheet’ from the ‘Show formatting rules to the following: drop-down menu.
Select your rule (condition) you wish to eliminate and then click the “Delete Rule Click Delete Rule.
In the end, only the formatting rule has been removed off the sheet.
To eliminate conditional formatting of the whole sheet at one time Click the option ‘Conditional Formatting’ in the tab Home, and then click “Clear Rules’. Select the ‘Clear Rules From the entire Sheet’ option.
The conditional formatting on the current worksheet has been eliminated.
The Formats option to the Quick Access Toolbar. Formats choice to Quick Access Toolbar
If you frequently use the Clear Formats tool often within your Excel spreadsheet, you are able to access it in a single click by including the tool in the Quick Access toolbar at the top of the ribbon. This can be extremely useful when you have a lot of spreadsheets and need to delete formatting from them. To include Clear Formats as a Clear Formats button to the Quick Access toolbar, follow these steps:
Start your Excel spreadsheet, it doesn’t matter what one you choose, simply select the File tab in the upper left corner, and then select “Options” from the left-hand sidebar of the backstage view.
Within the Excel Options window, click the Quick Access Toolbar section on the left-hand pane. In the ‘Choose Commands from:’ drop-downmenu, choose ‘All Commands’ in the right-hand side of the pane.
From the menu of commands below scroll down until you find “Clear Formats” option, then select it, then press the ‘Add’ button to add it into the right-hand side of the box.
You can also add additional commands like Clear All, Clear Contents Clear Hyperlinks. in the right-hand side section. When you’re done you can click OK.
The Clear Formats button is added to the Quick Access Toolbar located in the upper-left part of the Excel windows (where the redo, undo as well as save buttons are located). All you need to do is choose the range or the worksheet, and then click “Clear Fromats button on the Quick Access toolbar to remove formatting.
Add a Separate Clear Formats Button to the Ribbon
If you are feeling that you’re Quick Access toolbar is filled with too many buttons, you could create a custom group in the Excel ribbon and then add an additional “Clear Formats button that can be accessed by one click. This is how to include the ‘Clear Formats’ option to your Excel ribbon:
Start Excel. Open Excel by clicking the File tab in the left-hand corner. choose ‘Options’ for opening Excel Options.
Within the Excel Options window, click the Customize the Ribbon section on the left-hand sidebar. Alternately, click anywhere on the ribbon and choose the option to ‘Customize the Ribbon’.
From the ‘Choose Commands in the dropdown menu, choose ‘All Commands’ in the right-hand side of the pane.
It is possible to create new command lines for the custom groups that are displayed in the ribbon. To add them, click the tab that you wish to add your custom group (In this instance, the Home tab) in the main Tabs box, then select the ‘New Group’ button.
Select the new Group (custom) then select the ‘Rename’ option below.
Enter the name of this group into the ‘Display name Then, select Symbol If you wish then select “OK”.
Then, choose the group you just created (Formats (Custom)) under the Main Tabs. Then, scroll down to the command list in “Choose Commands from:” and then select the option to clear Formats. After that, click the Add button.
After that, click “OK” to apply the modifications.
The brand new option (Clear formats) in the Ribbon. you are now able to clear formatting by a single click.
That’s it.