Power Tools: 40+ add-ons for Google Sheets

Power Tools is a collection of 40+ add-ons for Google Sheets. They help speed up most of your daily tasks, both complex and simple, enhancing your performance and saving your time. Get help with cleaning up and organizing data in your Google Sheets. Pick the group below to learn the tools the add-on has to offer.

Video: Intro to Power Tools

Start Power Tools

Start the add-on from Extensions > Power Tools > Start:
Find Power Tools under the Extensions menu.
You will see the add-on pane with all toolsets on the right of your spreadsheet.

Tip. You can make Power Tools open its sidebar in each spreadsheet automatically by pinning the button at its very bottom:
The button designed to auto-start Power Tools.
Please bear in mind that this doesn't open any specific add-on. It simply opens Power Tools so you could proceed with the desired add-on right away.
Tip. Select the range of cells you want to process in your sheet before running the add-on or any time before clicking any action button.

Choose the action

Power Tools: 30+ add-ons for Google Sheets.

  1. Smart Toolbar is a set of one-click tools for the most common tasks:
    • Advanced Find and Replace to search, substitute, or export values, formulas, notes, hyperlinks, and errors in any selected sheets.
    • Sheets Manager to easily handle multiple sheets in your spreadsheet.
    • Function by Color to sum, count, and calculate in other ways different cells based on their colors.
    • Sort by Color to sort the selected range or the entire sheet by their font or fill colors.
    • Change Case to switch between different capitalizations of the text.
    • Flip to exchange records between columns, rows, or cells.
    • Remove to get rid of all blank and/or unused rows and columns on the sheet.
    • Freeze to lock top rows and left columns in your tables.
    • Calculate to quickly insert formulas with the most popular functions for multiple data ranges.
    • Unmerge to divide combined cells into multiple separate cells.
    • Unpivot Table to transform your cross-tab table and get columns as row values instead.

    One-click utilities of the Smart Toolbar.
    The majority of these utilities from the Smart Toolbar are also available straight from the Google Sheets menu — Extensions (or Add-ons) > Power Tools > Quick actions:
    Quick actions in Power Tools.

  2. Dedupe & Compare group contains add-ons that scan your data for duplicates, uniques, and their 1st occurrences. It's up to you to decide whether to color, remove, copy, or move what's been found. Use 5 add-ons to:

    Dedupe & Compare in Power Tools.

  3. Merge & Combine group is equipped with 5 utilities to bring data together:
    • Merge Values transfers values within rows, columns, or a range of cells and separates them with a delimiter of your choice.
    • Merge Sheets matches data in common columns of several spreadsheets and updates your main table with the corresponding records from other lookup sheets one by one.
    • Combine Duplicate Rows searches for duplicate entries in the columns you select, merges corresponding uniques and calculates subtotals.
    • Combine Sheets joins data from multiple sheets into one sheet preserving column headers.
    • Consolidate Sheets also joins data from multiple sheets into one but also calculates all numbers using the most common aggregate functions.

    Merge & Combine in Power Tools.

  4. Split Text from one cell into multiple cells by delimiters, strings, or position. If you work with dates, use Split Date & Time to separate date and time units from one another. Split names to get each name part separately. Or split one sheet into multiple sheets by column values or N rows.

    Split in Power Tools.

  5. Process your data using 6 powerful one-step add-ons:
    • Filter & Extract Data filters values based on multiple criteria and extracts all matching data as a formula or as copied values.
    • Function by Color calculates cells of the same color using 13 formulas.
    • Advanced Find and Replace searches and substitutes in values, formulas, notes, hyperlinks, and errors.
    • Sheets Manager lists all your sheets in one place and lets you quickly handle several tabs in one go: hide multiple sheets, protect, reorder, etc.
    • Fill Blank Cells fills all empty cells in the selected range with your custom value or the value from the above, below, left, or right adjacent cells. It also knows how to autofill formulas.
    • Sort by Color sorts your entire sheet or only the selected range by fill or font colors they contain.

    Process data in Power Tools.

  6. With the Text group you can alter text in different ways:
    • Add text to cells by position.
    • Change case and replace special characters; polish all text by removing extra spaces, adding lost spaces after punctuation marks, and turning text into Sentence case — all in one click and with the help of the Modify tool.
    • Use the options from the Remove group to get rid of unwanted characters and their substrings, excess spaces and delimiters.
    • Split cells by different chars or positions.
    • Divide full names to their name units.
    • Extract text, numbers, links, email addresses.
    • Find & manage multiple substrings: replace, remove, extract, add.

    Text toolkit in Power Tools.

  7. The Clear tool will remove different types of values from your spreadsheet, from dates and notes to formatting and hyperlinks. It also removes blank or unused rows and columns.
  8. Randomize features 2 helpful add-ons:
    • Random Generator to generate strong passwords or strings by mask or character sets, create lists of random dates and numbers, or use your own lists of values to mix up.
    • Shuffle to rearrange records in rows or columns in no particular order.

    Randomize in Power Tools.

  9. Tweak multiple formulas in your spreadsheet in a blink of an eye: convert relative cell references to absolute ones or even their values. Alter all selected formulas according to the pattern you set or auto-apply 12 most common functions to all selected columns.
  10. Last but not least, you will convert text to numbers or dates, change the number sign, and export the data to JSON or XML formats.

