How to Compare Two Columns in WPS Office Excel

Whether you are an accountant or an employer working with data analytics, you will find the need to compare two columns in Excel to find out the differences that could be helpful. Manually comparing multiple columns in Excel can be so much difficult and can take hours of hard work, which can ultimately decrease your efficiency in work.

That’s why the best way to do this is by adopting an automatic way to do that. You can use certain formulas and functions to achieve that. Below in this article, we are going to explain the top 3 ways to do this. Read on to find out.

Way 1. Using Conditional Formatting in Excel

1. Greater Than

In Excel, you can use the greater than function to compare columns using logical operators. The greater than operator ">" returns "TRUE" if the value in one cell is greater than the value in another cell and "FALSE" otherwise.

To compare two columns and get the result in a third column, you can use a combination of the IF and greater than operators. Folow these simple detailed steps to perform this function:

Steps

• Open the Excel worksheet on your PC and enter the values you want to add to the sheet. For this example, suppose you want to make an Excel sheet based on the attendance percentage of the employees.

• Select a specific column and go to the Home tab in Excel. There you will see a conditional formatting option whose drop-down will take you to functions. The first of them is greater than the function used to compare two or more columns. Select it.

• You can then write down the cell range you want to add the value you want the program to fetch from the column. You will also be able to select the formatting and highlighting style of the cells in column G if they are greater than the values in column F.

• Then, you can set the format for the cells in the column that are according to the conditional rules of the previous column.

• Now click OK. The formatting and function will be applied to the column values of the cells you want.

For Duplicate Values

When comparing two Excel columns for duplicate values, you are looking for instances where the same value appears in both columns. In other words, you want to identify any values that are duplicated in both Column A and Column B.

To achieve this, you can use Excel's built-in functions, such as COUNTIF or Conditional Formatting. Just try to follow the detailed steps with images to perform this function for duplicate values.

Steps

• Open a file in the Excel worksheet where you have at least two columns of data with percentages of employee’s attendance. We need to find employees who have the same attendance between column F and column G.

• Select both columns and go to the “Home” tab where you will find the “Conditional Formatting” option. By expanding this option, you will find “Highlight Cell Rules” and after this option, select “duplicate values”.

• You will be asked to select the formatting options you need to apply and the condition you want to Select the cell range to format according to specified conditional rules. (Duplicate in this case)

• Once you have applied the formatting and clicked OK, you will find that similar cells are highlighted with the color you selected for the formatting.

Way 2. Using Equals Operator

Just as you did in the above method to find the duplicate terms, you will have to use the equal operator to find similar terms in two columns. This is also a process of comparing two columns, so let’s see what is included in the steps.

All the steps included in this process are the same as mentioned above, except you will have to select “unique” from the drop-down list in place of “duplicate”.

Way 3. Using Vlookup Function (for Matches)

The VLOOKUP function is the easiest function to compare the values of two columns and you can use it for the above example easily. It stands for "Vertical Lookup" and is used to search for a value in the leftmost column of a table and retrieve a corresponding value from a specified column in the same row.

This function can be the best for comparing two columns especially when you are dealing with text fields of two columns.

Steps

• Open the same Excel sheet as you used in the above ways to compare two columns. You already have two columns with different attendance percentages of the employees in a company.

• Vlookup formula will allow you to find a specific value from one column in the second column. To do this, you will have to type the formula in any of the cells of the Excel sheet.

Simply put, the VLOOKUP function will state the following pattern of queries:

=VLOOKUP (the cell you want to look for, where to look for this value, column number in the range, the approximate or exact match of the phrase 1/TRUE or 0/FALSE).

• Once you have typed the formula in the address bar, press ENTER and the value that matches any value in the second column. After pressing the ENTER, the result will be displayed in the formula bar. This is the value that was present in the first column as well as the second one.

Free Alternatives to Microsoft Office

If you are feeling that you are having difficulty formatting the cells in Microsoft Excel or your PC does not have enough space in your disk storage. All of these problems have a common solution, and that is using WPS Office Suite in place of Microsoft Office.

WPS Office is a complete suite that includes a Word editor, Spreadsheet creator and editor, Presentation maker, and PDF editor. All of these programs have their own usage and can be used for daily life tasks in the office as well as during study. The best part is that this software is absolutely free to use with the same functionalities as MS Office.

Pros of WPS Office:

• Easy way to edit and share document files

• A comprehensive software tool to play with all document formats

• Intuitive interface and steps to navigate

• Cloud synchronization and storage

• Excellent accessibility features

• Compatible with multiple platforms and devices

• Free usage for most of the advanced features

FAQs

Q. 1. How can all the tick marks be removed from a Microsoft Excel workbook at once?

Below is a brief step-by-step guide to removing all tick marks in a Microsoft Excel workbook:

1. Press Ctrl + A to select all cells in the workbook

2. Right-click on any selected cell, and from the context menu, choose "Format Cells."

3. Go to the Format Cells options dialog box and then opt for the "Number" tab.

4. Select "Custom" from the Category list on the left.

5. In the "Type" field, delete the existing format code and replace it with a single hash (#) symbol.

6. Click Ok to apply changes.

Q. 2: How to compare columns in the Google Sheets?

The method of comparing columns in Google Sheets is quite the same as in MS Excel. You can use Conditional Formatting or using Equals Operator, or Vlookup Function to compare columns in the Google Sheets.

You can also use the IF function to compare column values in Google Sheets. This function is used to point out values that are the same in two columns and to find the similarity in the text within one Excel sheet.

Final Thoughts

In the above paragraphs, we mentioned 3 to four ways to compare two or more than two cells in Microsoft Excel. These include using conditional formatting, equals operator, or Vlookup function in the Excel sheet. All of these ways have their own benefits and usage and can be used in different conditions to compare two or more cells in Microsoft Excel.

However, if you don’t have enough space on your PC or don’t have enough budget to buy an MS Office subscription, there are other alternatives you can use in place of MS Excel sheets. The best of all alternatives is WPS Office. WPS is a complete Office Suite with all the same functionalities and features and that too for free.