To use the formula, click the Formula tab and then click the Lookup & Reference button and choose MATCH from the list. MATCH is part of the Lookup & Reference functions within Excel. For the purposes of this tutorial we will add the match function via the formulas menu. To use the Match function in Excel you can either use the Formula tab to insert the formula into the cell or you can type “=MATCH” into the cell address to initiate the function. If the value is not found then the function returns an #N/A to indicate that there were no matches found. The Match function searches for a value that matches the value you specify and then returns the row number of that value if the value is found. The LOOKUP function, the VLOOKUP function, and the MATCH function can all be used to compare values within columns of data depending on the results you need.įor the next example we will use the MATCH function to compare our two columns of data. Using the Match function to compare two columns of data within ExcelĮxcel offers a number of different formulas that allow you to compare data in two columns. It may also not be practical in long lists of data or large spreadsheets.įor an advanced course in Excel, why not sign up for the Advanced Excel Training course from udemy today and really learn to harness the power of Excel.
The problem with conditional formatting is that it is purely visual and does not help you to manipulate the data. It is clear if you look at the two lists above that Sarah is missing from the 2nd quarter sales and that Adam did not order in the first quarter.Īs you can see, from the above, conditional formatting is a very efficient and quick way to compare two columns of data within excel. The result of conditional formatting of our data is shown below: I chose the first option which formats all duplicate values in Red with a red fill for each cell, but you can choose whatever option suits you. You can then select the Color Formats you would like to use to highlight the duplicate values. For this example we will select the “Highlight Cell Rules” option and then choose “Duplicate Values”. The conditional formatting tab has a number of options to help you format data. To use the conditional formatting rules on your data, highlight the data you want to format and then choose “conditional formatting” from the Home tab in Excel.įor our example, I have selected the client names in column B and then clicked the “conditional formatting” tab. This function is in fact two functions in one, since it shows you which are duplicate values and at the same time, logically speaking, shows you which values are unique. In other words we are looking for unique values within the two tables of data.Ĭonditional formatting allows you to format data to highlight duplicate values. Let us assume that we want to know if there are clients that only ordered goods in the first quarter or if there are clients who only ordered goods in the second quarter. For our example, we will use a spreadsheet that contains orders from our clients. You can, for example, highlight all values that are greater than a certain value, or less than a certain value, as well as various other functions.Ĭonditional formatting also allows you to quickly and efficiently look for duplicate values within a set of data. Conditional formatting allows you to highlight values that meet specific requirements you set. This formatting is achieved through “conditional formatting.” Conditional formatting in Excel is a really simple way of visually highlighting values within your spreadsheet data. A simple visual comparison of two columns in ExcelĮxcel offers a way of formatting the data in columns to highlight data depending on your specific needs and requirements.
#Compare two columns in excel how to
To apply the concepts in this tutorial you need a basic understanding of how to work in Excel so a course Excel 2013 made easy will give you the skills you need to put the principles of this tutorial into practice.
This tutorial will show you two different ways to compare columns of data within Excel. There are also various ways of comparing data within Excel depending on the results you are looking for.
#Compare two columns in excel plus
For example, you can add columns of numbers by using the SUM function or you can add the numbers within a column individually using the PLUS function. Excel is a powerful spreadsheet that offers a multitude of functions that allows the user to manipulate data, and due to the sheer number of functions, there is often more than one way of finding a solution to a problem.
In the world of computers and programming, that is particularly true. There is often more than one solution to a problem.
0 kommentar(er)
