Find differences in Excel files using Conditional Formatting, VLOOKUP and VBA macro
Best and most effective methods to compare Excel files for differences:
How to compare two Excel files for differences?
If you are new to Excel, understanding how to effectively compare values in Excel sheets will save you a huge amount of efforts and time.
In this article we discuss ALL methods you can use to compare two excel workbooks and highlights the modifications you have made.
Unfortunately, Excel allows to open only one workbook in a window, so comparison operation is not easy task in Excel application. Excel itself is not fully oriented for comparison operations, so this gap is closed by the software, that was designed only to compare Excel files.
Let's try to analyze every solution and find its pros and cons and determine when you can use it.
Please, note. If you are looking for a way to compare two Excel file in a batch mode - there is no way to do this, using methods that are described below. You need an excel file compare tool for this. Only xl comparator application allows integration into third party SVN or GIT environment.
What is the simplest method to compare Excel files?
Note: all of the described methods are FREE solutions.
We will enumerate methods in by increasing their complexity and explain each step.
Reasonable question – why should I use this Excel diff tool?It is not free and I need only to see the difference between two Excel files.You need to know that xlCompare has free evaluation period and you can use almost all functions of the software.So, if you need to compare your Excel workbooks for differences for FREE – xlCompare is an ideal solution.You can use all power of the xlCompare to get your job done quickly and efficiently. And you should not pay for this!
Compare your Excel files using free web service.This is also good and robust solution to compare Excel file for changes, that will work on the most of spreadsheets.
Compare Excel worksheets using Conditional Formatting rules.This is good method, but it loses in all aspects in comparison to the methods described above. It doesn’t give you enough features and enough productivity. Also, there is a risk of errors.
Even if your worksheets are simple – try using xlCompare in the evaluation mode. You are not obliged to pay for it and this Excel diff tool solves your task in seconds.
- No registration required
- Easy to install and use
- Just drop your files into xlCompare and get the results!
Hope, you will enjoy the tool!
Compare Excel Files using VBA macro
If your worksheets are relatively simple, you can use alternate method to diff your Excel files - use VBA macro to compare data in your files. This methods is very easy and quick and perfectly works on the simple datasets.
Free alternative to paid Excel diff tools
Not all Excel users need to pay for Excel diff tool. Someone can't install the software on his office computer, other users have very simple spreadsheets and there is no reason to pay for professional tool.
That's why we would like to provide you a set of completely free Visual Basic macros use can use to compare your Excel files without using Excel diff tool.
This topic will be continuously extended with examples of VBA code, that performs Excel worksheet comparison and extracts unique rows.
Visual Basic for application is a powerful and feature rich development environment. If you are VBA programmer, probably, you can easily create a macro that compares Excel worksheets for differences. But, if you have only basic knowledge in VBA, we will fill this gap and give you an example of the VBA macros you can use to compare your Excel files for differences.
The topics below contain Visual Basic macros that you can use without any limitations on your computer to compare Excel worksheets and highlight differences.
What I need to do to use this code?
- Open your worksheet
- Use Alt+F11 to go to Visual Basic Editor
- Insert new module into your workbook
- Paste macro into this module
Our macros use worksheet names Sheet1 and Sheet2. However your file may have different names. In this case you need locate this line and enter actual worksheet names, that you are going to compare:
' Here you need to enter appropriate sheet names
Sheet1 = "Sheet1"
Sheet2 = "Sheet2"
If none of the published macros solve your task - just let us know the details. Our developers will quickly create a VBA code and share it with community.
List of the VBA macros:
Compare Excel Sheets cell-to-cell with VBA Macro
This macro compares 2 worksheets cell by cell and highlights changed cells. Just copy it into your workbook and use to compare 2 worksheets that have identical structure.
Option Explicit Public Sub CompareSheets() Dim Sheet1 As String Dim Sheet2 As String ' Here you need to enter appropriate sheet names Sheet1 = "Sheet1" Sheet2 = "Sheet2" Application.ScreenUpdating = False Dim Range1 As Range Dim Range2 As Range Set Range1 = Worksheets(Sheet1).UsedRange Set Range2 = Worksheets(Sheet2).UsedRange Dim nColCount As Long Dim nRowCount As Long nRowCount = WorksheetFunction.Min(Range1.Rows.Count, Range2.Rows.Count) nColCount = WorksheetFunction.Min(Range1.Columns.Count, Range2.Columns.Count) Dim nCol As Long Dim nRow As Long For nRow = 1 To nRowCount For nCol = 1 To nColCount If Range1.Cells(nRow, nCol) <> Range2.Cells(nRow, nCol) Then ' Here you can change style of the change cells Range1.Cells(nRow, nCol).Style = "Accent1" Range2.Cells(nRow, nCol).Style = "Accent2" End If Next Next Application.ScreenUpdating = True ' These lines will tile both worksheets on your screen ActiveWindow.NewWindow Sheets("Sheet2").Select Windows.Arrange ArrangeStyle:=xlVertical End Sub
Using Conditional Formatting to compare Excel worksheets in a VBA macro
Conditional Formatting is a powerful feature that allows you to highlight cells that match specific criteria.
It is a perfect way to highlight different cells on the worksheets, that have identical structure.
Option Explicit Public Sub CompareSheets() Dim Sheet1 As String Dim Sheet2 As String ' In this line you need to enter names of your worksheets Sheet1 = "Sheet1" Sheet2 = "Sheet2" Application.ScreenUpdating = False With Worksheets(Sheet1).UsedRange.FormatConditions.Add( _ Type:=xlExpression, Formula1:="=A1<>" & Sheet2 & "!A1") .SetFirstPriority With .Font .ThemeColor = xlThemeColorDark1 .TintAndShade = 0 End With With .Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent1 .TintAndShade = -0.499984740745262 End With .StopIfTrue = False End With With Worksheets(Sheet2).UsedRange.FormatConditions.Add( _ Type:=xlExpression, Formula1:="=A1<>" & Sheet1 & "!A1") .SetFirstPriority With .Font .ThemeColor = xlThemeColorDark1 .TintAndShade = 0 End With With .Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent2 .TintAndShade = -0.499984740745262 End With .StopIfTrue = False End With Application.ScreenUpdating = True ' tile windows on screen and show both worksheets ActiveWindow.NewWindow Sheets("Sheet2").Select Windows.Arrange ArrangeStyle:=xlVertical End Sub
What are the benefits of using this method?
From our point of vew Conditional Formatting is better. When you change any cell - comparison results are updated automatically and highlighting appears. Or it is automatically disappear, if cells become equal.
Previous method is a static one-time comparison. After you change anything on the worksheet, you need to compare it once again.
Compare Excel Sheets by Key Column using VBA Macro
All database tables contain key columns, so if you are working with database tables, you need to compare your worksheets by key.
This time we have more complex example and more "real". We will compare two Excel tables by the key column.
What is key? This is unique value, that identifies row on the worksheet. If keys are equal - they represent same record, otherwise they represent different records. Usually Primary Key is a number or string value, like SKU, Item No., User Name, ...
Do I need to sort the data? You don't need to sort your tables. We will do this for you using macro! Just copy it into your workbook and get the results.
The code below has following requirements:
- Key column is first in your table
- First row is a header in both tables
- Columns have identical structure
Option Explicit Public Sub CompareSheets() Dim Sheet1 As String Dim Sheet2 As String ' modify these lines if you need to change worksheet names Sheet1 = "Sheet1" Sheet2 = "Sheet2" Application.ScreenUpdating = False ' Sort data on the worksheets SortSheetByKey (Sheet1) SortSheetByKey (Sheet2) Dim Range1 As Range Dim Range2 As Range Set Range1 = Worksheets(Sheet1).UsedRange Set Range2 = Worksheets(Sheet2).UsedRange Dim nRowCount1 As Long Dim nRowCount2 As Long nRowCount1 = Range1.Rows.Count nRowCount2 = Range2.Rows.Count Dim nColCount As Long nColCount = WorksheetFunction.Min(Range1.Columns.Count, Range2.Columns.Count) Dim nCol As Long Dim nRow1 As Long Dim nRow2 As Long nRow1 = 1 nRow2 = 1 While nRow1 <= nRowCount1 And nRow2 <= nRowCount2 If Range1.Cells(nRow1, 1) < Range2.Cells(nRow2, 1) Then ' row from sheet1 is not present on the sheet2 Range1.Rows(nRow1).Style = "Accent1" nRow1 = nRow1 + 1 ElseIf Range1.Cells(nRow1, 1) > Range2.Cells(nRow2, 1) Then ' row from sheet2 is not present on the sheet1 Range2.Rows(nRow2).Style = "Accent6" nRow2 = nRow2 + 1 Else For nCol = 2 To nColCount If Range1.Cells(nRow1, nCol) <> Range2.Cells(nRow2, nCol) Then Range1.Cells(nRow1, nCol).Style = "Accent2" Range2.Cells(nRow2, nCol).Style = "Accent2" End If Next nRow1 = nRow1 + 1 nRow2 = nRow2 + 1 End If Wend While nRow1 <= nRowCount1 ' row from sheet1 is not present on the sheet2 Range1.Rows(nRow1).Style = "Accent1" nRow1 = nRow1 + 1 Wend While nRow2 <= nRowCount2 ' row from sheet2 is not present on the sheet1 Range2.Rows(nRow2).Style = "Accent6" nRow2 = nRow2 + 1 Wend ' arrange windows, so you can see both worksheets Application.ScreenUpdating = True ActiveWindow.NewWindow Sheets("Sheet2").Select Windows.Arrange ArrangeStyle:=xlVertical End Sub Private Sub SortSheetByKey(ByVal sSheetName As String) Dim oRange As Range Set oRange = Worksheets(sSheetName).UsedRange With Worksheets(sSheetName).Sort .SortFields.Clear .SortFields.Add2 Key:=oRange.Resize(, 1) _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortTextAsNumbers .SetRange oRange.Offset(1) .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub
How to compare two Excel files by placing them side by side
This is easy method, that is suitable for everyone. Even novice in Excel can compare Excel workbooks in this way.
- Open files you would like to compare in Excel application
Select View tab on the Ribbon and select View Side by Side command
Arrange your Excel windows vertically using Arrange All command
Select Synchronous Scrolling option on the View tab
Now you can scroll down your worksheets and compare cell values one by one.
- Method is relatively easy and requires only a few clicks
Method works only on the small worksheets of the same structure and layout. Just think on this: how will it work in the following cases:
- You need to find differences in formulas
- You have unordered rows in your Excel Tables
- Left worksheet contains some unique rows. This makes scrolling cell-by-cell complex.
This is just a few cons, we can continue.
This method is too weak and if it works for you - this is a good and working solution. In this case you should order a professional Excel files comparison tool. But we suggest you to add Contitional Formatting to this as described below to get better results.
How to highlight different values on two Excel sheets with Conditional Formatting
Conditional Formatting is a powerful feature in Excel, that allows to change cell style options depending on the various conditions. So, applying it to compared Excel sheets is the only solution to highlight differences in Excel.
After placing your worksheets side by side, like in the previous example, select compared range on the worksheet and use Conditional Formatting | New Rule command on the Home tab.
It opens a window, where you need to enter formula that compares two corresponding cell values and select Formatting Options for the changed cell.
Select following options
Rule Type: use a formula to determine which cell to format
Formula: =A4<>'Transactions (2)'!A4
What does it mean?
This simple formula returns TRUE only if cell A4 on the active worksheet and corresponding A4 cell on the other worksheet have different values.
By default Excel inserts $A$4 when you entering formula. You need to press F4 button a few times to change this reference to relative - A4. Without this your Conditional Formatting rule will work in a wrong way. Formatting of the every pair of corresponding cells will depend on the A4 cell only.
Apply Background Color:
Format ... button opens a Format Cells dialog, where you can select desired cell formatting. In our example we've applyed orange fill color to the changed cells.
Now you have new Conditional Formatting rule on your worksheet that highlights you difference in cell values.
This method is an upgrade of the previous one. We've added Conditional Formatting and got perfect difference report.
- This method clearly identifies the differences.
Cons (just a few of them)
- It doesn't detect inserted rows
If you have new rows on one of the worksheets, this destroys all results. They become wrong and useless.
- This method doesn't highlight changed formulas and changed cell formatting
Comparing formulas is an important part of the Excel worksheet comparison.
- If your workbook has 30 worksheets, you need to apply this method to every pair of the sheets
I think you agree with us - this is completely unacceptable.
So, our conclusion - this is a very good way to compare small worksheets with exactly same layout. It is not suitable for most of the real cases, as worksheet layout can be different in the Excel files.
Compare two excel files using VLOOKUP
This method is also often used, because it requies only a few manual actions and can be used by novice also. First you need to understand what VLOOKUP worksheet function does. Lets refer to Excel Help Library:
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.
In its simplest form, the VLOOKUP function says:
=VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match - indicated as 1/TRUE, or 0/FALSE).
Perfect. So, you can use this method for not cell by cell comparison, but to compare two columns for different values. In other words, this topic gives you answer to other question:
How to compare two columns in Excel
This method works perfectly if you have unordered tables.
Remember, VLOOKUP worksheet function returns #N/A error value if lookup value is not found. We would like to find and highlight missed cell values in two Excel columns, so better to avoid errors on the sheet. For this purpose we'll use worksheet function ISNA:
ISNA function returns the logical value TRUE if the value argument is an error value otherwise it returns FALSE.
Now we are ready to compare two Excel columns for different values.
1. To compare two Excel files for differences, open Excel file and apply New Window command on the View tab.
This is necessary to display two worksheets within same Excel workbook simultaneously. If you skip this step you can't see corresponding values.
2. Apply Side by side view and Synchronous scrolling
These commands put both Excel windows one versus one on your screen.
3. Add extra column to the right worksheet and enter the following formula:
In our example Excel translated it to
=IF(ISNA(VLOOKUP([@[Transaction Date]],Table1[Transaction Date],1,FALSE)),"MISSED","")
by replacing cell A2 with appropriate column name of the Table
What does this formula calculate?
VLOOKUP function checks if value of the cell from column A is present in the corresponding column A on the Transactions 2 worksheet.
In case of missed value VLOOKUP returns #N/A error value. This error value is converted into boolean value with ISNA function.
Result of ISNA is our criteria when we should mark this row as MISSED.
In case of ISNA = FALSE we return empty string, to indicate that this value is present on the other worksheet.
How to adopt it to my worksheets?
1. Replace A2 with first cell in the column you are comparing
2. Replace Transactions 2 with name of the corresponding worksheet.
3. Replace A2:A9 with a correct range that covers all values in compared column
Now you have MISSED value in the extra column that indicates new (inserted) value.
4. Let's use Conditional Formatting to highlight cells that represent missed rows.
Like in the second method we add new Conditional Formatting rule to the entire worksheet that have following formula:
Again we need to select Ryle type as Use a formula to determine which cell to format and enter following formula:
Important! You need to convert $E$2 to $E2 by pressing F4 key several times. Otherwize rule will work in a wrong way.
For this case we've selected other formatting for unique rows: Red Fill Color in a combination with white Font Color.
Now results of our comparison look highlighted:
This method finds and highlights matching and unique values on the Excel worksheets. But it doesn't compare all cells. In other words, it is a solution to following task:
How to compare 2 columns on Excel worksheet
- You can use it to compare Excel worksheets by a key column to identify missed records.
- As stated above this method doesn't compare all worksheet values. It compares just a pair of columns of the worksheet.
Create a report for two compared Excel worksheets
After we've learned Conditional Formatting and some worksheet functions, we can make a variation of the methods described above and get the worksheets compared with perfectly formatted differences.
In this method we will simulate Extended View option, that is present in xlCompare. It displays original and changed value in same cell, that makes report much more useful and native. To understand our goal, look into the image below, we've highlighted cell with changed value.
A little of theory. What we are going to use:
The CONCAT function combines the text from multiple ranges and/or strings, but it doesn't provide delimiter or IgnoreEmpty arguments.
CONCAT replaces the CONCATENATE function. However, the CONCATENATE function will stay available for compatibility with earlier versions of Excel.
Returns the character specified by a number. Use CHAR to translate code page numbers you might get from files on other types of computers into characters.
You don't need to know what these functions do and how to use them. We are giving you ready to use solution, that you can use out of the box.
Let's open files you are going to compare in Excel
In our example files are named Sales Transactions 1.xlsx and Sales Transactions 2.xlsx
Both worksheets that we are going to compare have same name - Transactions
Select first file
Insert new worksheet and give it name Summary
We will compare ranges A1:D19. Remember range of cells you are going to compare.
Open notepad on your computer and copy following text:
=IF(SHEET1!A1='[BOOK2]SHEET2'!A1, SHEET1!A1, CONCAT(SHEET1!A1,CHAR(10),'[BOOK2]SHEET2'!A1))
This is a template of the formula we are going to use to compare cell values and create difference report.
- Replace SHEET1 with name of the first sheet. If your worksheet name contains spaces - type it in single quotes.
- Replace SHEET2 with name of the corresponding sheet
- Replace BOOK2 with name of the second Excel file, including extension
In our example we've got following formula:
=IF('Transactions'!A1='[Sales Transactions 2.xlsx]Transactions'!A1, 'Transactions'!A1, CONCAT('Transactions'!A1,CHAR(10),'[Sales Transactions 2.xlsx]Transactions'!A1))
- Paste it into cell A1 on the Summary worksheet and hit ENTER
- Fill cells with this formula to the right - in our example this is column D
- Fill cells down to cover entire range of the table. In our example this is first 19 rows
Use Conditional Formatting to Highlight Changes
Now you have report on your screen, but it doesn't have formatting. Let's do final step to get the best comparison report, that is perfectly formatted.
- Keep all your cells selected and add new conditional formatting rule
- Select type of rule - Use a formula to determine which cells to format
- And paste this formula=NOT(ISERROR(FIND(CHAR(10),A1)))
- Select formatting for the modified values that you like. We've selected orange background and bold red font
Wrap cell text to simulate Extended View
- Right click on the selected area
- Select Format Cells menu item
- Enable Word Wrap on the selected range
- Click OK and get the final diff report
All cell values that are different on these worksheets are highlighted by color. In this report you have source value and value of changed cell in one place. Very similar to Extended View mode that you can find only in xlCompare.
With this method you don't need to select Side by Side layout - you have all of the differences on one sheet.
If you don't do extra clicks or scrolling - in several mouse clicks you get your worksheets compared for changed cell values and formatted with selected color options.
What you can do with this report:
- Customize the formatting (adjust column width, font and colors)
- Save it on disk for future use
- Send by email to colleague
- Make additional formatting and Print as PDF
- Present to your co-workers
- This report has good presentation part
- Method requires worksheets with identical structure
- Only changed cells are detected (it doesn't check worksheets for inserted rows)
Compare two excel files online
This is another good and robust method to compare xlsx and CSV files, that perfectly works on the small workbooks with similar structure. More information on this you find in the article below:
Compare Excel Files for differences online
Why xlCompare has better functionality than the methods above?
All the methods to compare Excel worksheet for differences, described above, are not so effective as desktop application that compares Excel files - xlCompare.
xlCompare points you directly to what is different on the worksheets.
It gives you a complete list of differences and you can browse and analyze all of them.
Ideal way to contrast discrepancies between two Excel files.xlCompare shows original and modified value in same cell and gives you full information on the modification including difference of the values.
Compare formulas and cell formatting.If you are working in the team environment, modified worksheets may look identical, so it is almost impossible to get an answer - what is changed in these worksheets. xlCompare loads all cells and compares cell values, formulas and cell formatting, names and tables. It gives you comprehensive report on the changes made by you or your colleague.
Compare Visual Basic Projects for differences.For VBA developers this is a real pain to compare and merge visual basic modules in the XLSM files and forms or controls. xlCompare is the only solution that compares visual basic forms and controls and gives you an easy-to-use commands to merge changes.
Create Comparison Report.The methods above give you only visual information, but you can't copy this report or save it to disk. xlCompare gives you a number of reports including saving color coded report into PDF format or outlined comparison report that contains information about all discrepancies found in two Excel files (worksheets). Report is structure by type of the difference and by worksheets.
3-Way comparison for XLSX and CSV files.What is 3-way comparison? Imagine you and your colleague modified same version of your common workbook. You need to analyze his changes and create common merged version of file. In other words, you need to compare two Excel worksheets that have same base file. In the SVN(GIT) environment this is called merge conflict. Usually, it takes a lot of time to resolve it, but with xlCompare you will do in a few seconds.
Command line mode.xlCompare is tool designed to by used in the command line mode also. If you are working with SVN or GIT, you need a way to compare and merge XLSM\XLSX\XLS\XLA\XLAM\CSV files by external diff checker. xlCompare fills this gap and gives you a diff viewer (xl comparator) for the file types listed above.
CONCLUSION: What is the reason of choosing xlCompare?
If you look back into the method listed above:
- Compare Excel files by viewing them side by side
- Using Conditional Formatting to compare two Excel worksheets
- Compare Excel files using VLOOKUP worksheet function
There are a number of operations you need to do to compare Excel files and get the result.
xlCompare works in the other way:
- drop your files into xlCompare window
- get the results
This Excel file compare tool converts set of complex operations into few simple mouse clicks. This is the actual reason, why we suggest you choose xlCompare.
Armed with this tool you will save a lot of time and efforts.
xlCompare makes complex work simple!
As a professional Excel/VBA developer, XLCompare has become a fundamental tool in my development process. It works on even the most code-heavy and complex of workbooks, and is something I just couldn't do without.
Compare Excel Files (Sheets) by Key Columns
Let's talk about comparing database tables stored in Excel worksheets. How database table looks in Excel. Usually, database is represented exactly like table in database management application:
- First row is a header, that contains list of fields
- All other rows contain database data in each cell
Excel is often used to store this type of data. Application logs, price lists, financial and accounting statements, ... - all of them are examples of the database tables.
Why is database table compared in the different way relatively to other types of worksheets?
Every table has a primary key column. Primary key is a unique identifier, assigned to record. Only this identifier defines - is this a new row or edited row.
How xlCompare works with database tables
If you know which column in your worksheet is a primary key column - select it and do the right click. There is a menu item - Set as Primary Key. This marks column as a key.
You can use several columns are key. Normally this is wrong, if database table doesn't have a numeric key field. But in the Excel tables we allow to use combination of columns to create primary key.
For example: you don't have ID column in the list of employees, but there is First Name and Last Name field. Select both of them and mark as Primary Key.
Which type of data should be in the key column?
xlCompare allows you to use ANY column as Primary Key. This could be numeric, strings, data or Boolean values.
Should you always define Primary Keys?
No. When you open your workbook in xlCompare is analyzes data on your worksheets and suggests you, key columns and key rows. Just look over your worksheets to make sure that selection is correct. If needed you can change it with Right Click menu.
How to make sure that database algorithm is used for my worksheets?
In the Comparison Wizard every pair of compared worksheets has icon that indicates algorithm used for this pair. Database icon means, that this pair of sheets is compared by Primary Key.
Use Options button to view and change the algorithm.
After you've got comparison results - algorithm icon is reflected in the Difference Explorer.
Review your worksheet data and if it has signs of the database - apply database algorithm to get accurate results.
Compare Excel Files with Changed Primary Keys
How to check if two Excel files are identical?
Most of the 750 million Excel users have this question. Unfortunately compare Excel files or sheets is not so easy as compare two sentences. If you have two Excel files that you need to compare, this article contains a solution for you.
Here we recommend the simplest method and point you to other methods to compare Excel sheets you can use on your computer.
The best way to compare Excel files for FREE - use xlCompare tool.
- You compare Excel files for free while xlCompare is in the evaluation mode
- xlCompare compares all worksheets in your Excel files
- xlCompare doesn’t limit processed ranges when you are using it to compare Excel files
- It gives you perfect comparison report with highlighted differences
- xlCompare detects changed cells and inserted\deleted rows
- You can use other features in the Excel diff tool to debug calculations in your Excel sheet
- You should not pay for this
If you need to compare your Excel files only few times - xlCompare is a perfect solution and you are not obliged to pay for it.
How to check if two Excel files match exactly?
Does Excel have a DIFF function?
How to get the difference between two values in Microsoft Excel worksheet. Excel cell can contain number, date(time) or text value. There is no universal method that calculates difference between numbers, text strings, dates and times. You can use the following options to get the diff:
- Minus operator for numeric values
- Use DATEDIF worksheet function, that returns difference in days, weeks, months
- Use NETWORKDAYS worksheet function to find the difference between two dates in Excel worksheet
xlCompare shows difference between to cells values in the Tooltip window. Out your mouse over the changed cell and xlCompare shows you:
- difference in numeric values
- difference between two dates
- most important - difference between two text cells
How do I match data in two Excel spreadsheets?
You have two spreadsheets you would like to compare for differences. If your spreadsheets have same structure - this task is pretty easy and can be done in a few seconds in Excel. All you need to know is a few commands and some Excel features.
What are the limitations? Can Excel do everything I need to match the data in my spreadsheets?
Unfortunately there are some limits. Conditional Formatting that perfectly highlights different cells can be applied within same workbook only. So, you need to copy both spreadsheets into one Excel file.
- Open both spreadsheets in Excel
- Use VIEW | VIEW SIDE BY SIDE command and arange your Excel windows vertically
- Enable VIEW | SYNCHRONOUS SCROLLING option to browse both spreadsheets synchronously
- Add new Conditional Formatting Rule using HOME | CONDITIONAL FORMATTING | NEW RULE ... command
- Configure rule that compare two worksheet cells on the same places and returns TRUE is cells are different.
- Apply the formatting you need. For example highlight changed cell with background color.
- Thats all. Conditional Formatting rules points you to the data that doesn't match.
- method is relatively easy and quick
- changed cells are perfectly identified
- method work ONLY on the spreadsheets with same layout
- you need to repeat this procedure to all the spreadsheets you are going to match
- it doesn't give you exact answer - does the data in your spreadsheets match
Is there an alternative?
You can use xlCompare tool to match data in your spreadsheets. This method is much more easy and quick and more informative.
- You are getting exact answer - are your spreadsheet identical?
- All worksheets are processed in one go
- Changed cells are highligthed with color. xlCompare points you directly to the differences.
- xlCompare quickly compares large spreadsheets
- xlCompare displays difference between changed cell values
- Filter matching and inserted rows
- Create difference report
- This solution is free only during the evaluation period.
xlCompare is reliable tool that quickly matches data in your Excel spreadsheets.
How do I compare two Excel files?
- Open xlCompare
- Drop first Excel workbook from the Windows Explorer into left panel of xlCompare
- Drop second Excel file into right panel
- One the Home tab choose Compare Files command
- In the Comparison Wizard review list of worksheets and primary keys
- Complete the Wizard to run the comparison
- The results of the comparison appear on the bottom of the Excel Compare window. This panel is called Difference Explorer. Changes are highlighted by color, depending on the kind of change.
How do you check if two Excel files are different?
You may think that there will be a lot of work, but you can actually compare two Excel files pretty easily. You need to know Excel for this.
Unfortunately, Conditional Formatting Rule can't depend on the cell from other workbook. Dependency is possible only within same file. So Conditional Formatting doesn't help in our case.
You need to place your workbook one versus one using View Side by Side command on the View tab and select the vertical split option.
Now scroll through the worksheets and compare them for differences.
xlCompare greatly simplifies this operation. Look into the next topic for details.
How To Check If Two Microsoft Excel Sheets Has Changed Cells
- Open xlCompare
- Drop your Excel files into xlCompare window
- After you drop second file xlCompare opens Comparison Wizard
- Press Compare button in the Wizard window
- Get the message from xlCompare that your workbooks are identical or get the detailed list of changes
How to compare two Excel sheets for differences in values
Get more information about comparison process. How we compare excel files for differences, what types of excel files are processed and what operations xlCompare performs to deliver you best comparison results. How to consolidate and update excel files and what do you need for this. Read about this process in detail. Compare 2 excel columns for differences and get the outlined report with explanation for the every found difference.
Spreadsheets are widely involved into all kinds of business processes. Excel File (workbook) is very suitable form to store and interchange datasets.
Some of the third-party applications give you table data in a form of the text file, where values are delimited with comma, space, tab or any other characters. This is various types of application logs, exported transactions, financial statements and other data. It can't be procced by a usual text comparator. We offer you xlCompare as a text file comparison tool for table type data. On our website below describes options you can use to open and compare text (CSV) files, delimited with specific character, in the xlCompare.
If you are working with XLSX files, xlCompare is an Excel file comparison tool you need to compare and merge XLSX files into one. This is fast and feature rich xlcomparator. Spreadsheets a widely used by Excel users, so spreadsheet comparison and merging work is a part of regular office work. xlCompare will greately help you with this.