How to Create a Doughnut Chart with Fixed-Size Segments in Excel
Question details
The user needs to create a doughnut chart in Excel where segments maintain a fixed size, specifically preventing the chart from ignoring zero values and resizing the remaining slices.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Visualizing variable or binary data in a doughnut chart while keeping the overall segment proportions perfectly uniform across different categories.
- Observed behavior
- By default, Excel doughnut charts ignore zero values when dividing the circle, which prevents the chart from showing fixed sections and causes the visible data to stretch unevenly.
Ensure your dataset is organized in a clean tabular format with a dedicated empty column next to it, as you will need to create helper calculations to force the chart segments to behave.
Use Helper Columns to Force Equal-Sized Segments
By creating helper data that calculates the difference between a maximum target value and your actual data, you can force the doughnut chart to draw equal-sized slices and hide the filler sections.
Because Excel natively collapses zero-value segments, the most reliable workaround is to mathematically generate "filler" segments. These fillers take up the empty space left by smaller or zero values, guaranteeing the total size of every category remains identical in the chart.
In an empty cell, calculate the maximum value of your dataset using a formula like `=MAX(B5:G13)` (adjust the range to match your actual data).
Next to your original values, create a helper column. In this column, subtract the original value from your calculated maximum segment size to determine the "difference" or filler amount for each row.
Highlight both your original data column and the new helper data column. Navigate to the 'Insert' tab on the Excel ribbon, click the 'Insert Pie or Doughnut Chart' icon, and select 'Doughnut'.
In the newly created chart, click twice slowly on one of the helper (filler) segments to select it individually. Right-click it, choose 'Format Data Point', and under the Fill options, select 'No fill'. Repeat this for all filler segments.
Automate Formatting with a VBA Macro
If you are generating multiple fixed-segment doughnut charts, you can use a VBA macro to automatically format all the calculated filler segments as transparent.
How to Create Fixed-Size Doughnut Charts in WPS Spreadsheet
WPS Spreadsheet provides robust data visualization features that seamlessly handle complex charting requirements. You can easily apply the helper-column technique to build fixed-size doughnut charts with full compatibility for Excel formulas and formatting.
- 1. Set up helper columns: Open your data in WPS Spreadsheet and add a new column. Use the formula `=MAX(Range) - Current_Value` to calculate the filler size for each data point.
- 2. Insert the chart: Select both the original data and your new helper column. Go to the 'Insert' tab on the top ribbon, click on the Chart icon, and choose the 'Doughnut' option.
- 3. Access segment formatting: Double-click the specific helper segments on the chart to open the properties pane on the right side of the screen.
- 4. Make segments transparent: In the properties pane, navigate to the Fill section and select 'No Fill' to hide the filler segments, leaving your fixed-size actual data perfectly aligned.
Frequently Asked Questions
Why does my Excel doughnut chart ignore zero values?
By default, Excel charts do not plot zero or empty values as visual slices. This causes the remaining data points to expand and fill the entire 360 degrees of the circle. To prevent this, you must use helper data to artificially reserve space for zero or smaller values.
Can I use this helper data method for pie charts as well?
Yes, the exact same logic applies to standard pie charts. By creating a filler segment that calculates the difference from a maximum value and setting its fill to transparent, you can create fixed-size pie slices just as you would with a doughnut chart.
How do I make a chart segment transparent?
Click once on the chart to select the entire series, then click a second time on the specific segment you want to hide. Right-click it, select 'Format Data Point', and under the Fill & Line options, choose 'No fill'.
Will my data labels still show properly if I use helper columns?
Data labels might initially default to showing the helper values on the transparent segments. You can manually click and delete the data labels on the hidden segments, or format your visible labels to display 'Value From Cells' pointing strictly to your original data range.
