#Compare two columns in excel and delete matches how to#
Locate the "Fill" option in the "Editing" section of the ribbon, then select "Down." When you do this, the rest of the cells fill to reflect if the two columns match or not.ĪBC10FALSE1212TRUE45FALSE77TRUE61FALSE Related: How To Calculate Average in Excel (With Examples) How to compare columns using the IF formula To quickly apply the "=A1=B1" formula to the rest of the column, you can select the empty cells as far down as the data goes. Select "Fill" and "Down for the blank column If the two pieces of data match, the cell changes to "True," and if they don't, it changes to "False."ĪBC10"=A1=B1"1212 45 77 61 4. Depending on the data in the columns, the cell changes to reflect if the equation is true or false. To compare the data from A1 and B1, write the equation "=A1=B1" and press "Enter". In the top cell of the blank column, write the equation If the space next to your data-filled columns is already blank, you can use the blank space instead of inserting another column. When you do this, the blank column inserts to the left, placing it beside your data. To insert a blank column, click on the column header to the right of the columns you want to compare.
To insert the equation, you want a blank column to the right of the two with your data. Insert a blank column to the right of the two columns Alter, add or remove any data you want to before comparing the two columns. Find the data you want to compare and ensure it's in the same sheet and side by side. To compare the data across rows in two columns in Excel, first launch the program. Launch Excel and locate the data you want to compare These are some steps you can follow to compare two columns in Excel using true or false: 1.
If you want a more general comparison throughout the data, consider the worksheet formula option below. This method of comparing data between two columns in Excel is useful if you want to directly compare the data straight across. Related: What Is the Net Sales Formula? (And How To Calculate It in Excel) How to compare data across columns in Excel These are some functions you can use to compare columns in Excel: Related: 10 Top Excel Accounting Formulas and Functions (With Examples) Functions you can use to compare columnsĭepending on how you want to compare the data in two columns, you can use different functions. For example, you may survey two groups of customers and want to compare the ratings of a product out of five. By comparing two columns in Excel, you can locate differences and similarities in data. You may track orders placed and orders shipped and want to locate unfilled orders. If you manage data in an Excel spreadsheet, you may want to compare the data in two columns for many reasons. In this article, we explain why you may want to compare two columns in Excel, detail functions you can use and provide three methods you can use to compare the data from two columns. If you want to compare two columns in Excel, learning more about the functions you can use to do so can be helpful.
Depending on the data you have in your spreadsheet, it can be helpful to compare columns to see how the data differs. Note: take a look at the third picture on this page to see that we swapped the last 2 arguments of the IF function.Excel is a spreadsheet program you can use to manage and organize data. You can also display the unique values in the second column. Note: take a look at the second picture on this page to see that we swapped the last 2 arguments of the IF function.Ģ. Display the unique values in the first column (these values do not occur in the second column). Unique Values in Each Columnĭo you want to compare two columns by displaying the unique values in each column? Simply swap the last 2 arguments of the IF function.ġ. At step 2, we match each value in the second column with the range in the first column. Note: at step 1, we matched each value in the first column with the range in the second column. You can also display the duplicates in the second column. As a result, the ISERROR function returns TRUE and the IF function returns an empty string.Ģ. The MATCH function in cell C4 returns a #N/A error (no letter D in the range B1:B7). As a result, the ISERROR function returns FALSE and the IF function returns the value in cell A1. Display the duplicates in the first column (these values also occur in the second column).Įxplanation: the MATCH function in cell C1 returns the number 5 (letter A found at position 5 in the range B1:B7). Let's start by comparing two columns and displaying the duplicates.ġ.
0 kommentar(er)
