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.

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.

Responses

Hi, I was wondering how to remove an invalid response when using dependent drop down lists and data validation. I've created a 4 dependent drop down lists for four Columns in my spreadsheet; however if I choose a different response in the first dependent drop down lists the invalid response are still populated in the other 3 dependent drop down lists. Is there a way to automatically have the cell clear if the response is invalid?

Hi Alan,

Thank you for your question. Unfortunately, our tools cannot help you with this particular task.

Hi,
I am new in using spreadsheets. I use it to document the mark list of my students. Each column carries marks of each subjects and one carrying the total marks. So if I cancel one column, can I automatically change the values of total marks column? Or should I edit each pupil’s total marks separately? Need help

Hi guys

I have run a merge and combine but I'm now left with a ton of rows that I don't need in my spreadsheet. They all have some data in them but one column is empty for those that I don't need.

Is there a way to have power tools delete all rows that have a specific empty field rather than me manually going in there and doing it myself?

Thanks!

Katerina Bespalaya (Ablebits Team) says:
March 17, 2020 at 7:49 pm

Hi Sarah,

If you can share a small sample spreadsheet with your source data and the expected result with support@apps4gs.com, we'll try to find a solution to your task. If you have confidential information in your table, you can replace it with some irrelevant data, just keep the format. Please also shorten your table to 10-20 rows.

Thank you.

Jonathan Gentry says:
December 27, 2019 at 3:55 pm

I am looking for a script that will clear cell(s) when the value of another cell changes or is equal to ("X").

I would like when Column "G" gets checked marked (TRUE), then Columns "D" & "E" become cleared (or no value) according to the respective row that was checked (TRUE).

For Example:

When "G3" is checked (TRUE), then D3 & E3 are cleared. It is important that both columns get cleared out as it affects Columns "E", "F", and "H" which all include very important information that needs to be automatically updated according to Column "G" to remove as much human error as possible.

Hello Jonathan,

Thank you for contacting us. I'm sorry but our add-on won't be able to help you with this task. The thing is, it's currently impossible to run the Clear tool automatically. You need to run it each time you want to remove some values. Sorry.

Hello,
I am trying to select multiple rows in my sheet and use the clear function, the problem is that only one row is cleared. How do I clear multiple rows at one time?

Hello Steve,

Thank you for reporting a problem to us.

For us to be able to help you better, we need more details on the issue. Please send us the screenshot of the options you select in the add-on to support@ablebits.com.
Also, if it's possible, please share your spreadsheet with us at support@4-bits.com. If you have confidential information there, you can replace it with some irrelevant data, just keep the format.

This will help us understand the problem better.
Thank you.

I want to know how to clear the numbers in a spreadsheet, but not remove the formatting. It's a day by day spreadsheet that I was using as a grade average calculator for my school grades for the first semester, and I want to use it again for the next one, so I made a new folder and copy pasted the formatting into the new one, except the same numbers are still there and the spreadsheet had a lot of conditional formatting that I don't want to erase, I just want to clear the numbers and data and stuff, so that when I re-enter the data day by day, the formatting will still be there.

And also, it's on my school laptop so I can't add any add-ons because they are blocked by the school

Hello, Svara,

Thank you for contacting us and for your question.

When you delete numbers from cells manually, you clear values only, not the formatting.
The same goes for conditional formatting - it is managed via the corresponding option in the Google Sheets menu. Deleting the value from the cell doesn't remove your conditional formatting rules. As soon as the value is entered back, the rules and formatting are back to play.

If you can't use our add-on, you could apply Filter by condition to leave only numbers visible and then select them all and delete.
Another way I can think of is using Google App Script, however, to out regret we won't be able to help with that.

If you have any further questions, please contact us at support@ablebits.com.
Thank you.

Post a comment

Seen by everyone, do not publish license keys and sensitive personal info!

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.