Power Query Problems

Create Individual Calendars Automatically from an Excel Schedule

Phi Hung VoPhi Hung Vo Oct 28, 2026 869 views

Question details

The user wants to automatically generate personalized monthly calendars by entering a person's name, extracting data from a master monthly schedule.

How to Automatically Create Individual Calendars from a Master Excel Schedule
Product
Microsoft Excel
Device & OS
not provided
Scenario
Managing a master schedule containing activities, participants, facilitators, locations, and vehicles, and needing to filter it into specific individual views.
Observed behavior
The goal is to input a participant or facilitator's name and have a formatted monthly calendar dynamically populate with their specific assigned activities.
Before you start

Ensure your master schedule data is formatted as a standardized Excel Table (Ctrl+T) with clear column headers for dates, participants, and activities to allow formulas or Power Query to extract data accurately.

Solution 1Recommended

Use Power Query to Filter and Extract Individual Data

Power Query is a robust method for automating data extraction from a master schedule into a dedicated individual table.

While complex visual calendar generation might require an experienced Excel developer, Power Query can easily automate the repetitive task of filtering a master list into separate participant schedules.

1
Convert Master Data to a Table

Select your master schedule data range, go to the Insert tab, and click Table (or press Ctrl+T). Ensure 'My table has headers' is checked.

2
Load into Power Query

Navigate to the Data tab and select 'From Table/Range' to open the Power Query Editor.

3
Filter by Participant Name

Locate the 'Participants' or 'Facilitators' column, click the filter drop-down arrow, and select Text Filters > Contains. Type the name of the individual to filter the schedule.

4
Load to a New Worksheet

Click 'Close & Load To...' in the top left corner, choose 'Table', and place it on a new worksheet dedicated to that specific individual. Right-click and 'Refresh' this table whenever the master schedule updates.

Use Power Query to Filter and Extract Individual Data
Handling Multiple Names: Using the 'Contains' filter ensures that if multiple people are listed in the same cell (e.g., 'John, Sarah'), the query will still successfully find and extract the row for the specified person.
Manage Schedules Efficiently

Easily Create and Manage Schedules with WPS Spreadsheet

WPS Spreadsheet provides powerful data filtering, dynamic array formulas, and built-in calendar templates to help you automate individual schedule generation without needing to hire an expensive consultant.

  1. 1. Open Your Master Schedule: Launch WPS Spreadsheet and open your existing master schedule workbook.
  2. 2. Browse Calendar Templates: If you need a highly visual grid calendar, go to the Home tab and click on 'Templates' to search for 'Monthly Calendar' layouts.
  3. 3. Apply Dynamic Extraction: Alternatively, create a new sheet and use the =FILTER() function to instantly generate an individual's itinerary based on the master data.
Fully compatible with Microsoft Excel (.xlsx) files, formulas, and formatting.Supports advanced dynamic array functions like FILTER and SORT for instant data extraction.Provides dozens of free, ready-to-use calendar and schedule templates in the Template Library.Lightweight, fast, and completely free to use for everyday scheduling tasks.
microsoft office alternative - wps office

Frequently Asked Questions

Can I make the individual calendar update automatically when the master schedule changes?

Yes. If you use the FILTER function, the individual schedule will update instantly. If you use Power Query, you will need to right-click the extracted table and select 'Refresh' (or set the query to refresh upon opening the file) to pull the latest data.

How do I handle activities where multiple participants are listed in the same cell?

You can use a 'Text Contains' filter in Power Query, or use the SEARCH function nested inside your FILTER formula (e.g., ISNUMBER(SEARCH(Name, Range))). This allows the spreadsheet to find a specific name even if it's mixed with other text.

Is it possible to automatically generate a visual grid calendar rather than a list of activities?

Converting a raw list of activities into a traditional grid-style calendar automatically usually requires complex VBA macros. For a simpler, code-free approach, maintain the data as a list or use a Pivot Table with a Timeline filter to easily browse activities by day.

What if I have hundreds of employees? Do I have to make a sheet for each one manually?

Creating hundreds of separate tabs automatically requires VBA scripting. A much more efficient approach is to build a single 'Dashboard' sheet with a drop-down menu of employee names. When a name is selected, a single FILTER formula updates the page to show only that person's schedule.