Microsoft Excel has an extraordinary tool for data analysis called “conditional formatting.” Its use makes it easy for users to create new rules to establish criteria before executing a specific action. For this reason, we recommend reading this article with which you will learn how to highlight weekends with conditional formatting in Excel
- 1 What is conditional formatting in Excel?
- 2 How to highlight weekends with conditional formatting in Excel
What is conditional formatting in Excel?
Conditional formatting is one of the most useful tools in the Excel application, although it is also possible to apply conditional formatting in Word. Thanks to conditional formatting, you can apply a specific format automatically to a group of cells according to a set of criteria established by the user.
In this sense, it is necessary to specify a condition or a group of conditions so that a certain format can be applied automatically.
What is conditional formatting for in Excel?
Conditional formatting in Excel is used to quickly identify a series of values, since these have particular attributes in the cells that contain them. Thus, a set of values can represent specific criteria according to the conditions that are stipulated with the conditional format. In fact, you can add conditional formatting from a macro if you need it.
How to highlight weekends with conditional formatting in Excel
Surely, at some point you will need to apply conditional formatting in Excel . If this is your case, we advise you to follow the procedure that we will explain below to carry out this task in cells that contain the days of the week and dates.
Prepare values before applying conditional formatting
First, you need to know how to apply conditional formatting to cells in a worksheet. For this, prepare a table that contains different dates in a column. With this data , a specific condition or criterion will be highlighted and thus you can check if the conditional format works.
Assuming that column A has ten different dates, we can apply conditional formatting so that those cells whose dates refer to a weekend (Saturday or Sunday) are highlighted.
Apply conditional formatting
Select the range of cells that contains the dates and under “Start” select “Conditional Formatting.” Click on “New rule” and in the new dialog box select the option “Use a formula that determines the cells to apply formatting”.
In the text box to insert a formula, write = WEEKDAY (A1, 2)> 5. Press the “Format” button and in the “Fills” tab choose a background color for the cells that meet the condition. Finally, press the “OK” button.
How does the formula work so that conditional formatting is applied?
The WEEKDAY formula is used in Excel to obtain the days of the week through a number ranging from 1 to 7. If we want to identify Monday as the first day, then it is necessary to specify the value 2 in the second argument of the formula Therefore, Friday will be number 5.
For this reason, the condition implies that the cell is greater than 5 to apply conditional formatting. In case you want to use the format only for Sunday, you must modify the formula as follows: = WEEKDAY (A1, 2) = 7.
How does conditional formatting work?
The operation of the conditional format is very clear, since it allows you to observe the data, carry out an analysis of it and even detect problems as the case may be . In this regard, conditional formatting can change the appearance of certain cells depending on the conditions we apply.
If the condition is true, then the cell range that is selected will receive a specific format (a new background color, a different font, borders of another color). If the condition is false, the cell range will remain intact because it will not have any formatting. Although Excel has built-in conditions , it is possible to create new rules according to our needs.