This post is a continuation of Microsoft Excel Advanced #3 where two columns were compared. In the figure below, we check in column C whether each value in column B is in column A. If it is, then "yes" is placed in column C. If it is not, then "no" is placed in column C. Note that the formula for doing this is shown in Microsoft Excel Advanced #3.
If you have many values that need to be checked, then going through all of column C looking for "yes" and "no" can be time consuming. To help with this, we select "Conditional Formatting" and "Use a formula to determine which cells to format".
Since cell C2 contains "yes", cell B2 is filled with red.
We want to apply this formula to the rest of the values in column B so we need to copy cell B2 and then Paste Special - Formats. Make sure that you paste only the formats. If you do a copy and paste, you will paste the contents as well, but we don't want that.
Now all of the red shaded cells (all that have "yes" in column C) will be grouped together.
If you have many values that need to be checked, then going through all of column C looking for "yes" and "no" can be time consuming. To help with this, we select "Conditional Formatting" and "Use a formula to determine which cells to format".
The formula that we write for cell B2 is as follows: =c2="yes". This is shown in the figure below.
We also indicate that we want the fill to be red if the formula is true.
We want to apply this formula to the rest of the values in column B so we need to copy cell B2 and then Paste Special - Formats. Make sure that you paste only the formats. If you do a copy and paste, you will paste the contents as well, but we don't want that.
Below we see that both cell B2 and cell B5 are now red.
Once that is completed, you can then sort by color. To do this, we select all values in columns B and C and then select Data - Sort. We are sorting by Column B and sorting on the color red.
Comments
Post a Comment