How To Clear Formatting In Google Sheets? – Working Methods In 2023!

We often customize our spreadsheets with different font sizes, colors, and styles and even apply conditional formatting for better data visualization. But at times, you may need to use the values only from those cells. But for that, you need to know how to clear formatting in Google Sheets the right way.

You can use the “Clear Formatting” option under the “Format” menu to clear the formatting from any cell in Google Sheets. Besides you can also use the “Paste Format Only” option from the blank cell to do it. You can also use the “Paint Format” option to do it. And for more advanced controls, you can use Apps Script codes as well.

Depending on the data set you are working with, your need for removing formatting can be different. So here, I’ll be going to give you various methods to do it the right way. And I’m going from the easiest to the hardest. Stay with me!

Things To Know Before Clearing Any Formatting In Google Sheets

There is no denying that you can easily remove any formatting in Google Sheets. And I’m soon going to reveal those methods. But before that, you need to keep in mind a few things to execute the steps!

• Clearing formats will also remove any conditional formatting or formulas from the cell on Google Sheets.
• It will also remove all the hyperlinks from the cell.
• Once cleared, you’ll get your content in “Values Only” format with the default font and color.

Don’t worry if you have mistakenly cleared any formatting in Google Sheets. This action is undoable. So, to revert back to the original formatting, just press the “Ctrl + Z” buttons together to undo the entire action.

How To Clear Formatting From A Single Cell In Google Sheets?

It is now very easy to customize and format any cell in Google Sheets, such as changing the color or the font. You can also apply conditional formatting on a single cell as well. But at times, you might need to remove those formatting only.

Google Sheets has an inbuilt option to remove just the formatting. Here are the steps!

• Step 1: Launch Google Sheets and open the spreadsheet.
• Step 2: Click on the cell from where you want to remove formatting.
• Step 3: Now, click on the “Format” option from the main header menu.

• Step 4: From the drop-down menu, click on the “Clear Formatting” option.
• Step 5: You can now see only the values (without any formatting) in that cell.

Alternatively, you can also use the keyboard shortcut to clear formatting from any cell in Google Sheets. To do that, first, select the cell. And then press the “Ctrl + \ (backslash)” keys to remove the formatting from the designated cell.

Bonus: Do you know that you can also remove duplicates in Google Sheets besides the formatting? Check out my detailed guide on removing duplicates in Google Sheets to know more.

How To Clear Formatting From Multiple Cells In Google Sheets?

Not just from a single cell, you may also need to remove formatting from multiple cells in Google Sheets. And it is quite possible to do so. That, too, is an easy way. Let’s check out the steps.

• Step 1: Open your spreadsheet in Google Sheets.
• Step 2: Click on the first cell of the desired range to select it.
• Step 3: Hold the “Shift” button and use the “Arrow” keys to select adjacent cells.
• Step 4: If you want to select non-adjacent cells, press and hold the “Ctrl” button and then click on all the individual cells that you want to select.
• Step 5: Check if all the cells are selected or not from where you want to remove all the formatting.
• Step 6: Now, click on the “Format” option from the header menu.

• Step 7: Click on the “Clear Formatting” option from the drop-down list to complete the operation.

You can also alternatively use the “Ctrl + \” shortcut to remove formatting from multiple cells in Google Sheets. And to do so, just use the shortcut after selecting all the cells in your desired cell range. This action is also undoable.

Besides formatting, you can also clear all the cell content from multiple cells as well in GS. To know more, don’t forget to check out my comprehensive guide on clearing cell content in Google Sheets.

How To Clear Formatting From A Whole Column?

If you want to remove the formatting from an entire column, selecting every individual cell on that column can be time-consuming, especially if you are working with a large data set.

However, there is an easy way available to do it in seconds. Here are the steps!

• Step 1: Click on the column header (where the column number is written).
• Step 2: This will highlight the entire column.
• Step 3: Click on the “Format” option from the main menu.
• Step 4: Finally, click on the “Clear Formatting” option to remove all the formatting from your selected column.
• Step 5: Alternatively, you can also use the “Ctrl + \” shortcut to remove formatting after selecting the column.

In the same manner, you can also remove formatting from an entire row. And to do that, click on the row header (where row numbers are written) to select the entire row. Follow the above method afterward to remove the formatting from the targeted row.

How To Clear Formatting From An Entire Sheet?

