Power Tools: 40+ add-ons for Google Sheets

Start Power Tools

Start the add-on from Extensions > Power Tools > Start:

You will see the add-on pane with all toolsets on the right of your spreadsheet.

Choose the action

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.

   

   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:
   

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:

   

   • Find and remove duplicate rows in one spreadsheet.
   • Compare sheets for duplicates or uniques.
   • Compare two columns on one sheet for dupes or uniques.
   • Find and remove duplicate cells in one spreadsheet.
   • Combine duplicate rows and merge all related data together.
   • Compare sheets cell by cell for differences.
   • Quickly find and remove duplicate rows within just one step.
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.
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.

   

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

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:
   
2. Go to the Favorite tools tab and you'll see the action saved there.
   

To remove the action from favorites, click that star icon again.

Power Tools extra options

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

• 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:
  
  • 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.