Recent & Favorite tools

When you run simple one-step tools such as Split Text or Add Text, or save scenarios for such add-ons as Remove Duplicates or Merge Sheets, these operations appear under the Recent tools tab on the main pane:
See recently used tools in Power Tools.
You can click any of these records to run the action again quickly without opening the required tool.

Since Recent tools display only a limited number of recently run add-ons, the oldest actions will sooner or later disappear from the list. To keep some of them always at hand, you can save them to Favorite tools:

  1. Hover your mouse over the necessary recent action and click on the star icon that appears next to it:
    Add the tool to favorites.
  2. Go to the Favorite tools tab and you'll see the action saved there.
    Find the action under the Favorite tools tab.
Tip. All scenarios for Remove Duplicates and Merge Sheets automatically fall into both tabs, Recent and Favorite tools.

To remove the action from favorites, click that star icon again:
Remove the add-on from favorites.

Power Tools extra options

At the bottom of the Power Tools pane you will see more buttons:
Extra buttons.

  • The first button is always pinned by default. It makes Power Tools run automatically in each spreadsheet you open up. Of course, it's up to you whether to leave it be this way.
  • If you haven't subscribed to the product yet, the key icon will hint how many trial days you have left. Click this key to either order a subscription or activate your key.
  • Next comes our logo. Hover your mouse over it and you'll see the version of the add-on that is uploaded to Google servers. Click on the logo to open the page where you can access the detailed description of all our add-ons for Google Sheets.
  • The icon with three lines gives you other possibilities:
    More Power Tools options.

    • Order a new subscription or activate your existing one.
    • Make use of the knowledge resources such as help pages, a list of known Google Sheets issues that may limit the tools' capabilities at some points, and the list of new features and improvements that appear in the collection with each version.
    • You can also reset all properties (revert all checkboxes and other settings you may have used in the collection to their default state) and enable/disable the auto-start at any time.

Next steps with Power Tools

  • Wonder where to get even more info about the add-ons, installation, ordering, and activation? We've gathered all pieces in the Power Tools for Google Sheets category in our Docs section.
  • Want to improve your overall Google Sheets skills? Come find a variety of useful articles on our free Ablebits blog.
  • Have something to ask, the feature to request, or the feedback to share? Contact our team directly by using the form at the very bottom of this page or emailing support@ablebits.com.

Responses

Thomas Lott says:
December 1, 2024 at 5:01 am

How do I add/import a power query from an Excel spreadsheet? I saved an Excel spreadsheets as a Google Sheets file. This spreadsheet originally had power queries in it, but they seem to have been removed when I saved the file as a Sheets file. I would like to get them back into the Sheets file, but I can't seem to find a way to pull or copy the power queries back into the file from the original Excel file.

Is there a limit to the number of power queries that a Sheets file can have?

A few of the power queries in the file extract data from an Azure Cloud database. Is this possible to run this type of power query in Google Sheets?

Zach says:
November 12, 2024 at 3:08 pm

How does the merge sheet function work with filters? I attempted to merge data from a sheet I had filtered but the data that was imported had errors. I filtered again, copied the data to a new sheet and used that to merge to resolve, but the excel companion seems to not require this extra step of copying filtered data before merging.

Thanks

Katerina Bespalaya (Ablebits Team) says:
November 13, 2024 at 10:17 pm

Hello Zach,

Please note that the add-on doesn't ignore the data hidden with a filter, not with a formula. If you use the FILTER function, the formulas won't be transferred to the resulting sheet.

You can send us screenshots of the errors you get by email, support@ablebits.com, and specify what filter is applied to your tables. We'll look into the issue further.

