To IF, or not to IF. That is the question!!

Jul 11, 2018 | Excel Hints and Tips

In Microsoft Excel, there are a huge number of functions that perform different operations and one of these is IF.  It provides the opportunity to do 2 different things based on a specific condition.  The not to IF part is if you want to change the format of cells based on a condition.  For this, you would use conditional formatting instead.  I often get asked on training courses about this.  The IF function cannot change formatting.  The IF function is a powerful tool in its own right but is regularly combined with other functions to provide more flexibility.

IF Function

With any function understanding the structure is vital to get the required result.  Here is the setup for IF

=IF(logical test, value if true, value if false)

Now, unless you are really into this type of thing what on earth does this mean.  Let me translate!!  If a condition is met we do one thing otherwise we do something else.  For example, if more than 10 of a product are purchased we will give a 10% discount otherwise we will charge full price.  It can be used in many different situations.  Another is if we sell more than our target value then we put in the text “Yes” otherwise the text “No”.  This is often performed on 1 cell and then autofilled for others.

IF

The function can either be typed into the cell (function name and cell references can be upper or lower case) or by using the Insert Function button from the formula bar or formula tab.  The insert function helps guide you through the process and is particularly useful if you are using a function for the first time or haven’t used it for a while (see below).  Because this is a function if the data changes the result automatically updates.

Insert function

But this doesn’t work for colours.  For that we will need conditional formatting.

Conditional Formatting

Conditional formatting changes the format of the cells based on a condition.  Here we will look at just one of many of the options that can be used.  If the information changes then the formatting also changes.

Firstly, select the cells that you want to apply the conditional format to.  On the home tab select Conditional Formatting then Highlight Cell Rules.

Conditional formatting

From here the cells obeying certain criteria can be formatted as required.  There are preset options to choose from or custom format can be used for flexibility so you can go as subtle or bonkers as you like.  These options can be layered for multiple different types of format for different sets of data.  This makes it so much easier to see figures that are particularly high or low, or specific text that is important.  Without this, you can miss the most important results from a sea of text and numbers.

Want to learn more about what IF functions or conditional formatting can do then email me at lara@laramellortraining.co.uk to discuss your training requirements