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!
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.
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.
- Select the first data set – cells A1:D19.
- Hold down the CTRL key and highlight the second data set – cells H1:k19.
- On the right of the Home tab, in the Editing group, click on the Find & Select button.
- Select Go To Special.
- In the top right of this box select Row Differences.
- 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.
- 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.
- I press F4 again to compare the two Date columns – and we can see three more changes highlighted in the right-hand side table.
- 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.
- Now all I need to do is go to the Fill Color button and select a colour.
- 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.
Thank you for watching and I’ll be back with another useful ‘Keep it Simple‘ tip – next month!