Lena says:
May 24, 2024 at 1:23 pm

Hi there,
Is it possible to use Power Tools in a shared document?

I installed it with my account and it worked fine on a document that was accessible only for me. But when I try to replicate the (color-based) formula in a sheet that's shared between me and my coworkers, I get the following error: "Unknown function: 'valuesByColor'.

Please advise.

Cheers,
Lena

Maryia Vehera (Ablebits Team) says:
May 24, 2024 at 8:35 pm

Hello Lena,

Thank you for the question.

Yes, it is possible to use Power Tools in shared documents and your colleagues will see the results of most add-ons. However, when you have the results as a formula using Combine Sheets, Consolidate Sheets and Function by Color, they may not load. In this case, all people from the shared document need to install & run the corresponding add-on to enable the custom functions from the formulas on their side. This may be necessary since we don't control the amount of data available to users in shared documents. This process is regulated on the Google side.

You might get this error if you are not the owner of the shared spreadsheet, and the owner doesn't have Function by Color or Power Tools installed. If this is not your case, please contact us at support@ablebits.com and we'll do our best to help you further. Thank you.

Chad says:
March 27, 2024 at 3:52 pm

Just bought, and like so many others, it's not showing as available anywhere in the Extensions despite your download page claiming it's been added. Extremely frustrating and I can see this issue goes back quite a while. Do you have a fix for this yet?

Daria Markelova (Ablebits Team) says:
March 29, 2024 at 1:47 pm

Hello Chad,

Thank you for your comment. Please note that we don't manage the Extensions menu. Please check the following:
1 - If you're not the owner of the spreadsheet, please make sure that you were granted with the rights to edit the file.
2 - If there is the Excel format (xls, xlsm, etc.) next to the file name, please convert the document to a spreadsheet. Just go to File > Save as Google Sheets. The spreadsheet will be converted, and the add-on menu will appear then.
3 - In case you open the file under your work account and none of the above solves the issue, please contact the administrator of your organization to check if add-ons from Google Workspace Marketplace are prohibited on the domain level.
4 - If you face the issue under your personal account, please send us a screenshot illustrating the problem to support@ablebits.com.

Nico Mallo says:
February 26, 2024 at 10:49 am

I have purchased power tools as I am using it for most of my tasks in work. After purchasing, i got an activation key but after copy and pasting it to activation, it says invalid or wrong code. Then now, power tools is now unable to install on my google spreadsheet. Tried several uninstall and install but still the same.

Daria Markelova (Ablebits Team) says:
February 26, 2024 at 3:20 pm

Hello Nico,

Thank you for the comment. Sorry for the difficulties with installation, there's some problem on the Google side that's directly impacting all Workspace add-ons. The issue has occurred only today, and our team is actively investigating it.
The problem has already been reported to Google, so please follow the link and click "+1" at the upper right corner. It will let Google know how many people are affected.

Regarding your activation key, please email us at support@ablebits.com with your key, we will check it in our database and see what we can do.

Anastasia says:
March 2, 2023 at 4:06 pm

Hello support team,

I've made a purchase on 3/1/2023 for 29.95$. The money were withdrawn from my visa but afterwards I received the note: " Your purchase was declined. Please, enter correct visa number". Since then neither money was returned, nor tool access provided.

Please, get back to me asap.

Katerina Bespalaya (Ablebits Team) says:
March 2, 2023 at 7:13 pm

Hello Anastasia,

Thank you for your message. Sorry to hear that you're having difficulties with the purchase.

For us to be able to help you better, please send us any proof of the charge withdrawn from your card at support@ablebits.com. If you've got an order number when making the purchase, please send it to us as well. We'll contact our e-commerce service 2Checkout to clarify the issue. Thank you.

Gideon says:
January 22, 2023 at 4:44 pm

Hello, I do not want to give access to my whole Google Drive. I want to limit the tools' access to only the spreadsheets that I need it for. How can I limit the access?

Steve says:
November 17, 2022 at 3:31 pm

I've downloaded Power Tools 30 day trial and it shows in Extensions but is faded and will not let me click on.

Daria Markelova (Ablebits Team) says:
November 17, 2022 at 8:39 pm

Hello Steve,

Thank you for your comment. Sorry to hear you've faced difficulties of this kind with our add-on.

Please note that there is currently an issue on the side of Google that occurs when you are logged in under more than one account. Please sign out and log back in only under the account you used to grant permissions to the add-on and see if the issue persists.

