How to Choose the Right Chart in Excel?

How to Choose the Right Chart in Excel?

5 mins read1.7K Views Comment
Rashmi Karan
Manager - Content
Updated on Jul 20, 2023 12:09 IST

The article helps you in choosing the right chart for an effective data visualization to achieve business goals.


Data visualization is the key to business success, and presenting the data accurately and informally is important. If you are unfamiliar with the concept of the chart in Excel and how to choose one to present the numbers, you won’t be able to find the key story and present the key story to the stakeholders. 

When it comes to deciphering numbers, then you must use the right pictures. While your data can be presented through various charts, you might want to select the one that perfectly represents your data. In this article, we will guide you on choosing the right chart in Excel so that your data looks crisp and your storytelling is perfect.

Must Read – What is MS Excel?



Points to keep in mind to choose the best chart in Excel

Before feeding in your data to create a chart, it is essential to establish your business goals. Charts are multimedia elements that help others understand the flow of data and what story we want to tell. 

Below are different business goals and the corresponding charts to help you pick the right one. 

Must Read – What is Data Visualization?

Data Comparison

To compare data sets, you have several chart options in Excel. These charts depend on the number of data sets or hierarchical structure. For example,  

To see comparisons, you can use one of these chart types:

Column or Bar Chart – Use two or more data points to show relational differences between categories.

TreeMap – Used to display a hierarchical comparison with rectangles.

Sunburst – Used to show a hierarchical comparison with rings.

Scatter plot – Use at least two data sets when the data indicates measurements.

Bubble chart – Use at least three data sets when the third set determines the size of the bubbles.

Read MS Excel Tutorials

Data Composition 

You can use a composition chart to present the components of a whole data. You can show the sales numbers achieved by a sales manager, website traffic, or a department’s contribution towards overall business revenues, all relative to the total.

To represent such data, you can use any of these chart types:

Pie Chart – Use a data series where the whole number equals 100%.

Doughnut Chart – Use more than one data series where each relates to a larger quantity.

Stacked Column, Bar, or Line Chart – View parts of a set that change over time.

Data Distribution 

You can use a distribution chart if you have massive data sets and must present their distribution. Such charts work best for surveys, marks obtained by students, etc. 

The chart types below can be used to visualise your data’s distribution.

Histogram – Used to show the frequency of values ​​sorted into bins.

Pareto Chart – Used to locate defects’ frequency and cumulative impact. It further helps in process improvement.

Spread Chart – Used to show relationships between data sets.

Box and Whisker Chart – Shows variations within multiple data sets and their relationships. The chart uses the quartile, median, minimum, and maximum values.

Explore MS Excel Courses

Data Trends 

Trends are dynamic, and to keep a tab on such dynamic data, use any of these chart types:

Line Chart – Use multiple data points to display trends over similar days, months, years, time intervals, or categories.

Area Chart – Use a line graph to emphasize the degree of change over time.

Column or Bar Chart – It displays data trends over a given period – days, months, or years.

Other types of charts in Excel

Other exciting charts in Excel that you can use for data visualization include – 

Waterfall – Used to show a data set’s positive and negative impact through a series of changes.

Funnel – Used to show the decreasing steps in a process.

Surface – Used to show trends or optimal relationships in values ​​between dimensions.

Radar – Used to display values ​​relative to a center point.


Though we have listed different chart types against different business goals, these are not hard and fast rules. You can certainly use a different type of chart or table if you think it represents your data well.

Excel also helps you choose the right chart with its Recommended Charts feature. Select the data you want to plot on a chart to use the tool. Then head over to the Insert tab and click on “Recommended Charts” in the Charts section of the ribbon.

Recommended Charts feature

Excel will analyze your data and provide recommendations in the Insert Chart window. In the Recommended Charts tab, you can review the suggestions on the left and then see a preview and brief description on the right. The recommended options will appear, and you can choose the one most suits your purpose. 

Top Trending Articles:

Data Analyst Interview Questions | Data Science Interview Questions | Machine Learning Applications | Big Data vs Machine Learning | Data Scientist vs Data Analyst | How to Become a Data Analyst | Data Science vs. Big Data vs. Data Analytics | What is Data Science | What is a Data Scientist | What is Data Analyst


How can I show the relationship between two variables in Excel?

A scatter plot is a suitable chart for visualizing the relationship between two variables. It helps identify correlations or patterns in the data.

What chart type is best for showing trends over time?

Line charts are typically used to display trends over time. They are ideal for illustrating changes and patterns in data over continuous intervals.

How can I compare data across different categories?

Bar charts are great for comparing data across different categories. They allow easy comparison by displaying data as horizontal bars.

What chart type is appropriate for displaying hierarchical data?

Treemap charts are useful for visualizing hierarchical data. They use rectangles to represent the hierarchy and display relative sizes using area proportions.

Can I change the chart type in Excel if my initial choice doesn't work well?

Yes, Excel provides flexibility to change the chart type easily. You can experiment with different chart types until you find the one that best represents your data and supports your analysis.

Download this article as PDF to read offline

Download as PDF
About the Author
Rashmi Karan
Manager - Content

Rashmi is a postgraduate in Biotechnology with a flair for research-oriented work and has an experience of over 13 years in content creation and social media handling. She has a diversified writing portfolio and aim... Read Full Bio


We use cookies to improve your experience. By continuing to browse the site, you agree to our Privacy Policy and Cookie Policy.