Using the IF Statement to highlight due and not due statements
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
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.