Whether it's summer knocking on our doors or winter hitting Westeros, we're still working on Google Sheets and need to compare different parts of the sheets against each other. In this article, I'll show you how to reconcile your data and give you tips on how to do it quickly.
- Compare two columns or sheets
- Compare two columns in Google Sheets for matches and differences
- Addons for Google Sheets to compare two columns and sheets
- Compare data in two Google Sheets and recover missing records
- find missing data
- Drag the corresponding data
- Merge worksheets with the add-on
- Conditional formatting to compare data in two Google Sheets
- Mark duplicates on two sheets or columns
- Compare two Google Sheets and Columns to see the differences
- Compare two lists and mark records in both
- Quick ways to combine columns and highlight records
Compare two columns or sheets
One of the tasks you might have is to search two columns or sheets for matches or differences and identify them somewhere outside the tables.
Compare two columns in Google Sheets for matches and differences
I'll start by comparing two cells in Google Sheets. This allows you to scan entire columns row by row.
Example 1. Google Sheets - compare two cells
For this first example, you need an auxiliary column to insert the formula into the first row of data to be compared:
If the cells match you will see TRUE otherwise FALSE. To check all cells in a column, copy the formula to other rows:
Principal.To compare columns from different files you need to use theIMPORTRANGE function:
Example 2. Google Sheets - Compare two lists for matches and differences
- A cleaner solution would be to useIF function. You can set the exact status foridentical and different cells:
Principal.If your dates are written in different case and you want to consider these words as different, here is the formula for you:
Where EXACT analyzes the case looking for the complete identities.
- Identify only the lines withdouble cells, use this formula:
- Mark only the lines withunique recordsbetween the cells in two columns, take this:
Example 3. Compare two columns in Google Sheets
- Is there a way to avoid copying the formula on each line. You can forge an array IF formula in the first cell of the auxiliary column:
This IF matches each cell in column A with the same row in column C.records are different, the line is marked accordingly. The beauty of this array formula is that it automatically highlights all rows at once:
- If you prefer to name the linesidentical cells, fill in the second argument of the formula instead of the third:
=ArrayFormel(IF(A2:A=C2:C,"Match",""))(Video) How to Compare Two Excel Sheets and Find Differences
Example 4. Compare two Google Sheets to see the differences
Often, you need to compare two columns in Google Sheets that belong to a huge spreadsheet. Or they can be completely different spreadsheets, such as reports, price tables, work shifts per month, etc. So in my opinion you can't create an auxiliary column or it can be too difficult to manage.
If this sounds familiar, don't worry, you can always mark the differences on another sheet.
Here are two tables with products and their prices. I want to find all cells with different content between these tables:
Start by creating a new worksheet and enter the next formula in A1:
=SE(Blatt1!A1<>Blatt2!A1,Blatt1!A1&" | "&Blatt2!A1,"")
Observation.You must copy the formula over the area corresponding to the size of the larger table.
As a result, you only see cells that differ in content. The formula also pulls records from both tables and separates them with a character that you type in the formula:
Principal.If the spreadsheets to be compared are in different files, just paste them inIMPORTRANGE function:
=IF(Blatt1!A1<>IMPORTRANGE("2nd_spreadsheet_url","Planilha1!A1"),Blatt1!A1&" | "&IMPORTRANGE("2nd_spreadsheet_url","Blatt1!A1"),"")
Tools for Google Sheets to Compare Two Columns and Sheets
Of course, any of the above examples can be used to compare two columns of one or two tables, or even match sheets. However, there are some tools we have developed for this task that you will greatly benefit from.
Compare Columns or Spreadsheet Add-on
This first one compares two Google Sheets and Columns to duplicates or uniques in 3 steps. Mark the found records with a status column (which, by the way, can be filtered), or color them, copy or move them to another location, or even delete cells and delete entire lines with duplicates.
I used the add-on to find the rows from Sheet1 that are missing from Sheet2 based onfruitERRPColumns:
So I saved my settings in a scenario. Now I can run them quickly without having to go through all the steps again when the records in my tables change. I just need to launch this scenario from the Google Sheets menu:
For your best convenience, we describe all the tool's options on its page.help pageand in this video:
Try it yourself and see how much time you save. :)
Compare sheets cell by cell
The other compares their Google spreadsheets for differences. Whether you have two or more worksheets, they will all be examined cell by cell and will generate a complete report showing the differences of all worksheets grouped accordingly.
Here is an example of the same two tables. The add-on creates a report not only with different cells (highlighted in yellow), but also with single rows (highlighted in red and blue):
If you want to see the report and all its parts up close, feel free to read it.it's tutorialor watch this demo video:
Or, better yet, trybeyond the complementsyourself and see how much time they save you. :)
Compare data in two Google Sheets and recover missing records
Comparing two Google Sheets for differences and repeats is half the work, but what about missing data? There are also special functions for this, for example VLOOKUP. Let's see what you can do.
find missing data
Imagine you have two lists of products (in my case, columns A and C, but they could be on different worksheets). You need to find those that are on the first list but not on the second. This formula will do that:
How the formula works:
- VLOOKUP looks for the product of A2 in the second list. If present, the function returns the product name. Otherwise, you'll get a #N/A error, which means the value in column C was not found.
- ISERROR examines what VLOOKUP returns and returns TRUE if it is the value and FALSE if it is the error.
So cells with FALSE are exactly what you're looking for. Copy the formula to other cells to check each product in the first list:
Observation.If your columns are on different worksheets, your formula will refer to one of them:
Principal.For a single cell formula, it must be an array formula. This formula automatically fills all cells with results:
Another clever way would be to count all occurrences of the product of A2 in column C:
=IF(CONTIF($C:$C, $A2)=0, "Not found", "")
If there is absolutely nothing to count, the IF function marks cells withNot found. Other cells remain empty:
Where there's VLOOKUP, there's MATCH. you know that right? ;) Here's the formula for combining products instead of counting:
Principal.Feel free to provide the exact range from the second column if it stays the same:
Drag the corresponding data
Your task might be a little more complicated: you might need to get all the missing information for the records common to both tables, for example B. Update the prices. If yes, do you need to wrapAGREEMENT in the INDEX:
The formula compares fruits in column A with fruits in column D. For everything it finds, it pulls prices from column E into column B.
As you might have guessed, another example would use thoseGoogle Sheets VLOOKUP functionthat we described some time ago.
However, there are a few more tools for the job. We also describe all of them on our blog:
- To beenough for the basics: find, combine, and update records.
- To benot only updates the cells, but also adds related columns and mismatched rows.
Merge worksheets with the add-on
If you are tired of formulas, you can use ours.Add-on for Merging Worksheetsto quickly combine and merge two Google Sheets. In addition to its basic purpose of recovering lost data, it can also update existing values and even add incompatible rows. You see all changes either in color or in a filterable status column.
Version 2.0 of Merge Sheets not only merges 2 tables (one main table with one lookup), but multiple sheets in a row (one main table with multiple lookups). The data from the research sheets will be added to your main directory one by one: just like you added them in the add-on.many additional optionswill make your merger as comprehensive as you need it to be.
Principal.Watch this video about the Merge Sheets add-on. Although it only covers 2 sheets, it clearly shows the possibilities for add-ons:
Conditional formatting to compare data in two Google Sheets
There is another standard way that Google offers to compare your data - coloring matches and/or differences viaconditional formatting. With this method, all the records you are looking for immediately stand out. Your task here is to create a rule with a formula and apply it to the correct data range.
Mark duplicates on two sheets or columns
Let's compare two columns in Google Sheets for matches and color only the cells in column A that match the cells in the same row in column C:
- Select the range of records to be colored (A2:A10 for me).
- Go toFormat > Conditional Formattingin the table menu.
- Enter a simple formula for the rule:
- Select the color to highlight the cells.
Principal.If your columns constantly change size and you want the rule to take all new entries into account, apply it to the entire column (A2:A, assuming the data to be compared starts at A2) and change the formula as follows :
This processes entire columns and ignores empty cells.
Observation.To compare data from two different worksheets, you need to make further adjustments to the formula. You see, conditional formatting in Google Sheets doesn't support cross-sheet references. However, you can access other worksheets indirectly:
In this case, specify the range to apply the rule - A2:A10.
Compare two Google Sheets and Columns to see the differences
To highlight records that do not match cells in the same row in a different column, the drill is the same as above. You select the range and create a conditional formatting rule. However, the formula is different here:
Modify the formula again to make the rule dynamic (allow for any newly added values in these columns):
And use the indirect reference to another worksheet if the column to compare exists:
Observation.Don't forget to specify the range to which to apply the rule - A2:A10.
Compare two lists and mark records in both
Of course, it's more likely that you have the same records scattered across your columns. The value in A2 in one column is not necessarily in the second row of another column. In fact, it can appear much later. This, of course, requires a different method of finding the elements.
Example 1. Compare two columns in Google Sheets and highlight the differences (Unique)
To highlight unique values in each list, you need to create two conditional formatting rules for each column.
Cor Column A:
Color Column C:
Here are the only ones I have:
Example 2. Find and mark duplicates in two columns in Google Sheets
You can color in shared values after making small changes to both formulas in the previous example. Just let the formula count anything greater than zero.
Duplicate colors between columns in A only:
Duplicate colors between columns only in C:
Principal.Find many more examples of formulas to highlight duplicates in Google Sheetsit's tutorial.
Quick ways to combine columns and highlight records
Sometimes conditional formatting can be tricky: you might accidentally create some rules in the same range or manually apply colors to cells with rules. Also, you need to keep track of all areas: the ones you highlight about the rules and the ones you use in the rules themselves. All of this can get very confusing if you're not prepared and don't know where to look for the problem.
fortunately wowCompare Sheets collection to Google Sheetshas two easy-to-use solutions for you.
Add-on to compare and highlight duplicates or uniques
Compare columns or sheetsit's intuitive enough to help you combine two columns in a table, two different tables in a worksheet, or even two separate worksheets, and highlight the unique or duplicate data that might be going into your data.
This is how I highlighted duplicates between two tables based onfruitERRPColumns with the tool:
I can also save these settings in a reusable scenario. When the records are updated, I call this scenario with just one click and the add-on immediately starts processing all the data. Therefore, I avoid tweaking all these settings repeatedly in the add-on steps. You will see how the scenarios workin the example aboveEin this tutorial.
Principal.Have you watched the demo video for the Compare Columns or Sheets add-on?To hear.
Add-on to compare Google Sheets and highlight the differences
Compare sheets cell by celldon't fall back. It sees any differences between two columns or sheets. In fact, it compares as many sheets as you need, even from different files. Typically, one of these tables acts as your main table, and you compare it with others. The add-on highlights the differences in these other worksheets so you can immediately spot them:
this help pageand the demo video below gives you a better idea of how it compares various Google Sheets for differences:
make sure thatInstall add-on from Google Storeto follow the instructions.
Anyway, all these methods are now at your disposal - try them, modify them and apply them to your data. If none of the suggestions help your specific task, feel free to discuss your case in the comments below.
You might also be interested in
- How to find and remove duplicates in Google Sheets
- Google Spreadsheet COUNTIF function
- How to merge multiple Google Sheets into one without copying and pasting