Excel chart practical skills and advanced cases

Key points of this article

Organize the original data into standardized tables first, so that the charts can be automatically updated with the data to avoid redrawing every month.
The type of chart is determined by the relationship to be expressed: columns for comparisons, lines for trends, and combination charts for actual versus target comparisons.
Effects such as target lines and peak markers are implemented using auxiliary series. After the data changes, the chart will automatically follow.
Check the coordinate axis starting points, units, legends and data sources before delivery. These details determine whether readers can understand them at a glance.

When many people learn Excel charts, they try the functions of "insert column chart" and "set data labels" one by one. When they actually want to make a report, they still don't know where to start. Being able to use a single function does not mean that you can make a usable chart. This is exactly the problem that many people want to solve when searching for "Complete Excel Operation Skills: Charts": to see how a complete project is made, rather than an isolated functional description.

This article is written for people who need to make regular reports, such as professionals who need to report sales, project progress or expenses to their superiors every month. It is also written for students who need to put charts in papers or courseware. Let's take a monthly sales report as an example. Starting from the raw data, we make a combination chart with target lines and peak values that can be directly put into PPT.

After reading, you will get a set of reusable processes: how to organize data, how to choose chart types, how to add auxiliary series, and how to check delivery. Replace the sample data with your own and the same steps will apply.

It’s always unclear when making charts. How to choose the type and data structure? One sentence conclusion

Conclusion in one sentence: first think clearly about what you want readers to compare, and then decide on the type of chart; the data structure is unified into a table with "one column, one field, one row, one record".

your situation	Recommended practices	reason
Compare the numerical sizes of several categories	Column or bar chart	Length is intuitive, bar charts are easier to read when category names are longer
Look at trends over time	Line chart	Continuous lines can show ups and downs and trends
It is necessary to look at the actual value and compare it with the target	Combination chart of column and line	Two relationships are presented simultaneously in the same picture
Look at the proportion of each part to the whole	Pie chart or donut chart, with categories limited to five or six	The angle is difficult to discern when there are too many categories. Use a sorted bar chart instead to make it clearer.

Scenario: Monthly sales report needs to be shown to the leader

Suppose you have to submit a sales report every month. I have a 12-month sales breakdown sheet, which contains three columns: month, actual sales, and department goals. What is to be delivered is a chart that can fit into a PPT: the bar represents actual sales, a line represents the target, and the peak month of the year needs to be marked separately.

There are usually three places where things get stuck: the target line is animated by hand, and must be redone every time the data is updated; the peak labeling is done by manually dragging the text box, and the data becomes misaligned as soon as the data changes; the numbers in the chart and table do not match, and the leader has to go back and check when asked. The following process is designed to solve these problems.

Complete process

Organize the source data: one field per column (month, actual sales, target sales), and one month per row. Don't merge cells, and don't mix headers and data in the same area.
Convert the data area into a table: Select the data and use the "Create Table" function (or press Ctrl+T). After adding months in this way, the data range of the chart will automatically expand.
Select the two columns of month and actual sales, insert the column chart, and get the basic chart.
Add the "Target Sales" series to the chart, and change the chart type of this series to a line chart to get a combination chart.
Add a new column "Peak Mark", fill in the value only in the row with the highest sales throughout the year, and leave the remaining cells blank. Add it to the chart, set it to show only markers and no lines, and add a data label for the point. Empty cells will not be drawn, so the labels will move as the data changes.
Unify the coordinate axes and units, such as "10,000 yuan" as the unit; delete redundant grid lines; place the legend at the top or bottom; write the conclusion in the chart title, such as "Sales grew steadily in the first half of the year" instead of just "sales chart".
Copy to PPT or document, or export to PDF for delivery.
If you need the English version, you can upload an .xlsx file containing text at omnidoc.top, select bilingual comparison or translation only, and download the results. After translation, you need to check whether the chart title, axis text, and terms in the table still correspond to the data. The machine-translated content must also be checked item by item.

Where things can easily go wrong

Data range not extended: The chart does not change after adding a new row of data. The way to find out is to add a new month and see if the chart is updated. The solution is to use the table function, or check whether the last row of the data area has been written.
The starting point of the vertical coordinate is automatically truncated: When the starting point of the column chart is not zero, small differences will be magnified, which can easily mislead readers. The way to find this is to see if the vertical axis starts at zero. The processing method is to start the column chart coordinate axis from zero; if it really needs to be truncated, clearly indicate it on the chart.
Double axes cause confusion: In combination charts, two axes are sometimes used if the target and actual values are of different magnitudes. The processing method is to share one axis for the same indicator as much as possible; when dual axes are really needed, both axes should be marked with units, and the series should be distinguished by color.
Annotation misalignment: The manual text box will deviate from its original position after the data changes. The solution is to replace text boxes with data labels and auxiliary series.
Inconsistency between chart and table numbers: usually caused by hand-copied numbers. The solution is to have the chart reference the table area directly, rather than entering numbers manually.

🎯 Which method is suitable for you?

If you have to repeat the same report every month

First create a template with tables and auxiliary columns, then only replace the data, and the chart will be updated accordingly, saving you the time of redrawing every month.

If you want to put it into a paper or courseware

Choose a simple color scheme, remove decorative effects, the coordinate axes and units must be complete, and clearly indicate the data source and statistical caliber in the legend.

If you want to show it to someone who is not familiar with data

Write the conclusion directly in the title, such as "Fourth quarter sales rebounded and exceeded target", and then use color to highlight only one or two key data points.

If you only need to make a one-time small image

There is no need to build an auxiliary series. A basic column chart with a manual line is enough, but you must confirm that the data will not change afterward.

❓ Frequently Asked Questions (FAQ)

1. Why is the data in the chart not updated with the table?

Usually this is because the chart refers to a fixed range, not a table. Convert the source data into a table, or check whether the data area contains new rows.

2. Why is the target line in the combination chart disconnected?

When there are blank cells in the auxiliary series, the lines will break at that point. If you want the lines to be continuous, you need to fill in the target value for each month.

3. Are charts suitable for pie charts?

It can be used when there are few categories and only the proportion is expressed. When there are more than five or six categories, it is recommended to switch to bar charts, as it is easier for readers to compare lengths rather than angles.

4. What should you look for when choosing free charting tools?

Check file size and data size limits, whether exports include watermarks, whether data needs to be uploaded to the server, and whether the chart format can be fully preserved. When sensitive data is involved, local processing is prioritized.

📝 Summary

The value of Excel charts lies in the complete project, not in individual functions. First, organize the data, then select chart types based on relationships, use auxiliary series to achieve target lines and annotations, and finally check delivery details to create Excel charts that can be reported directly. Applying this process to your own data will make your next report much easier.

Open OmniDoc and process your table file