'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fix Excel Progress Bar Chart Dropping at 99% or 100%
Question details
The user needs to fix an issue where a percentage-based progress bar chart displays incorrectly as it reaches completion.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating or viewing a percentage progress bar chart that utilizes dual horizontal axes.
- Observed behavior
- The chart and progress icon display correctly until the value reaches approximately 99% or 100%. At that point, the progress bar visually drops back down to about 45% due to mismatched primary and secondary horizontal axis scales.
Ensure you know how to toggle the visibility of chart axes. You will need to temporarily reveal any hidden secondary axes to adjust their minimum and maximum bound settings.
Align the Primary and Secondary Horizontal Axis Scales
By manually setting the minimum and maximum scale bounds for both horizontal axes, you prevent Excel from automatically applying mismatched scales that distort the chart at 100%.
When using two axes for a single chart, Excel's default 'Auto' scaling can assign different maximum bounds to the primary and secondary axes. Since 100% equals 1.0 in decimal format, forcing both axes to range from 0 to 1 ensures the visual progress bar matches the actual data percentage.
Click anywhere on your progress bar chart to select it. Click the 'Chart Elements' icon (the plus sign) that appears next to the chart, hover over 'Axes', click the right arrow, and check all horizontal axes so they are temporarily visible.
Right-click the primary horizontal axis (usually at the bottom) and select 'Format Axis'. In the Format Axis pane under Axis Options, set the Minimum bound to 0 and the Maximum bound to 1.
Select the secondary horizontal axis (usually at the top). In the Format Axis pane, similarly set its Minimum bound to 0 and Maximum bound to 1. Both axes are now properly aligned.
Once the scales match, return to the 'Chart Elements' menu and uncheck the axes you wish to hide to restore the clean look of your progress bar chart.
Create and Manage Progress Bar Charts Easily with WPS Spreadsheet
WPS Office Spreadsheet provides intuitive and robust charting tools that make it simple to build customizable progress bars. You can easily manage primary and secondary axes, manually adjust scale limits, and ensure your data visualizations perfectly represent your data.
- 1. Open your workbook in WPS: Launch WPS Office and open your .xlsx file containing the progress bar chart.
- 2. Access axis settings: Double-click the horizontal axis on your chart to instantly open the right-side properties pane.
- 3. Adjust scale options: Navigate to the Axis Options tab, locate the Bounds section, and manually type '0' for the Minimum and '1' for the Maximum bounds.
Frequently Asked Questions
Why does my Excel chart use a secondary axis in the first place?
Secondary axes are often used in progress bar charts to overlap different data series, such as the actual progress bar and a visual milestone icon. This allows two different visual elements to share the same physical space on the chart while referring to their respective axes.
Why use '1' for the maximum axis bound instead of '100'?
In spreadsheet programs like Excel and WPS Office, percentages are stored as decimals. The value 100% is mathematically equivalent to 1.0. Entering 1 in the Maximum bound setting ensures the axis reads 100% correctly.
Can I build a progress bar without dealing with chart axes?
Yes. A simpler alternative is to use Conditional Formatting's 'Data Bars' feature directly within a cell. While it offers less graphical customization than a dedicated chart, it completely avoids issues related to axis scaling.
