Using Excel’s conditional formatting to colorize the weekends

In this period of the year most of us are starting to make plans for next year, Excel is an incedibly powerfull tool that can help you visualize your plans. Using Excel’s conditional formatting you can colorize the weekends and make them standout more than the other days of the week or vice versa. This article is an How to colorize the weekends in Excel using conditional formatting.

To colorize the weekends in Excel using conditional formatting, based upon the date in column B, you can use the following formula:



To create a rule using conditional formatting:

  • Select the cells that you want to apply the conditional formatting to.
  • Click “Conditional Formatting”.
  • Choose “New Rule”.
  • In the “New Formatting Rule” dialog box, choose “Use a formula”.
  • Under “Format values”, type the formula: =IF(OR(WEEKDAY($B2)=1;WEEKDAY($B2)=7);1;0)
  • The formula uses the dates in column B (You can select your own column with dates, by replacing the $B2 part in the formula with the column letter of your choice).
  • Click “Format”.
  • In the “Color” box, select your favourite color.
  • Click “OK” until all dialog boxes are closed.


Suggestions for improving this article are welcome, please let me know and drop me a line.