To display all the values unsorted, click on ‘Clear Filter’ and the column will show all values again. You do the same if you need to filter out only the duplicates – simply use the option ‘Sort by Color’ again to have the duplicates displayed all together. The filter option now shows in the header of both columns and we can conveniently sort the data in a column based on the colour.įor example, we can display only the unique values in the first column. Select the whole area (header included), go to the Home tab, click on ‘Sort & Filter’ and choose ‘Filter’. How to Turn on the Filter to Help You Sort the ValuesĪs soon as the unique values have been highlighted, we can turn on the filter to help us sort the values, which might help in further data processing. Confirm, and let’s have a look at other useful tricks that can help you efficiently compare the column contents. Let’s use this option and have the values highlighted in yellow. Of course, we can choose the colour we want to use to highlight the values. So, we want to highlight the values that are present only in one of the columns. Here in this window, we can choose whether we want to highlight duplicates – the values showing in the selected area twice, or we can choose the option ‘Unique’, which means that Excel will highlight the values that occur in both columns only once.Īnd this is exactly what we want because we can quickly find the differences between the columns in terms of the values they contain. Then we go to the Home tab, click on Conditional Formatting and hover over ‘Highlight Cell Rules’.įrom the options that appeared, we select ‘Duplicate Values’. To find differences between the contents of two columns in Excel, we need to select both columns first. Here we discuss Compare Two Columns in Excel and how to use the Compare Two Columns in Excel along with practical examples and a downloadable excel template.4 How to Remove to Highlighted Data How to Compare Two Columns in Excel to Find Differences This has been a guide to Compare Two Columns in Excel. Through these techniques, we can easily compare the data in the dataset and highlight those cells.We also can highlight the data for the unique values also.Things to Remember about Compare Two Columns in Excel Click for a Duplicate option in the left side field and choose the color for highlighting the cells. It will open a dialog box for Duplicate Values.Now click on Duplicate Values from the list. Click on Highlight Cells Rules, and it will again open a list of options. Click on Conditional formatting under the Styles section. Let’s take the below example to understand this process. Here we are comparing the data in the whole list column-wise. Click on the Fill tab and choose the color from the color palette.The formula we have used here is =$B4=$C4.Click on the Format button, and it will display the Format Cells window. After clicking on this option, it will display a Formula field, as shown in the below screenshot. Click on the last option, “Use a Formula to determine which cells to format”, under the Select a Rule Type section.It will open a dialog box for New Formatting Rule, as shown in the below screenshot.It will open a drop-down list of formatting options.Go to the HOME tab and click on Conditional Formatting under the Styles section.Here we have selected cell range B4: C18. We have given year-wise performance data where we want to analyze if we have achieved the same performance in the same year. If the same data point exists in both the columns row-wise, then we want to highlight those cells. Using Conditional Formatting – Example #2īy using conditional formatting, we can highlight the data in cells.
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |