OmniDocHow to display data labels in Excel charts and retain decimals
Explain how to display data labels in an Excel chart: add labels to single or multiple data series, set decimal places in a number format, and check to make sure the labels are consistent with the original values.
Key points of this article
- Before displaying data labels on the chart, select the entire chart and then add labels to cover all data series.
- The number of decimal places should be set in the number format of the data label so that the original cell value is not changed.
- For multi-series charts, check the format of each series to avoid inconsistent decimal places in labels of a certain series.
- The label is rounded according to the displayed value, and the original value is still used for calculation. When checking, a few points must be checked against the source data.
When making Excel charts, many people will encounter two problems: specific numbers cannot be seen on the columns or polylines, or the numbers in the labels are too long and the decimal places are not neat. This article is written for people who need to show charts to others or put them into reports. It explains how to display data labels, how to retain two decimal places, and how to process multiple data series together.
After reading this, you should be able to label the entire chart uniformly, set fixed decimal places, and know what to check when you're done. The following steps apply to common column charts, bar charts, and line charts. The specific interface location may be slightly different depending on the Excel version. Just follow the general idea.
How to make the chart display data and retain two decimal places? One sentence conclusion
First add a data label to the chart, then select the Numeric category in the label's number format and set the number of decimal places to 2. What is changed in this way is the display mode of the label, and the original data is not affected.
| your situation | Recommended practices | reason |
|---|---|---|
| There is only one data series, all values must be displayed | Select the chart, add data labels, and set the number format to numerical value and 2 decimal places. | Less steps, just set it up once |
| Multiple data series require uniform decimal places | After selecting the entire chart, add labels, and then check the format one series at a time. | Adding once can cover all series, but the format needs to be confirmed separately. |
| The original data has different digits but should be displayed uniformly. | Set decimal places in label format without changing source data | The original value is not changed and subsequent calculations are not affected. |
| I just want to mark a few key points | Select these data points individually and add labels | The graphics are cleaner and less crowded with labels |
| Data is only in PDF table | First organize the table into Excel data, and then create the chart | The numbers in the screenshot cannot be directly involved in chart calculations |
Before you start: Confirm these points first
- Is the data area correct: The cells referenced by the chart must contain complete values and headers. If rows or columns are missing from the data area, labels added later will also be missing.
- Does the original data need to be changed: If only two decimal places are required for display, it is recommended to set it in the label format instead of modifying the source table, so that the calculation results will not change due to display.
- Chart Type: Label options for column, bar, line, and pie charts are not exactly the same. Pie charts can also choose to display percentages, and you need to decide whether to display numerical values or proportions based on reporting requirements.
- Whether the data source is editable: If the data is in a scanned PDF, it needs to be text recognized (OCR) first and then organized into a table. The PDF editor on the desktop client supports OCR, and you can also export the PDF to Excel. After exporting, you still need to check the numbers against the original text.
Steps to add data labels and set decimal places in Excel charts
- Open the workbook containing the chart and confirm that the data area referenced by the chart is correct and the header is clear.
- Click on a blank area of the chart to make the entire chart selected. The labels added in this way will apply to all data series.
- Open the chart elements menu. There is usually a plus button in the upper right corner of the chart, or find "Add Chart Elements" in the chart tools and check "Data Labels".
- View default labels. The label generally displays the original value. If the source data has more decimal places, the label will be displayed very long. In this case, proceed to the next step of adjustment.
- Open the format settings of the data label. Right-click on any data label, select Format Data Label, and open the settings panel on the right or pop-up.
- Set number format. Select the Numeric category in the Number category and set the number of decimal places to 2. The label immediately displays to two decimal places, for example 3.5 becomes 3.50.
- Handling multiple series. If the chart has multiple series, click the labels of each series one by one to confirm that the formats are consistent; if separate settings are required, adjust them separately.
- Save the file and view the chart again to confirm that the label position does not block the columns or lines.
Check each item after completion
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| Number of tags | Count the data points of each series and see if they all have labels. | The missing series are selected separately and tagged. |
| Decimal place | Whether the label is unified into two digits, such as 3.50 instead of 3.5 | Check whether the number format of the series has been set to numeric value and fill in 2 digits |
| Numerical accuracy | Spot check 3 to 5 tags on the source form | If the values do not match, check whether the data region is selected correctly, and whether the source cell has been mistakenly modified. |
| Label occlusion | See if the label overlaps with a column, broken line, or legend | Adjust the label position or reduce the number of data points that need to be displayed |
| Round off | Confirm that the rounded result is displayed | The label is only displayed, and the source data still prevails when accurate calculation is needed. |
# Core comparison
There are two common ways to retain decimals, but the difference lies in the scope of influence.
| Mode | Scope of influence | Whether to change the original value | Suitable for the scene |
|---|---|---|---|
| Set the digital format of the data label | Only chart labels are affected | Do not change | The display of the chart requires a unified decimal number, and the source table remains intact. |
| Set the number format of the source cell | Affect the table and chart labels that may reference it | The display changes, and the calculated value usually remains the same. | The whole table needs to be uniformly displayed. |
If it is only for charts, priority should be given to setting it on the label, and the scope of change is small.
# frequently asked questions (FAQ)
1. The numbers on the label don't match the table. What's wrong?
First see if the label is rounded to the displayed value. For example, if the source data is 3.456, the label displays 3.46 after setting two decimal places, which is a normal display. If the gap is large, check that the data area referenced by the chart is correct.
2. I changed the decimal place of the cell. Why didn't the chart label change?
The numeric format of the label may have been set separately and no longer follow the source cell. Open the format panel of the data label and confirm that the number format is numeric and fill in the correct number of decimal places.
3. What if there are too many chart labels huddled together?
You can keep only the labels of key data points, and the other points are not displayed. You can also enlarge the chart, or adjust the label position so that it does not overlap with other elements.
4. The table in the scanned version of PDF, how to make Excel chart?
First, the scanned parts are identified by OCR, and the table numbers are converted into editable content, and then exported to Excel or manually sorted into a table. The identified figures are prone to wrong words. It is recommended to check them one by one against the original text and then build the chart after confirming that they are correct.
# Summary
Most of the requirements can be accomplished by displaying the data to the Excel chart, first adding the data tag, and then setting the value and two decimal places in the label format. Multiple data series should be checked one by one, and after completion, spot check several labels on the source data to ensure that the chart is accurate.
