Using flexible conditional formats in Excel

Intro:
Hello, and welcome to the CPA Australia podcast, your weekly source for accounting education, career and leadership discussion

Neale Blackwood CPA:
In the August 2021 article, I've covered how you can apply conditional formats to a range using a formula. Now, the beauty of using a formula is that you have a lot more options available to you than just the built-in conditional formats. In the companion video for this podcast, I go through a couple of ways to create sequential dates. So, the first technique uses the rows function. So, the rows function calculates how many rows within a range and I will show you a technique where you can create a sequential list of dates that are flexible based on an input date. The rows function works in all versions of Excel. If you are lucky enough to have the subscription version of Excel, then you can use the sequence function, which allows you to create a sequence of dates as well. So, check out the companion video to see how I do that.

Neale Blackwood CPA:
So what I covered in the article was how you can highlight weekends in a list of dates. Now, the table that we're working with has the date and it also has the day in the column next to it. So, what we can do is we can apply the format across the columns. Now, a lot of conditional formats are sort of based on the cell and the cells value. Well, with the formula, you can actually use other values to figure out whether or not to apply a condition and that's what we're going to work through here.

Neale Blackwood CPA:
We select the whole range that we want to work with. We click on the home ribbon and the conditional formatting dropdown and then select new rule. Now, when you're creating a formula-based rule, so that in the top section, you click the bottom option, which is Use a formula to determine which cells to format, and then you use a formula that returns true when you want to apply the format. So that means the type of formula that you would place at the start of an if function. So, you would be checking for something greater than or equal to, those type of comparisons, so that's the type of formula you'll be entering by itself in the condition format formula box. So, in this case, we need to identify the weekends.

Neale Blackwood CPA:
Now, the trick with this formula is the range we're working with is A2 all the way down to B32. Now, we need to build the formula based on A2. Now, because this is applying to column B as well, we need to fix A reference. So, the formula we're going to use is the weekday function. So, it's going to be equals, weekday, open bracket, $A2. Now, the $A means that we're always going to be looking at column A. So, even in column B, we are going to be referring to the corresponding row in column A. So, $A2 means that the reference to A doesn't change, but the row reference does change because as we need that to update for each of the rows in the conditional format.

Neale Blackwood CPA:
So, the trick here is to create a formula that applies to the top left cell of the range you've chosen, but leave, in this case, the row number without the dollar sign, so that the row number changes, but always refer to column A, so the dollar A2, so equals, weekday, open bracket, A2, comma. Now, the weekday function in its default form uses a more, how can I say, unhelpful numbering structure for the week. So, I'm going to go equals, weekday, open bracket, dollar A2, comma 2, and then close the bracket.

Neale Blackwood CPA:
The comma 2 uses a very useful numbering structure, which is Monday equals one and Sunday equals seven. So, the weekday function that we've created, I can then say greater than 5 and that is an easy way to refer to the weekend because Saturday is 6 and Sunday is 7. So, having created a formula that returns true for the format that you want to apply, you then need to determine the format. Now, I'm just going to use the fill options, I click the format button, then click fill tab, and then select a colour. I'm just going to use grey and then you click okay and then you can click okay again and that will apply the format. So that applies the grey format to both column A and column B for the weekends. So, Saturday and Sunday, so that's the first part done.

Neale Blackwood CPA:
Now, I want to make it a little bit more flexible and also put a colour on the public holidays. So, in this case, I'm going to use the colour blue for any public holidays. So, in column F in the example, I had a list of public holidays. Then, I want to have this blue colour pop up. In this case, we, again select the same range and we use the new rule as well in the conditional formatting. Now, the formula for this one. So, again, we click the user formula to determine which cells to format, so that's the bottom option in the top section. Now, the formula for this one is equals countifs, open bracket. Then, we refer to the range F2 down to F6, which holds our holiday dates, comma, and then again, we use that $A2 reference, which is the top left reference, close the bracket, is greater than zero. So, the countif function counts how many times something exists in a range.

Neale Blackwood CPA:
Now, if it's greater than 0, it means that that date that we're referring to is in the public holiday range and so that will return true if it is a holiday. So, then we use the format button again. This time, I'm using a blue colour in the fill tab and click okay and okay again. Now, what I wanted to also show you in this article was that you can move the rules, so we just created two separate rules, and you can move them around and it will affect how things display. So, I've used an example of December. So, in December, this year, Christmas is on the Saturday. So, Saturday and Sunday are on the weekend and they're also Christmas and Boxing day, which means that the public holidays that we get will be the Monday and the Tuesday. So, what I wanted to do was to still show the weekend as grey, but with the sequence that we created these rules because the dates are listed in the holiday, so 25th and the 26th, the Saturday and the Sunday, they have turned blue when I change my input date to December.

Neale Blackwood CPA:
So, this flexible listing that I've got has an input cell. When I change that to the 1st of December, basically, all four days from the 25th to the 28th, all turned blue. To fix that what I can do is click on my conditional formatting. So, again, select the range, always select the range, click on conditional formatting, click on manage rules this time, which is the last option in the dropdown and you'll see the two rules are listed. At the moment, the blue rule for the holidays is actually the first rule in the list and that basically means it takes precedence over the second rule.

Neale Blackwood CPA:
So, what we need to do is click on the grey rule, which is the second rule and there is a little up arrow that you can select and it's on the right hand side of the duplicate rule button. Click that when you've got the grey rule selected and that will move the grey rule above the blue rule and in doing so that will then make the weekends grey colour take precedence over the public holiday blue colour.

Neale Blackwood CPA:
So, again, you just click apply and then okay and then that will make sure that all your weekends are always grey and any public holidays will then be blue. So, the grey will take precedence over the blue. Now, you may have wanted the Saturday and Sunday to be blue and that's okay. That was the first way we did it. So, you don't need to make any changes there, but the sequence of the rules and the sequence you create them will affect or can affect how the rules are applied and also how the colours or the formats are applied. So, you may need to move them around, and I said, that's how you do it. Just use those buttons. There's an up arrow and a down arrow. There is really no limit to how many conditional formats you can apply to a range. So, you can have more than two depending on what you're trying to achieve. Okay. Hope you've found that useful and conditional formatting using formulas is really flexible, and as I mentioned, you can apply formats to more than one column based on values in one of the columns. Thanks for listening.

Intro:
Thanks for listening to the CPA Australia podcast. For more information on today's episode, please visit the show notes at www.cpaaaustralia.com.au/podcast. Never miss an episode by subscribing to our podcast on apple podcasts, Spotify, or Stitcher.