Following on from my previous post about invoice lists, this tutorial will show you how to set up conditional formatting within the list in Excel – I hope you find it useful 🙂

I find that a handy format to use is to have the Due Date cell turn red once it’s overdue.  In order to achieve this we need to do 2 things.  The first is to have “today’s” date somewhere within the spreadsheet. Type in the formula:

=today()

This will automatically show the date of when you open the spreadsheet. I tend to include it at the top of the spreadsheet in a lighter or different colour to everything else…

The second thing we need is to make sure the due date column has a formula in it. This would depend on what your payment terms are.  For example, if your terms are 7 days from the date of invoice then the formula would be the “Invoice Date” cell + 7, if terms were 28 days then the formula would be “Invoice Date” cell + 28, and so on…

Then we need to set a condition to format the Due Date column. To do this, select the first cell contained within the table under the Due Date heading, and click on Conditional Formatting in the Home tab on the ribbon, and select New Rule…

This will open up a dialogue box asking us to select which type of rule we want.  In this case it will be a formula, so click on “Use a formula to determine which cells to format”…

Type in the formula into the box. We basically want to format the cell if it is earlier than today’s date, so we would use the following formula…

“Due Date” cell is < (less than) the “Today’s Date” cell.  So in this example it would be “G4<A1”. Type this in the box…

We need to make cell A1 an absolute reference (fixed) otherwise the cell will move down one row when the formula is pasted down the rest of the column.  With the cursor positioned after the “1” in the formula, press F4 and you’ll notice the dollar sign appear either side of the A and 1…

This means that when the formula is dragged (or copied & pasted) down the rest of the column, it will still refer to cell A1 which contains today’s date.  We now need to set a format e.g. turn the cell red if it’s overdue.  Click on Format…

Click into the Fill tab, and choose whichever colour you prefer, in this example I’ll use bright red, and click OK…

The preview box will now be whatever colour you’ve chosen, click OK again and you’ll notice that the cell in Due Date column has now changed – this is because the due date (8th Feb) is earlier than today’s date (9th Feb)…

The formula and formatting now needs to be copied down the rest of the column. A quick way of doing this is to double click the bottom right corner of the cell…

This will then copy everything down to the last row within the table, the only problem is all the cells have now turned red! This is because there is no date in the Invoice Date column for these rows, so Excel automatically takes 1st Jan as the date…

As soon as you start inputting the Invoice Dates on the next rows down, it will change the Due Date cells and the formatting will work…

If you don’t want the red cells to be there until you fill in the rest of the table, then one way round it is to change the Due Date formula to an “IF” statement.  So, for example, we would say that IF the Invoice Date cell is blank, then the Due Date cell should also be blank.  To do this we would use the following formula…

Again, double click on the bottom right corner of the cell and it will automatically paste the new formula down the rest of the column in the table. You’ll see that the cells immediately go blank, but as soon as you start entering details into the table, the Due Date column will automatically update…

And that’s it! It may sound a bit confusing to anyone who doesn’t normally use formulas, but in this example conditional formatting is a great way of seeing immediately which invoices need to be chased for payment.  And as business owners, keeping an eye on cash flow is vital!