6 Easy Ways to Clear Formatting in Google Sheets

Formatting is a big part of Google Sheets!

But too much formatting can become cluttered. You might need to clear all your formatting from the sheet and start over when this happens.

Formatting such as applying cells border, font size, font style, cell color, conditional formatting might all need to be removed.

So how do you deal with this situation when you have to restore the cells to the default formatting?

This article will introduce you to 6 easy ways to quickly remove all cell formatting that has been applied.

Get your copy of the example workbook and follow along!

Clear Formatting from the Format Menu

The Format menu gives us the ability to change the formatting of the selected range and it also allows users to remove any applied formatting.

We can restore a selected range to the default formatting with the help of the Format menu.

Follow these steps to remove formatting in Google Sheets.

  1. Select the cell or a range from which you want to remove the formatting.
  2. Click on the Format tab at the top.
  3. Click the Clear formatting option at the bottom of the menu

That’s it! Your selected range will have all the formatting removed so you might want to note down any special color codes for future use.

Clear Formatting with a Keyboard Shortcut

We can also use a keyboard shortcut to clear formatting in Google Sheets.

To clear the format by using a shortcut key, follow these steps.

  1. Select a range from which to clear formatting.
  2. Press Ctrl + \ key on your keyboard.

Hold the control key and then press the backward-slash key and your formatting will be removed.

📝 Note: The light gray lines around each cell will remain, but if needed you can hide these worksheet gridlines too for a completely cleared look!

Clear Formatting by Paste Format Only

Copy and paste are the most common commands, and it is used to move text or data in an instant.

When copying, the command will copy data along with its formats. While pasting copied data you can limit the command to just give you the plain text, format, formula, condition, etc. This can be achieved using Paste Special special option.

Let’s see how we can use the Paste option for clearing format.

Follow these steps to restore the formatted cell to the default cell format with the help of Paste Special Format.

  1. Select the range of cells that you want to copy. This is a cell without any formatting.
  2. Click on the Edit menu at the top.
  3. Click the Copy option from the list.

We have selected and copied cell H3 which has the default formatting.

  1. Select the range from which formatting needs to be removed. In this example the range A3:I12 is selected.
  2. Go to the Edit menu.
  3. Click the Paste Special option from the list. It will open the submenu with all the Paste Special options.
  4. Click Format Only.

We can select multiple cells or ranges if needed by holding Ctrl and clicking on a cell or dragging the mouse to select the range.

Above you can see the results of the data with the cleared format from using the Paste Special Format only option.

Notice there is a clipboard icon in the lower right corner of the pasted range. You can use this switch the paste special option and instead show the Paste values only option.

We can eliminate the Edit tab option for copy and pasting by using the mouse with a right-click on the destination cell or range.

Clear Formatting with Paste Format Only Keyboard Shortcut

With the above methods, we looked at 2 ways to copy and paste the data.

The first one is with the help of the Edit Tab and the second one is with the right-click option.

But you can make the task easier by using a keyboard shortcut.

  1. Select the cell and press Ctrl + C to copy a cell with default formatting.
  2. Select the destination range from which you want to clear formatting.
  3. Press the Ctrl + Alt + V keyboard shortcut to use the Paste Special command.

This will paste only the format and your selection will now have any formatting removed.

Clear Formatting by Paint Format

Paint Format can be used to copy the format and paint it to any other object, similar to copy and paste but only of Formats.

Paint Format is only available in the toolbar and not in the menu options, which makes this task easier as you don’t have to remember any shortcut keys or you don’t have to check the menu option to copy and then paste the formatting to the data.

For example, you have formatted part of the report using a specific font type, color, font size, and borders. Now you want the same formatting to be used for all other sections of the report.

This is where paint format comes into the picture and saves a lot of time.

The same format options are just one click away when you use Paint Format. It’s easier and more efficient.

This can be used to not only copy new formats but can also to restore the default format.

Wondering how you can use this nifty tool?

