Highlight weekends with conditional formatting

by | Dec 8, 2022 | Excel

Some­times, we want to high­light rows of week­ends. In such a case, we can use con­di­tion­al for­mat­ting but, first of all, we have to cre­ate a for­mu­la which will return TRUE for Sat­ur­day and Sun­day, i.e. week­end days. We can start with the WEEKDAY func­tion which will return the num­ber of days in a week (Fig.1).

High­light week­ends with con­di­tion­al formatting

=WEEKDAY(A2)

WEEKDAY function
Fig. 1 WEEKDAY function

We have our results. How­ev­er, this numer­a­tion assigns num­ber 7 to Sat­ur­day, and num­ber 1 to Sun­day. It’s not prop­er from our per­spec­tive. We have to mod­i­fy it by chang­ing the week day num­ber sequence. The best option we can choose is num­ber 1 for Mon­days and num­ber 7 for Sat­ur­days. That’s why we have to write 2 in the sec­ond WEEKDAY func­tion argu­ment (Fig.2).

=WEEKDAY(A2,2)

Selecting the correct option
Fig. 2 Select­ing the cor­rect option

And now, Sat­ur­day is 6 and Sun­day is 7. We can clear­ly see that week­ends are num­bers big­ger that 5 (Fig. 3). 

Weekend bigger that 5
Fig. 3 Week­end big­ger that 5

Now, we can sim­ply cre­ate a log­i­cal test that defines if the week day num­ber is greater than 5 (Fig. 4).

=WEEKDAY(A2,2)>5

A simple logical test
Fig. 4 A sim­ple log­i­cal test

When we copy our for­mu­la down, we can see that we have TRUE for Sat­ur­day and Sun­day, and FALSE for the rest of the days (Fig 5). 

TRUE and FALSE
Fig. 5 TRUE and FALSE

The results are prop­er, but we have to remem­ber that we want to high­light the whole row. We have to ask our­selves how our ref­er­ence should behave. We know that we will be copy­ing the for­mu­la down and to the right (hor­i­zon­tal­ly). What we have to remem­ber is that we always want to look at col­umn A, which is the Date col­umn. Even if we want to go one or two columns to the right, we always want to look at the cell from col­umn A (Fig. 6). 

A cell from column A
Fig. 6 A cell from col­umn A

That’s why, we have to lock the col­umn by press­ing the F4 key, but not the rows. It means that we have to put only one dol­lar sign (Fig. 7). 

=WEEKDAY($A2,2)>5

Locking the column
Fig. 7 Lock­ing the column

When we copy the for­mu­la down, noth­ing changes. But, when we copy it to the right and down, we can see that TRUEs and FALSEs are in a row (Fig. 8).

Rows
Fig. 8 Rows

Now, we have to copy our for­mu­la and the select the range on which we want to add con­di­tion­al for­mat­ting. The range starts with cell A2, so let’s press this cell, then Shift + →, two times to the right and Ctrl + Shift + ↓ to the end of our data. Cell A2 has to stay active and we will start cre­at­ing our for­mu­la from the per­spec­tive of this cell (Fig. 10).

Selected range with an active cell
Fig. 9 Select­ed range with an active cell

Now, we go to the Home tab, then to Con­di­tion­al For­mat­ting, and we select the New Rule option (Fig. 10). 

New Rule option
Fig. 10 New Rule option

In the win­dow that will appear, we have to select the Use a for­mu­la to deter­mine which cells to for­mat bar, then paste our for­mu­la into the Edit the Rule Descrip­tion and press the For­mat but­ton (Fig. 11). 

New Formatting Rule window
Fig. 11 New For­mat­ting Rule window

In the For­mat Cells win­dow, we press the Fill but­ton, then choose the col­or we like. Let’s choose a shade of grey, then the OK but­ton, and the OK but­ton once again in the New For­mat Rule win­dow (Fig. 12).

Fig. 12 For­mat Cells window

Now, we can see that each day of the week­end is high­light­ed the way we want­ed (Fig. 13). 

Fig. 13 High­light­ed weekends

https://www.youtube.com/watch?v=6jhZhEyk64A

Highlight weekends and holidays with conditional formatting

by | Dec 5, 2022 | Excel

