'%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 Show Percentage Before Value in Excel Chart Labels
Question details
The user wants to format chart data labels so that the percentage is displayed before the numerical value.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Customizing chart data labels for a presentation or report to emphasize the percentage over the raw data value.
- Observed behavior
- By default, Excel might display values before percentages or in a fixed order. The goal is to rearrange the data label format to explicitly show the percentage first.
Ensure your chart type supports percentage data labels natively, such as a Pie chart or Doughnut chart. For other chart types, you may need to calculate the percentages in a separate column first.
Manually Format and Rearrange Data Labels
Use the Format Data Labels pane to enable both value and percentage, then manually adjust the text box or use custom label selections to place the percentage first.
Excel's default behavior often places the value before the percentage when both are selected in the Label Options. To bypass this and show the percentage first, you can directly edit the label's text box or configure individual label options.
Right-click on any data label within your Excel chart and select 'Format Data Labels' from the context menu to open the formatting pane on the right.
In the Format Data Labels pane, under 'Label Options', check the boxes for both 'Value' and 'Percentage'. Choose a separator such as '(New Line)' or 'Space'.
Double-click an individual data label to select its specific text box. Click inside the text box to place your cursor.
Highlight the percentage text and drag it, or manually type the percentage before the value. Alternatively, for complex charts, use the 'Value From Cells' option to map to a helper column where you have concatenated the percentage before the value using a formula.
Customize Chart Labels with WPS Spreadsheet
WPS Spreadsheet provides intuitive chart formatting tools that make it easy to adjust data labels, customize layouts, and format values and percentages exactly how you need them.
- 1. Insert a Chart: Open your dataset in WPS Spreadsheet, highlight your data, and go to the 'Insert' tab to choose a Pie or Doughnut chart.
- 2. Add Data Labels: Click on the chart to reveal the 'Chart Elements' shortcut (+ icon) and check the 'Data Labels' box.
- 3. Format Data Labels: Right-click the labels, select 'Format Data Labels', and use the task pane to check both 'Percentage' and 'Value'.
- 4. Adjust the Order: Double-click any label to enter text-editing mode and position the percentage in front of the value, or use the 'Value From Cells' option to link to a custom-formatted column.
Frequently Asked Questions
Why is the Percentage option grayed out in my Data Labels menu?
The native Percentage option is only available for chart types that represent parts of a whole, such as Pie and Doughnut charts. For Column, Bar, or Line charts, you must calculate the percentage in your spreadsheet cells and use the 'Value From Cells' option.
Can I use a formula to display the percentage before the value?
Yes. You can create a helper column that combines the percentage and value using a formula (e.g., =TEXT(C2,"0%") & " (" & B2 & ")"). Then, in the Format Data Labels pane, check 'Value From Cells' and select your new helper column.
How do I change the separator between the percentage and the value?
In the Format Data Labels pane under the Label Options section, scroll down to the 'Separator' dropdown menu. You can select options like Comma, Semicolon, Period, New Line, or Space to cleanly divide your percentage and value.
