Tag: Excel

Spreadsheet highlight common cell values between two columns

Scenario

Suppose we are having two columns in a spreadsheet and we are trying to find the common values between them. That is highlight column values if they are present in another column.

For example lets say we have some ids in two different columns. Both columns have some data in common and some data exclusive to each other. But our focus here is to highlight the values in second column if they are present in first column and vice-versa.

To do this we are going to use conditional formatting, as the name suggests it will format the cells based on a condition. Here we are going to specify our own condition.

Look at the image below

spreadsheet with two columns with values/ids

Here we want to highlight the values in column ‘D’ if they are present in column ‘A’.

So to do that, first select the column i.e column ‘D’ and then go to conditional formatting. Go to Format->Conditional -> Condition->Formula (choose from dropdown) in the input box enter the formula COUNTIF($A:$A, $D1) like in the image below.

conditional formatting applied on 3rd column

After you click ‘OK’ you can see that 2nd column values that are present in column 1 will be highlighted like below.

image showing values in 2nd column being highlighted

Now to do the reverse i.e to highlight values in first column from 3rd column we need to do apply similar formula COUNTIF($D:$D, $A1) on column ‘A’.

Note
  1. You can remove conditional formatting rule that has been applied
  2. For this formulae to apply there should be common values between these columns.
  3. We can choose custom formatting like different color, size etc for conditional formatting.

You can also watch video to see how it is done in Google Spreadsheet

Excel Spreadsheet add value in cells/columns based on a condition from another cells/column

The function SUM in Excel is quite useful to add values in a column. But this one is not useful when we have to count the values based on a criteria that should satisfy in another column.

For Example we have a scenario where we have to count items cost that are delivered and undelivered and Grand Total. To get the Grand Total we can use sum function directly. But to count total price which are delivered and undelivered we have to use the sumif function. Look at below screenshots.

To calculate all items Total:

To calculate Items that are delivered:

To calculate Items that are undelivered

This is how it looks finally