What are the Excel chart types and what data are each suitable for?

Key points of this article

For charts, first look at the relationship that the data wants to illustrate: use a column chart for comparison, a line chart for trend, a pie chart or donut chart for proportion, a histogram or box plot for distribution, and a scatter chart for the relationship between two variables.
The type name determines what can be expressed. The appearance is just the presentation method. Both column charts and bar charts can be compared. The difference lies in the length and quantity of the category names.
There are "recommended charts" only after Excel 2013. In the 2010 version, you need to choose from the chart types in the Insert tab.
Pie charts are only suitable for data where a few parts add up to the whole. When there are many categories, it is easier to read using column charts or bar charts.
Before selecting the type, organize the data into a structure of one column of categories, one column or several columns of values, so that the chart can be generated as expected.

When many people make Excel charts, the first step is to randomly choose a style that looks pleasing to the eye. As a result, the resulting chart is made, but the meaning of the data is unclear. This article is written for people who need to make graphs in their worksheets but are not sure which graph to choose. For example, students who want to submit monthly reports to their leaders or put experimental data graphs in their papers.

After reading, you will know which data relationships are suitable for common Excel charts, and how to judge the type by yourself when there is no "recommended chart" prompt in Excel 2010. This article talks about selection ideas and does not involve specific drawing operation details.

Which Excel chart is right for your data? One sentence conclusion

The basis for choosing a chart is what you want readers to see: to compare sizes, to see changes, to see how much each part occupies, or to see where the data is concentrated. Answering this question first and then choosing the type is usually more reliable than starting with appearance.

your situation	Recommended practices	reason
Compare values across several categories, such as quarterly sales by department	Column chart; use bar chart when category names are long	The length directly corresponds to the numerical value, and readers can compare the height at a glance.
Look at changes in the same metric over time, such as electricity usage for 12 consecutive months	Line chart	The trend of the broken line can clearly show the rise, fall and fluctuation.
Look at the proportion of several parts to the whole, such as the four types of expenses accounting for the total budget	Pie or donut chart with no more than five or six categories	The relationship between the whole and the parts is intuitive, but there are too many categories and it is difficult to distinguish from different angles.
Look at the distribution of data, such as which interval a batch of test scores are concentrated in	Histogram; use box plot when you need to see the degree of dispersion	Group counts or five-number summaries can show central tendencies and outliers
See whether two variables are related, such as advertising investment and sales	XY scatter plot	Each point represents a pair of data, and trends and outliers can be seen

How are chart types divided?

Excel's chart types can be understood as two levels: "data relationship" and "presentation form". The type name determines what the chart can express. For example, a line chart emphasizes the time sequence, and a pie chart emphasizes the proportion of the whole and its parts. If the same set of data is replaced by another type, the readers will get different information.

According to data relationship, there are roughly four categories

Comparison type: column chart, bar chart, radar chart. It is suitable for looking at the size of each item, and the radar chart is suitable for comprehensive comparison of a small number of indicators.
Trend type: line chart, area chart. The area chart can also reflect the cumulative amount, which is suitable for viewing changes in the total amount over time.
Proportion type: pie chart, donut chart, stacked column chart. Stacked column charts can compare the composition of multiple sets of data at the same time.
Relationship and distribution categories: scatter plots, bubble charts, histograms, stock price charts, etc. Scatter charts look at correlations, bubble charts add a third value to the scatter points, and stock price charts arrange data in the order of opening, high, low, and closing.

Common misunderstanding: Not all"good-looking"pictures are suitable

Pie charts are not suitable for multi-category or multi-period data. When there are too many categories, it is difficult to identify the differences between sectors. In this case, it is more clear to use a bar chart.
Line charts are not suitable for unordered categories. If the horizontal axis is names with no sequential order such as "Beijing, Shanghai, Guangzhou", connecting them with polylines will make readers mistakenly think that they have a temporal relationship, and it is more reasonable to use a column chart instead.
Three-dimensional images and three-dimensional effects are easily misleading. The perspective of a three-dimensional column chart will make columns of the same height look different in size. It is recommended to use a flat chart when making formal reports.
The data source structure is wrong, and it is useless to select the type. If categories and values are mixed together, or if the same category appears repeatedly, the chart will incorrectly split the data. The data should be organized into clear tables before selecting a type.

📊 Core comparison

