'%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 Display Achievement Percentages on an Excel Overlay Chart
Question details
The user wants to display percentage values for achievement directly on an Excel overlay chart without adding a secondary axis.
- Product
- Microsoft Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Creating or modifying an overlay chart to track project or sales achievement versus a target plan.
- Observed behavior
- The user needs to show calculated percentage labels on the chart series rather than the default raw data values.
Ensure your chart data is properly organized in your worksheet, ideally with the raw achievement data and your target plan data in adjacent columns.
Use Built-in Format Data Labels to Show Percentages
Add data labels to your chart series and format them to display percentages instead of raw values directly through the properties pane.
This is the quickest method if Excel's built-in percentage calculation meets your needs. It replaces standard data values with relative percentages directly on the chart.
Click on the specific 'Achievement' data series on your overlay chart to highlight it.
Right-click the highlighted series and choose 'Add Data Labels' from the context menu.
Right-click one of the newly added text labels on the chart and select 'Format Data Labels'.
In the Format Data Labels pane on the right, navigate to 'Label Options', check the box for 'Percentage', and uncheck the 'Value' box to hide raw numbers.
Calculate Custom Percentages and Use 'Value From Cells'
If you need a specific Achievement-versus-Plan calculation that isn't generated automatically, calculate the percentages in a separate column and link them to the data labels.
Create and Customize Overlay Charts Effortlessly with WPS Spreadsheet
WPS Spreadsheet provides a highly intuitive charting tool that makes adding data labels, calculating achievement percentages, and formatting complex overlay charts a seamless experience.
- 1. Open Your Data File: Launch WPS Spreadsheet and open the file containing your chart data.
- 2. Insert or Select Chart: Select your overlay chart and click 'Chart Elements' or right-click the series to select 'Add Data Labels'.
- 3. Open Properties: Double-click the newly inserted data labels to open the properties pane on the right side.
- 4. Apply Custom Percentages: Choose 'Label Options' and select 'Percentage' or 'Value From Cells' to perfectly display your custom achievement rates.
Frequently Asked Questions
Why is the Percentage option grayed out in my Format Data Labels pane?
The built-in 'Percentage' option is typically available by default for charts that represent a whole, like pie or doughnut charts. For column or line overlay charts, you will likely need to use the 'Value From Cells' feature to link to your custom calculated percentages in the worksheet.
How do I remove the raw data values from the chart labels?
In the Format Data Labels pane under the 'Label Options' tab, simply uncheck the 'Value' box after you have checked 'Percentage' or 'Value From Cells'. This forces the label to only show the percentage.
How do I format the custom percentage labels to look cleaner?
You can adjust the decimal places by selecting the data labels, scrolling down to the 'Number' category in the Format Data Labels pane, selecting 'Percentage', and setting the decimal places to your preferred length (e.g., 0 for whole numbers).
