How to Clear Content in Google Sheets (Values, Formatting, Notes)

- Written by Puneet

excelchamps-free-courses-puneet-gogia

In Google Sheets, when you work on data, you might need to clear Content from all the cells or some specific cells to enter data into the cells again.

Now, there are multiple ways that you can use to do this. And there are other things you can clear from a worksheet in Google Sheets.

In this tutorial, we will learn all the methods one can use to clear Content or values from a cell or a range of cells. So, let’s get started…

Clear Values from the Selected Range with a Keyboard Key

This is one of the easiest ways to clear Content from the selected range of cells. This allows you to do this quickly without using any complex steps.

Let’s say you want to delete Content from the range A1:A5. To do so, select the range and then press Delete on your keyboard.

clear-values-from-selected-range

When you do this, all the values (including the drop-down list and checkboxes)  from the selected range of cells are removed. The key is the same whether you are using Mac or Windows.

When you use this keyboard key, you can only remove the values from the cells, meaning the Content inside the cell. But if you have some formatting applied to the cell, that formatting won’t be clear with this. If you want to remove formatting, use another keyboard shortcut Ctrl + \.

with-keyboard-remove-values-from-cells

This is the keyboard shortcut that removes formatting from the selected cells. If you want to remove Content or values from the entire worksheet, you need to use the keyboard shortcut Ctrl + A to select the entire range and then use the above shortcuts to clear Content and formatting.

Clear Content from a Range using the Edit Option

If you want to clear values from the selected cells, use the delete option from the “Edit” menu. Select the range of cells from which you want to clear the Content. For example, select cells A1 to A5 if that’s the range you want to clear.

clear-content-from-a-range

Clicking on the “Value” option will clear values from the selected range. In the same options, we can clear notes from the range. You can also use the keyboard shortcut Ctrl + \ to clear formatting after removing formatting.

You can also use the “Format” menu option to clear the formatting from the selected range.

from-format-menu-choose-clear-formatting

Use Google App Script to Clear Content or Values from a Range

If you have a specific range that you want to clear every time, then you can use app script code to clear everything from that range.

function clearEverything() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var range = sheet.getRange('A1:A5');
range.clear({contentsOnly: false});
}

The above code clears the Content and formatting from the range A1:A5 of the active worksheet. You can edit the code from the range variable if you want to change the range.

You can use the code below to remove values from the selected cell or the range.

function clearSelectedCells() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var range = sheet.getActiveRange();
range.clear();
}

To use the above codes, go to the “Extension” menu and click the “App Script” option to open the Script Editor in a new tab. In the script editor, paste the code we have above. Save the script, then click the Run button (►) to run it.

Clear Content but Keep Formulas

Let’s say you need to clear values but don’t want to clear formulas. In this case, you can use Find and Replace to target and clear only the cells containing values (excluding formulas).

clear-content-but-keep-formulas
  1. First, click the rectangle at the top-left corner (above row 1 and to the left of column A) to select the entire worksheet, or use the keyboard shortcut Ctrl + A.
  2. Press Ctrl + H to open the Find and Replace dialog. In the “Find” field, enter ^[^\=]*$. This regular expression will match any cell content that does not start with an equal sign (which is the indicator of a formula).
  3. Now leave the “Replace with” field empty and check the box for “Search using regular expressions.”
  4. In the end, click “Replace all” to clear all matched cells.

In this regular expression,

  • ^: Asserts the position at the start of a line.
  • [^\=]*: Matches any sequence of characters that does not include an equal sign (=), which indicates the cell does not contain a formula.
  • $: Asserts the position at the end of a line.

Deleting Cells Instead of Clearing Values

You can delete the cells from the worksheet instead of clearing the values. You can select the cells first and then go to Edit > Delete > Cells Shift Up.

delete-cells-instead-of-clearing-values

Delete Cells Vs. Clear Value

There are a few differences between clearing Content or values and deleting cells in Google Sheets.

  • Delete Cells – It permanently removes selected cells, rows, or columns and their Content. It also shifts adjacent cells to fill the gap left by the deleted cells, altering the layout of your sheet. And can cause errors in formulas referencing the deleted cells.
  • Clear Values – Removes the data within the selected cells but leaves the cells themselves in place. It keeps the layout intact, and only the Content within the cells is removed. Formulas referencing the cleared cells remain intact but return empty or default values.

Understanding these differences allows you to choose the appropriate action based on whether you want to maintain the sheet’s structure or remove cells entirely.