OmniDocHow to make a pie chart in Excel and how to display percentage labels
Explain clearly the steps for making an Excel pie chart, focusing on how to make data labels display percentages, and provide the basis for selecting a pie chart, the verification method after completion, and the handling of common problems.
Key points of this article
- Pie charts are only suitable for expressing "parts occupy the whole". It is recommended to limit the categories to 3-5. When there are more than 6 sectors, the sectors will be too narrow and difficult to identify.
- To display the percentage, you need to cancel "Value" in the data label settings and check the percentage. The percentage is automatically calculated by the software based on the total of the selected data.
- Do not mix the data area with the total row, otherwise the denominator of the percentage will be wrong and the proportion of all sectors will be low.
- After finishing, add up all the labels and check. The total should be close to the whole. The difference of about one percentage point caused by rounding is normal.
After many people finish making Excel pie charts, they find that the labels only display the original numbers, or the values are displayed without proportions; the text in the small sectors is still crowded together and cannot be seen clearly. The problem usually occurs when the data area is not selected correctly, or the label settings are not changed. This article is written for people who need to make several sets of data into pie charts and mark the percentages.
After reading, you should be able to judge whether the data at hand is suitable for a pie chart, follow the steps to insert the pie chart, change the labels to percentages, and check whether the results are correct. These methods are common in common desktop office software and do not depend on a specific version.
Which pie chart is right for your data? One sentence conclusion
A pie chart expresses "the proportion of each part to the whole", so it has prerequisites for data. The table below can help you make a quick judgment.
| your situation | Recommended practices | reason |
|---|---|---|
| 3–5 categories, the sum of which is the whole | Standard pie chart with labels showing percentages | You can tell at a glance which piece is bigger |
| More than 6 categories | Use a bar chart instead, or merge small items into "Other" | Sectors are too narrow and labels are illegible |
| To compare proportions across multiple years | One pie chart for each year side by side, or use a stacked bar chart instead | It is inconvenient to compare sizes between pie charts |
| The data has negative numbers, or the items do not form a whole | Instead of a pie chart, use a column chart | The pie chart requires that each item is non-negative and the total represents the whole |
| I want to see both the original quantity and the proportion. | Label with both "Value" and "Percentage" checked | Both types of information are retained on the graph |
Before you start: Confirm these points first
Confirm whether the data forms a whole. Each piece of the pie chart is part of the whole, so the sum of the items should have practical meaning, such as "sales by channel in the first quarter." If it is independent data such as "scores of different indicators", the pie chart will mislead the reader.
Confirm category quantity. The more categories, the thinner the sectors, and the easier it is for labels to overlap. When there are more than 6 items, it is recommended to merge the items with a small proportion into "others", which not only simplifies the chart but also makes the main parts more prominent.
Confirm that the data area is clean. Do not include total rows, blank rows or remark text in the selection. The software uses the sum of the selected data as the denominator to calculate the percentage. If one more row is added to the total, the proportion of each sector will be lowered.
Confirm data source. If the data is still in a PDF table, you can use the OmniDoc desktop client to export the table to Excel first, and then make charts to save the time of manual copying. The download address is download.omnidoc.top.
Steps to make a pie chart and display percentages in Excel
- Organize data. Write the category name in the first column and the value in the second column. Do not leave a blank column between the two columns.
- Select the data area. Use the mouse to drag the contents of the two columns and select the table headers together, and the generated legend will be clearer.
- Insert pie chart. Find the pie chart category in the chart insertion function of the software and select the standard two-dimensional pie chart.
- Select the chart. Click anywhere on the pie chart, and a border will appear around the chart, indicating that the entire chart has been selected.
- Add data labels. Add data labels via the plus sign next to the chart or the relevant entry in the chart tool.
- Switch to percentage. Open the format settings of the data label, uncheck "Value" and check "Percent". At this time, what is displayed on the sector is the ratio, not the original number.
- Adjust the label position. If the labels of small sectors overlap each other, you can drag the labels to the outside of the sectors one by one, or select a more appropriate method in the label position options.
- Confirm the label content. Click on any label to make sure it shows a percentage. If you need to display the category name at the same time, check "Category Name" in the label settings.
Check each item after completion
Once the pie chart is ready, don’t put it directly into the report. Checking the following items will help you avoid most mistakes.
| Check item | What do you think? | What to do if there is a problem |
|---|---|---|
| Is the total percentage close to the whole? | Add up the numbers of all the labels | The difference of about one percentage point is caused by rounding, which is normal; when the gap is obvious, check whether a line is omitted, or whether a cell is in text format. |
| Whether the label shows the percentage | See if the number on the label has a percent sign. | Go back to the label setting, cancel "value" and keep only "percentage" |
| Whether the small sector labels overlap | Enlarge the chart and view it block by block | Drag the label to the outside of the sector, or merge small items into "other" |
| Can colors and legends be distinguished? | Look at the adjacent sectors against the legend | Adjust the color of adjacent sectors, the difference should be obvious; you can also mark the category name directly on the sector. |
| Whether the data source is correct | Spot check two or three values against the original table | Go back to the data area to modify the selection, and the chart will be updated with the data. |
# Core comparison
There are three common ways to display the labels of pie charts, which are suitable for different scenarios.
| Tag content | Applicable scenario | Matters needing attention |
|---|---|---|
| Show only percentages | In the body of the report, the reader is only concerned with the proportion relationship. | Need to match the legend or text description category |
| Category name plus percentage | There are fewer categories. I hope readers can understand them without looking at the legend. | Category names tend to huddle together when they are long |
| Numerical value plus percentage | Not only the original quantity but also the proportion | There are a lot of label text, so it is recommended to be used only for charts with large sectors. |
# frequently asked questions (FAQ)
1. The percentage on the pie chart is different from what I calculated by hand. What's wrong with it?
First, let's look at two points: first, the denominator of the percentage is the total of the selected area, and if the selection contains aggregate rows or excess data, the results will be different; second, the percentage displayed by the software may be rounded to an integer or a decimal place, so the sum may not be exactly the whole.
2. There are negative numbers in the data. Can you make a pie chart?
You can't just do it. Each sector of the pie chart represents a non-negative part, and negative numbers cannot correspond to the sector. In this case, it is recommended to use a column chart or a bar chart to express the positive or negative values.
3. The pie chart label is too crowded, how to deal with it?
Priority is given to merging small categories into "other" to reduce the number of sectors. Second, drag the label to the outside of the sector, or shorten the category name. Enlarging the size of the chart can also significantly reduce overlap.
4. After the pie chart is made, how to put it in the Word or PDF document?
Select the chart in Excel, copy it and paste it into Word. If you need to put it in PDF, you can export the document to PDF first, or save it in PDF format in Word. When you need to mark or modify, you can use the PDF editing function of the OmniDoc desktop client to directly modify the text and supplement the signature.
# Summary
The key to making an Excel pie chart is to make sure that the data is suitable for the pie chart, select the right data area to insert the standard pie chart, and then switch the data label to the percentage. Add and check the labels after you are done, and you can avoid most of the mistakes. When there are many categories or the data does not form a whole, a different chart will be clearer than a hard pie chart.
