How To Find The Difference In Excel

Excel is extremely versatile and this leads to it being the most pop spreadsheet software in the market today. This widespread use is more often that not likely to lead to situations where you desire to find the difference between certain tables, columns or unabridged spreadsheets. In this article, we walk you through just how to do that.

We assume you have a basic working noesis of Excel.If not, nosotros do recommend yous try out this Excel course that'due south been made actually easy or alternatively this Excel crash course for beginners.  Once you're washed, read on to learn most some of the near commonly used functions to compare and retrieve data in Excel.

Use Conditional Formatting to Differentiate and Highlight Values

Say you have two lists of data (List 1 and List2) in your excel worksheet. You want to compare them to discover out whether they have some common data, or highlight the dissimilar information sets. How would yous exercise it? Here's one mode you can use to compare two lists and identify the different or duplicate data.

1. Select the two columns that you want to compare
2. In the Habitation tab, click "Conditional Formatting". You will get a drop-down menu.
3. Click on "Highlight Prison cell Rules," and then go straight to "More Rules" and click on information technology.
4. Here you will find many options. Depending upon your preference choose one of them. For example, say you desire to highlight all the data that'southward dissimilar in the both the columns. First select "Format simply unique or duplicate values.", then, choose "Format just unique". Alternatively, if you want to format only duplicate values, select "Duplicate values".
5. Now Click the "Format" button and set up the font, edge and fill options to highlight the cells y'all want.
6. Click "OK" to ostend the changes.

Using IF part to differentiate between values of two lists

If you have two columns containing numerical values, you lot tin use the If() function to find whether the value of ane cavalcade is greater than the other,  less, equal to or not equal to. Just follow these simple steps.

1. Lets say you have the list values in A1 and B1. Now identify your cursor in C1.
2. Enter the part, "=if()". Figure how  you lot want to compare the values in A1 to B1 – equal, greater, lesser etc. IF () has three parts. The offset part is the provisional function, the second is the cell value if the condition is met and the 3rd role is the jail cell value if the condition is not met. Hither are the formulas that you can utilize.

• =IF(A1>B1,"True","Imitation")
• =IF(A1<B1,"True","Fake")
• =IF(A1=B1,"Truthful,"False")
• =IF(A3<>B3,"truthful","fake") (non equal to)

You tin use one of the functions at a time.

1. Enter the formula and press the "Enter" Key.
2. Now yous can either copy and paste the formula into C2, C3  and so on. Or yous can concur the handler of your cursor in C1 and drag it down.

How to Compare information in 2 Excel Spreadsheets

Say y'all desire to compare two unlike spreadsheets to figure whether or not the data in them is different, or alternatively, whether in that location are whatever duplicate rows? Manually going through the entire list of data is a herculean task. Fortunately, MS Excel has a part known equally Countif () which makes this job easy. Let'south have a look at how this office works.

1. Let's say your data is saved in Sheet1 and Sheet2 of the Excel workbook.
2. Enter the following formula in jail cell B1 of Sheet1 =COUNTIF(Sheet1!A:A,Sheet2!A) and Printing Enter. If the record is unique the effect will be 0, else the count will tell you how many rows in Sheet2 lucifer cavalcade A of Sheet1.
3. Copy the formula from cell B1 through every bit many rows as yous have in the tabular array on Sheet1.

Using Vlookup

This part is usually used to search and compare data in an Excel worksheet rapidly. "V" in Vlookup stands for "Vertical." This office has 4 parts – the lookup value, table range containing the value, the column to brandish once the match is found and the type of lucifer. You can play effectually with this in diverse means to compare or find differences in your data. Hither's i example.

1. Presume that y'all accept a three column table which contains the Employee ID, Section and Proper noun of the Employee. Let'due south say want to return the proper name of the employee when you know his/her Employee ID. Follow the steps carefully.
2. Sort the values either in the ascending or descending club. Then click the first cell of the fourth cavalcade. Click on the "Formulas" tab and select the "Lookup & Reference" button.
3. From the drop-down list cull Vlookup(). The dialogue box opens.
4. In the ""Lookup value" field, enter the value y'all have to search.
5. Click the "Table Array" field, to select range of cells or the entire table which contains the data y'all are searching for.
6. In the "Column Index Number" field, enter "iii," that is it has to search in all three columns.
7. In the "Range Lookup" field, enter "False." False indicates the verbal match, while truthful will find a close enough lucifer.  In the fx bar, the office will look like this – =VLOOKUP(38,A2:C10,3,Fake)
8. Now click Ok Push button. Excel will compare the "lookup value" against the iii columns and display the jail cell value in the fourth column, if the match is establish.

If y'all need to go deeper or are looking at a actually large data set, there are a bunch of special tools that aid y'all compare not only xls files, but as well files in csv and other popular formats. One such tool is ExcelDiff by Suntrap systems. It detects difference in jail cell content, type, formatting and presents it in a piece of cake-to-understand visual format. They have a gratuitous 30 day trial in case you'd like to experiment.  Some other such popular tool is the DiffEngineX which is a scrap more powerful in that it lets you compare "groups" of differences and even highlights the exact deviation, even in formulas.

Of form, information technology's always better to endeavour things out for yourself. We're always at that place to help you lot out similar with our advanced Excel grade, that can show you other better ways to achieve your goals.

Source: https://blog.udemy.com/excel-diff/

Posted by: cooperowas1981.blogspot.com

Share this post