Charts in Excel: how to choose the right chart for your data

A good chart can be understood in a few seconds. A poorly chosen one forces readers to decode it or, worse, gives them the wrong idea. Excel offers dozens of chart types and variants, and it is easy to end up always using the same one or picking the most eye-catching option instead of the clearest.

In this guide you will learn how to choose a chart type based on the question you want to answer, when to use column, line, pie, scatter, histogram or combo charts, how to create them step by step and which mistakes to avoid.

First, decide what you want to say

The chart type does not depend on the data but on the message. The same monthly sales can be shown to compare regions or to see the trend over the year, and each case calls for a different chart. Before you click anything, decide which of these questions you want to answer:

Column and bar charts: comparing categories

These are the most versatile charts and almost always a safe choice. Columns are vertical and bars are horizontal. They are used to compare amounts, quantities or counts across categories: sales by region, expenses by department, orders by product.

Use this sample data to practise:

A few guidelines to make the chart easy to read:

• Choose horizontal bars if category names are long or if you have many of them: the labels can be read without tilting your head.
• Sort the data from largest to smallest before creating the chart, unless the categories have a natural order (months, age ranges).
• Start the axis at zero. If the vertical axis starts at 7,000, a small difference looks huge.
• Use clustered columns to compare two or three series (for example, this year and last year) and stacked columns only if the total also matters.

Line charts: showing change over time

When the horizontal axis is time (days, months, quarters, years), a line chart is the clearest option. The line connects the points and makes the trend, seasonality or a sudden change visible.

• Use it with dates or ordered periods, never with unordered categories such as regions or products: the line would suggest a continuity that does not exist.
• Do not put more than four or five series in the same chart. If you need more, split them into several small charts with the same scale.
• If the axis shows dates out of order or with strange gaps, check that the column contains real dates and not text.

For just a few periods (three or four), columns can work just as well. The line clearly wins from six or eight points onwards.

Pie and doughnut charts: parts of a whole

The pie chart is the most popular and also the most misused. It only makes sense when the parts add up to a meaningful whole (100% of sales, of the budget or of the responses) and there are few categories.

If you want to show the percentages in the table, calculate column C with this formula and apply a percentage format:

=B2/SUM($B$2:$B$6)

Keep these limitations in mind:

• The eye struggles to compare similar angles. In the example, North (22.1%) and Central (19.6%) are hard to tell apart in a pie chart, but easy in a bar chart.
• With many slices the chart becomes unreadable. Group the small ones into “Other”.
• It does not work for comparing two periods. For that, use 100% stacked bars, one per period.

The doughnut chart is a variant with a hole in the middle where you can write the total. It has the same strengths and the same problems.

Scatter, histogram and combo charts

Scatter (XY): the relationship between two variables

Each point represents a record with two numeric values: for example, advertising spend for each month (X axis) and sales for that month (Y axis). If the points form a sloping cloud, there is a relationship. From Chart Elements you can add a trendline to see it better. Do not confuse it with a line chart: here the X axis is numeric, not a series of categories.

Histogram: how values are distributed

It groups values into ranges and shows how many fall into each one: receipt amounts, delivery times, grades. It has been available as a chart type since Excel 2016. It is useful for finding out whether most orders fall within one range or whether there are values far away from the rest.

Combo: two measures in one chart

It lets you show, for example, monthly sales as columns and the margin percentage as a line on a secondary axis. Use it sparingly: two different scales are confusing if both axes are not clearly labelled.

How to create the chart and avoid mistakes

Step by step

1. Organise the data with headers in the first row and no empty rows or columns in between.
2. If the data will grow, turn it into a table with Ctrl+T: the chart will expand automatically when you add rows.
3. Select the range, including the headers, and go to Insert › Recommended Charts. Excel suggests options based on your data.
4. Choose the type and adjust the details on the Chart Design and Format tabs.
5. Write a title that says what the chart shows, not just the name of the data: “East leads sales” is more useful than “Sales by region”.
6. Add data labels if the exact values matter, and remove the legend if there is only one series.

Common mistakes

• 3D effects: they distort proportions and make the chart harder to read. Always use the flat version.
• Too many colours: if every column has a different colour for no reason, readers look for a meaning that is not there. Use one colour and highlight only what matters.
• Axes without units: state whether they show units, thousands or percentages.
• Years treated as a series: if the first column contains numeric years, Excel may plot them as data. Leave the header cell of that column empty or use Select Data to fix it.

If you are going to share the report with someone who does not use Excel, you can convert the workbook to PDF with our free Excel to PDF tool. Beforehand, check the page orientation and that the chart fits in the print area, and review the resulting PDF before you send it.

Conclusion

Choosing the right chart starts with the question, not with the button. Column or bar charts to compare, line charts for change over time, pie charts only for a few parts of a whole, scatter charts for relationships and histograms for distributions. With well-organised data, a title that explains the message and a simple design, your charts will be understood at first glance.

Frequently asked questions