MrExcel.com - Your One Stop for Excel Tips & Solutions

When do I Use Which Chart Type?

August 01, 2023 - by

When do I Use Which Chart Type?

Problem: There are 80 chart types. When should I use which chart type?

Strategy: Here are several examples:

  • For time series with equal points, you can either use column or line. People expect time to move from left to right. Use column for 12 points or less, lines for 12 points or more.
  • For time series with unequal points or with hours, use scatter.
  • Don’t use pie charts over time. Instead, use a stacked 100% column chart.
Four pie charts arranged horizontally are difficult to read. Instead, a single stacked column chart looks better.
Figure 1236. Replace four pie charts with one stacked 100% column.
  • For comparing sales of products that have long names, a bar chart allows plenty of room for the long text labels along the left axis.
  • Never use a pie chart for item comparison. Pie charts should only be used to show how several items add up to 100%.

Excel offers some other chart types that have not been covered.

If you have survey data for your company and a competitor, you can plot both results on a single radar chart. This shows the relative ranking for each of the questions.

This radar chart ranks Our Company versus the Other Guys along five axis: Speed, Price, Readability, Quality, and Value.
Figure 1237. We are slow, but are winning everywhere else.

A bubble chart is like a scatter chart, but the size of the point conveys a third bit of data. For example, you might compare miles along the x-axis, age along the y-axis, and price of the car as the size of the bubble.

A bubble chart show three dimensions: Age of various used Ford Explorer vehicles along the vertical axis. Mileage of each vehicle along the horizontal axis. Price of the vehicle is the size of the bubble. In theory, older cars with more miles (the top right of the chart) should have the smallest bubble size, meaning the lowest price.
Figure 1238. The size of the bubble indicates price.

Excel offers four types of stock charts. The name of the chart tells you the order in which the data columns should be arranged.

A stock price chart showing High, Low, Close. The High and Low is represented by a vertical line, the closing price is indicated by a tiny marker to the right of the line.
Figure 1239. A high-low-close chart in Excel.

Excel also offers surface charts and donut charts.

Jon Peltier offers utilities to create a variety of charts:

Jon Peltier offers an add-in with Waterfall, Box Plot, Cluster-Stack, Panel, Marimekko, Dot Plot, and Cascade Charts.
Figure 1240. Visit

Problem: I need to track a sales pipeline. This is basically a bar chart, but the bars are centered.

Strategy: The February 2016 update of Office 365 includes a funnel chart.

A funnel chart is a bar chart where the bars are centered. The largest bar is at the top, descending to the smallest value at the bottom. Here: 500 Prospects 425 Qualified Prospects 200 had a Needs Analysis 150 Price Quotes 100 Negotiations 90 Closed Sales
Figure 1241. Funnel charts are new in Office 365.

Filled Map charts were introduced in Office 365 in 2017. You can create charts by country, state, county, or zip code. Select data like the data shown here.

Data for a map chart has state names in D and Count in E. This subset of the data shows Alabama = 9, Florida = 19, Georgia = 3, Illinois = 7, Indiana = 4.
Figure 1242. Choose data with any geography field.

On the Insert tab, choose Map Charts. Excel will insert a chart like this one.

The resulting map chart shows each state in a different shade of the same color. Florida, with the highest seminar count is darkest, followed by Texas, Illinois, and Missouri.
Figure 1243. Darker colors indicate higer numbers.

This article is an excerpt from Power Excel With MrExcel

Title photo by Anne Nygård on Unsplash