Help & Support
Send Feedback
Power Tools
A set of quick solutions for daily tasks in Google Sheets: change case, sum values by color, remove duplicates and extra characters, split text to columns by delimiters or position, and many more.
www.ablebits.com
95,760 users
Works with
Install
Overview
Power Tools add-on cuts the clicks on repeated  tasks and brings features for  organizing and unifying data in Google Sheets. 

Extra spaces, formulas and data in the wrong format are among many of the issues the add-on deals with:
-	Take advantage of the new smart toolbar with handy assistants: insert or delete cells and shift adjacent data, change case, or swap values.
-	Click once to AutoSum numbers in every row or column; sum and count cells by color.
-       Search all selected sheets for certain values, formulas, notes, and hyperlinks; see a list of found entries, export or replace them in a click.
-	Get "Text to columns" feature: split values in a column by any delimiter,  string, or by position.
-	Automate some routine tasks: add or remove text by position, transform formulas, change formula reference type, export range to XML or JSON.
-	Bring order to your sheet: delete unwanted characters, types of data, and clean up formatting.
-       Get every needed data tool in one place: remove duplicates or bring them to one row, compare and merge sheets, randomize data, and much more.

The add-on keeps history of the recent operations at hand so that you can quickly re-apply a set of actions to a different range. You can also star the most frequent features to have access to your favorites at any time.

When you import data from different sources or share the same spreadsheet with colleagues, it becomes critical to bring it in order in the shortest time possible. Even such simple tasks as deleting all notes from your sheet can waste a lot of time. Power Tools helps you do this in a click. Here is a full list of tasks it performs:

➊ Smart toolbar offers a set of one-click actions
- Advanced find and replace in all selected sheets
- Insert cells and shift data down or right
- Delete cells and shift data up or left
- AutoSum all numbers in columns or rows
- Sum by fill and text color
- Flip values in adjacent cells, rows, or columns with or without their format
- Change case
- Freeze or unfreeze top rows and left columns

Main tool sets group solutions by type:

➋ Remove unwanted data
- Delete leading and trailing spaces 
- Reduce spaces between words to one
- Remove html entities, non-printing characters, and delimiters
- Get rid of any character or string in the range
- Delete characters by position, or those that follow or precede certain text
- Remove empty or unused rows and columns

➌ Clear data by type
- Erase any combination of data types from the selected range: formatting, numbers, dates, booleans, notes, text, or hyperlinks 

➍ Work with text
- Capitalize each cell or word, change case to uppercase or lowercase
- Add your text at the beginning or at the end of the selected cells, before or after certain text, or at any position you like
- Replace accented characters with non-accented, turn codes to symbols and back

➎ Convert data format
- Convert text-formatted numbers and dates to numeric and date format
- Change number sign
- Export range to JSON or XML

➏ Try out the formula tools
- Switch formula reference type from relative to absolute or vice versa
- Get values instead of the formulas
- Automatically add functions to all selected columns
- Apply new calculations to all formulas at once

➐ Split tool set
- Separate values by any character or string
- Split text by position 
- Extract name parts into separate columns

➑ Data tool set
- Identify duplicate records in any combination of columns on your sheet
- Compare two columns or sheets to find duplicates
- Join values in rows, columns, or the entire range and keep cells unmerged 
- Merge sheets by a key column
- Shuffle values in rows, columns, or the entire range
- Generate random numbers, dates, booleans, custom lists, or strings
- Combine duplicate rows, add subtotals, and bring related values together

Try out all the *PRO features that you can still enjoy in the coming paid version of Power Tools.

If you have any questions about the add-on, please post them in our community: http://goo.gl/5aqwsD
We'll be happy to assist!
Reviews
March 23, 2017
I only downloaded this addon because it claimed to be able to sum and count by color. I tried using it and the function failed!!! You really should validate your functions before advertising your Power Tools
Submit reply
Cancel
April 3, 2017
Hello Robert, Could you please share some details about your task and the steps you followed? Please also make sure the spreadsheet has a locale set in spreadsheet settings. If you can email us at gapps.ablebits@gmail.com, we'll do our best to assist you.
March 17, 2017
I mainly use the Advanced Find and Replace, but I love it...although it now tells me every time I open it that an update is available here, but there is no instruction/button for updating it...
Submit reply
Cancel
April 3, 2017
Hello Rie, We really appreciate your kind words! The add-on is updated automatically, please send a screenshot of the message you see to gapps.ablebits@gmail.com, it will help us understand why you see it.
March 12, 2017
Good, help alote. Can this add-on runs automatically when a certain cell is edited?
Submit reply
Cancel
April 3, 2017
Thank you for your feedback, Jacky! I'm sorry, but the add-on can't run automatically at the moment.
February 9, 2017
It looks great but I have a issue. Range of 7 cells, 1 red, 1 gray, 1 yellow and 4 clear (white). Count of yellow gives 1, count of white gives 4, but sum of count yellow and white gives ... 10! =COUNT(valuesByColor("#ffffff", "#000000", C13:I13)) + COUNT(valuesByColor("#ffe599", "#000000", C13:I13))
Submit reply
Cancel
March 6, 2017
Hello Robert, Thank you for your feedback. I'm sorry, but we haven't provided for a way to process more than one color at a time, so you need to enter one formula for each color you want to count. The only way to combine the results now is to have a master formula that will sum up the results by each color. We will consider embedding this possibility in one of the future versions of the add-on.
January 28, 2017
Doesn't work at all on Chrome 55 on Windows 10, OR Chrome on Mac OS. When you click Add-ons -> Power Tools -> Start, the side bar appears, but is completely blank/empty. Tried refreshing the page, didn't fix it. Tried disabling my ad blocker, didn't help.
Submit reply
Cancel
February 2, 2017
Hello J Smith, This issue was caused by LastPass Chrome extension. If you have it, please make sure you update it to version 4.1.39. If you don't, could you please specify what other browser extensions you have and see if other add-ons load correctly at the same time? We'll do our best to assist you.
February 1, 2017
It was great, but now I'm expereincing the blank sidebar as well. Windows 10 Google Canary via Chrome 58.0.2993.0 However, when I open Chrome in verson 55 it works as expected.
January 30, 2017
Hello Jeffrey, I'm really sorry about that. Could you please specify if you have LasPass Chrome extension? If you do, please try to disable it and start the add-on, it should work correctly.
January 29, 2017
Same issue here!
January 29, 2017
It used to work but today I have the same issue. It's completely blank. I have even uninstalled and reinstalled, and tried using an incognito browser. Nothing helps...
January 20, 2017
Grateful for this intuitive tool! I use these features on a regular basis. Couldn't do my job without it.
Submit reply
Cancel
The BEST !!
Submit reply
Cancel
January 7, 2017
Great tools offered!
Submit reply
Cancel
November 11, 2016
Search result doesn't sort sheets in alphabet order. but your service "Advanced Find and replace" did.
Submit reply
Cancel
I can't freeze panes...
Submit reply
Cancel
October 5, 2016
Hello Septhian, Could you please make sure you select the cell below the rows and to the right of the columns you'd like to freeze before clicking the icon?
1-10 of 50
Additional information
Version
39
Updated
May 22, 2017
Search
Clear search
Close search
Google apps
Main menu