How to highlight weekends and holidays in Excel?
When working with daily record tables in Excel, it is often useful to visually distinguish weekends or public holidays from regular weekdays for easy data review. Such highlighting is especially helpful for attendance sheets, project timelines, or business logs, where differentiating between workdays and non-working days aids in both visual management and subsequent analysis. Excel provides a range of methods to accomplish this, including Conditional Formatting, custom helper formula columns, or more advanced automation with VBA. Choosing an appropriate solution may depend on your table layout, how often new data is added, and whether holiday lists change frequently.
- Highlight weekends and holidays with Conditional Formatting
- Excel Formula - Use a helper column to flag weekends and holidays
- VBA Code - Automatically shade weekends and holidays
Highlight weekends and holidays with Conditional Formatting
Excel’s Conditional Formatting feature lets you automatically highlight weekends (typically Saturdays and Sundays) and holidays within your date records—without writing any calculations into your data. This method is suitable for users who frequently review or update their worksheet, and want color cues for non-working days that update instantly when data or holidays change.
To set up Conditional Formatting for weekends and holidays, follow these instructions:
1. Select the range with dates that you want to highlight.
2. Go to the Home tab, click Conditional Formatting, and choose New Rule to open the rule editor. See screenshot:
3. In the New Formatting Rule dialog:
- Select Use a formula to determine which cells to format from the rule type list.
- In the formula input box, enter the following formula: =NETWORKDAYS($A2,$A2,$F$2:$F$6)=0
- Click the Format button to set your highlight style.
4. In the Format Cells window, switch to the Fill tab and select a background color for weekends and holidays. This color distinguishes non-working days from regular ones for quick visual reference.
5. Click OK twice to close dialog boxes. Your chosen highlight will automatically appear for any date matching weekends or listed holidays within the selected area. Holidays can be updated at any time by editing your holiday range, and the formatting will update accordingly.
Tips & Troubleshooting: If the highlight does not appear, double-check your date formatting (the formula works on genuine Excel dates); mismatches may cause rules to fail. Adjust the formula cell references if applying to multiple columns. Also, if your data spans several years, update your holiday list as needed to reflect the correct non-working days.
Advantages: No extra columns or manual work required; fully dynamic as your table grows or as the holiday list changes.
Potential limitations: Conditional formatting has a maximum number of rules per worksheet (rarely reached), and overly complex formulas can slow workbook performance in large datasets.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
Excel Formula – Use a helper column to flag weekends and holidays
For users who want more control over color-coding or who wish to sort/filter by working or non-working days, adding a helper column with formulas is a reliable choice. This technique clearly marks each row as a weekend, holiday, or regular day, and can be used to filter, manually format, or summarize your data.
This method is especially useful in scenarios where color coding alone isn't enough, or when you want to tally totals for weekends/holidays directly.
Assume your dates are in column A starting at row2, and holidays are listed in F2:F6. Insert a new column next to your date column and enter a heading like “Day Type”.
1. In cell B2 (the helper column), enter the following formula:
=IF(OR(WEEKDAY(A2,2)>=6,COUNTIF($F$2:$F$6,A2)>0),"Weekend/Holiday","Workday")This formula does two checks for each date:
- WEEKDAY(A2,2)>=6: Treats Saturday (6) and Sunday (7) as weekends (if your weekends are different, adjust accordingly).
- COUNTIF($F$2:$F$6,A2)>0: Checks if the date matches one in the holiday list.
2. Then drag the fill handle down to apply the formula to other rows. Once populated, filter the table by “Weekend/Holiday” to isolate or manually highlight these lines. You may also apply Conditional Formatting based on this column if you want coloring.
Tips: If your weekend days differ, change the WEEKDAY logic (e.g., use1 for Sunday or specific numbers for your region). Always keep the holiday list up-to-date. If your data range changes, reapply the fill/copy as needed.
Advantages: Helpler columns allow more custom filtering and reporting. Easily identify, count, or color only as needed.
Disadvantages: Requires an extra column in your data and manual color fill unless used with further conditional formatting.
VBA Code – Automatically shade weekends and holidays
When you frequently need to update date ranges, or if you wish to automate the process of highlighting weekends and holidays beyond what formulas and conditional formatting can do, using a simple VBA macro is very efficient. This is ideal for users managing longer lists or repeated worksheet generations.
With a macro, Excel will instantly shade weekends and holiday dates for you based on your chosen date range and holiday list. You can change highlight colors in the code as needed, and re-run the macro whenever your data changes.
1. Click Developer > Visual Basic. In the Microsoft Visual Basic for Applications window, select Insert > Module.
2. Copy and paste the following code into the newly created Module:
Sub ShadeWeekendsAndHolidays()
Dim rngDates As Range
Dim rngHolidays As Range
Dim cell As Range
Dim xTitleId As String
xTitleId = "KutoolsforExcel"
On Error Resume Next
Set rngDates = Application.InputBox("Select the range with dates:", xTitleId, Selection.Address, Type:=8)
Set rngHolidays = Application.InputBox("Select the range with holiday dates:", xTitleId, , Type:=8)
On Error GoTo 0
If rngDates Is Nothing Then Exit Sub
If rngHolidays Is Nothing Then Exit Sub
Application.ScreenUpdating = False
For Each cell In rngDates
If IsDate(cell.Value) Then
If Weekday(cell.Value, vbMonday) >= 6 Or Not IsError(Application.Match(CDbl(cell.Value), rngHolidays, 0)) Then
cell.Interior.Color = RGB(255, 199, 206) ' Light red fill; adjust as needed
Else
cell.Interior.ColorIndex = xlNone ' Remove fill from regular days
End If
End If
Next cell
Application.ScreenUpdating = True
End Sub3. Press F5 key to run this code, a prompt will ask you to select your date cells, then your list of holiday cells. Follow the on-screen instructions.
The macro highlights weekends (Saturday/Sunday) and holidays (as per your list) with a fill color. Adjust RGB(255,199,206) in the code to use your preferred color if desired.
Tips:
If you accidentally select the wrong range, simply re-run the macro. All existing fills on the selected range are reset for non-matching cells.
Ensure your dates and holidays use correct Excel date values (not text).
Advantages: Saves time on repeated tasks and provides great flexibility. No need to reapply formatting manually.
Disadvantages: Macros must be enabled for your workbook; users unfamiliar with VBA should save before running and try on backup copies first.
More relative articles:
- Color Alternate Rows For Merged Cells
- It is very helpful to format alternate rows with a different color in a large data for us to scan the data, but, sometimes, there may be some merged cells in your data. To highlight the rows alternately with a different color for the merged cells as below screenshot shown, how could you solve this problem in Excel?
- Highlight Approximate Match Lookup
- In Excel, we can use the Vlookup function to get the approximate matched value quickly and easily. But, have you ever tried to get the approximate match based on row and column data and highlight the approximate match from the original data range as below screenshot shown? This article will talk about how to solve this task in Excel.
- Highlight Cell If Value Is Greater Than Another Cell
- To compare the values in two columns, such as, if the value in column B is greater than the value in column C in the same row, then highlight the values from column B as below screenshot shown. In this article, I’m going to introduce some methods for highlighting cell if value greater than another cell in Excel.
- Highlight Rows Based On Drop Down List
- This article will talk about how to highlight rows based on drop down list, take the following screenshot for example, when I select “In Progress” from the drop down list in column E, I need to highlight this row with red color, when I select “Completed” from the drop down list, I need to highlight this row with blue color, and when I select “Not Started”, a green color will be used to highlight the row.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in