Excel Tip – Quickly Compare Two Datasets

It’s December 1st which means CHRISTMAS is coming, YAAAY! It’s also time for another TIP – double YAAAY!

Well, today, you’re in for a treat with this SIMPLE tip! This is one of my favourite features to teach – how to quickly compare two datasets and see the differences between them, WITHOUT using a Function or Formula! Don’t believe me? Just wait ’til you see this…

You can view the video or follow the step-by-step shown underneath.

Enjoy and remember: Keep it Simple!

Hello!

You’ll have seen many different ways to compare data in Excel but today I’m going to show my favourite way and it doesn’t require knowing VLOOKUP or any other function!
This is going to blow you away – with its simplicity!
Lets take a look.

In columns A:D I have some data that I’ve copied – to columns H:K.

Showing the two datasets to be compared

In the copied data, I’ve kept all the Invoice Numbers the same, but I’ve made some changes to the data in the other 3 columns.

I’m now going to show you how to highlight those changes.

Step 1

Select the first data set – cells A1:D19.
Hold down the CTRL key and highlight the second data set – cells H1:k19.

Select both datasets using the Control key

Step 2

On the right of the Home tab, in the Editing group, click on the Find & Select button.
Select Go To Special.

Click on the Find & Select button then click the Go To Special button

Step 3

In the top right of this box select Row Differences.

Select Row Differences on the top right of the Go To Special box

Then click OK.
The first columns of our two tables have now been compared and there are no differences showing, because I kept them the same.

Shows that the first column on both tables have been compared and are no longer highlighted

Step 4

I press the F4 key to repeat – the Customer columns are compared – and we can see two changes highlighted in the right-hand side table.

Shows using the F4 to repeat the comparison and 2 cells are now highlighted in the 2nd column of the 2nd table

Step 5

I press F4 again to compare the two Date columns – and we can see three more changes highlighted in the right-hand side table.

The third column has now been compared on both tables and on the right table 3 cells are also highlighted in the 3rd column

Step 6

I press F4 once more to compare the two Amount columns – and we can see three more changes highlighted in the right-hand side table.

Shows 3 more cells highlighted in the right table, 4th column. 8 cells in all are highlighted in the right table.

Step 7

Now all I need to do is go to the Fill Color button and select a colour.

Shows me applying a Fill Colour to the highlighted cells

Step 8

De-select – and now we can easily see the differences between the two datasets.
That, my friends, is simplicity!

Just one caveat before I go – this only works if you place the two datasets on the same sheet.
Easy!

Thank you for watching and I’ll be back with another useful ‘Keep it Simple‘ tip – next month!

1 Comment

Cancel reply

Fab!

Jay Reply March 6, 2020 at 16:37

Excel Tip - Quickly Compare Two Datasets