Chart & Visualization Issues

How to Start a Stacked Bar Chart Range at 100 in Excel

Aamir Naveed Akram Oct 12, 2026 870 views

Question details

The user wants to configure an Excel stacked bar chart so that the visual transition between positive and negative ranges starts at a custom value, specifically 100, rather than the default zero.

How to Start a Stacked Bar Chart Range at 100 Instead of Zero

Product: Excel
Device & OS: not provided
Scenario: Creating a stacked bar chart to visualize data against a specific baseline target (e.g., 100).
Observed behavior: By default, Excel stacked bar charts pivot around zero, but the goal is to shift this split to occur at the value of 100.

Before you start

Ensure your dataset includes the exact values you want to chart and identify your target baseline (e.g., 100) before configuring the chart settings.

Solution 1Recommended

Split Series to Create a Custom Baseline

Manually divide your data into two separate series (above and below 100) to force the visual transition at the desired value.

Since Excel stacked bar charts natively pivot at zero, creating a visual split at 100 requires manipulating the source data rather than simply changing axis bounds.

Prepare Data Columns

Create two new columns adjacent to your original data: one for values below 100 and another for values above 100.

Calculate Series Values

Use an IF formula to populate these columns. Calculate the difference from 100 so the chart plots the variance (e.g., =IF(B2>100, B2-100, 0)).

Insert Stacked Bar Chart

Select the newly created data columns, navigate to the 'Insert' tab on the ribbon, and choose 'Stacked Bar Chart' from the Charts group.

Format Series Order

Right-click the chart, choose 'Select Data', and use the up and down arrows to arrange the series in the correct bottom-to-top order to ensure the split aligns perfectly.

Adjust Colors

Click on each data series in the chart, open 'Format Data Series', and manually set contrasting Fill colors for the 'above 100' and 'below 100' portions.

Split Series to Create a Custom Baseline

Visual Accuracy: This method ensures the visual transition occurs exactly at 100 without artificially hiding data by altering the axis bounds.

Create Professional Charts in WPS Office

Easily Customize Stacked Bar Charts with WPS Spreadsheet

WPS Spreadsheet provides powerful charting tools that allow you to split data series, customize axis ranges, and format colors effortlessly, offering a highly compatible alternative to Microsoft Excel.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the data you want to visualize.
2. Prepare and Select Data: Organize your data into two distinct columns for values above and below 100, then highlight the range.
3. Insert Stacked Bar: Navigate to the 'Insert' tab, click on 'Chart', and select the 'Stacked Bar' option from the gallery.
4. Customize Appearance: Double-click the chart series to open the formatting pane on the right side, where you can easily adjust the series order and set custom fill colors.

Split and manage complex data series easily.100% compatibility with Microsoft Excel (.xlsx) formats.Intuitive UI for customizing chart axes and colors.Free to use with comprehensive data visualization tools.

microsoft office alternative - wps office

Frequently Asked Questions

Can I just change the minimum bounds of the axis to 100?

Changing the minimum bounds of the axis to 100 will hide any data below 100 entirely rather than creating a split series. To show both sides of 100 (like positive and negative variance), you must split the data series using formulas.

What formula should I use to split my data at 100?

You can use an IF function to calculate the variance. For the 'Above 100' column, use `=IF(B2>100, B2-100, 0)`. For the 'Below 100' column, use `=IF(B2<100, 100-B2, 0)`.

Will this method work for stacked column charts as well?

Yes, the exact same data preparation and formatting principles apply to Stacked Column charts if you prefer a vertical layout instead of a horizontal bar chart. Just select 'Stacked Column' from the Insert tab instead.

Excel & Data Problems