If this doesn't help, please email us at support@ablebits.com.

Lorie says:
January 19, 2022 at 7:35 pm

I constantly get the message to purchase this on my screen EVERY time I open GD and files. I do not want this to pop up everytime and I can't figure out how to get rid of it. Help.

Joel Johnson says:
January 10, 2022 at 4:48 am

Is there a way to use the Power Tools in a macro?
Also, do you have a Power Tool that forces google sheets to refresh the ENTIRE sheet, not just TIME, DATE, etc. ?

Katerina Bespalaya (Ablebits Team) says:
January 10, 2022 at 1:16 pm

Hello Joel,

Thank you for your questions.

I'm sorry but our add-on can't be run from a macro.

However, please note that there are scenarios available for Remove Duplicate Rows, Compare Sheets, Combine Duplicate Rows and Merge Sheets: you can save the settings you use most often into scenarios and run them in a couple of clicks. One-step operations can also be run quickly from the Recent/Favorite actions. And Combine Sheets & Consolidate Sheets can combine data using a formula that will auto-reflect all changes made in source sheets.

As for your second question, could you please give us more details? What do you mean by refreshing the ENTIRE sheet? Thank you.

Aziz says:
November 17, 2021 at 9:18 pm

Hello,

Is it possible to automatically combine duplicated rows? Instead of manually doing it every time. We do have spreadsheet where all orders come automatically. Once we receive a lot of orders, we have to manually duplicate rows. Is it possible for example to set up fixed time day or two times a day and merge combine duplicated rows?

Katerina Bespalaya (Ablebits Team) says:
November 18, 2021 at 11:40 am

Hello Aziz,

Thank you for your question.

If you are using the Combine Duplicate Rows tool, I'm afraid it's currently impossible to set up a certain time for the add-on to be run. You need to run it manually each time you want to combine duplicate rows. But I will forward your request regarding trigger support to our developers for consideration.

At the moment you can use scenarios as an alternative. You can save the settings you use most often and give this set a name – this is your scenario. Once you run it, your sheet will be processed right away, and you won't need to go over the same steps again. You can view, edit, and delete scenarios quickly anytime. Feel free to check the detailed instructions on how to work with scenarios on this help page.

If you have any other questions or need further assistance, please email us at support@ablebits.com.

Armen says:
October 22, 2021 at 10:37 am

Good afternoon.
Do I understand correctly that if I buy two "Power Tools add-on for Google Sheets, 12-month subscription" licenses, I will receive two activation codes for 12 months for my two different accounts (regardless of which computer they will work on) ?

Daria Markelova (Ablebits Team) says:
October 22, 2021 at 11:24 am

Hello Armen,

If you purchase two Power Tools subscriptions at a time, you will receive 1 activation key for both subscriptions. This key will allow you to activate the add-on under 2 different Google accounts. If you need 2 different activation keys, please make separate orders.

In case you have more questions, please don't hesitate to email us at support@ablebits.com.

Gabriel says:
May 5, 2021 at 9:34 am

Hi, I want to compare prices (from monthly prices catalogue) to same destinations from different companies (some have got the destinations and some don´t). I want to see a column with the destinations that operate both companies and their prices (so I can check which destination is cheaper between both companies)
Is there any way to do that?
Thanks in advance!

deborah says:
February 17, 2021 at 8:11 pm

How to uninstall the addon, I don't use it anymore and there is nothing in my addons > Manage addons section.

If I go to power tools it wants me to install, but its in the addons dropdown and keeps telling me that my free trial has expired.

Can't work out how to get rid of it.

Etienne says:
August 10, 2020 at 8:48 am

Hi
Want to be able to show other 2 R600,00 paid by client in rows under merge and combine. The Total is correctly calculated.

****** 30/05/2020
****** 02/06/2020
R800,00 20/07/2020
R600,00 29/07/2020 R2600,00

Spela says:
April 7, 2020 at 6:26 pm

Hi

Please inform me, if I can merge duplicates by last date inserted?

Thanks

Ben says:
August 8, 2019 at 5:51 pm

I see that I can mark my favorite actions with a star to have quick access to them in Favorite Tools bin at the bottom of the sidebar, but I don't know where I would add a star to do that. I also don't see any tools listed in the Recent Tools bin. How can I take advantage of those?

Mary Trifuntova (Ablebits Team) says:
August 8, 2019 at 6:00 pm

Hello Ben,
Thank you for your comment.

