Skip to content
Tutorials Center
My learning

Topics

Lesson 51 of 93 ยท Charts and Visualization

Choosing the Right Chart Type

Pick the right Excel chart for your question: column, bar, line, pie, scatter, area, histogram, waterfall, box and whisker and combo, with Python examples.

Excel offers dozens of chart types, but each exists to answer one kind of question. A line chart answers "how did it change?", a scatter chart answers "are these two things related?", a histogram answers "how are the values spread out?". Picking the wrong type does not just look odd: it can hide the very thing you wanted to show.

Start from the question, then pick the chart. This lesson goes through the types you will use most, says when each one fails, and shows the same ideas in Python with matplotlib so you can see the shapes. Excel's own charts cannot run on this site, so the Excel steps are plain text, and the matplotlib pictures are stand-ins that look similar but are not Excel's rendering.

What is your question?

Compare Column or bar.
Change over time Line or area.
Relationship Scatter.
Distribution Histogram or box plot.

Choose by the question, not by the look.

1 / 2

Column and bar: compare categories

A column chart uses vertical bars, a bar chart uses horizontal ones. Both compare values across categories. Bars always start at zero, because the length of the bar is the message.

  • Use columns when categories are short and have a natural order, for example months or age groups.
  • Use bars when category names are long, or when you want to rank items. Horizontal text is easier to read. Remember that Excel draws the first category at the bottom of a bar chart; tick Categories in reverse order on the axis to put the first one on top.
  • Clustered versions put series side by side for direct comparison. Stacked versions put them on top of each other and show a total, but only the bottom segment has a common baseline, so the others are hard to compare. 100% stacked shows shares.

Line: change over time

A line chart connects points in order, which says "these belong together in sequence". Use it for time series such as daily sales, monthly temperature or share prices. Time goes on the horizontal axis. With several series, keep to four or five lines.

Do not use a line when categories have no order (countries, products): the slope between them would mean nothing. An area chart is a line with the area below filled in. It suits a single series or a stacked set that shows how a total is made up over time. Overlapping areas hide each other.

Pie and doughnut: use sparingly

A pie chart shows each part as a slice of a whole that adds to 100%. It works only when there are at most five or six slices and one or two of them clearly matter ("most of the budget is rent"). Humans compare angles and areas poorly, so a pie with similar slices is hard to read, and a pie with 12 slices is useless. If you need to compare values, use a bar chart sorted by size. A pie also cannot show negative numbers or several series.

Scatter: relationships

An XY scatter puts one numeric variable on each axis, one dot per row. Use it to see whether two measures move together (advertising spend and sales), to spot clusters and outliers, and as the base for a trendline (see Formatting and Improving Charts and Correlation, Regression and Forecasting). Unlike a line chart, a scatter treats the horizontal values as real numbers with true spacing.

Histogram, box and whisker: distributions

A histogram counts how many values fall into each bin (a range such as 0-10, 10-20...). Choose it from Insert > Insert Statistic Chart. You can set the bin width, number of bins or overflow and underflow bins in the axis options. It shows the shape: symmetric, skewed, two peaks. A histogram is not a column chart: the columns touch, and the horizontal axis is a number line.

A box and whisker chart (same menu) summarizes a distribution with the median, the quartiles (the box) and the whiskers, plus dots for outliers. Several boxes side by side compare groups quickly. For the statistics behind them, see Statistics for Analysts.

Waterfall: how a total is built

A waterfall chart starts at a value, shows each increase and decrease as a floating bar, and ends at a total. Use it for profit bridges, cash flow, or "start balance to end balance". In Excel it is in the same statistics menu. Right-click a bar and choose Set as Total to anchor a bar to the axis.

Combo: two kinds, sometimes two scales

A combo chart mixes types, such as columns for revenue and a line for profit margin. If the units differ a lot (dollars and percent), put one series on a secondary axis. Use it with care: two axes with different scales can suggest relationships that are not there, so label both axes clearly with units. Choose it from Insert > Combo or by right-clicking a series and picking Change Series Chart Type.

Examples in Python

The three examples below use matplotlib (real Python, in your browser). Colors and fonts differ from Excel, but the shapes are the same.

Python Column, bar, line and pie with the same data

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Python Scatter, histogram and area

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Python Waterfall and combo chart with a second axis

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The sections above cover the types you will use most. Excel offers several more, each for a specific job. All are under Insert > Charts (or Recommended Charts > All Charts). The steps below assume your data is in a clean range with headers (Chart Basics).

Area and stacked area

An area chart is a line chart with the space under the line filled. Use it for a total that changes over time, where the volume matters. A stacked area shows how parts add up to the total over time (revenue by product line). Avoid regular (non-stacked) areas with several series, because front series hide the back ones; reduce the fill transparency if you must. Select the data, Insert > Area > Stacked Area, and keep the series count to about five.

Surface chart

A surface chart is a 3-D sheet over a grid of two variables, useful for seeing where a result is highest or lowest (for example profit across price and volume). Put one variable down the first column and the other across the first row, with the result in the cells, then choose Insert > Waterfall, Funnel, Stock, Surface or Radar > 3-D Surface. The Contour version is the same data seen from above with bands of color. It is hard to read precisely, so use it for exploring, not for reporting exact numbers.

Radar chart