Some­times, we want to high­light whole rows of hol­i­days and week­ends. In our case they will be the sec­ond, the third and the fourth of Sep­tem­ber, the sixth of Sep­tem­ber and so on, depend­ing on how many hol­i­days and week­ends we have. We can high­light them with con­di­tion­al for­mat­ting, but first of all, we have to cre­ate a prop­er for­mu­la. We are going to use the NETWORKDAYS func­tion. We want to start with the date from the first row and fin­ish with the same date. We also have to add hol­i­days and lock it (Fig. 1). 

=NETWORKDAYS(A2,A2,$G$2:$G$3)

If a giv­en day is a work­ing day, the func­tion returns 1, and if it’s a day off, it returns 0 (Fig. 2). 

Working days and days off
Fig. 2 Work­ing days and days off

Now, we want to high­light the rows, where we have 0. We can do it by cre­at­ing a prop­er log­i­cal test. We have to check whether a val­ue returned by the NETWORKSDAYS func­tion is equal to 0 (Fig. 3).

=NETWORKDAYS(A2,A2,$G$2:$G$3)=0

Fig. 3 A sim­ple log­i­cal test

Now, we want to ask our­selves how we want our ref­er­ence to behave. Since we want to copy the for­mu­la down, we want to change rows, as there are dif­fer­ent dates in each row. We also want to high­light a whole row, so we always want to look at the first cell in the row because it’s a date. Hav­ing this in mind, we have to lock the col­umn by insert­ing a sin­gle dol­lar sign before the name of the col­umn, but not before the row num­ber. We must do this both in the start date and the end date argu­ment (Fig. 4).

Locked cell
Fig. 4 Locked cell

After copy­ing it down and to the right, we have prop­er results. We have TRUE for week­ends and hol­i­days, and FALSE for work­ing days (Fig. 5). 

Proper results of TRUE and FALSE
Fig. 5 Prop­er results of TRUE and FALSE

Now, we can copy our for­mu­la in the edit mode and select the range in which we want to have con­di­tion­al for­mat­ting. We click on the Shift + →, two times to the right, and Ctrl + Shift + ↓ to select the data to the end. While cell A2 is still active, we can go to Home tab, then Con­di­tion­al For­mat­ting, then the New Rule option (Fig. 6). 

New Rule option
Fig. 6 New Rule option

In the New Rule For­mat­ting win­dow, we have to select the Use a For­mu­la to deter­mine which cells to for­mat bar, then paste the for­mu­la into the Edit the Rule Descrip­tion bar, and click on the For­mat tab (Fig. 7). 

New Formatting Rule window
Fig. 7 New For­mat­ting Rule window

In the For­mat Cells win­dow, we select the Fill tab, then choose a nice col­or, then press the OK but­ton in the win­dow, then the OK but­ton in the sec­ond win­dow (Fig. 8).

Format cells window
Fig. 8 For­mat cells window

And there we have it. We can see that each row with a day off is high­light­ed in green (Fig. 9)

Highlighted rows
Fig. 9 High­light­ed rows

Even if we mod­i­fy the hol­i­days, which are our cri­te­ria, our high­light­ed rows also change (Fig. 10). 

Criteria modification
Fig. 10 Cri­te­ria modification

https://www.youtube.com/watch?v=TY8sHfaVgaM

SUMIFS AVERAGEIFS and COUNTIFS functions SUM with conditions

by | Nov 15, 2022 | Excel

Today, we are count­ing sums, aver­ages and num­ber of trans­ac­tions with more than one con­di­tion. In our exam­ple, we have trans­ac­tion sales table and we want to count the val­ues for giv­en months. How­ev­er, the months are writ­ten as the first day of the month and the last day of the month. It means that we have to add two con­di­tions in order to count it. Let’s start with sums. We can use the SUMIFS func­tion. The first argu­ment of this func­tion is a range on which we will be sum­ming. In our case it’s the sales col­umn so we have to select cell C2 and press the Ctrl + Shift + ↓ com­bi­na­tion, then the F4 key to lock it (Fig. 1).

