Tips from Mr.Excel

Tips from Mr.Excel

Part 1

Using the IF Statement to highlight due and not due statements

Hello,

Many, in my classes, in my Facebook escapades, at functions have always sought to learn how to have this and many other tasks accomplished using Microsoft Excel. I must say that the majority have been Accountants. This is not to say, however, that other professions will not benefit from this tip.

 

This will be the first of a series of articles called….”Tips from Mr.Excel”

In today’s tip, I will be using screen shots taken from Excel 2013. Those using Excel 2007+ can still follow through.

Let’s get started folks. Select the images to see a clearer view.

Open a new Microsoft Excel Workbook

Type the following values in the respective cells so that you have something like this:

Click inside Cell E2, select the HOME Tab and under the Font group, click the small down arrow next to the Fill Color command and select the Red Color (See image below)

Click inside cell E3 and following a similar procedure to that in the previous bullet, fill E3 with a blue color.

Make all your headings bold

Select cells B2 to B3

Select the HOME Tab and in the Styles group, select Conditional formatting…New Rule.

In the New Formatting Rule Dialog that pops up, select “Use a formula to determine which cells to format”

In the Edit Formatting Rule Dialog, type the following formula:


The formula simply means that if the values in cell B2 contain a date equal to or before today, return TRUE, else return FALSE.
Let’s now define the color to shade all cells that satisfy that condition

Select the format command in that dialog box to invoke the Format Cells dialog

Select the Fill Tab and then Red as in the image below

After confirming that you have chosen Red by looking at the sample beneath, click OK

Click OK as well in the Edit Formatting Rule Dialog

Repeat the procedure that started at bullet 6 above but this time, use the following options
Formula: =IF($B2<=TODAY(),TRUE,FALSE)=FALSE
Color: Blue
Your final result should look something like this

Try typing other dates into those 2 cells and see how the colors dynamically change!!

Please note that you will have to select one of the 2 cells B2 or B3 and Copy…Paste Special….Formats to all the cells below B3 where you will eventually add more data. You do not need to do this if you are using Excel Tables though.

Mr.Excel

This email address is being protected from spambots. You need JavaScript enabled to view it.