The Paint format command can be located on the left in the toolbar. Follow these steps to use it to remove formatting.

  1. Select the cell that has the formatting that you want to copy. In this case select a cell with no formatting applied.
  2. Click on the Paint Format icon in the toolbar.
  3. Select the entire range of cells from which you want to clear the format.

When you click on the Format Painter icon you will see it becomes green. This indicates the Format Painter is active and the next cell or range you select will get painted with the format.

  1. Paint format is inactive.
  2. Paint format is active and the next cell or range you select will get formating applied.

Clear Format with Apps Scripts

Now for some advanced techniques for clearing format.

You can also use Apps Scripts to clear the format!

A script is a sequence of instructions interpreted to perform a task and Apps Scripts is a JavaScript based coding platform that allows you to create automation and functions in Google Sheets and other apps.

Check out this beginner’s guide on Apps Scripts in Googles Sheets to get started.

Where Can You Find Apps Scripts in Google Sheets?

The Script Editor is in the Extensions menu.

Go to the Extensions menu and select Apps Scripts from the options. It will open the script editor in a new window.

Remove the existing code then copy and paste the script below to which will clear the format in the range A:I.

function clearFormattingOnly() {
 var RangeClr = SpreadsheetApp
 .getActiveSpreadsheet()
 .getSheets()[0]
 .getRange('A:I')
 .clearFormat()
}

Save and Run the Script. You will see in the spreadsheet that all format has been removed from columns A through G.

Here is how the code works.

  1. A function named clearFormattingOnly() is defined. A function is a block of code that is used to perform an action.
  2. A variable called RangeClr is declared and assigned class SpreadsheetApp.
    • A class defines the variables and the methods common to all objects of a certain kind.
    • SpreadsheetApp can provide the initial point of access to our Google Sheets.
  3. Using getActiveSpreadsheet() will get the active spreadsheet by name.
  4. getSheets()[0] returns first sheet which in this example is sheet1.
    • getSheets() returns an array of all the sheet objects in the spreadsheet and [0] will select the first one. This can be excluded if we are just working with a single sheet.
  5. The range is stored in getRange(‘A: I’)
  6. and .clearFormat() will clear the format of selected range.

When you run the script, it will only clear the defined range which was A:I but you can also avoid hardcoding the range to clear and use a dynamic selection instead. The below script will clear formats from the range selected by the user.

function clearSelectedFormattingOnly() {
 var selectedRange = SpreadsheetApp.getActiveSpreadsheet().getActiveRange()
 var clrformat = selectedRange.clearFormat();
}

The user will need to select a range of cells from which to clear formatting. Then when they run this code, it will only remove formatting from the selected range.

Conclusions

The methods used to clear formatting will give similar results.

Using keyboard shortcuts and Paint Format will make the process a bit easier by eliminating any copy and paste steps.

Clearing formats can also be achieved through the use of Apps Scripts to automate this task and potentially save time.

This article has covered the 6 easy ways to remove any formatting in your Google Sheets. Hopefully, this will help you to get rid of any unwanted formatting in your workbooks.

Do you use any of these methods? Do you know any other ways to remove formatting in Google Sheets? Let me know in the comments below!

About the Author

Varsha Jadhav

Varsha Jadhav

Varsha is a data analyst who loves working with Google Sheets and Apps Scripts Automation. She enjoys helping businesses make data-driven decisions and is always looking for new ways to improve her skills and knowledge so that she can better serve her clients.

Related Articles

Comments

3 Comments

  1. Bob

    There is now way to make a script that auto runs and removes formatting whenever I paste something into a cell?

  2. Tom W3

    as of Nov 6, 2022, after Clear Formatting for either a cell or a range of cells with some data, the old format gets restored (or at least the text color) as soon as you edit the contents of that cell.
    Any ideas?
    Firefox 106.0.2 browser on a Macbook from 2019.

Get the Latest Google Sheets Tips

Write For Us

Are you a tech enthusiast with a talent for writing great content? Come write for us!

Follow Us

Follow us on social media to stay up to date with the latest in Google Sheets!