I'm sorry, but only simple one-step operations can be added to the list of Recent and Favorites tools. You see, merging sheets or removing duplicates, for example, involves 4 or 5 steps that have different settings depending on the spreadsheet where you run the add-on. Thus only one-click tools can be found in the Recent tools list where you can mark them with a star and add to your Favorites list.

Melany says:
August 2, 2019 at 10:41 pm

I need to generate 2500 unique 3 string uppercase alphanumeric codes. Can not duplicate even in a different order (example: M8V, V8M). Only using A-Z and 1-6. Is there a code for this? Thank you.

Amanda says:
June 5, 2019 at 10:34 pm

Hello, is there a way to get Power Tools to run automatically when a new row of data is added?

Natalia Sharashova (Ablebits Team) says:
June 6, 2019 at 7:00 am

Hello Amanda,

I'm afraid not.
All tools should be run each time you want to process the data. Some one-click operations (except the wizard-based add-ons from Merge & Combine, Dedupe & Compare, and Randomize) can be started quickly from the Recent/Favorite tools tabs at the bottom of Power Tools.

Giang says:
May 17, 2019 at 6:08 pm

Hi ,

We purchased a lifetime subscription on 05.18.19 but today, we were told that our TRIAL period has expired and being prompted to purchase.

Please advise.

Natalia Sharashova (Ablebits Team) says:
May 17, 2019 at 6:16 pm

Hi Giang,

You see, there's an issue on the side of Google that occurs when you are logged in under more than one account. This may force the tool to lose your key. Unfortunately, we can't control or bypass this issue, so I kindly ask you to sign out and log back in only under the subscribed account.
Then please try to unlock the tool again using your activation key.

If this doesn't help, please email us to support@ablebits.com. Thank you.

Pete Fisher says:
May 17, 2019 at 2:22 pm

If I purchase a lifetime subscription and I have two Google accounts used for different personal business, can I use the same product key to activate Power Tools on both?

Katerina Bespalaya (Ablebits Team) says:
May 17, 2019 at 2:54 pm

Hello Pete,

Thank you for your question.

Please note that each subscription is account-based, meaning it can be activated under one Google account at a time. If you want to use the add-on under two accounts, then you need to get two subscriptions. To obtain multiple subscriptions with one payment, simply change the number of required licenses on the purchase order page.

Alternatively, you can transfer the subscription from one account to another when it is necessary: https://www.ablebits.com/docs/gsuite-add-ons-licensing/#transfer-subscription

Please let us know if you have any other questions.

sarra.c says:
April 29, 2019 at 1:44 pm

hello, I am having some problems merging sheets. i have been following the same instructions but when I changed the document only a few cells would be updated. I deleted all formulas, checked the content, deleted all duplicated but nothing worked. Any suggestions?

Alleyne Warner says:
February 13, 2019 at 11:43 pm

Hey guys,

Do you have something that will keep a Google Sheet sorted on a specific Column?

I'm seeing some formula stuff that does this but I'm lazy and want to point and click :)

Thanks in advance!

Alleyne

Mary Trifuntova (Ablebits Team) says:
February 14, 2019 at 11:46 am

Hello Alleyne,

Unfortunately, we do not have such an add-on in our Power Tools. However, it looks like the standard Sort Range option available in Google Sheets or the =SORT function may be helpful.

Please let us know if there is anything we can help you with.

Warang says:
February 7, 2019 at 9:08 am

Hi,

I've just add on the Power Tools for testing with my Google Sheet. I need to remove the empty row but nothing happen. What should I do? Thanks

Nathan Merwitz says:
November 18, 2018 at 1:45 am

If I click the add-on tab, I can see the Help button on Power Tools, but I cannot see the Start button.

How do I see the Start button?

LX says:
November 9, 2018 at 9:30 pm

Hi ,

We purchased a lifetime subscription on 05.24.18 but today, 11.09.18, we were told that our TRIAL period has expired and being prompted to purchase.

Please advise.

Howard Marx says:
October 6, 2018 at 5:23 pm

Madam/Sir,

On March 12, 2018, I paid my yearly subscription in full for USD $29.99 (reference Number: YK5GRV) and today (October 6, 2018), the Power Tools is asking me to either buy now or enter a key. Why?

What is going on?

ZANDREA Rox says:
October 5, 2018 at 10:43 pm

Hello
I used power tools to create this formula: =COUNTA(valuesByColor("#ffffff", "#000000", '10121685 PENDING RELEASE'!V7:V12))

and it's definitely returning incorrect results. Can you please advise why?

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.