=SUMIFS($C$2:$C$1193

SUMIFS AVERAGEIFS and COUNTIFS func­tions SUM with conditions
SUMIFS function with a locked range
Fig. 1 SUMIFS func­tion with a locked range

We write a com­ma, and then we are going to the cri­te­ria range 1. This will be the range where we will be check­ing the first cri­te­ri­on, which is the third argu­ment of the SUMIFS func­tion. In our case, we are check­ing dates, so let’s press cell A2, Ctrl + Shift + ↓, then the F4 key. We are adding a com­ma again, and now we have to write the first con­di­tion, which is the first cri­te­ri­on. The order of our con­di­tions is not impor­tant because, in the end, all con­di­tions have to be met to count the thing we want. Let’s start with the From date. We want val­ues that are big­ger or equal to this date, which means that we have to write the sym­bols of larg­er than and equal in dou­ble quotes, then an amper­sand because we are com­bin­ing this text with the val­ue from cell E2 (Fig. 2).

=SUMIFS($C$2:$C$1193,$A$2:$A$1193,”>=”&E2

SUMIFS with the first condition
Fig. 2 SUMIFS with the first condition

After press­ing the F9 key, we can see that Excel changed the date into a num­ber. This makes Excel check our con­di­tion prop­er­ly (Fig. 3).

=SUMIFS($C$2:$C$1193,$A$2:$A$1193,“>=43831”

Excel changed the dated
Fig. 3 Excel changed the dates

How­ev­er, let’s move back to pre­vi­ous text by press­ing Ctrl + Z. Now, we have the first con­di­tion. We can see that the sec­ond con­di­tion is nec­es­sary with sec­ond critiri­on because the next two argu­ments are in square brack­ets (Fig. 4).

=SUMIFS($C$2:$C$1193,$A$2:$A$1193,”>=”&E2

Arguments in square brackets
Fig. 4 Argu­ments in square brackets

Cri­te­ria range 2 in our case is the same as cri­te­ria range 1, so we can just copy it (Ctrl +C, Ctrl +V), but we real­ly need to put it because each cri­te­ria range is work­ing just with the argu­ment of its cri­te­ria. It means that cri­te­ria range 1 is work­ing with cri­te­ria 1, and cri­te­ria range 2 is work­ing with cri­te­ria 2. In our case, the cri­te­ria 2 are num­bers small­er than or equal to the last day of the month in cell F2. 

Now, we have the whole for­mu­la (Fig. 5). 

=SUMIFS($C$2:$C$1193,$A$2:$A$1193,”>=”&E2,$A$2:$A$1193,”<=”&F2)

Whole SUMIFS formula
Fig. 5 Whole SUMIFS formula

When we want to sum the val­ues from the sum range, the first and the sec­ond con­di­tion have to be met. Let’s put the for­mu­la and copy it down. We can see that at the end every­thing is work­ing prop­er­ly because we have writ­ten the for­mu­la prop­er­ly (Fig. 6).

Proper results
Fig. 6 Prop­er results

We still have some more val­ues to count. Let’s take the aver­ages. Since I’m a lazy per­son, I will copy the whole for­mu­la in edit mode, then go to the Aver­age col­umn, paste it and change only the name of SUMIFS into AVERAGEIFS. After enter­ing it, we can see that only the sum range changed its name into aver­age range. The remain­ing argu­ment names stayed the same. It’s because the SUMIFS and the AVERAGEIFS for­mu­las have the same syn­tax, but they count sums and aver­ages cor­re­spond­ing­ly (Fig. 7). 

Argument name change
Fig. 7 Func­tion name change

Let’s put the for­mu­la into our cell and copy it down. We have the results (Fig 8).

SUMIFS reults
Fig. 8 SUMIFS reults

Let’s now count the num­ber of trans­ac­tions. We will use the COUNTIFS func­tion. This func­tion does­n’t have any range on which we could count sums, aver­ages and so on. It only needs cri­te­ria ranges and cri­te­ria. Our first cri­te­ria was the one with dates. So, let’s write A2, then press Ctrl + Shift + ↓, then the F4 key to lock it and a com­ma. I want to show you that the order of con­di­tions is not impor­tant. Let’s take val­ues small­er that or equal to the date in cell F2, com­ma, then the same cri­te­ria range, then the sec­ond cri­te­ria will be greater than or equal to the val­ue from cell E2. Those are all the argu­ments we need for a COUNTIFS func­tion (Fig. 9).

=COUNTIFS($A$2:$A$1193,”<=”&F2,$A$2:$A$1193,”>=”&E2)

COUNTIFS formula
Fig. 9 COUNTIFS formula

After copy­ing the for­mu­la down, we have the results. We can check if our aver­age is cor­rect just by look­ing at it because it is just the sum divid­ed by the num­ber of trans­ac­tions (Fig. 10).

Checking the results
Fig. 10 Check­ing the results

We can see that the num­bers are exact­ly the same as in the Aver­age col­umn (Fig. 11). 

The same numbers
Fig. 11 AVERAGEIFS and divide gives the same results

https://www.youtube.com/watch?v=oei27KKIRPk