If you have applied some formatting on an entire spreadsheet in Google Sheets, it is still possible to clear all while keeping the values in the cells. The steps are pretty easy, though.

• Step 1: Launch Google Sheets and open the spreadsheet from which you want to clear all formatting.
• Step 2: Click on the blank cell icon on the top-left corner of the spreadsheet (On the left side of the “A” header).
• Step 3: This will select every cell in the spreadsheet.
• Step 4: Alternatively, press the “Ctrl + A” buttons together to select the entire spreadsheet.

• Step 5: Click on the “Format” option from the header menu.
• Step 6: Finally, click on the “Clear Formatting” option to remove all the formatting from the entire spreadsheet.

If you are using a Mac system, use the “Cmd” key instead of the “Ctrl” key to do all the operations that I’ve mentioned above. The rest of the steps are identical in both Windows and Mac.

Bonus: It is now also possible to search for a particular string in Google Sheets the easy way. To know in detail, check out my latest guide on the search operation in Google Sheets.

How To Clear Formatting by Paste Format Only?

Now, this is a little bit of a workaround method that I’ll be talking about. At times, your spreadsheet may encounter glitches while removing formatting, especially from a large dataset.

And in that case, you can try to use a blank cell to replicate the format in all the formatted cells. In that way, you can transfer the blank format. Check out the steps to do it!

• Step 1: Launch Google Sheets and open your spreadsheet where you have formatted cells.
• Step 2: Click on any blank cell (without format) to select it.
• Step 3: Click on the “Edit” option from the main header menu.
• Step 4: Now, click on the “Copy” option (alternatively, you can use the “Ctrl + C” keyboard shortcut).

• Step 5: Once copied, select any cell with the format on your cell range.
• Step 7: Click on the “Edit” button again from the header menu.
• Step 8: Hover your mouse over the “Paste Special” option to get the side-loaded menu.

• Step 9: Click on the “Format Only” option (Alternatively, you can use the “Ctrl + Alt + V” keyboard shortcut).
• Step 10: Now, hold the “Ctrl” button and drag the mouse to select the entire range.
• Step 11: Click on the paste setting option (clipboard sign) at the bottom-right corner of the selected range.
• Step 12: Click on the “Paste Special Format Only” option from the drop-down menu.
• Step 13: You can now see the formatting is now removed from the entire range.

You can do this entire process with the keyboard shortcuts (recommended for pros). This will also eliminate time requirements to execute the operation. And it is also easy to do it!

• Step 1: Use your arrow keys to navigate to a cell that doesn’t have any formatting.
• Step 2: Press the “Ctrl + C” buttons together to copy it.
• Step 3: Hold the “Shift” button and use your arrow keys to select the entire range from which you want to remove formatting.
• Step 4: Press the “Ctrl + Alt + V” buttons together to paste the formatting.
• Step 5: You can now check if the formatting is removed from the entire range or not!

Bonus: The “CONCATENATE” function is one of the most versatile functions in GS, as you can do various things using this function. To know more about it and its real-life application, check out my detailed guide on the “CONCATENATE” function in Google Sheets.

Clear Formatting Using The “Paint Format” Option In Google Sheets

Besides the blank format pasting option, there is another way to remove formatting in Google Sheets. And that is by using the “Paint Format” option. It will take much less time to remove formatting if you have a large, selected range.

Here are the steps to do it the easiest way.

• Step 1: Launch Google Sheets in any browser and open your spreadsheet that has formatting.
• Step 2: Click on any blank cell that doesn’t have any formatting.
• Step 3: Click on the “Paint Format” option (Paint roller icon) from the secondary header menu. 

• Step 4: Check if the “Paint Format” option is highlighted or not.
• Step 5: Once highlighted, click on the first cell on the formatted range, hold down, and drag the mouse to select the entire formatted range.
• Step 6: Release the mouse button, and you’ll see all the formatting is now gone!

Only use this option if you have a blank cell in the spreadsheet from where you can copy the blank format. All the paint formats you have copied will be auto-applied to the entire range.

Using Apps Script To Remove Formatting In Google Sheets

We have already discussed all the basic and advanced methods to remove or clear formatting in Google Sheets. But if you have a large data set, or if you want to remove formatting from multiple spreadsheets in the shortest possible time, you can take the help of Apps Scripts to write your custom code.

If you need advanced controls while performing the operation, Apps Script can also help you in that case. Here are the steps you need to follow.