A radar (spider) chart compares several measures of one or a few items on spokes from a center, such as skills ratings for two candidates. Every spoke must be on a comparable scale (for example 1 to 10). Use it only with a handful of spokes and series; area-filled radars are hard to compare. Insert > Radar > Radar with Markers.

Stock charts

Stock charts need columns in a specific order: Open-High-Low-Close (and Volume-Open-High-Low-Close if you want volume). Select the date column and those columns, then Insert > Stock > Open-High-Low-Close. Excel draws candlesticks: a body from open to close (white for up days, black for down days by default) and a wick from low to high. The date axis skips weekends only when the dates are missing from the data, so keep the date column as real dates.

Treemap and Sunburst

A treemap shows hierarchical data as nested rectangles sized by value: a product category with products inside it. A sunburst shows the same hierarchy as rings, with the top level in the center. Both need category columns in hierarchy order (Category, Subcategory) and one numeric column, and both accept positive values only. Use them for composition with many small parts where a pie would be a mess; a sorted bar chart is still easier to read for precise comparison. Insert > Hierarchy chart > Treemap or Sunburst; in Format Data Series choose label options (Banner or Overlapping).

Maps: filled map and 3D Map

A filled map (Insert > Maps > Filled Map) colors countries, states, provinces or postal codes by a value. The location column must be a recognizable place name; if Excel mis-identifies one, check spelling or add a country column. Charts sent to the Bing-based map service need an internet connection. 3D Map (Insert > 3D Map, Windows) plots latitude and longitude or place names on a globe and can animate points over a date column as a tour. For both, put the location in its own column and make sure each place name is unambiguous ("Paris, France", not "Paris").

Pie of pie, bar of pie and 3-D pie

When a pie has many small slices, a pie of pie or bar of pie (Insert > Pie > Pie of Pie) pulls the smallest slices into a second chart. In Format Data Series, set Split series by (Position, Value, Percentage or Custom) and the number of values or the threshold; the combined slice is labeled "Other". A 3-D pie looks impressive but distorts slice sizes, so avoid it for accuracy (Pie and doughnut: use sparingly). Use leader lines (Data Labels > Show Leader Lines) to keep labels readable when they sit away from the slices.

Trendlines, stacked columns and multi-category axes

  • Trendline on a stacked column: Excel does not allow trendlines on stacked charts. Add a hidden Total series (a line with no line and no markers), then add the trendline to that.
  • Clustered-stacked columns (groups of stacks): lay out the data with blank columns between groups, or use a pivot chart with two row fields. The simplest recipe is a spacer series, one row per group and one column per stack, with blank rows to make the gaps.
  • Multi-level category axis: select a two-column category range (Region, then Quarter, with the region repeated or merged). Excel draws grouped labels on the axis automatically. Make sure the outer level is typed only on the first row of each group and left blank under it.

Which type, in one table

Chart Best for Watch out
Area (stacked) Parts of a total over time Hidden series, too many layers
Surface / contour Result over two variables Hard to read exact values
Radar Profiles on 5 to 8 comparable measures Different scales mislead
Stock Open-high-low-close prices Column order matters
Treemap / Sunburst Hierarchy with sizes Positive values only, many tiny cells
Filled map / 3D Map Values by place Ambiguous place names, internet needed

Run it: how a candlestick is derived

A stock chart is just four numbers per day. This JavaScript stand-in classifies each day the way Excel colors the candle, and builds the text candle (body between open and close, wick from low to high). It does not draw a real chart.

JS Candlestick fields: body, wick and up/down color

Press Run (or Ctrl + Enter) to see the console output here.

No output.

A quick decision guide

Your question Good choice Avoid
Which category is biggest? Sorted bar chart Pie with many slices
How did it change over months? Line chart Unordered bars
Do two numbers move together? Scatter Line chart
What does the spread look like? Histogram, box plot Average only
How do gains and losses add up? Waterfall Stacked columns
Two measures with different units? Combo with labelled axes Unlabelled dual axes

Also check the Quick Analysis button that appears when you select data: it previews a few suitable charts. And remember Sparklines, Data Bars and Icon Sets for tiny charts inside cells.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which chart best shows how monthly sales changed over two years?

    Lines connect ordered points and make trends visible. Pie charts show parts of one whole, not change over time.
  2. 2. When is a pie chart acceptable?

    Pies work only for a small number of parts of one whole. Many similar slices are hard to compare, and negative values cannot be shown.
  3. 3. How does a histogram differ from a column chart?

    A histogram groups numbers into ranges (bins) and shows how many fall in each, which reveals the shape of a distribution.
  4. 4. What is the main risk of a combo chart with a secondary axis?

    Two independent scales can be set to make lines cross or align arbitrarily. Always label both axes with units.
  5. 5. You want to show a start balance, several increases and decreases, and the end balance. Which chart fits?

    A waterfall shows each change as a floating bar and ends at a total, which is the shape of a build-up.

Summary

  • Choose the chart from the question: compare, change, relationship, distribution, part of a whole or build-up.
  • Column and bar charts compare categories and must start at zero; sort bars to rank them.
  • Line and area charts show change over time; pies are only for a few parts of a whole.
  • Scatter charts show relationships, histograms and box plots show distributions, waterfalls show build-ups.
  • Combo charts need clearly labelled axes when they use a secondary scale.

Next: Formatting and Improving Charts.