Below you'll find the information about the latest releases of Power Tools for Google Sheets and other Ablebits add-ons for spreadsheets that are available as single add-ons. Release notes showcase new features, fixes, and improvements.
Remember Compare Columns or Sheets add-on? It compared just two sheets at a time. Well, we enhanced it and now it compares multiple sheets in one go! ;)
As you can see from this scheme, you will also be able to handle the found dupes (or uniques), e.g. color them or remove, in all sheets & not just the main one like it used to.
We've already updated the tutorial for the tool, so feel free to look through so you don't miss anything out.
Another legacy of the old Compare Columns or Sheets add-on.
Comparing columns from multiple Google sheets is half the job. But comparing two columns within the same Google sheet has proved to be just as important. That's why we singled out this little helper.
Here I'll only tell you that it's a one-step quickie now. So no need to go over the steps to compare just two columns on one Google sheet.
Check out this tutorial to learn what it has to offer.
We haven't left our text tools behind. These new features have been brewed and polished for months till they're finally ready to meet you.
Substring Batch Toolkit will let you handle multiple substrings at a time. There are 4 tools for different actions:
Power Tools already offers several ways to deal with blanks in your tables. It deletes empty & unused rows & columns, fills blank cells with the values from neighboring cells or with your custom ones.
But this new feature introduces yet another way. It removes those single empty cells from your tables while shifting the other data left or right: it's up to you.
You will find the tool as 2 simple checkboxes in the Clear group:
As you may know, our Sheets Manager can copy & move multiple sheets to any other spreadsheet in your Drive:
We've improved it just a bit so you could copy those selected tabs to multiple different files at once :)
When you see the window where you are to select the spreadsheet, press and hold Ctrl (for non-adjacent) or Shift (for adjacent) on your keyboard to select multiple files:
A new checkbox lets you not just sort all cells in the range by color, but only those with values:
It may seem like a small option but it makes a difference:
Another small but mighty option was added to the String group of Random Generator: unique values. Make sure to tick it off if you'd rather avoid duplicates generated for your data set:
Yet another comparison tool you may think… But think twice ;D This add-on doesn't just look for duplicates or uniques. It compares cells in each row between multiple Google sheets and marks all differences.
There are 2 ways to track those differences:
I believe one demo video speaks better than words, so here are 2 minutes to see this new arrival in action:
This step-by-step guide describes each setting in detail and shows all possibilities offered by the comparison report.
Merge Sheets underwent some major changes as well. First and foremost, it finally lets you merge more than 2 sheets at a time:
You add as many lookup sheets as you need on the 2nd step, and the add-on takes the required data from them one by one to your main sheet:
I'm not going to go over all the details and other new features here since I announced them earlier in this blog post, but here are a few highlights:
Function by Color now knows how to sum or count not just one but all colors in the range of interest. It builds one formula that returns the names of all colors with their calculated results:
The settings are almost the same and you'll find them on the additional tab — All colors:
The upgrade didn't bypass Split Sheet. It features a new way to split your table: by a certain number of rows:
You'll be able to fine-tune the number in 2 ways:
Our Power Tools has acquired its own Sort by Color. And it works unlike the Google Sheets built-in tool, so I recommend you check it out anyway:
Our add-on gathers all colors together and sorts them in the order you specify directly in the add-on:
The built-in Google Sheets tool gathers only the rows of the selected color at the beginning of your table:
So based on your task, you may need one tool or another. Another is in our Power Tools ;) These instructions will help you get started.
It means that rather than selecting each column individually and processing them one by one you can change all required columns with one tool in one go:
Whenever you need to enter multiple words to see if cells contain/equal to/etc. at least one of them (OR condition), you can now simply copy the list of such words and paste it into the add-on. The tool will take them as parts of your condition automatically:
The Convert tool now turns all text written in parentheses into negative numbers, just like Google Sheets does when you change the Number format:
And this is apart from the fact that Convert also recognizes more formats in general, including custom ones!
All add-ons that let you add files from Drive (Merge Sheets, Compare Sheets, etc.) will see your starred items as well. This is super useful if you want to instantly add some files of interest to handle it with the tool:
This one is an enhanced version of our long-existing Compare Columns or Sheets. It used to compare just two columns or Google sheets. Now it will deal with several sheets at a time :)
Even if your sheets are in different spreadsheets, you'll be able to add them all from your Drive to the add-on right on the first step:
One of these sheets will still have to be the main one. Each of the other selected sheets will be checked against the main one.
Another nice thing this version offers: you can handle the found duplicates or uniques not just on the main sheet. That is, color them, delete them, etc.
Of course, you can still do that only on the main sheet:
But also on each compared sheet (BUT the main one):
And even on all sheets altogether:
Having compared multiple sheets, you'll probably wonder where those duplicates are in other compared sheets. For that, the add-on will add cross-sheet links if you choose to:
These references will lead right to the related duplicates in the compared sheets as well as to the source sheets when you copy/move the records:
Check out this tutorial for all the details on all the available options.
Since Compare Two Sheets or Columns has been changed to support multiple sheets, we had to make this separate quickie: Compare Two Columns.
It's for when you must compare two columns on the same Google sheet. Many of you asked to keep this ability, so here it is :)
It's now a one-step add-on with just a few settings to tweak:
Easy-peasy, right? This tutorial digs deeper into the options if you'd still like to explore them :)
Merging 2 sheets is good but merging multiple sheets is way better!
With this major upgrade, you can now add numerous files from your Drive right to the add-on and select as many lookup sheets from them as you need:
The add-on will go through each lookup sheet one by one (in the order you add them to the add-on and they appear in the tree view), matching them with your main sheet and merging the necessary information.
Should they update one another or what? Learn how the add-on handles such columns in this part of the Merge Sheets help page. Forewarned – forearmed, right? ;)
Make Merge Sheets not just update your main sheet with the 1st value, but also take all extra matching rows belonging to the same key value with it:
You can also personalize this merge a bit and put all those additional matching rows at the end of your main table (rather than right after the row with the same key value):
This new setting resides on the 5th step:
All this talk about the new options and multiple lookup sheets would be pointless if we didn't review the add-on completely. We significantly improved the algorithm so you could have a faster and more reliable Merge Sheets at hand.
Instead of loading arrows, there is now a percent count and the process name for your convenience:
It's a game changer for handling large amounts of data because you will understand how much longer (at least approximately) it's going to work.
Even if Merge Sheets catches some problem, it'll show you a gentle notification with the issue description & its possible solution.
Speaking of enhancements! The limit for total cells in a spreadsheet is finally expanded to 10 million. This allowed us to include more lookup sheets & pull those additional matching rows to your main sheet. Yay!
In addition to your personal and shared Drives, you can now search for the necessary main or lookup files among your starred ones:
If your lookup sheets have notes, the add-on doesn't leave them behind anymore. It pulls them to the main sheet along with their corresponding values:
The first thing that will catch your eye — lots of new style patterns! We designed multiple new style sets featuring various shades and colors. Some of them may even remind your favorite movie or a video game ;)
All in all, you have 52 built-in styles ready to be used as is, tailored to your needs and saved as your custom sets.
All patterns are grouped into 6 types based on their color themes: practical, monochrome, light, juicy, contrasting and even dark.
We believe even the most demanding Google Sheets users will find something to their liking. Go ahead and prove us wrong ;)
You can finally format borders:
There's also an addition to the horizontal alignment: the automatic type.
Use it and the values in your Google table will be automatically aligned based on their format just as they normally do in spreadsheets, e.g. numbers to the right and text to the left.
However neat the whole style is, you probably don't always want to apply it to your entire table.
The new version makes it possible to style only particular table element(s) from the chosen style set: font, its size/formatting/color, fill colors, borders, horizontal/vertical alignment, and text wrapping.
We redesigned the add-on completely! It now keeps up with the updated Google Sheets design. But what's more important — you get access to any setting from just one window — without any extra steps. Enjoy! :)
We get it — some parts of your table may share formatting, for instance, the font color in the left and right columns. And we agree — applying the same formatting to each part is unreasonable.
That's why you need the 2.0 version. It lets you select all necessary elements and format them all at once. Awesome!
The updated add-on has more fancy, simple and funny fonts so you could personalize your styles. Let us know if there are still some you're missing ;)
Post a comment
Seen by everyone, do not publish license keys and sensitive personal info!