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.

How to clear data

Run the Clear tool

  1. Go to Add-ons > 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.

    Tip. You can pick Add-ons > Power Tools > Clear on the first step to see the tool's options right away.
  3. 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:
    Clear various types of data from the selected range.

    • Select the Clear all option to erase all types of data 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.

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

    Remove empty rows and columns

    The options from the bottom block let you remove empty rows and columns. You can use them with or without the Clear options. 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.
    • 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.

Responses

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.

Reply

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

Reply

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.

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?

Reply

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.

Reply
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.

Reply

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.

Reply

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!

Reply
Katerina Bespalaya 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.

Reply

Ask a question (posted publicly)

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.
Privacy policy Terms of use Contact us

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.