WPS Spreadsheet: Create a Gauge Doughnut Chart for Negative and Over-100% Values
Question details
The user needs a gauge-style doughnut chart that correctly represents values below 0% and above 100%.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A standard gauge chart is being used to visualize a percentage or performance value, but the data can fall outside the 0% to 100% range.
- Observed behavior
- Negative values point or display in the wrong direction, while values above 100% stop at the chart maximum instead of showing the overflow.
Record the actual value and decide how negative and overflow values should appear on the gauge. Create a small helper table beside or below the source data so the chart-driving segments can remain separate from the visible report.
Build a layered gauge doughnut chart with helper segments
Use this method when the gauge must show negative values, in-range values, and values above 100% without being limited by a standard doughnut scale.
A standard gauge maps the value directly to a 0%–100% arc, so it cannot independently position negative or overflow values. Helper cells divide the input into positive, negative, overflow, remainder, and hidden segments; layered doughnut series then control how each portion appears.
The exact segment order, colors, and rotation depend on the desired gauge design. Keep the helper data outside the visible chart area and use a sample workbook when the gauge must match a specific visual layout.
In WPS Spreadsheet, add columns for Actual, Positive, Negative, Overflow, Remainder, and Hidden. For a value in A2, use formulas such as =MAX(0,MIN(A2,100)) for Positive, =MAX(0,-A2) for Negative, =MAX(0,A2-100) for Overflow, and =MAX(0,100-PositiveCell) for Remainder. Set Hidden to the span needed to create the blank half of the gauge.
Place the positive, negative, overflow, remainder, and hidden values in chart-ready rows or columns. Keep negative and overflow segments as separate values instead of allowing a single percentage cell to drive the entire chart.
Select the helper range, open the WPS Spreadsheet ribbon, choose Insert > Chart, select Doughnut, and click Insert. If WPS includes an unwanted helper category, open Chart Tools > Select Data and remove or reorder the series.
With the chart selected, use Chart Tools > Select Data > Add to add the helper series for negative, positive, overflow, and hidden segments. Arrange the series so the indicator or highlight series is layered over the background gauge series.
Right-click a ring and choose Format Data Series. Adjust the Doughnut Hole Size and Angle of First Slice, then set the hidden segment to No Fill and No Line. Apply distinct colors to negative, in-range, and overflow segments, and resize or overlay the rings until the indicator points to the intended position.
Create a 0%–100% gauge for in-range values only
Use this simpler option when values are guaranteed to remain between 0% and 100% and no negative or overflow display is required.
Create Custom Gauge Charts in WPS Spreadsheet
WPS Spreadsheet lets you build a custom gauge from helper cells and layered doughnut series, giving you control over negative values, overflow above 100%, colors, hidden segments, and chart rotation.
- 1. Enter the source value: Open WPS Spreadsheet and place the percentage or performance value in a source cell.
- 2. Build helper segments: Add formulas for positive, negative, overflow, remainder, and hidden values in a separate helper table.
- 3. Insert a doughnut chart: Select the helper table, then choose Insert > Chart > Doughnut.
- 4. Add and format series: Use Chart Tools > Select Data to add or reorder the series, then use Format Data Series to set colors, rotation, hole size, and hidden segments.
Frequently Asked Questions
Why does a normal doughnut gauge stop at 100%?
A normal gauge is usually built from a value, its remainder to 100%, and a hidden half. Values above 100% leave no remainder, so the chart reaches its maximum. Use a separate Overflow helper segment and a layered doughnut series to show excess values.
How can I display a negative value on a WPS Spreadsheet gauge?
Do not chart the negative source value directly. Create a Negative helper cell with a formula such as =MAX(0,-A2), keep the positive value in a separate segment, and add both through Chart Tools > Select Data so the negative state can be formatted and positioned independently.
How do I hide half of a doughnut chart to make a gauge?
Add a helper value for the hidden half, select that ring slice, open Format Data Series, and set its Fill to No Fill and its Border or Line to No Line. Adjust Angle of First Slice to place the visible arc at the bottom or another desired position.
Can the gauge update when the source value changes?
Yes. Use formulas in the helper cells instead of manually entered segment values. When the source cell changes, WPS Spreadsheet recalculates the positive, negative, overflow, and remainder segments and updates the chart.
Why does my custom gauge look different from another workbook?
Gauge appearance depends on helper segment order, doughnut series order, the Angle of First Slice, hole size, and whether the rings are layered. Compare the source workbook and expected layout, then adjust these settings from Chart Tools and Format Data Series.
