Clear data by type in Google Sheets

Power Tools provides you with various utilities to speed up most of your daily tasks. Learn how to work with the Clear tool to remove blank rows and columns, numbers or text, dates and notes, delete formatting, hyperlinks, and Booleans in your Google spreadsheet.

Video: How to remove blanks in Google Sheets

Before you start

The Clear tool also handles cells in filtered & hidden rows & columns.

How to clear data

Run the Clear tool

  1. Go to Extensions > Power Tools > Start to open the add-on in Google Sheets:
    Start Power Tools.
  2. Click on the Clear icon on the add-on sidebar:
    Click the Clear icon on the Power Tools sidebar.

Clear data by type

Select the range with your cells and tick off the checkboxes next to the data types you'd like to remove under the Clear cells group:
Clear various types of data from the selected range.

  • Select the Clear all option to erase all types of data including images and formatting in the selected range.
  • Choose to Clear formatting to reset formatting to default in all cells of interest.
  • Check the Clear numbers option to remove all cells formatted as numbers in the highlighted range.
  • Clear hyperlinks will unlink all cells with hyperlinks within the range.
  • Tick off Clear formulas to get rid of all formulas in the selected range.
  • Use Clear dates to remove all date-formatted cells.
  • Clear notes allows you to delete all notes in the range in one click.
  • Select the Clear text option to delete all cells formatted as text in the range.
  • Decide to Clear booleans to remove all TRUE and FALSE boolean values from the cells.
  • Have all Data validation rules removed from all cells in the selection using the Clear validation option.

Press Run and all the data types you picked will be removed from the range of your choice.

Delete empty rows, columns & cells in Google Sheets

The options from the 2nd group let you remove empty rows and columns from Google sheets. Tick off the checkboxes next to whatever you want to delete:
Choose to delete empty rows and columns.

  • Remove all empty rows
    Pick this option to delete all blank rows within the data in your sheet.
  • Remove all empty columns
    Similarly, choose to delete all blank columns within your data.
  • Remove unused rows
    Once your table is a complete set of data, you can get rid of all empty rows under it by checking this box.

    Tip. Deleting unused rows helps to make your spreadsheet smaller and contributes to processing data faster and easier.
  • Remove unused columns
    The same way you can delete all empty columns at the end of your data.

    Tip. This option will lighten your spreadsheet and make it easier to process your data.
  • Remove from all sheets
    Have the tool remove all empty and/or unused rows and/or columns from the entire spreadsheet in one go.

Once the needed options are ticked off, click Clear and see the result.

Tip. You can quickly delete all empty and unused rows and columns right from the toolbar:
Remove blanks from the Smart Toolbar.

Remove blank cells & shift data up or left

The 3rd group lets you delete empty cells while shifting the data left or/and up:
Remove blanks, shift data.

  • Remove empty cells and shift up
    Use it to delete those single blanks scattered all over your table. All remaining data will be shifted up.
  • Remove empty cells and shift left
    This one will shift all data left after deleting all empty cells from your table.

Please contact us here

Our working hours:
Pacific Time (PT) 11:00 PM (previous day) – 2:00 PM
Central European Time (CET) 08:00 - 23:00
Eastern Time (ET) 2:00 AM – 5:00 PM
Central Time (CT) 1:00 AM – 4:00 PM
Pacific Time (PT) 11:00 PM (previous day) – 2:00 PM
Australian Eastern Daylight Time (AEDT) 6:00 PM – 9:00 AM (next day)
If you want to attach files (e.g. screenshots or log files), it will be possible right after you send this form and we automatically create a support ticket for you.