Chart & Visualization Issues

How to Automatically Hide Weekends and Highlight Weekdays in Excel

Maira Mehtab Sep 21, 2026 869 views

Question details

The user needs to automatically hide Saturdays and Sundays while applying distinct color highlights to specific weekdays (Mondays and Fridays in one color, Tuesdays through Thursdays in another) across a three-month worksheet.

Product: Excel
Device & OS: not provided
Scenario: Setting up a multi-month schedule, tracker, or calendar where non-working days are hidden and specific working days are color-coded for visual clarity.
Observed behavior: The goal state is to have weekends fully hidden from view and workdays automatically formatted with specific colors based on the exact day of the week.

Before you start

Ensure your worksheet is saved as a Macro-Enabled Workbook (.xlsm) since automatically hiding rows requires VBA, and test the code on a sample copy of your data before applying it to your main file.

Solution 1Recommended

Highlight Weekdays using Conditional Formatting

Use Excel's built-in Conditional Formatting with the WEEKDAY function to automatically color-code Mondays/Fridays and Tuesdays-Thursdays.

Conditional formatting dynamically changes the appearance of cells based on their values. By combining it with the WEEKDAY function, you can identify the exact day of the week and assign the corresponding background colors.

Select the target data range

Highlight the entire range of dates you want to format across your three-month worksheet.

Create a new formatting rule

Navigate to the Home tab on the ribbon, click on 'Conditional Formatting', and choose 'New Rule'.

Enter the formula for Mondays and Fridays

Select 'Use a formula to determine which cells to format'. Enter the formula =OR(WEEKDAY(A1)=2, WEEKDAY(A1)=6) (assuming A1 is the first cell in your range). Click 'Format', choose your preferred color for Mondays and Fridays, and click 'OK'.

Enter the formula for Tuesdays through Thursdays

Create another rule using the formula =AND(WEEKDAY(A1)>=3, WEEKDAY(A1)<=5). Click 'Format', pick a different background color, and apply the rule.

Relative References: Ensure you do not use absolute references (like $A$1) in the formulas unless you intend to format entire rows based on a single column's value. Use A1 (relative) if formatting just the date cells, or $A1 if formatting entire rows based on column A.

Solution 2

Automatically Hide Weekends using a VBA Macro

Since native Conditional Formatting cannot hide entire rows, use a VBA macro to scan the date column and automatically hide rows falling on a Saturday or Sunday.

Automate Data Formatting with WPS Office

Easily Format and Manage Calendars with WPS Spreadsheet

WPS Spreadsheet offers comprehensive support for advanced Conditional Formatting and VBA macros (in supported versions), allowing you to seamlessly set up complex tracking sheets and automatically hide weekends.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your project tracker or calendar workbook.
2. Apply Conditional Formatting: Highlight your dates, navigate to the Home tab, and select Conditional Formatting to input your WEEKDAY formulas.
3. Run your formatting macros: Access the Developer tab, open the VBA editor, and insert your row-hiding script for weekend automation.
4. Save with Macro support: Save your document as a Macro-Enabled Workbook to preserve your scripts for future use.

Fully compatible with Microsoft Excel (.xlsx and .xlsm) formats without losing formatting rules.User-friendly interface for building customized conditional formatting scenarios.Lightweight and fast performance, perfectly handling multi-month datasets and large arrays.Supports powerful developer tools to automate repetitive tasks with macros.

microsoft office alternative - wps office

Frequently Asked Questions

Can I hide weekends in Excel without using VBA macros?

Excel does not have a built-in feature to automatically hide rows purely based on cell values without VBA. However, you can use the Filter tool on your date column to manually uncheck Saturdays and Sundays, which effectively hides them from view.

What does the WEEKDAY function do in Excel?

The WEEKDAY function returns a number from 1 to 7 that corresponds to the day of the week for a specific date. By default, 1 represents Sunday and 7 represents Saturday. It is widely used in conditional formatting and date calculations.

Why is my conditional formatting applying to the wrong rows?

This commonly happens if the cell reference in your formula does not match the active cell of your selected range. Always make sure you are referencing the top-left cell of your selection, and check whether you are incorrectly using absolute references (like $A$1 instead of A1).

How do I unhide the weekend rows once the VBA macro runs?

You can manually unhide them by selecting the entire worksheet (clicking the triangle in the top-left corner), right-clicking any row number, and selecting 'Unhide'. Alternatively, you can create a second macro that sets EntireRow.Hidden = False to quickly unhide all rows.

Excel & Data Problems