How to Choose the Right Chart for Your Data (A Question-First Guide)

You’ve pulled the numbers, and now you’re staring at Excel’s Insert Chart menu with dozens of options. Pick the wrong one and your audience squints, misreads the point or asks you to “just send the spreadsheet.”

The fix is simple: stop choosing charts by how they look and start choosing them by the question you’re answering. Every good chart answers one question, such as “which is biggest?”, “how did it change?” or “are these related?” Once you know the question, the chart type almost picks itself.

This guide gives you a question-first chart chooser, explains when each common chart works, and lists the mistakes that trip up even experienced analysts.

Start with the message, not the chart

Before you open the chart menu, write down in one sentence what you want people to take away. For example: “West region sales grew every quarter while East stayed flat.”

The UK Government Analysis Function, which publishes chart guidance for public-sector analysts, puts it bluntly: if you can’t write down the message your chart is giving in a few sentences, reconsider the chart. The US Web Design System gives similar advice: keep each visualization to one central idea and state the intended message in text.

That one sentence tells you 3 things:

• What’s being compared (regions, products, time periods)
• What kind of relationship you’re showing (ranking, trend, share, correlation)
• What to highlight (the West line, the biggest bar, the outlier)

Key takeaway: A chart is an answer to a question. If you can’t say the question in one sentence, no chart type will save it.

The question-first chart chooser

Use this table to match your question to a chart type.

The rest of this guide walks through each one.

Comparing categories: bar and column charts

Bar charts are the workhorse of business reporting. Use them when you’re comparing amounts across categories: sales by product, tickets by team, costs by department.

Tips that make bar charts work:

• Sort by value. Rank bars from largest to smallest unless the categories have a natural order, such as age bands or survey scales. Sorted bars let readers see the ranking instantly.
• Start the axis at zero. Bar length is how people judge size. A cut-off axis makes a 5% difference look like a 50% difference.
• Use horizontal bars for long labels. Product names and job titles are much easier to read sideways than tilted under a column.
• Keep clusters small. If you’re comparing groups side by side (for example, 2025 vs 2026 for each region), keep it to about 4 bars per cluster.

If you need a non-zero baseline to show small differences, switch to a dot plot instead of truncating a bar chart.

Showing change over time: line charts

When the question is “how did this change?”, use a line chart. Lines show direction, speed and turning points better than any other chart.

• Time goes left to right, always.
• Limit lines to about 4. More than that turns into spaghetti. Use small multiples (a grid of mini charts) or highlight one line and grey out the rest.
• Label lines directly at the end of each line instead of using a legend, so readers don’t have to look back and forth.
• Be careful with the y-axis. A line chart can start above zero to show variation, but say so clearly on the chart.

For a handful of time periods (say, 4 quarters), a column chart also works. Once you have many periods or more than one series, lines are clearer.

Showing parts of a whole: stacked bars and pie charts

Part-to-whole questions ask “what share of the total does each piece make up?” You have 3 main options:

• 100% stacked bar: Best when comparing shares across several groups, such as channel mix by region. Keep it to about 4 segments per bar.
• Stacked column: Shows both the total and its parts, for example monthly revenue split by product line. Only the bottom segment is easy to compare, so put the most important category there.
• Pie or donut chart: Fine for a single total split into a few clearly different parts. The Analysis Function advises 5 categories or fewer and warns against pies when slices are similar in size.

If you find yourself making 3 pie charts side by side to compare groups, switch to a stacked bar. Comparing angles across separate pies is very hard for the human eye.

Relationships, distributions and specialty charts

Scatter plots

Use a scatter plot when you want to know whether 2 measures move together, such as ad spend vs leads, or store size vs revenue. Each dot is one item. Add a trendline if you want to show the general direction, and label any outliers worth discussing. Remember that correlation isn’t proof that one thing causes the other.

Histograms and box plots

A histogram shows how values are spread: how many orders fall into each $10 band, or how many support tickets took 1, 2 or 3 days to close. It answers “what’s typical?” and “how much variation is there?” in a way an average can’t.

Box and whisker charts summarize a distribution in one compact shape: middle, quartiles and outliers. They’re useful for comparing spread across groups. Both chart types are built into modern Excel, which added histogram, box and whisker, waterfall, treemap and other chart types starting with Office 2016.

Specialty charts worth knowing

• Waterfall: Shows how a starting value becomes an ending value through a series of increases and decreases. Popular for profit bridges (“revenue minus costs equals profit”) and budget variance.
• Map: Use only when location matters to the decision. A shaded US map by state looks impressive, but a sorted bar chart often answers “which state is highest?” faster.
• KPI cards: A single big number with a comparison (“$84k, up 6% vs last month”) is often the best “chart” for a dashboard headline.
• Tables: When people need to look up exact values, a well-formatted table beats any chart. Don’t force a visual where a lookup is the real need.

Common chart mistakes (and the fix)

Accessibility is worth a special mention. The US Web Design System advises against relying on color alone, recommends checking color contrast, and suggests providing a text summary or data table alongside the chart. Those habits help every reader, not only people using screen readers.

Where to start: a 5-step chart checklist

Run through this list every time you make a chart:

1. Write the question and the one-sentence answer. For example: “Which product had the highest margin? Widget C, at 42%.”
2. Identify the relationship. Is it ranking, trend, part-to-whole, correlation or distribution?
3. Pick the chart from the chooser table. When in doubt, a sorted bar chart is the safest choice.
4. Strip out clutter. Remove 3D, heavy gridlines, borders and unneeded legends. Label directly where you can.
5. Write a headline title. Replace “Sales by region” with “West region sales grew 18% this year.” Then add a short text note on what to do next.

If your data lives in a spreadsheet, a pivot table makes it easy to summarize first and chart second. See our pivot table tutorial for a step-by-step build with a linked PivotChart. When you’re ready to combine several charts into one view, read how to build a business dashboard. For the bigger picture on reporting tools, see what is business intelligence, and look up unfamiliar terms in our glossary.

Conclusion

Choosing the right chart isn’t about design talent. It’s about asking the right question first. Comparisons get bars, trends get lines, shares get stacked bars or a small pie, relationships get scatter plots, and spread gets histograms. Keep one message per chart, start bars at zero, cut the clutter and say the point in your title. Do that consistently and people will understand your data the first time they see it. For practical small business reporting ideas, see data analytics for small business.

Sources

• UK Government Analysis Function: Data visualisation: charts — matching chart types to relationships, bar axis at zero, pie and stacked bar limits, dual axis and 3D warnings
• U.S. Web Design System: Data visualizations — one central idea per chart, color and contrast, not relying on color, text alternatives
• Microsoft 365 Blog: New and modern chart types in Office 2016 — waterfall, histogram, box and whisker, treemap and other chart types in Excel
• Microsoft Support: Create a PivotChart — building charts from summarized data