A set of 30+ solutions for daily tasks in Google Sheets.
If bringing your spreadsheets to order in the shortest time possible is your goal, Power Tools is a must-have. It cuts the clicks on repeated tasks and enhances your Google Sheets with features that organize and unify your data.
Among its add-ons you'll find those that remove duplicates, compare and vlookup sheets, import ranges from multiple sheets, merge cells, find and replace data, sum and count colored cells, generate numbers and other records. Text toolkit utilities are also included and let you change case, add and remove strings and characters, trim whitespaces, and split text to columns.
All the tools are divided into 10 different groups based on their main purpose:
➊ Smart toolbar offers some one-click actions. The main ones are:
- Advanced find and replace. Search in values, formulas, links, errors, and notes
- Sum and Count colored cells (other functions included)
- Change text case: capitalize first or all letters, apply sentence and lowercase, toggle text
Other toolbar instruments let you:
- Remove empty rows and columns
- Flip data
- (Un)freeze top rows and left columns
- Unpivot tables
- Unmerge all or selected cells
- Auto calculate all numbers
➋ Dedupe and Compare data:
- Find and remove duplicates in any combination of columns in your sheet
- Compare two columns or sheets to find duplicates
- Highlight duplicates or uniques, copy or move them to another location
- Identify dupes in a status column
➌ Merge and Combine sheets:
- Merge cells in rows, columns, or the entire range and keep all records
- Merge two Google sheets by key columns: vlookup data from another table, considering text case and ignoring blanks
- Combine duplicate rows and merge unique data referring to the same record
- Combine data from multiple sheets into one based on column headers
➍ Work with text:
- Capitalize each word or cell, change case to uppercase or lowercase, toggle and polish text
- Add text by position:
1. At the beginning or at the end of the selected cells
2. Before or after certain text
3. At any other position you need
- Remove unwanted data:
1. Get rid of any characters or strings in the range
2. Remove line breaks, commas, and any characters or strings in the range
3. Remove characters by position, or when they follow or precede certain text
4. Delete leading and trailing spaces
5. Reduce spaces between words to one
6. Remove HTML entities and tags, non-printing characters, and delimiters
- Split cells in different ways:
1. By any characters
2. By capital letters
3. By strings
4. By position
5. Separate names by name units
- Replace accented characters with regular, smart quotes with straight ones, turn codes to symbols and back
➎ Process data with one-step tools:
- V-lookup multiple matches based on multiple criteria
- Sum and count cells by their color
- Find and replace in values, formulas, links, errors, and notes
➏ Split toolset:
- Separate values by any character or strings
- Split text by position
- Separate first and last name in Google Sheets, extract titles and other name parts into individual columns
- Split one table into multiple sheets by columns.
- Shuffle values in rows, columns, or the entire range
- Generate random numbers, dates, booleans, custom lists, or strings
➑ Try out the formula tools:
- Toggle between relative and absolute cell references
- Convert formulas to values
- Autosum columns and automatically add other functions to all selected columns
- Add new calculations to all Google Sheets formulas at once
➒ Clear contents in Google Sheets by type:
- Erase any combination of data types from the selected range: formatting, numbers, dates, booleans, notes, text, or hyperlinks
- Delete all empty or unused rows and columns
➓ Convert data format:
- Convert text-formatted numbers and dates to numeric and date format
- Change number sign
- Export Google Sheets range to JSON or XML
Power Tools keeps the history of the recent operations at hand so you could quickly re-apply actions to a different range. You can also favor the most frequent features to have access to them at any time.
Or save the entire sets of options to find duplicates or merge two tables in a click.
30 DAYS OF FREE USE
Fully functional 30-day trial period. All payments are secure and include an unconditional 30-day money-back guarantee.
If you have any questions about the add-on, please post them here:
We'll be happy to assist!
Power Tools is a proud member of the Ablebits product family. We build add-ons that help you make the most of Google Workspace. Visit our official website to learn more:
***** V1.13 (5 May 2021) *****
★ New features ★
- Consolidate Sheets. Plus add-on to combine data with a formula! Have the result for SUM and COUNT returned as a formula to consolidate all existing and future records from the selected sheets. The formula is always connected to your original files: the resulting calculations will consider all changes and new values automatically. Make sure to visit the help page for more details.
- Scenarios are finally available for the following add-ons: Merge Sheets, Remove Duplicate Rows, Combine Duplicate Rows, Compare Sheets. Make sure to visit the related help pages for more details.
- Advanced Find & Replace:
1. Now you can delete rows with found values.
2. Preserve text formatting. If there are multiple text strings in a cell formatted in different ways, this option will let you keep all formatting upon replacing values.
1. Extract. Extract text, numbers, hyperlinks, URLs and email addresses from any part of the selected text.
2. Match case. Decide when to consider the text case with the corresponding checkbox in the 'Add', 'Remove' and 'Split' tools.
3. Replace symbols. Single smart quotes (‘’) are replaced with single straight quotes ('') as well.
- Remove Duplicate Rows, Compare Sheets, Quick Dedupe. New action with found values is available – delete entire rows from the sheet.
- Compare Sheets. If sheets of interest are in separate spreadsheets, you can add them for comparison from Drive straight from the add-on.
- Multiple VLOOKUP Matches. You can now look for cells that 'start with' or 'end with' particular characters or strings.
- Split Sheet. A new way to split – to multiple new spreadsheets.
- Convert. A compliment for users who work with Japanese texts: convert characters to Hiragana and full-width or half-width Katakana.
- Clear. The 'Clear validation' option will remove 'Data validation' from the selection leaving only values in cells.
- Consolidate Sheets: now it's possible to consolidate data from just one table.
- Merge Values: you can merge cells not only from neighboring columns but from multiple non-adjacent ranges as well.
- Function by Color: 'Refresh results', Merge Values, Advanced Find and Replace, Combine Sheets: significant performance improvements in processing large amounts of data.
- Text: fields where you can enter text strings now support such key shortcuts as 'ctrl+z', 'ctlr+y', 'ctrl+c', etc.
- Minor UI and UX improvements.
- Merge Sheets:
1. Step2 didn't load if the add-on was run from a file with one sheet with hidden columns.
2. Didn't change the name of the main sheet if the add-on was started from a chart sheet.
- Merge Values: the date and currency number formats changed after the merge.
- Combine Sheets: didn't work if you chose to preserve formatting but one of your tables contained headers with blank colored rows.
- Consolidate Sheets:
1. Allowed you to press the final 'Consolidate' button a few times making the tool throw errors.
2. Errored after pressing 'Cancel' in the notification message about the large selection.
3. In some cases, counted the amounts of data incorrectly, hence, didn't notify about the large selection and extra time needed to process these cells.
- Remove Substrings Individually: if substrings to remove were copy-pasted from the sheet, the add-on added [Line breaks] to remove them as well.
- Compare Sheets:
1. If the range was too small to process (1 cell), you could still proceed with the tool.
2. You couldn't change the range after pressing 'Cancel' in the pop-up notifications.
- Advanced Find & Replace:
1. Didn't search in hyperlinks if they weren't created by the corresponding function.
2. Couldn't find anything if the chart sheet was selected to search in as well.
3. Didn't export cells and rows with found errors.
- Split Names: didn't work with only 2 rows without headers.
- Multiple VLOOKUP Matches:
1. The formula missed the final argument making headers merge with data from the 1st row.
2. The formula didn't return results for 'Date time' cells if conditions were built for dates only.
3. The formula didn't work with big ranges that contained column BY.
- Split Sheet:
1. Ignored the range you selected before running the add-on and picked up the entire used range anyway.
2. Didn't process a new range selected before hitting 'Split'.
3. Couldn't create a new sheet if it was named after the record with 100+ chars.
- Clear: didn't work with 'Remove from all sheets' selected if the file contained a chart sheet.
***** V1.12 *****
★ New features ★
- Combine Sheets. One more way to combine data – with a formula! Have the add-on build a formula to join all existing and future records from the chosen sheets together. The formula is always connected to your original files. Hence, all source changes, all new cells, rows and columns will appear in the combined table automatically. Make sure to visit the help page for more details.
- Split Text. Two new options were introduced by your request:
1. Split values by capital letter.
2. Replace source data with the first result column.
- Add Text by Position. Add text to non-blank cells only using the 'Skip empty cells' checkbox.
1. Remove html tags. This feature removes all opening and closing HTML elements (e.g. <strong>, <br>, </li> ) from the text.
2. Replace smart quotes (“”) with straight quotes ("").
- Clear. Remove empty/unused rows/columns from all sheets at once.
- Convert number sign. An extra setting lets you 'reverse all number signs' in the selected range at once.
- The entire drop-down menu for 'Add-ons > Power Tools' was changed:
1. Instead of accessing groups of utilities as before, now you will instantly process selected data with quick tools of the Smart Toolbar.
2. The 'Refresh results' option of the 'Function by Color' tool is also in the menu so you could swiftly update the results of existing formulas.
- Merge Sheets: since you can add spreadsheets to merge from Drive, we made it possible to start the add-on even if there's one sheet in the file.
- Advanced Find & Replace, Text, Remove: all support 'Line break' now. Press 'Alt+Enter' on your keyboard to enter it manually or pick [Line break] from the drop-down list.
- Consolidate Sheets: automatically took the existing table as the custom location for the result.
- Clear: 'Clear all' now removes all images from cells as well.
- Split Text: 'Treat consecutive delimiters as one' will be also displayed in the 'Recent tools' tab after its usage.
- Random Generator: the limits for custom list size were expanded.
- Minor UX and UI improvements.
- All add-ons errored if your 'Data validation' rules prevented them from editing the cells.
- Function by Color:
1. COUNTA and AVERAGEA ignored cells with zero-length strings and whitespace.
2. Pulled all data used for counting to a sheet with the formulas if they differed.
- Merge Sheets, Compare Sheets: the 'Auto detect' button didn't revert matching columns to their default state after picking them manually.
- Consolidate Sheets, Combine Sheets:
1. Didn't accept the custom place for the result if entered from the keyboard.
2. 'Select range' didn't accept the correct range after the small one.
- Combine Sheets: accepted one cell as a range to combine.
- Dedupe & Compare add-ons couldn't process data if the range referred to columns only (e.g. A:D).
- Split by Position: didn't work if there were merged cells on the way.
- Remove Characters Individually: treated a set of characters copy-pasted into the field as a string rather than individual symbols.
- Clear Hyperlinks: cleared not only hyperlinks but also the values themselves.
- Text and Split groups: the 'Esc' key button didn't close the drop-down lists of recently used characters. It selected them instead.
- Add-ons didn't work if you run them from a sheet with its own separate chart.
- Small utilities that go to the 'Recent tools' tab didn't work if the history limit was exceeded.
***** V1.11 *****
★ New features ★
- Split Date & Time. Separate cells containing 'Date time' values into columns with only 'Date' units and 'Time' units.
- A couple of new options were introduced into Function by Color:
1. Fill your result cells with colors of the pattern cell used for calculations.
2. Quickly check and change formula colors and calculation ways with the 'Edit selected formula' option.
***** V1.10 *****
★ New features ★
- Multiple VLOOKUP Matches. Do a quick vlookup across multiple columns and find all matches from all directions based on numerous criteria.
- Consolidate Sheets. Summarize records from several spreadsheets based on their position or common headers.
- Quick Dedupe. This one-step tool will instantly find and eliminate duplicates in your table. It colors and deletes, copies and moves dupes to a new sheet, as well as marks them in a status column.
- Split Sheet. Split one table into multiple sheets by columns.
- Merge Sheets: you can not only update your existing main table but also merge tables in a new spreadsheet.
- Auto-start is now available. Pin the button at the very bottom of the add-on and it will run automatically in each spreadsheet.