Chart & Visualization Issues

How to Group Multiple Blocks for the Same Room in an Excel Chart

Kushani NimanthikaKushani Nimanthika Nov 10, 2026 869 views

Question details

The user needs to display multiple schedule blocks for the same room on a single horizontal row in an Excel chart.

How to Group Multiple Blocks for the Same Room in an Excel Chart
Product
Excel
Device & OS
not provided
Scenario
Creating a block schedule or Gantt-style chart where multiple events occur in the same location (room) at different times.
Observed behavior
Duplicate room names and text-axis settings fail to group the blocks correctly, resulting in separate rows for the same room instead of a consolidated timeline.
Before you start

Ensure your schedule data includes the exact start time and duration for each block, and consolidate all events for the same room onto a single row in your spreadsheet.

Solution 1Recommended

Use a Stacked Bar Chart with Restructured Data

By restructuring your source data into alternating start times, durations, and gaps, and applying a stacked bar chart, you can display multiple events on the same room's row.

Standard bar charts treat each data row as a separate entry, even if the category names match. To force multiple blocks onto the same row, you must structure the data chronologically across columns for a single room, using a stacked bar chart to visualize the timeline.

1
Restructure your source data

Organize your data with rooms as row labels (one row per room). Create columns sequentially: the first event's start time, the first event's duration, the gap duration until the second event, the second event's duration, and so on.

2
Insert a Stacked Bar Chart

Select your newly structured data range. Go to the 'Insert' tab, click on the Bar Chart icon, and select 'Stacked Bar' to generate the initial chart.

3
Format the invisible spacer series

Right-click the data series representing the 'Start times' and 'Gaps' in the chart. Select 'Format Data Series', go to the Fill & Line options, and choose 'No fill' and 'No line'.

4
Adjust the vertical axis settings

Right-click the vertical axis (room names) and select 'Format Axis'. Check the box for 'Categories in reverse order' so the rooms appear from top to bottom instead of bottom to top.

Use a Stacked Bar Chart with Restructured Data
Chart Appearance: The 'No fill' segments act as invisible chronological spacers, making the colored duration blocks appear at the correct intervals on a single horizontal line.

Create Professional Schedule Charts in WPS Office

WPS Spreadsheet provides powerful chart tools, including stacked bar charts, to help you visualize complex schedules and room bookings effortlessly. It offers an intuitive interface and is fully compatible with Microsoft Excel formats.

  1. 1. Open WPS Spreadsheet: Launch WPS Office and open your prepared schedule dataset in WPS Spreadsheet.
  2. 2. Insert the Chart: Highlight your data range, navigate to the 'Insert' tab on the top ribbon, and click 'Chart'.
  3. 3. Select Stacked Bar: Choose 'Bar' from the list of chart types, then click the 'Stacked Bar' option to generate the base visualization.
  4. 4. Customize Visibility: Click on the spacer series in your new chart, navigate to the properties panel on the right, and set both Fill and Line to 'None'.
Easily create and customize Gantt and schedule chartsFully compatible with Microsoft Excel (.xlsx) formatsFree, lightweight, and fast to load large datasetsIntuitive drag-and-drop chart formatting capabilities
microsoft office alternative - wps office

Frequently Asked Questions

Why do duplicate room names create separate rows in my chart?

By default, Excel treats each row of data as a distinct category or data point in standard bar charts, even if the label text is identical. To group them visually on one line, the data must be merged into a single row using multiple alternating series.

How do I calculate the gaps between schedule blocks?

You can calculate the gap by subtracting the end time of the previous event from the start time of the next event. For example, if Event 1 ends at 10:00 AM and Event 2 starts at 11:00 AM, you would use a simple subtraction formula to find the 1-hour gap duration.

Can I add data labels to the individual blocks?

Yes. Right-click the visible colored blocks in your stacked bar chart and select 'Add Data Labels'. You can then format these labels to show the specific event name, start time, or duration.

Does this method work for dates as well as times?

Yes, this Gantt-chart style approach works perfectly for both dates and times. You just need to ensure your start values and duration gaps correspond to the correct numerical date or time formats in your spreadsheet cells.