#How to Choose a Chart to Fit Your Data in Microsoft Excel

If you want to add a visual to your Excel spreadsheet that summarizes data at a glance, a chart is perfect. Depending on the type of data you have, however, it can be difficult to know which chart fits the best.

Here, we’ll look at different types of data and the charts available in Microsoft Excel. In some cases, you may have more than one type of chart that suits your data just fine. But in others, there may be only one option.

We’ll break down the types of charts and corresponding data into four categories: Comparison, Composition, Distribution, and Trends.

Excel Charts for Comparison Data

When you want to compare one set of data to another, you have several chart types that work. Some depend on the number of data sets while others use a hierarchical structure. For example, you might compare income from various sources or performance in categories for an employee review.

For displaying comparisons, you can use one of these chart types:

• Column or Bar: Use with two or more data points to show relational differences between categories.
• Treemap: Use to show a hierarchical comparison with rectangles.
• Sunburst: Use to show a hierarchical comparison with rings.
• Scatter: Use with at least two data sets when the data signifies measurements.
• Bubble: Use with at least three data sets when the third set determines the bubble sizes.

• Pie: Use with one data series where the whole equals 100 percent.
• Doughnut: Use with more than one data series where each relates to a larger amount.
• Stacked Column, Bar, or Line: Use to display parts of a whole that change over time.

RELATED: How to Make a Pie Chart in Microsoft Excel

Excel Charts for Distribution Data

If you want to display how a large data set is disseminated, use a distribution chart. This type of graph works well for things like survey results based on age, frequency of complaints in a call center, or test scores across schools.

For displaying the distribution of data, you can use one of these chart types:

• Histogram: Use to show the frequency of values sorted into bins.
• Pareto: Use to show the relative portion for each category to the total and include significant factors.
• Scatter: Use to show relationships between data sets.
• Box and Whisker: Use to show variations within multiple data sets and their relationships. The chart uses quartile, median, minimum, and maximum values.

RELATED: How to Create and Customize a Pareto Chart in Microsoft Excel

Excel Charts for Trend Data

When you think of trends, these are things that change over time. For instance, styles of jeans change throughout the decades. You had bell-bottoms in the 70s, high-waisted in the 80s, button-fly in the 90s, and so on.

For displaying data that changes over time, you can use one of these chart types:

• Line: Use with multiple data points to show trends over days, months, years, or similar timeframes, or by category.
• Area: Use instead of a line chart to emphasize the degree of change over time.
• Column or Bar: Use to show values across days, months, or years.

• Waterfall: Use to show positive and negative effects to values.
• Funnel: Use to show decreasing stages in a process.
• Surface: Use to show optimum trends or relations in values across dimensions.
• Radar: Use to show values in relation to a center point.

Excel will analyze your data and provide recommendations in the Insert Chart window. On the Recommended Charts tab, you can review the suggestions on the left and then check out a preview and brief description on the right. If you see a chart you want to use, click “OK” to pop it into your spreadsheet.