Excel conditional formatting to highlight the difference between 2 data sets

I had a situation to provide differences between 2 data sets with most readable format to business, based on existing data and differences business will make a decision on next course of action.

After multiple different experiments, I have provided the data in Excel sheet by highlighting the differences. I just used conditional formatting feature!
Considering the time invested I thought it is worth to use the Excel as template.

• Consolidate the data into single Excel sheet, sort with primary key. Make sure that table1 and table2 data is in consecutive rows
excel_1
• Identify if there is any difference using simple formula (=IF(Sheet1!B3=Sheet1!B2,0,1)) in a difference sheet
o Based on this formula, we will highlight the differences in the first sheet.
excel_2
• Enable Conditional Formatting on B2
o Home -> Conditional Formatting -> Manage Rules/New Rule
excel_3
• Use a formula to determine which cells to format -> =Sheet2!B2=1 -> Select format
excel_4
excel_5
• Change Applies to value to include all cells to be formatted.
excel_6
• Change the formatting to give same color to 2 rows to improve readability.
excel_7

In this example, there are 3 changes which are highlighted and easy to understand! Sample is given for small data set but the same can be used for huge data…

Excel sheet is available for reference at: https://onedrive.live.com/redir?resid=4A5897AB0DE9AFDC!48987&authkey=!ADzOkxOwyiY4V08&ithint=file%2cxlsx

Advertisements
This entry was posted in Productivity, Uncategorized and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s