Comparing two excel sheets and get differences

drop05

Active Member
Joined
Mar 23, 2021
Messages
285
Office Version
  1. 365
Platform
  1. Windows
Hello, am wondering if there is a way to get two excel sheets and compare them

The two are the same format wise and should have the same values, but sometimes we make mistakes and put the wrong number in and want to see where the difference is between the two. Is there a way to do this with vba or excel over all? And maybe have it create a table to show where the value is different like the cell?
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
conditional format may do that
The two are the same format wise and should have the same values,

if it starts in A2 and the other sheet is the same order sheet2!A2

then you could just do

=Sheet1!A2<>Sheet2!A2
and colour

And then on the other sheet add
=Sheet1!A2<>Sheet2!A2


for example i setup 2 sheets
in Sheet1 - i made a few changes - so differences existed

Book2
ABCDE
1
212345
3678910
41112131415
51617181920
62122232425
72627282930
83132333435
93637383940
104142434445
114647484950
125152535455
135657585960
146162636465
156667686970
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:E15Expression=A2<>Sheet2!A2textNO


Heres sheet 2 -

Book2
ABCDE
1
212345
367893
41112131415
5161181920
6212223125
72627282930
8311333435
9363738240
104142434445
114647484950
12514535455
135657585960
146162636465
156667686970
16
Sheet2
 
Upvote 0
conditional format may do that
The two are the same format wise and should have the same values,

if it starts in A2 and the other sheet is the same order sheet2!A2

then you could just do

=Sheet1!A2<>Sheet2!A2
and colour

And then on the other sheet add
=Sheet1!A2<>Sheet2!A2


for example i setup 2 sheets
in Sheet1 - i made a few changes - so differences existed

Book2
ABCDE
1
212345
3678910
41112131415
51617181920
62122232425
72627282930
83132333435
93637383940
104142434445
114647484950
125152535455
135657585960
146162636465
156667686970
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:E15Expression=A2<>Sheet2!A2textNO


Heres sheet 2 -

Book2
ABCDE
1
212345
367893
41112131415
5161181920
6212223125
72627282930
8311333435
9363738240
104142434445
114647484950
12514535455
135657585960
146162636465
156667686970
16
Sheet2
this is very helpful, one questions is how did you get it to include the range? I am doing it for a cell but would like it to have the conditional formatting for a range on the sheets
 
Upvote 0
but would like it to have the conditional formatting for a range on the sheets
can you explain further
the example i gave was for a range - but checking same cells on both sheets

did you want to check if a value exists in a range ? regardless of column and row
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top