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 ways to combine your data and give you tips on how to do it quickly.
- Compare two columns or sheets
- Compare two columns in Google Sheets to find matches and differences
- Plugins for Google Sheets to compare two columns and sheets
- Compare data in two Google Sheets and get missing records
- Find the missing data
- extract corresponding data
- Merge worksheets with the plugin
- Conditional formatting to compare data in two Google Sheets
- Mark duplicates across 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 to find 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 a helper column to insert the formula into the first row of data to be compared:
=A2=C2
If the cells match you will see TRUE otherwise FALSE. To check all cells in a column, copy the formula to other rows:
Advice.To compare columns from different files you need to use theIMPORTRANGE function:
=A2=IMPORTRANGO("hoja_de_calculo","Hoja1!A2")
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:
=IF(A2=C2,"matching","difference")
Advice.If your dates are written in different case and you want to treat those words differently, this is the formula for you:
=IF(EXACT(A2,C2),"Corresponds","Different")Where EXACT examines the case and finds complete matches.
- Just identify the lines withdouble cells, use this formula:
=IF(A2=C2,"Correspondence","") - Mark only the lines withunique recordsbetween the cells in two columns, take this:
=SI(A2=C2,"","Different")
Example 3. Compare two columns in Google Sheets
- Is there a way to avoid copying the formula on each line? You can forge an IF array formula in the first cell of the auxiliary column:
=Array-Formula(IF(A2:A=C2:C,"","Different"))This IF matches every cell in column A with the same row in column C.the records are different, the line is marked accordingly. The beauty of this array formula is that it automatically checks each row at once:
- If you prefer to name the linesidentical cells, replace the second argument of the formula with the third:
=Array-Formel(IF(A2:A=C2:C,"Match",""))(Video) Compare Two Columns in Google Sheets and Highlight Differences Using Conditional Formatting
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 I don't think you can create a help column or it might be too hard to maintain.
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 put all cells with different content between these tables:
First, create a new worksheet and enter the following formula in A1:
=SI(Hoja1!A1<>Hoja2!A1,Hoja1!A1&" | "&Hoja2!A1,"")
To useYou must copy the formula to 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:
Advice.If the spreadsheets to be compared are in different files, just embed them againIMPORTRANGE function:
=SI(Hoja1!A1<>IMPORTRANGO("2nd_spreadsheet_url","Hoja1!A1"),Hoja1!A1&" | "&IMPORTRANGE("2nd_spreadsheet_url","Hoja1!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 compare spreadsheets. However, there are some tools we have developed for this task that you will greatly benefit from.
Add-in to compare columns or sheets
This first one compares two Google Sheets and Columns to duplicates or uniques in 3 steps. Let it mark found records with a status column (which can be filtered by sign) or color them, copy or move them to another location, or even delete cells and remove entire rows with duplicates of any kind.
I used the plugin to find the rows from Sheet1 that are missing from Sheet2 based onfruitjRRPColumns:
I then saved my settings for a scenario. Now I can run them quickly without having to go through all the steps again every time the records in my tables change. I just need to launch this scenario from the Google Sheets menu:
For your convenience, we have described all of the tool's options in itshelp 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, it will examine them cell by cell and generate a complete report with the differences of all worksheets grouped accordingly.
Here is an example of the same two tables. The plugin creates a report not only with different cells (highlighted in yellow), but also with single rows (highlighted in red and blue):
To get a closer look at the report and all of its parts, feel free to read itit is textbookor watch this demo video:
Or better yet, tryboth pluginsyourself and see how much time they save you. :)
Compare data in two Google Sheets and get missing records
Comparing two Google Sheets for differences and repeats is half the battle, but what about missing data? There are also special functions for this, for example VLOOKUP. Let's see what you can do.
Find the missing data
Example 1
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:
=FEHLER(BUSCARV(A2,$C:$C,1,0))
Here's how the formula works:
- VLOOKUP finds 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 is returned by VLOOKUP and returns TRUE if it is the value and FALSE if it is the error.
So cells with FALSE are what you are looking for. Copy the formula to other cells to check each product in the first list:
To useIf your columns are on different worksheets, your formula will refer to one of them:
=FEHLER(BUSCARV(A2,Hoja2!$C:$C,1,0))
Advice.To work with a formula for a cell, it must be an array. This formula automatically fills all cells with results:
=Array-Formel(ISERRO(VLOOKUP(A2:A10,$C:$C,1,0)))
example 2
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 withLost. Other cells remain empty:
Example 3
Where there's VLOOKUP, there's MATCH. you know it well ;) Here's the formula for connecting products instead of counting:
=IF(ERROR(MATCH($A2,$C:$C,0)),"Not Found","")
Advice.Feel free to specify the exact range of the second column if it's still the same:
=IF(ERROR(MATCH($A2,$C2:$C28,0)),"Not Found","")
extract corresponding data
Example 1
Your task might be a bit more complex: you might need to extract all the missing information for common records from both tables, for example B. update prices. If yes, do you need to wrapAGREEMENT in the INDEX:
=INDEX($E:$E,CORRESP($A2,$D:$D,0))
The formula compares the fruits in column A with the fruits in column D. For everything it finds, it extracts the prices from column E to column B.
example 2
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 beIt does the basics: find, combine, and update records.
- To beIt not only updates cells but also adds related columns and mismatched rows.
Merge worksheets with the plugin
If you are tired of formulas, you can use ours.Plugin to merge worksheetsto quickly combine and merge two Google Sheets. In addition to its basic purpose of extracting missing data, it can also update existing values and even add rows that don't match. You can display all changes in color or in a filterable status column.
Version 2.0 of Merge Sheets not only merges 2 tables (one parent with one lookup), but multiple sheets in one row (one parent with multiple lookups). The data from the survey sheets will be added to its parent one by one: just like you added it in the plugin.many additional optionsThis will make your merger as complete as you need it to be.
Advice.Watch this video about the Merge Sheets plugin. Although it only consists of 2 sheets, it clearly shows the additional possibilities:
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 differencesConditional format. With this method, all the records you are looking for will be highlighted immediately. Your task here is to create a rule with a formula and apply it to the correct data range.
Mark duplicates across 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).
- BecomesFormat > Conditional Formattingin the table menu.
- Enter a simple formula for the rule:
=A2=C2 - Choose the color to highlight the cells.
Advice.If your columns are constantly resizing 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 manner:
=E(A2=C2,IT'S EMPTY(A2)=FALSE)
This processes entire columns and ignores empty cells.
To useTo 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:
=A2=INDIREKTO("Hoja2! C2:C")
In this case, specify the range to which the rule should apply: 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 sweep is the same as above. Select the range and create a conditional formatting rule. However, the formula is different here:
=A2<>C2
Modify the formula again to make the rule dynamic (allow any newly added values in these columns):
=E(A2=C2,IT'S EMPTY(A2)=FALSE)
And use the indirect reference to another worksheet if the column to compare exists:
=A2<>INDIREKTO("Hoja1! C2:C")
To useDon't forget to specify the range to apply the rule: A2:A10.
Compare two lists and mark records in both
Of course, it's more likely that the same records are 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 articles.
Example 1. Compare two columns in Google Sheets and highlight differences (unique)
To highlight unique values in each list, you need to create two conditional formatting rules for each column.
Cor Column A:=CONTAR.SI($C$2:$C$9,$A2)=0
Choir Column C:=CONT.SE($A$2:$A$10,$C2)=0
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.
Duplicates colors between columns in A only:=KONTAR.SI($C$2:$C$9,$A2)>0
Color copies between columns only in C:=CONT.SE($A$2:$A$10,$C2)>0
Advice.Find many more examples of formulas to highlight duplicates in Google Sheets atit is textbook.
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 should keep an eye on all areas: those you emphasize through the rules and those 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 oursCompare Google Sheets Collection of Sheetshas two easy-to-use solutions for you.
Addon 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 unique or duplicate items that might be making their way into your data.
Highlighted duplicates between two tables accordinglyfruitjRRPColumns with the tool:
I can also save this configuration in a reusable scenario. When the records are updated, I call this scenario with just one click and the plugin starts processing all the data right away. So I avoid tweaking all those settings over and over again through the extra steps. You will see how the scenarios workin the example abovejin this tutorial.
Advice.Have you watched the demo video for the Compare Columns or Sheets plugin?To hear.
Add-on to compare Google Sheets and highlight the differences
Compare sheets cell by celldon't stay behind. View any differences between two columns or worksheets. Compare as many spreadsheets as needed, even from different files. Typically, one of these tables acts as a parent table and compares it with others. The plugin 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 or plug-ins from the Google Storefollow instructions consistently.
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
FAQs
How do I compare two Google Sheets for matching Data? ›
- Start the tool.
- Step 1: Select your main table.
- Step 2: Choose the table for comparison.
- Step 3: Decide what to find.
- Step 4: Pick the columns to compare.
- Step 5: What to do with the results.
- See the result.
To compare two files, make sure they are both open, and then in the Ribbon, select Inquire > Compare > Compare Files.
How do I compare two columns in different worksheets for matches? ›Navigate to the "Home" option and select duplicate values in the toolbar. Next, navigate to Conditional Formatting in Excel Option. A new window will appear on the screen with options to select "Duplicate" and "Unique" values. You can compare the two columns with matching values or unique values.
How do I match in Google Sheets? ›- Type “=MATCH” or go to “Insert” → “Function” → “Lookup” → “MATCH”.
- Input a “search_key” by manual input or cell reference.
- Select a range in which you will find a match with the “search_key”.
- Define how to search if necessary.
When comparing two lists of data, select both columns of data, press F5 key on the keyboard, select the “Go to special” dialog box. Then select “Row difference” from the options. Matching cells of data across the rows in the columns are in white color and unmatched cells appear in grey color.
How do you find matching values on two spreadsheets? ›Select both columns of data that you want to compare. On the Home tab, in the Styles grouping, under the Conditional Formatting drop down choose Highlight Cells Rules, then Duplicate Values. On the Duplicate Values dialog box select the colors you want and click OK.
Can you compare data in two Google Sheets? ›You can compare the values in the same cells in two separate sheets with conditional formatting. Highlight the cells in the first sheet that you wish to compare and then, in the Ribbon, select Home > Conditional Formatting.
What is the best way to compare two files? ›- Open one of the two versions of the document that you want to compare.
- On the Review menu, select Compare Documents.
- In the Original document list, select the original document.
- In the Revised document list, browse to the other version of the document, and then select OK.
- We will click on Cell H4.
- We will insert the formula below into Cell H4. =INDEX(D4:D10,MATCH(H3,B4:B10,0))
- We will press ENTER.
Compare Two Columns in Excel Using IF Condition
In Excel, you can compare two columns using the IF condition. The formula to compare two columns is =IF(A2=B2,”Match”,” ”). It returns the result as Match against the rows that contain matching values, and the remaining rows are left empty.
How do you compare two columns and highlight matches? ›
- Select the entire data set.
- Click the Home tab.
- In the Styles group, click on the 'Conditional Formatting' option.
- Hover the cursor on the Highlight Cell Rules option.
- Click on Duplicate Values.
- In the Duplicate Values dialog box, make sure 'Duplicate' is selected.
- Write down all the lookup sheet names somewhere in your workbook and name that range (Lookup_sheets in our case).
- Adjust the generic formula for your data. ...
- Enter the formula in the topmost cell (B2 in this example) and press Ctrl + Shift + Enter to complete it.
MATCH is a function in Google Sheets that allows you to look up a value in a table of data and return the row and column position of that value. This can be useful for finding data in a large table, or for creating dynamic formulas that refer to specific data in a table.
What is match formula in Google Sheets? ›MATCH returns the position in an array or range of a matched value rather than the value itself. To return the value itself or another value corresponding to the row or column the match is found in, use INDEX , HLOOKUP , or VLOOKUP .
How do I get all matching values in Google Sheets? ›- To run the tool, go to Extensions > Multiple VLOOKUP Matches > Start in the Google Sheets menu: ...
- For Multiple VLOOKUP Matches to work, you should specify your source data table, set up your lookup criteria, and decide upon the number of matches to pull:
You can use the IF Function to compare two lists in Excel for matches in the same row. If Function will return the value TRUE if the values match and FALSE if they don't. You can even add custom text to display the word “Match” when a criterion is met and “Not a Match” when it's not met.
Which graph is most appropriate for comparing two similar data sets? ›Bar charts are good for comparisons, while line charts work better for trends. Scatter plot charts are good for relationships and distributions, but pie charts should be used only for simple compositions — never for comparisons or distributions.
What graph is used to compare two sets of data? ›A bar chart is especially useful with comparing two sets of data. The difference in the bars give us a quick snapshot that allows us to draw some conclusions.
How to match data in Excel from 2 worksheets and highlight the differences? ›- Open Spreadsheet Compare.
- In the lower-left pane, choose the options you want included in the workbook comparison, such as formulas, cell formatting, or macros. ...
- On the Home tab, choose Compare Files.
The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. For example, if the range A1:A3 contains the values 5, 25, and 38, then the formula =MATCH(25,A1:A3,0) returns the number 2, because 25 is the second item in the range.
How do I cross reference two Excel sheets for matches? ›
- Type “=MATCH(” and link to the cell containing “Kevin”… the name we want to look up.
- Select the all the cells in the Name column (including the “Name” header)
- Type zero “0” for an exact match.
- The result is that Kevin is in row “4”
Use the diff command to compare text files. It can compare single files or the contents of directories. When the diff command is run on regular files, and when it compares text files in different directories, the diff command tells which lines must be changed in the files so that they match.
How do I know if two files have the same content? ›Comparison Using cmp
GNU cmp compares two files byte by byte and prints the location of the first difference. We can pass the -s flag to find out if the files have the same content. Since the contents of file1 and file2 are different, cmp exited with status 1.
WinMerge can compare both folders and files, presenting differences in a visual text format that is easy to understand and handle.
What is better than VLOOKUP Google Sheets? ›XLOOKUP is a function in Google Sheets and Microsoft Excel that can identify values in an array or range quickly. The function is more versatile than LOOKUP, VLOOKUP, and HLOOKUP. XLOOKUP first came to Excel in 2019 and Google Sheets in August 2022.
What is the difference between VLOOKUP and match in Google Sheets? ›The main difference between VLOOKUP and INDEX MATCH is in column reference. VLOOKUP requires a static column reference whereas INDEX MATCH requires a dynamic column reference. With VLOOKUP you need to manually enter a number referencing the column you want to return the value from.
What is the difference between VLOOKUP and INDEX match in Google Sheets? ›In conclusion, both VLOOKUP and INDEX MATCH are powerful functions in Google Sheets that can be used to retrieve data from a table or range. While VLOOKUP is more straightforward and easier to use, INDEX MATCH is more versatile and allows for more flexibility when retrieving information from a range.
Can you match across multiple columns? ›We can use an array formula that is based on the MMULT, TRANSPOSE, COLUMN, and INDEX functions to lookup a value by matching across multiple columns.
How do you correlate two columns? ›It is denoted by r and values between -1 and +1. A positive value for r indicates a positive association, and a negative value for r indicates a negative association. By using corr() function we can get the correlation between two columns in the dataframe.
How do I compare two columns in the same table? ›Here's the generic SQL query to two compare columns (column1, column2) in a table (table1). mysql> select * from table1 where column1 not in (select column2 from table1); In the above query, update table1, column1 and column2 as per your requirement.
How do I compare two columns in Google sheets and highlight duplicates? ›
- Select the range with records to color (A2:A10 for me).
- Go to Format > Conditional formatting in the spreadsheet menu.
- Enter a simple formula to the rule: =A2=C2.
- Pick the color to highlight cells.
Use VLOOKUP when you need to find things in a table or a range by row. For example, look up a price of an automotive part by the part number, or find an employee name based on their employee ID.
How does INDEX match work? ›=INDEX() returns the value of a cell in a table based on the column and row number. =MATCH() returns the position of a cell in a row or column. Combined, the two formulas can look up and return the value of a cell in a table based on vertical and horizontal criteria.
How do I use VLOOKUP to match data in Google Sheets? ›- Select the range with your data (A1:D9).
- Specify how many matches to return (all in our case).
- Choose which columns to return the data from (Item, Amount and Status).
- Set one or more conditions.
This means that the formula is used to search for a key in the first column of a specified range. It then returns the value of the specified cell with the range's row. Just like in Excel, Google Sheets also allows you to VLOOKUP from another spreadsheet within a file, or even another Google Sheet file altogether.
Can we use VLOOKUP for 2 different Google Sheets? ›Here are the steps that you need to follow to VLOOKUP from another workbook in Google Sheets: Click on the first cell of your target column (where you want the VLOOKUP results to appear). For our example, click on cell B3 of the Sales sheet. Type: =VLOOKUP, followed by opening parentheses.
How do I find partial match in Google Sheets? ›Google Sheets VLOOKUP function can search the value for a partial match using wildcard characters asterisk (*) and question mark (?). A question mark (?) is used to match any single character, but an asterisk (*) is used to match any sequence of characters.
How do I index match multiple results in Google Sheets? ›Step 3: Use the =INDEX(reference,MATCH(1,(criteria1)*(criteria2)…,0)) to index match on multiple criteria. This formula will match with multiple criteria and lookup the corresponding values based on the values selected from the dropdowns in cell A13 and B13. And done! That's how simple it is to use this function!
How can Vlookup find matching data in two sheets? ›- Write down all the lookup sheet names somewhere in your workbook and name that range (Lookup_sheets in our case).
- Adjust the generic formula for your data. ...
- Enter the formula in the topmost cell (B2 in this example) and press Ctrl + Shift + Enter to complete it.
- Click on the first cell of your target column (where you want the VLOOKUP between sheets results to appear). ...
- Type: =VLOOKUP, followed by opening parentheses.
- Next, select the cell containing the value you want to look up.
Can you do a VLOOKUP with 2 criteria Google Sheets? ›
In Google Sheets, VLOOKUP cannot natively search with multiple criteria. Use FILTER instead. The value that is returned from the formula. The array or range to be filtered.
Can you do a VLOOKUP between two spreadsheets? ›Excel also allows you to use VLOOKUP across multiple sheets in case you want to apply the formula to several sheets simultaneously. We perform this action in combination with the IFERROR formula.
How do I use a VLOOKUP to find a match? ›- Step 1: Organize the data. ...
- Step 2: Tell the function what to lookup. ...
- Step 3: Tell the function where to look. ...
- Step 4: Tell Excel what column to output the data from. ...
- Step 5: Exact or approximate match.
- Select all the cells in both lists.
- Press the “F5” key to open the “Go to Special” tool.
- Click on the button that says “Special.”
- Select the “Row differences” option, then click “OK” to highlight all the cells with differences between the two rows.
A Dual Axis Line Chart is one of the best graph to compare two sets of data. The chart has a secondary y-axis to help you display insights into two varying data points. More so, it uses two axes to easily illustrate the relationships between two variables with different magnitudes and scales of measurement.
Which chart type can display two different data series in same chart excel? ›A combination chart can be made up of area, bar, column, dot, and line charts. Each data series can be represented by a different type of chart. They are all then displayed simultaneously on the same chart.
What graph compares two sets of data? ›A bar chart is especially useful with comparing two sets of data.