How to Create a Visual Excel Calendar from Dates on Another Sheet
Question details
The user wants to create a calendar view in Excel that shades event dates from a source list on another worksheet, including events that span multiple days.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- The user has an event list with start dates, end dates, and categories on a separate worksheet and needs a monthly calendar grid that automatically highlights matching dates.
- Observed behavior
- The calendar grid needs to shade single-day and multi-day event dates with different colors by category using conditional formatting.
Prepare an event source worksheet containing at least Start Date, End Date, and Category columns, and create a calendar sheet with real Excel date cells in a monthly grid. Make sure all dates are stored as date values rather than text so the conditional formatting formulas can compare them correctly.
Build a Calendar Grid with Real Excel Dates
Use this setup first if you do not already have a calendar grid, because conditional formatting can only highlight real date values.
In a new worksheet, select the cell that will start your calendar, such as B4, and type the first day of the month as a real date, for example 10/1/2025.
In the next cell, enter =B4+1 and drag the fill handle across the row to complete the week.
Select the completed week, drag the fill handle down to create the needed number of rows, and format the cells as day numbers by clicking the Number Format drop-down in the Home tab.
Use Conditional Formatting to Shade Event Dates
Apply this formula-based rule when the calendar grid and event source are ready, so the calendar updates automatically as event dates change.
Create a Conditional Formatting Calendar in WPS Office Excel
WPS Office Spreadsheet supports the real date grids, cross-sheet references, and COUNTIFS conditional formatting rules required to build a visual Excel calendar that shades single-day and multi-day events.
- 1. Open your workbook in WPS Spreadsheets: Install WPS Office, launch WPS Spreadsheets, and open the workbook that contains your event source list and calendar sheet.
- 2. Prepare the calendar grid with real dates: On the calendar sheet, enter the first date of the month and use the fill handle with =previous_cell+1 to create rows of date cells.
- 3. Start a conditional formatting rule: Select the calendar date range, click Home > Conditional Formatting > New Rule, and choose "Use a formula to determine which cells to format".
- 4. Enter the cross-sheet date rule: Enter a rule such as =COUNTIFS(EventStart,"<="&B4,EventEnd,">="&B4,EventCategory,"Meeting")>0, set the fill color, and repeat for each category.
- 5. Save and verify the calendar: Update an event date on the source sheet to confirm the calendar recalculates, then save the file as .xlsx or another Microsoft Excel-compatible format.
Frequently Asked Questions
Can I shade events that span multiple days?
Yes. Use a conditional formatting rule that checks whether the calendar cell date is greater than or equal to the event start date and less than or equal to the event end date, such as =COUNTIFS(EventStart,"<="&B4,EventEnd,">="&B4)>0.
Why are my conditional formatting highlights not appearing?
Confirm that the calendar grid contains real Excel dates, the source start and end dates are date values, and the formula uses the active cell as a relative reference. Text dates or mismatched ranges are the most common causes.
How do I use different colors for each event type?
Create a separate conditional formatting rule for each category. Add the category range and category text to the COUNTIFS formula, then assign a different fill color in the Format dialog.
Can the calendar refer to events on another worksheet?
Yes. In the formula, reference the source sheet by its name, for example Events!$B$2:$B$100. WPS Office Spreadsheet supports cross-sheet range references in conditional formatting.
Will the calendar update automatically when I edit event dates?
Yes. Conditional formatting recalculates with the workbook. If it does not update, go to Formulas > Calculation Options and make sure calculation is set to Automatic.