• Step 1: Launch Google Sheets and open your spreadsheet.
• Step 2: Click on the “Extensions” option from the main header menu.

• Step 3: Click on the “Apps Script” option to go to the code editor.
• Step 4: In the code editor box on your right-side box, Delete the first three default lines.
• Step 5: Paste the following Apps Scripts code as it is:

// Function to remove formatting from a single cell
function removeFormattingFromCell() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var cell = sheet.getActiveCell();  
  // Clear formatting for the selected cell
  cell.clearFormat();
}
// Function to remove formatting from multiple cells
function removeFormattingFromRange() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getActiveRange();  
  // Clear formatting for the selected range of cells
  range.clearFormat();
}
// Function to remove formatting from the entire spreadsheet
function removeFormattingFromEntireSheet() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();  
  // Clear formatting for the entire sheet
  sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).clearFormat();
}

• Step 6: After you’ve pasted the code, click on the “Save Project” (floppy icon) button.
• Step 7: Now, click on the “Deploy” button and grant all the necessary permissions.
• Step 8: Relaunch Google Sheets and click on the “Extensions” option from the header menu.
• Step 9: Finally, click to run the Apps Script code you have just deployed.

Depending on the need (single cell, multiple cells, and the entire spreadsheet), you can use the designated modifiers in the script. Here are the details.

• removeFormattingFromCell: Call this function when you want to remove formatting from a single active cell.
• removeFormattingFromRange: Use this function when you want to remove formatting from a selected range of cells.
• removeFormattingFromEntireSheet: Call this function when you want to remove formatting from the entire active sheet.

Note: if you want to remove formatting from a single cell in Google Sheets, select the cell first before running the App Script code. You also need to pre-defined the entire range beforehand if you want to use App Script to remove formatting from multiple cells in Google Sheets.

How To Get Data Without Any Formatting In Google Sheets?

Google Sheets is a versatile web tool, no doubt about it! And in the last couple of years, it has come up as a strong competitor of the mighty MS Excel. This program now gives you the option to get any data without any formatting.

In that way, you need to stay away from the burden of removing formatting after the operations. Steps are also quite easy!

• Step 1: Copy the entire data that you want to include in the spreadsheet.
• Step 2: Launch Google Sheets and open the spreadsheet where you want to get the data.
• Step 3: Right-click on the first cell of your desired range to get the contextual menu.
• Step 4: Once the menu appears, hover your mouse over the “Paste Special” option.

• Step 5: On the new side-loaded menu, click on the “Paste Values Only” option.
• Step 6: You can now see only values are imported on the cell range.

You can alternatively use the keyboard shortcuts to paste values only in cells. And to do it, copy the entire cell range, open the spreadsheet in Google Sheets, and then use the “Ctrl + Shift + V” keyboard shortcut.

Bonus: Do you know that it is now possible to insert page breaks in GS? That makes it easy to segregate different data sets. To do it the right way, check out my detailed guide on inserting page breaks in Google Sheets.

Final Note

Unless you need some advanced control while removing formatting in Google Sheets, it is better to stick to the “Clear Formatting” option that you can find in the “Format” menu. And this is also an undoable action that you can reset by using the “Ctrl + Z” shortcut.

But if you need better control and faster speed, it is better to use any of the advanced methods I’ve mentioned above. And if you want to use the Apps Script to remove formatting from multiple spreadsheets, you need to use the right parameter among the three I’ve mentioned in this post.

FAQs

How do I clear content in Google Sheets but keep the formatting?                                                             

You need to first select and copy the data that you want to include in Google Sheets. Then to include just the format (without the value), click on the “Edit” option from the header menu. Hover your mouse over the “Paste Special” option and then click on the “Paste Format Only” option.

How do I clear formatting in Google Sheets mobile app?

Launch the Google Sheets app on your smartphone and open the spreadsheet from where you want to clear formatting. From the main menu, tap on the “Format” option. Finally, tap on the “Clear Formatting” option from the drop-down menu.

Is there any third-party extension/add-ons to remove formatting in Google Sheets?

Yes, there are multiple add-ons or extensions available in the Google Workspace Marketplace that can remove/clear all the formatting from a designated cell range or from the entire spreadsheet. And my top recommendations for 2023 are List Cleaner Pro and Autocrat. These two extensions are not just useful but equally versatile.