Wednesday, April 30, 2014

Spreadsheet tip - Conditional formatting is good, but sometimes =IF() is better

In my last post, I mentioned that I recently worked on a big mail merge exercise producing 6000+ employee letters.

Well, as part of that exercise, one of the things I needed to do was merge data from a few other spreadsheets into one big worksheet. (I'll explain how I did that some other time.)

When merging spreadsheets, usually there is also a need to compare data and mark (or otherwise identify) differences in the data. Most spreadsheet applications provide conditional formatting as way to do this. Conditional formatting allows the user to mark or highlight cells based on content or formula rules. But, it is not always useful to use conditional formatting only, as there is often no easy way to filter data based on cell formatting.

(OK, yes, there is this Microsoft knowledge base article: https://support.microsoft.com/kb/213923 - but, I have to say it seems complicated compared to what I am going to explain next.)

A simple method I use is to insert an  =IF() formula in a separate column to give my spreadsheets something to filter. For example, when I've merged data from different worksheets, a simple thing I want to know (and filter on) is whether two cell values are the same. So the formula t use is something like:

=IF(A2=B2,"","X")

This translates to:

if Cell A2 is equal to Cell B2, then do nothing; else if Cell A2 is not equal to Cell B2, show "X"

After copying this formula down through all the rows I need to compare, I can set the spreadsheet to filter on "X", and show me all the cells where the data in Column A is not the same as the data in Column B.

=IF() formulas are very useful and versatile, and you can create a range of formula "rules" from simple to complex.

To beautify my spreadsheets I often combine conditional formatting with =IF() "rules" of this kind. They go well together, and very often the same =IF() formula can be used to create the conditional formatting.

No comments:

Post a Comment