How to compare two large spreadsheets in Excel - 365 small Bus?

Copper Contributor

Is there an app or way to compare two  spreadsheets using Microsoft Apps for Business?

(Other than side by side which could take for ever 4x over...
I see stuff for office 2013 and 365 for enterpise...  There does not seem to be any add ins that apply.

8 Replies

@Kevin York 

Comparing two large spreadsheets in Excel can be a complex task, especially when dealing with a lot of data. While Excel does not have a built-in feature specifically for spreadsheet comparison, you can use various methods and tools to achieve this, even in Excel 365 for Small Business.

If you are looking for a step-by-step solution to compare two large spreadsheets in Excel 365 Small Business, here's a more detailed approach using conditional formatting to highlight the differences:

Step 1: Prepare Your Spreadsheets

  1. Open both spreadsheets in Excel.

Step 2: Select the Range to Compare 2. In the first spreadsheet, select the range of cells that you want to compare with the second spreadsheet.

Step 3: Access Conditional Formatting 3. Go to the "Home" tab in the Excel ribbon.

Step 4: Create a New Rule 4. In the "Styles" group, click on "Conditional Formatting."

Step 5: Choose "New Rule" 5. Select "New Rule" from the dropdown menu.

Step 6: Use a Formula to Highlight Differences 6. In the "New Formatting Rule" dialog box, choose the option that says "Use a formula to determine which cells to format."

Step 7: Enter the Formula 7. In the "Format values where this formula is true" field, enter a formula to compare the current cell in the first spreadsheet to the corresponding cell in the second spreadsheet. For example:

  • If comparing cell A1 in the first spreadsheet to cell A1 in the second spreadsheet, the formula might be: =A1<>[SecondSpreadsheet.xlsx]Sheet1!A1

Step 8: Set the Formatting 8. Click on the "Format" button to specify the formatting you want for cells that meet the condition. For example, you can choose a different background color.

Step 9: Apply the Rule 9. After defining the formatting, click "OK" to close the "Format Cells" dialog.

Step 10: Review the Rule 10. In the "New Formatting Rule" dialog, you should see a preview of how your changes will look. Click "OK" to apply the rule.

Step 11: Review the Differences 11. Now, Excel will highlight any cells in the selected range where differences exist between the two spreadsheets.

Step 12: Repeat for Other Columns 12. If you want to compare other columns, repeat the above steps for those columns.

By following these steps, you can quickly identify and highlight differences between the two spreadsheets in Excel 365 Small Business. This method allows you to visually spot discrepancies without manually comparing each cell.

Remember to save your work, and make sure both spreadsheets are open while applying the conditional formatting. The text and steps were created with the help of AI.


My answers are voluntary and without guarantee!


Hope this will help you.

Was the answer useful? Mark them as helpful and like it!

This will help all forum participants.

Thanks, Perhaps I will try it, but there are already conditional formatting in place. It is a shame because I have used compare functions or perhaps it was a duplicate file finder... that compared long excel sheets and was smart enough to know when the entries were the same even though they were on different lines. Perhaps there is a way to combine both sheets, sort, then highlight duplicate rows or first cells...
"Spreadsheet Compare is only available with Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, or Microsoft 365 Apps for enterprise."
Is that going to work with my current subscription? (Microsoft 365 Business Standard) the tutorial shows the older (better looking) versions of office aps.



There is no mention of where to get this.  Also, it is not available from within the excel ap.

@Patrick2788   This does not work in my version of office 365.  I have a small business account.


There is no option as listed: 


and it is not in the ribbon.



I cannot find any other information about making it available to excel.  since, as I mentioned above, my version of offices does not include this:   "Inquire is only available in the Office Professional Plus and Microsoft 365 Apps for enterprise editions."


I read the article several times.


Problem is I do not trust free aps, and do not wish to pay more than a days pay for something that I could do in a day.