chart type	suitable data	Not suitable for situations
Column chart	Numerical comparison of a small number of categories, subtotals by time	Category names are too long, or there are too many categories crowded together
Bar chart	Comparisons with long category names that require horizontal reading	Need to express chronological order
Line chart	Continuous changes in the same indicator over time	There is no sequential relationship between categories
pie chart	The proportion of several parts to the whole, and the sum of the parts is the whole	There are many categories, or the differences between each part are very small
area chart	Amount and composition over time	Need to accurately compare specific values across multiple series
Scatter plot	Correlation between two numerical variables	The data has only one column and no paired values.
Histogram	Frequency distribution of a set of continuous data in each interval	Category data, such as product name

How to choose the type when there is no recommended chart?

Many people search for "excel has no recommended charts". The reason is that the "recommended charts" feature only appeared after Excel 2013, and there is no such prompt in Excel 2010. This does not affect the selection, it just needs to transfer the judgment process from the software's suggestions to your own hands.

Organize the data structure first, then select the type

Organize the data into a standard table: the first column is the category or time, the following columns are numerical values, and the indicator name is written clearly in the header. If merged cells, blank rows or summary rows are mixed in the data area, the generated chart will often have extraneous series.

Then use "What do you want to explain" to check in

First write a sentence to explain the conclusion that the chart wants to express, for example, "Sales are growing in each quarter of this year." The keywords in this sentence point to the type: "growth" and "quarter" point to the line chart or column chart, and "proportion" points to the pie chart. If the conclusion is unclear, it usually means that the data itself needs to be sorted out.

For uncertain situations, compare the two pictures.

If you are not sure about a column chart or a line chart, you can make one of each and put them together to see which one is easier to answer your question. This kind of comparison is more reliable than choosing based on feeling, and can also detect whether there are problems with the data structure.

How to use it in practice

First write the conclusion you want to express in one sentence in the worksheet, and make it clear whether it is a comparison, trend, proportion, distribution or relationship.
Check the data area: the category is in the first column, the value is on the right, the header is clear, and there are no merged cells or blank rows.
In Excel 2010, after selecting the data, find the part to insert a chart in the ribbon, and select from the chart type according to the table above; in newer versions, you can first look at the recommended charts, and then compare them with your own judgment.
If the title, legend or table text in the chart needs to be translated into other languages, you can upload the .xlsx file to omnidoc.top, select bilingual comparison or download only the translation. After machine translation, please check whether the technical terms and numbers are accurate.

🎯 Which method is suitable for you?

When making daily reports, you only need to highlight contrast: use column charts or bar charts first, and keep the color scheme simple.
Do time series analysis, such as monthly indicators: use a line chart with equally spaced time scales on the horizontal axis.
Do distribution and correlation analysis in papers or professional reports: use histograms, scatter plots, and indicate the sample size and data source in the legend.

❓ Frequently Asked Questions (FAQ)

1. What is the difference between a pie chart and a donut chart?

Both are used to express proportions. The middle of the donut chart is left blank, and a total number or explanatory text can be placed in the center. Neither is suitable when there are many categories, and the difference is mainly in the layout.

2. When should you choose a column chart or a bar chart?

When the category names are short and the number is small, use a column chart; when the category names are long, or when there are many categories that need to be arranged vertically, it is easier to read using a bar chart.

3. What are the chart types in Excel 2010?

The built-in types in the 2010 version include major categories such as column, line, pie chart, bar, area, XY scatter, stock price, surface, donut, bubble and radar, and there are subtypes under each category. The specific choice can be judged according to the previous comparison table.

4. Why is the chart I selected different from what I expected?

Common reasons are that the data area is selected incorrectly, the header is regarded as a numerical value, or the total row is also included in the series. Check the data source range and adjust series and categories as needed.

5. Are charts more complex and more professional?

uncertain. The function of diagrams is to allow readers to quickly understand conclusions. Simple flat diagrams are usually clearer than diagrams with three-dimensional effects and too many colors.

📝 Summary

The core of choosing Excel charts is to first think clearly about the relationship that the data wants to express: use column charts or bar charts for comparisons, use line charts for trends, use pie charts for proportions, and use histograms and scatter charts for distribution and correlation. When encountering a situation where there is no recommended chart prompt, organize the data structure, write down the conclusions to be explained, and then make a judgment based on the type, the choice will be more secure.

Open OmniDoc and work with your spreadsheet file