How to Hide a Data Series to Create a Gap in an Excel Stacked Chart
Question details
The user wants to hide a specific data series in a stacked chart to create a gap, ensuring the chart only displays the remaining visible zones.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Modifying a stacked bar or column chart to make the foundational series invisible, commonly used to build Gantt charts or floating charts.
- Observed behavior
- The chart currently displays all series in the stack with visible colors, preventing the desired floating visual effect.
Ensure you have your stacked column or bar chart properly inserted into your spreadsheet, and verify that the data series you want to hide is positioned at the base of the stack.
Make the Target Chart Series Transparent
Use the Shape Fill and Shape Outline tools to completely hide the selected series while maintaining its structural position in the chart.
By changing the fill and border to 'None', the data series becomes invisible but still supports the remaining series in the stack. This is the core technique for creating floating bars.
Click once on the specific data series (e.g., the bottom blue series) in your stacked chart. It is crucial to single-click so the entire series is highlighted. Do not double-click, as this will select only a single data point.
Navigate to the 'Format' tab located on the top ribbon menu. Click on 'Shape Fill' and select 'No Fill' from the drop-down list.
If a border or outline remains visible around the transparent series, click on 'Shape Outline' in the 'Format' tab and select 'No Outline'.
Create and Customize Stacked Charts in WPS Spreadsheet
WPS Spreadsheet offers robust charting tools that make it incredibly simple to build stacked charts, hide specific series, and create floating visuals or Gantt charts.
- 1. Insert a Stacked Chart: Highlight your dataset, navigate to the 'Insert' tab, and select a 'Stacked Column' or 'Stacked Bar' chart.
- 2. Select the Series to Hide: Click once on the bottom data series in the chart area to highlight all data points in that series.
- 3. Apply No Fill and No Line: Right-click the series, choose 'Format Data Series', and under the Fill & Line options, select 'No fill' and 'No line'.
Frequently Asked Questions
Why did my chart only hide one bar instead of the entire series?
This happens if you double-click on the chart. Double-clicking selects a single data point instead of the whole series. To fix this, click a blank area outside the chart, then single-click the series you wish to hide.
Can I use this technique to make a Gantt chart?
Yes. The most common way to create a Gantt chart in spreadsheets is to build a stacked bar chart with 'Start Date' and 'Duration' series, and then hide the 'Start Date' series using the No Fill method.
How can I select the invisible series if I need to change it back?
If you cannot click the invisible series, go to the 'Format' tab on the ribbon. On the far left, you will find a 'Chart Elements' dropdown box. Click it and select your hidden series by name to select it without clicking the chart area.
