Best Types of Charts in Excel for Data Analysis, Presentation and Reporting

Last Updated: April 11, 2025

This article will show you the best Excel charts for data analysis, presentation, and reporting.

You will learn about the various types of charts in Excel, from column charts, bar charts, line charts, and pie charts to stacked area charts.

How to select the best Excel chart?

The type of Excel chart you select for your analysis and reporting depends upon the type of data you want to analyze and report and what you want to do with the data:

  1. Visualize data (make sense of data, especially big data).
  2. Classify and categorize data.
  3. Find a relationship among data.
  4. Understand the composition of data.
  5. Understand the distribution of data.
  6. Understand the overlapping of data.
  7. Determine patterns and trends.
  8. Detect outliers and other anomalies in data.
  9. Predict future trends.
  10. Tell meaningful and engaging stories to decision-makers

The following are the most popular Excel charts and graphs:

  1. Line chart.
  2. Clustered Column Chart.
  3. Combination chart.
  4. Stacked column chart.
  5. 100% stacked column chart.
  6. Stacked area chart.
  7. Bar chart.
  8. Pie chart.
  9. Number chart.
  10. Gauge chart (Speedometer chart).
  11. Scatter chart.
  12. Histogram.
  13. Actual vs target chart.
  14. Bullet chart.
  15. Funnel chart.
  16. Venn diagram.
  17. Sankey diagram.

When to use a Line chart?

The following are the various scenarios where a line chart can be particularly helpful:

  1. Tracking trends over time.
  2. Handling many data points.
  3. Order of categories is important.
  4. Emphasizing changes between two variables.
  5. Spotting small changes or variations.
  6. Highlighting anomalies or outliers.
  7. Forecasting future trends.
  8. Showing rate of change.
  9. Time series analysis.
  10. Budget tracking.
  11. Performance analysis over specific intervals.

Use a line chart to observe trends (like uptrend, downtrend, short-term trend, sideways trend, long term) over a period. 

Tracking trends over time

Start by understanding what each axis represents. Then, look at the trend of the line – whether it rises, falls, or stays consistent – to interpret the changes in data over time.

Line charts are great for showing long-term changes, such as trends over several months or years.

In a line chart, the axes are crucial in presenting the data.

The horizontal axis (x-axis) usually represents categories or time periods, while the vertical axis (y-axis) shows the values you’re measuring.

The relationship between these two axes allows a line chart to effectively display how one variable (the values on the y-axis) changes in relation to another (the categories or time periods on the x-axis).

This makes line charts particularly useful for tracking changes and trends over time.

#2 Handling many data points.

If you have a lot of data points, a line chart can display them without looking cluttered, unlike bar or column charts, which might seem too crowded.

Handling many data points

#3 Order of categories is important.

Use a line chart instead of a clustered column chart if the order of categories is important.

Use a line chart instead of a clustered column chart
order of categories line chart important

For example, if you want to show progress or changes over time, the line chart helps maintain chronological order.

#4 Emphasizing changes between two variables.

Line charts are perfect when you want to highlight how one variable changes in relation to another. The changes are plotted on the vertical axis against the horizontal axis.

Here’s a line chart that illustrates how one variable (Y-Values, the sine of X) changes in relation to another variable (X-Values).

line chart sine wave

#5 Spotting small changes or variations.

Line charts are excellent for displaying data with small variations that might not be as noticeable in other chart types.

line chart highlighting small variations

The chart shows Y-values that vary slightly around a sine wave pattern, plotted against X-values.

The line chart’s continuous nature makes it easier to spot these minor fluctuations, which could be lost or less apparent in other charts, like bar or column charts.

This demonstrates the effectiveness of line charts in revealing subtle changes and variations in data.

#6 Highlighting anomalies or outliers.

Line charts can help spot unusual data points. If a line suddenly spikes or drops, it can indicate something significant, like a sudden increase in website traffic.

line chart highlighting unusual data points

The line chart displayed above is designed to help spot unusual data points. It represents website traffic over 30 days.

On the 16th day, there’s a noticeable spike in traffic, which stands out distinctly against the otherwise consistent traffic levels.

The sudden increase in the chart can indicate something significant, like a successful marketing campaign, a viral post, or other events leading to increased website visits.

This example illustrates how line charts effectively highlight anomalies or unusual occurrences in data sets, allowing for quick identification of significant changes or events.

Line charts can be used for predictive analysis. By extending the line, you can forecast future trends based on past data.

line chart for predictive analytics

The line chart shown above is designed for predictive analysis. It features original data (in blue) that follows a linear trend over 30 days.

Using this data, a linear regression model has been trained to forecast future trends. The model’s predictions are extended to 45 days and depicted in red on the chart.

The extended red line demonstrates the forecasted trend based on past data, visually representing expected future values.

This kind of analysis is useful for various applications, such as predicting sales, website traffic, or stock prices based on historical trends.

The chart effectively illustrates how extending the trend line in predictive models can help forecast future trends.

#8 Showing rate of change.

Line charts are useful for illustrating the rate at which something changes.

For example, a steep line indicates a rapid change, while a gradual slope shows a slower change.

line chart illustrating rate of change

The line chart above illustrates the rate of change using two different types of growth.

The green line represents a rapid change, shown by a quadratic function (value = time²), which increases steeply as time progresses. This steep incline indicates a rapid rate of change.

In contrast, the orange line represents a slower change, modelled by a square root function (value = √time). This line has a more gradual slope, indicating a slower rate of change over the same time period.

This visualization effectively demonstrates how the steepness of a line in a chart can depict the rate at which values change: steeper lines suggest rapid changes.

At the same time, more gradual slopes indicate slower changes.

This is a fundamental concept in interpreting line charts, particularly in fields like economics, science, and engineering, where understanding the rate of change is crucial.

#9 Time series analysis.

Line charts are essential for time series analysis, where you track the same variable over regular intervals, like daily temperature readings or monthly sales figures.

time series analysis monthly sales figures

The line chart above is designed for time series analysis and depicts monthly sales figures over a year.

Each point on the chart represents sales data for a specific month, with the months on the horizontal axis and the corresponding sales figures on the vertical axis.

The connected points form a line that makes it easy to follow the sales trend throughout the year.

This type of visualization is particularly useful for identifying patterns, such as seasonal fluctuations or, unusual spikes or dips in sales.

Time series analysis like this is essential in many fields for tracking changes in variables over time and making informed decisions based on those trends.

#10 Budget tracking.

Line charts can track expenses or revenues against a budget over time, helping to see areas over or under the budget quickly.

tracking expenses and revenue against budget over time

The above line chart tracks monthly expenses and revenues against a yearly budget. The red line represents monthly expenses, and the blue line represents monthly revenues.

The green dashed line indicates the set budget level for each month.

This visualization quickly assesses how expenses and revenues compare to the budget over time.

It’s easy to identify months where expenses exceed or fall below the budget and how revenues stand in relation to the budgeted amount.

Such a chart is particularly useful in financial management, providing a clear visual representation of fiscal health and highlighting areas needing attention or adjustment.

#11 Performance analysis over specific intervals.

Line charts are great for comparing performance in different intervals, like comparing quarterly sales or academic scores year over year.

quarterly sales comparison year over year

The line chart above compares quarterly sales figures year over year. It includes data for four quarters (Q1, Q2, Q3, and Q4) across three years (2020, 2021, and 2022).

Each line represents the sales figures for a specific quarter across the different years, with distinct markers and colours for clarity.

This visualization makes it easy to compare the performance of each quarter year over year.

You can quickly see trends, such as which quarters showed growth or decline over the years.

This type of analysis is particularly valuable in business for assessing seasonal performance, planning future strategies, and understanding historical sales patterns. ​

When to use a Clustered column chart?

The following are the various scenarios where a clustered column chart can be particularly helpful:

  1. Comparing multiple data series.
  2. Suitable for comparative analysis.
  3. Consistent units of measurement.
  4. Comparable data sizes.
  5. Displaying extremes.
  6. Focus on short-term trends.
  7. The order of categories is not important.
  8. Comparing subcategories within a main category.
  9. Visualizing part-to-whole relationships.
  10. Evaluating performance across different groups.
  11. Presenting survey results.

#1 Comparing multiple data series. 

A clustered column chart is ideal for comparing 2 to 4 data series. Avoid using it for just one series or more than four, as it can get cluttered.

Following is an example of a clustered column chart with just one data series:

clustered column chart

The following chart contains just five data series, and it has already started looking cluttered:

clusterd column chart contains five data series

The chart below contains 11 data series and is very difficult to read and understand:

The clustered column chart below contains 11 data series

If you want to create a column chart that contains a lot of data series, then you can try switching ‘row’ and ‘column’ of the chart and see whether it makes any difference:

switching ‘row and ‘column of the chart

For example, after switching the row and column of the chart (with 11 data series), it looks like the one below:

after switching the row and column of the chart

Though it still looks cluttered, this chart is much easier to read and understand.

#2 Suitable for comparative analysis.

Unlike a simple column chart, which represents a single variable, clustered column charts display multiple data variables, making them suitable for comparative analysis.

clustered column chart expenses by type and order

This chart compares different types of expenses (rent, utilities, marketing) over four quarters (q1, q2, q3, q4).

Each group of bars represents a quarter, with individual bars showing the expenses for each category within that quarter.

This layout allows for an easy comparison of how each type of expense changes from quarter to quarter.

#3 Consistent units of measurement.

Use a clustered column chart when the data series you want to compare has the same unit of measurement. 

So, avoid using column charts that compare data series with different units of measurement.

For example, in the chart below, ‘Sales’ and ‘ROI’ have different units of measurement. 

The data series ‘Sales’ is of type number. Whereas the data series ‘ROI’ is of type percentage:

data table
chart ‘Sales and ‘ROI have different units of measurement

#4 Comparable data sizes. 

Use a clustered column chart when the data series you want to compare are of comparable sizes. 

So, if the values of one data series dwarf the values of the other data series, then do not use the column chart.

For example, in the chart below, the values of the data series ‘Website Traffic’ completely dwarf the values of the data series named ‘Transactions’:

website traffic and transactions chart

#5 Displaying extremes. 

Use a clustered column chart when you want to show the maximum and minimum values of each data series you want to compare.

For example, if you have monthly sales data for several products, a clustered column chart can clearly display each product’s highest and lowest sales figures side by side.

highest and lowest monthly sales for each product

This visual comparison can quickly convey which products have the most variation in sales and the overall range of sales figures for each product.

Use a clustered column chart to focus on short-term trends, particularly when comparing multiple data series over a short period, like days or weeks.

For example, if you’re analyzing weekly sales data across different store locations, a clustered column chart can visually represent each store’s weekly sales.

weekly sales data across different store locations

This setup makes it easy to identify any short-term trends or anomalies, such as a sudden spike or drop in sales at a particular location.

#7 The order of categories is not important.

Use a clustered column chart when the order of categories is not important to the interpretation of the data.

This chart type emphasizes comparing different categories or groups rather than the sequence or progression of data points.

For example, suppose you’re comparing the performance of different departments within a company, such as sales, marketing, and finance.

department performance accross quarters

In that case, the order in which these departments are listed on the chart doesn’t affect the interpretation of the data.

Each department’s performance is displayed independently of the others, allowing for easy comparison without implying any sequential relationship.

#8 Comparing subcategories within a main category.

If you have data broken down into subcategories within a larger category, a clustered column chart can effectively compare these subgroups side by side.

A clustered column chart is particularly useful for comparing data broken down into subcategories within a larger category. 

This type of chart allows you to display each subgroup side by side within the larger category, making it easy to compare the subgroups.

For example, imagine you have sales data for a clothing store categorized by seasons (Spring, Summer, Fall, Winter). 

Within each season, you have subcategories like Men’s Wear, Women’s Wear, and Children’s Wear. 

A clustered column chart can effectively display this data, showing each clothing type’s sales side by side for each season. 

clustered column chart sales by season and category

This layout helps quickly compare how different types of clothing perform in each season.

#9 Visualizing part-to-whole relationships.

When you want to show how different parts contribute to the whole in multiple categories, clustered column charts can display this effectively, especially if each category has the same parts or subcategories.

sales of car components by car types

The clustered column chart above shows how different parts (Engine, Wheels, and Body) contribute to the total sales in various car categories (Sedan, SUV, Hatchback).

Each group of bars represents a car type, with individual bars showing the sales of each component for that type of car.

This layout allows for an easy comparison of how different components contribute to the sales of each car type.

For example, you can quickly see which components are more popular in Sedans versus SUVs or Hatchbacks.

This type of chart is particularly useful in manufacturing and sales analysis, providing insights into which parts drive sales in different market segments. ​

#10 Evaluating performance across different groups.

Clustered column charts are excellent for comparing performance metrics across different groups or segments, like comparing sales across different regions or customer segments.

#11 Presenting survey results.

Clustered column charts are good for presenting survey data, especially where respondents are divided into different demographic groups and you want to compare responses to the same questions across these groups.

survey responses across different age groups

The clustered column chart above presents survey responses across different demographic age groups (18-24, 25-34, 35-44, 45+).

Each group of bars represents an age group, with individual bars showing the percentage of responses to three different survey questions.

This chart format makes it easy to compare the responses to each question across the various age groups.

For example, you can quickly see how the responses to Question 1 vary between the youngest and oldest age groups or how the opinions on Questions 2 and 3 differ across all groups.

This type of visualization is particularly useful in survey analysis, where comparing responses across different demographic segments can provide valuable insights into the preferences and opinions of different age groups.

Breaking a clustered column chart.

The chart below contains 11 data series and is very difficult to read and understand:

chart below contains 11 data series

One method of making this chart easier to read and understand is breaking it into several smaller clustered column charts.

For example, you can create one column chart that compares the sales performance of various countries in January. 

Create another column chart which just compares the sales performance of various countries in Feb and so on:

column chart compares the sales performance of various countries

The rule of thumb is to avoid presenting too much data in one chart, regardless of the chart type.

Combination chart.

A combination chart is simply a combination of two or more charts. 

For example, the combination of a column chart with a line chart. 

I use combination charts a lot, and I think you must know how to create them as they are very useful.

#1 Use a combination chart when you want to compare two or more data series that have different units of measurement:  

combination chart sales and roi

#2 Use a combination chart when you want to compare two or more data series that are not of comparable sizes:

website traffic and transactions combination chart

#3 Use a combination chart when you want to display different types of data in different ways that can be represented in the same chart. For example, line, bar and column charts can be used on the same chart.

Stacked column chart.

Use a stacked column chart when you want to compare data series along with their composition, and the overall size of each data series is important:

stacked column chart

100% stacked column chart.

Use a 100% stacked column chart when you want to compare data series along with their composition, but the overall size of each data series is not important:

100 percent stacked column chart

This chart is used to show the percentage of multiple data series in stacked columns.

Stacked area chart.

Use a stacked area chart when you want to show the trend of composition and emphasize the magnitude of change over time.

For example, the following stacked area chart shows the breakdown of website traffic:

stacked area chart

Bar chart.

Use a bar chart whenever the axis labels are too long to fit in a column chart:

bar chart

What are the different types of bar charts?

Horizontal bar charts – Represent the data horizontally. The data categories are shown on the vertical axis, and data values are shown on the horizontal axis.

Vertical bar charts – Also called a column chart. It represents the numerical values represented in the vertical bars. These are used mainly to display age ranges and salary ranges.

Grouped bar charts – Grouped bar charts represent the different time period numbers that belong to a single category.

Stacked bar charts – It is a bar chart that represents the comparisons between categories of data but with the ability to compare and break down the data.

Pie Chart.

#1 Use a pie chart to show a 100% composition of data. In other words, the various pie slices you use must add up to 100%.

This means do not create a pie chart where the various pie slices do not represent parts of the whole pie.

For example, the following pie chart is not a good representation of data composition as the two pie slices add up to 82% and not 100%:

website traffic pie chart

#2 Use a pie chart to show the composition of data only when you have got one data series and less than five categories to plot.

For example, the following pie chart shows the breakdown of website traffic sources in the last month:

following pie chart shows the breakdown of website traffic sources

Here, I have only four categories (search traffic, referral traffic, direct traffic, and campaigns) to plot. 

So a pie chart is ideal for showing the breakdown.

However, if there were more than four categories to plot, like eight or ten categories, the pie chart would have become cluttered and hard to read. 

For example, the following pie chart looks cluttered because it has too many categories:

following pie chart looks cluttered

#3 Pie charts generally express the part to the whole relationship in your data. When your data is represented in ‘percentage’ or ‘part of’, then a pie chart best meets your needs.

#4 Use a pie chart to show data composition only when the pie slices are of comparable sizes.

In other words, do not use a pie chart if the size of one pie slice completely dwarfs the size of the other pie slice(s):

size of one pie slice completely dwarfs the size of the other pie slice

#5 Order your pie slices so that as you look clockwise from top to bottom, the biggest pie slice comes first, followed by the second biggest pie slice and so on. This makes the pie chart easy to read:

the biggest pie slice comes first

These pie charts are made from the following data:

These pie charts are made from the following data

To create a pie chart where the biggest pie slice comes first, followed by the second biggest pie slice and so on.

I have sorted the data in decreasing order (from largest to smallest).

Best practices for using pie charts.

Limit the number of pie slices

Always make sure to use a minimal number of slices when creating a pie chart. It is really difficult to read a large number of slices.

If you have more than five categories, it is recommended to use a different chart type.

Make sure all data adds up to 100%

Verify that the pie slices are valued at 100% when added up.

Include annotations: Include percentages and labels for your pie chart to make it easy to read.

Pie charts work best for 25%, 50%, 75% and 100%. 

Don’t compare multiple pie charts.

Do not use multiple pie charts for comparison, as the slice sizes are really difficult to compare side by side.

Number chart.

If you want to visualize just one type of data, and it contains a numeric value that does not fall in any range/interval, then use the number chart:

number chart

Gauge chart (Speedometer chart).

If you want to visualize just one type of data, and it contains a numeric value that falls in a range/interval, then use the gauge chart (also known as the speedometer chart):

speedometer chart

A gauge chart should be used when you want to validate if your data falls in the acceptable range or not. 

A gauge chart would generally have the maximum value as the default and thresholds as low, medium and high, which indicates if the data is falling within the acceptable range.

Thresholds would be displayed in red, green and yellow for specified values.

Scatter chart.

#1 Consider using a scatter chart when you want to analyze and report the relationship/correlation between two variables:

scatter chart

This chart shows that the relationship between the two variables (‘x’ and ‘y’) is linear. As the value of the variable ‘x’ increases, there is a corresponding increase in the value of the variable ‘y’.

#2 Create a scatter chart only when ten or more data points are on the horizontal axis. The more data points, the better it is for a scatter chart. Conversely, just a few data points (like five or six) are not good enough for creating a scatter chart.

#3 Use a scatter chart when you want to show ‘why’. For example, why is revenue correlated with average order value, or why is conversion rate correlated with the number of transactions?

Histogram.

Use a histogram to show frequency distribution for quantitative data.

A histogram represents the visual representation of numerical data that falls within a specified range of values called ‘bins’. It looks exactly like a vertical graph. 

histogram

Note: You would need to install the ‘Analysis ToolPak’ to create a histogram in Excel.

Actual vs target chart.

The ‘Actual vs target’ chart is a combination chart that requires some formatting.

You can’t insert this chart straightaway into your Excel spreadsheet. 

Use this chart when you have multiple goals and want to show progress towards each goal.

The chart below shows whether target sales were achieved in each quarter:

‘actual vs. target chart

This chart is based on the following data table:

actual vs target chart data table

Bullet chart.

A Bullet chart is a combination chart (though it looks like a single bar chart) which is used to show progress towards a single goal using a range of predefined qualitative and quantitative parameters. 

You can’t insert this chart straightaway into your Excel spreadsheet, and it is also quite tricky to create.

If you have multiple goals and you want to show progress towards each goal, then use the ‘Actual vs. target’ chart. 

But if you have only one goal and you want to show progress towards this goal (by using both qualitative and quantitative data), then use the bullet chart.

A bullet chart can be a vertical bar chart or horizontal bar chart. The choice of vertical or horizontal alignment depends on the space available to use for data visualization.

The chart below shows the performance of sales in Quarter 4:

bullet chart

This chart is based on the following data table:

bullet chart 2

If the actual sales are between $0 to $240,000, then sales performance is considered ‘Poor’.

If the actual sales are between $240,000 to $300,000 ($240,000 + $60,000), then it is considered ‘Fair’.

If the actual sales are between $300,000 to $360,000 ($240,000 + $60,000 + $60,000), then it is considered ‘Good’.

If the actual sales are between $360,000 to $400,000 ($240,000 + $60,000 + $60,000 + $40,000,) then it is considered ‘Excellent’.

Funnel chart.

As the name suggests, the funnel chart is used for funnel visualization. It is perfect for showing lead funnels and sales funnels.

Funnel charts visually represent the progressive reduction of data from one phase to another phase.

The first stage is usually referred to as the intake stage.

This chart is available in MS Excel (2016 and above). You just need to select your data table and then insert the ‘Funnel’ chart.

The chart below shows different stages of the purchase funnel and how users moved from one stage to the next:

funnel chart

 This chart is based on the following data table:

This chart is based on the following data table

For example, let us take an example of an ecommerce business.

Funnel charts can be used to understand how many users have added the products to the cart, provided shipping details and completed the purchase. 

It also provides us with information about users who have abandoned their carts. 

Funnel charts are mostly used for the sales process and to identify potential problems.

A funnel consists of a higher value, called the head, and the lower part, which is referred to as the neck.

Venn diagram.

Use a Venn diagram to show the overlapping of data. 

The multi-channel conversion visualizer chart used in Google Analytics to visualize multi-channel attribution is a Venn diagram:

Venn diagram to show the overlapping of data

In web analytics, we can use a Venn diagram to determine whether or not a website has attribution problems.

If there is little to no overlap between two or more marketing channels, the website doesn’t have attribution issues.

If there is a good amount of overlap, the website has attribution issues. You should seriously consider taking multi-channel attribution into account while analyzing and interpreting the performance of marketing campaigns.

To learn more about attribution modelling read this article: Beginners Guide to Google Analytics Attribution Modelling

Another great use of Venn diagrams is in visualizing the backlinks overlaps between websites:

use of Venn diagrams is in visualizing the backlinks overlaps between websites

The tool I have used to create this Venn diagram is Venny.

Note: You can create a Venn diagram in Excel. Check out this tutorial on the Microsoft Office website: Create a Venn diagram.

What are the different types of Venn diagrams?

Two-set diagrams – Two circles with overlapping properties. This is the most common and simplest form of the Venn diagram used to compare two metrics or variables.

Three-set diagrams – This lets you visualize the relationship between three subjects rather than two variables. 

Four-set diagram– This comprises four circles with overlapping properties. It is a bit more complex than the normal Venn diagram, and each circle represents the different aspect of the data and their comparison to other variables.

Sankey diagram.

Sankey diagram

A Sankey diagram visualizes the flow of data between different stages, groups or categories of a process through a series of connected nodes or paths.

Each node represents a stage, group, or category in the process, and each path represents the flow of data between those stages, groups, or categories.

The width of each path is proportional to the amount of flow it represents.

So you can easily see which stages or categories are the most important or have the greatest impact on the overall process.

By visualizing the flow of data in this way, Sankey diagrams can help you understand the structure of a process and identify areas where improvements can be made to increase efficiency or effectiveness.

In the following cases, you can use a Sankey diagram:

#1 You want to show the flow of website visitors through different pages or sections of a website.

#2 You want to visualize the customers’ purchase journey.

#3 You want to identify patterns, trends, and areas of opportunity or concern in your customers’ purchase journey.

For example, you might notice that many customers drop out of the consideration stage, indicating that you need to improve your marketing or product messaging to engage potential customers better.

In Excel, you can create Sankey diagrams using third-party add-ins or manually using Excel’s built-in charting tools.

Creating Sankey diagrams manually in Excel can be time-consuming and may not produce the most aesthetically pleasing or informative charts.

Using third-party add-ins can often provide more advanced features and flexibility in creating Sankey diagrams.

Some popular add-ins for creating Sankey diagrams in Excel include NodeXL and Tableau.

Which charts should be avoided for reporting purposes?

Throughout this article, I have talked about the charts that should be used. 

However, there are some charts that should be avoided for reporting purposes unless your target audience is as data-savvy as you.

The following are those charts:

#1 Charts to avoid > Treemap.

Treemap chart

#2 Charts to avoid > Waterfall chart.

Waterfall chart

#3 Charts to avoid > Radar chart.

Radar chart

#4 Charts to avoid > Bubble chart.

Bubble chart

The reason you should avoid reporting data via these charts to your clients is simple.

Most people have no idea what you are trying to communicate via these charts. 

Use these charts only when your target audience is as data-savvy as you.

How to change the chart type in Excel?

MS Excel allows you to change the chart type.

For example, you can convert a clustered column chart into a stacked column chart. Or you can convert a column chart into a bar chart.

For example, let’s convert the following column chart into a bar chart:

convert the following column chart into a bar chart

Follow the steps below to change the chart type in Excel:

Step-1: Open MS Excel and navigate to the spreadsheet which contains the chart you want to edit

Step-2: Select the chart, and then from the ”Design’ tab, click on the ‘Change Chart Type’ button:

from the Design‘ tab click on the ‘Change Chart Type button

You will now see the ‘Change Chart Type’ dialog box, like the one below:

‘Change Chart Type dialog

Step-3: Click on ‘Bar’ (from the left-hand navigation) and then click on the ‘OK’ button:

recommended charts bar chart

Excel will now change your column chart into a bar chart:

change your column chart into a bar chart

What is data visualization in Excel?

Data visualization is the presentation of data (both qualitative and quantitative data) in a graphical format.

In Excel, charts and graphs are used to make a visual representation of data.

What are the benefits of data visualization

Through data visualization, you can easily:

  1. Visualize data (make sense of data, especially big data)
  2. Classify and categorize data
  3. Find a relationship among data
  4. Understand the composition of data
  5. Understand the distribution of data
  6. Understand the overlapping of data
  7. Determine patterns and trends
  8. Detect outliers and other anomalies in data
  9. Predict future trends
  10. Tell meaningful and engaging stories to decision-makers

Data presentation is a very important skill for an optimizer (marketer, analyst). In fact, it is so valuable that LinkedIn lists it as one of the top skills that can get you hired.

Excel charts are commonly used for data visualization and presentation. But selecting the right Excel chart is always a challenge.

If you use an incorrect Excel chart for your analysis, you may misinterpret data and make the wrong business and marketing decisions.

If you use an incorrect Excel chart for your presentation, stakeholders may misinterpret your charts and make wrong decisions.

Therefore, selecting the right Excel chart is critically important.

What are the most common data types that can be visualized?

The following are the most common data types that can be visualized:

#1 Quantitative data (also known as interval/ratio data) is the data that can be measured.
For example, ten customers, sales, ROI, weight, etc.

#2 Qualitative data can be classified/categorized but cannot be measured.
For example, colors, satisfaction, rankings, etc.

#3 Discrete data – quantitative data with a finite number of values/observations.
For example, five customers, 17 points, 12 steps, etc.

#4 Continuous data – quantitative data with value/observation within a range/interval.
For example sales in the last year.

#5 Nominal data – qualitative data that cannot be put into a meaningful order (i.e. ranked).
For example, {Blue, Yellow, Green, Red, Black}

#6 Ordinal data – qualitative data that can be put into a meaningful order (i.e. ranked).
For example, {very satisfied, satisfied, unsatisfied, very unsatisfied} or {strong dislike, dislike, neutral, like, strong like}.

The anatomy of an Excel chart.

To read an Excel chart, you must understand the various components of the chart.

Consider the following data table in Excel:

data table in

This data table has five variables: ‘Month’, ‘Sales’, ‘Cost’, ‘Profit’, and ‘ROI’:

This data table has got five variables

This data table is made up of categories and data series:

This data table is made up of categories and data series

Categories – Here, the first category is ‘Jan’, the second category is ‘Feb’, the third category is ‘Mar’, etc.

Data series – A data series is a set of related data points.

Data points – A data point represents an individual unit of data. 10, 20, 30, 40, etc., are examples of data points. In the context of charts, a data point represents a mark on a chart:

Data point represents an individual unit of data

Consider the following Excel chart, which is made from the data table mentioned earlier:

Excel chart which is made from the data table

This chart is made up of the following chart elements:

anatomy of an Excel chart
  1. Primary Horizontal Axis:
  2. Primary Vertical Axis
  3. Secondary Vertical Axis
  4. Primary Horizontal Axis Title
  5. Primary Vertical Axis Title
  6. Secondary Vertical Axis Title
  7. Chart Title
  8. Data Labels
  9. Gridlines
  10. Legend
  11. Trendline

In Excel, categories are plotted on the horizontal axis and data series are plotted on the vertical axis:

categories are plotted on the horizontal axis and data series are plotted on the vertical

From the chart above, we can conclude the following:

  • Months are plotted on the primary horizontal axis.
  • Sales, cost, and profit are plotted on the primary vertical axis.
  • ROI is plotted on the secondary vertical axis.

Following are examples of other Excel chart elements:

  1. Data Table with legend keys
  2. Data Table with no legend keys
  3. Error bars (Standard Error)
  4. Error bars (Percentage)
  5. Error bars (Standard Deviation)
  6. Primary Major Horizontal Gridlines
  7. Primary Major Vertical Gridlines
  8. Primary Minor Horizontal Gridlines
  9. Primary Minor Vertical Gridlines
  10. Linear Trendline
  11. Exponential Trendline
  12. Linear Forecast Trendline
  13. Moving Average Trendline

#1 Data table with legend keys.

Data table with legend keys 1

#2 Data table with no legend keys.

Data table with no legend keys 1

#3 Error bars (standard error).

Error bars standard error

If you want to see the margin of error as a standard error amount, then use the standard error bar.

About Error Bar.

An error bar is a line through a point on a graph, parallel to one of the axes, which can help you see margins of error at a glance.

#4 Error bars (percentage).

Error bars percentage

If you want to see the margin of error as a percentage, use the percentage error bar.

#5 Error bars (standard deviation).

Error bars standard deviation

If you want to see the margin of error as a standard deviation, then use the standard deviation error bar.

#6 Primary major horizontal gridlines.

Primary major horizontal gridlines

About Chart Gridlines.

Chart gridlines are the faint lines that appear on the plot area of a chart. They are used to make the data in a chart that displays axes easier to read. They can appear both horizontal and vertical.

#7 Primary major vertical gridlines.

Primary major vertical gridlines

#8 Primary minor horizontal gridlines.

Primary minor horizontal gridlines

#9 Primary minor vertical gridlines.

Primary minor vertical gridlines

#10 Linear trendline.

Linear trendline

Use the Linear trendline if your data set is linear (resembles a straight line) and the data values are increasing or decreasing at a steady rate.

About Trendlines.

The trendlines are used to graphically display trends in data. A trend is a movement in a particular direction.

A trend can be short (or seasonal), intermediate, or long term. Longer the trend more significant it is. For example, a 3 months trend is not as significant as 3 years trend.

#11 Exponential trendline.

Exponential trendline

Use the exponential trendline if data values increase or decrease at increasingly higher rates.

#12 Linear forecast trendline.

Linear forecast trendline

Use the Linear forecast trendline if your data set is linear (resembles a straight line), the data values are increasing or decreasing at a steady rate, and you want to forecast the data.

#13 Moving average trendline.

Moving average trendline

Use the moving average trendline if there is a lot of fluctuation in your data.

How to add a chart to an Excel spreadsheet?

To add a chart to an Excel spreadsheet, follow the steps below:

Step-1: Open MS Excel and navigate to the spreadsheet, which contains the data table you want to use for creating a chart.

Step-2: Select data for the chart:

Select data for the chart

Step-3: Click on the ‘Insert’ tab:

Click on the ‘Insert tab

Step-4: Click on the ‘Recommended Charts’ button:

Click on the ‘Recommended Charts button

Step-5: Select the chart you want to use from the ‘Insert chart’ dialog box and then click on the ‘ok’ button:

‘Insert chart dialog

You should now be able to see the chosen chart inserted in your spreadsheet:

see the chosen chart inserted in your spreadsheet

How to add, change, or remove a chart element in Excel?

To add, change or remove a chart element in Excel (2013 or above), follow the steps below:

Step-1: Open MS Excel and navigate to the spreadsheet which contains the chart you want to edit.

Step-2: Select the chart, and then from the ”Design’ tab, click on the ‘Add Chart Element‘ drop-down menu:

from the Design‘ tab click on the ‘Add Chart Element‘ drop down menu
‘Add Chart Element‘ drop down menu

Step-3: Select the chart element you want to add, change or remove from one of the drop-down menus.

For example, if you want to add a data table to your chart, then click on ‘Data Table‘ > ‘With legend Keys‘:

data table with legend keys

You can now see your chart along with the data table:

see your chart along with the data table

How to add a trendline to a chart in Excel?

Follow the steps below:

Step-1: Open MS Excel and navigate to the spreadsheet which contains the chart you want to edit.

Step-2: Select the chart, and then from the ”Design’ tab, click on the ‘Add Chart Element‘ drop-down menu.

Step-3: Click on the ‘Trendline’ drop-down menu and select the type of trendline you want to add to your chart.

‘Trendline drop down menu

How to change the color or style of a chart?

Step-1: Open MS Excel and navigate to the spreadsheet which contains the chart you want to edit.

Step-2: Select the chart, and then from the ”Design’ tab, click on the ‘Change Colors‘ drop-down menu to change the colours used in your chart:

change the color of a chart

If you want to change the style/design of the chart, then click on one of the styles under the ‘Design’ tab:

change the style design of the chart

How to build data visualizations in Excel?

You can build a data visualization in Excel through the following charts and graphs:

  1. Clustered column chart.
  2. Combination chart.
  3. Stacked column chart.
  4. 100% stacked column chart.
  5. Bar chart.
  6. Line chart.
  7. Number chart.
  8. Gauge chart (Speedometer chart).
  9. Pie chart.
  10. Stacked area chart.
  11. Venn diagram.
  12. Scatter chart.
  13. Histogram.
  14. Actual vs target chart.
  15. Bullet chart.
  16. Funnel chart.

Articles on data analysis and reporting.

Frequently asked questions about types of charts in Excel.

What is data visualization?

Data visualization is the presentation of data (both qualitative and quantitative data) in a graphical format. Through data visualization you can easily: make sense of data (especially big data), classify and categorize data, find relationships among data, understand the composition of data, understand the distribution of data, understand the overlapping of data, determine patterns and trends, detect outliers and other anomalies in data, predict future trends and tell meaningful and engaging stories to decision-makers.

Why is it important to use the correct Excel chart?

If you use an incorrect Excel chart for your analysis, you may misinterpret data and make the wrong business and marketing decisions. If you use an incorrect Excel chart for your presentation, stakeholders may misinterpret your charts and make wrong decisions. Therefore, selecting the right Excel chart is critically important.

What is a data series?

A data series is a set of related data points.

What is a data point?

A data point represents an individual unit of data. 10, 20, 30, 40, etc., are examples of data points. In the context of charts, a data point represents a mark on a chart.

What should be the criteria for selecting an Excel chart?

The type of Excel chart you select for your analysis and reporting should depend upon the type of data you want to analyse and report and what you want to do with the data. Do you want to classify and categorize data or find relationships among data, or understand the composition, distribution or overlapping of data?

What is quantitative data?

Quantitative data (also known as interval/ratio data) is the data that can be measured. For example, ten customers, sales, ROI, weight, etc.

What is qualitative data?

Qualitative data can be classified/categorized but cannot be measured, for example, colours, satisfaction, rankings, etc.

What is discrete data?

It is quantitative data with a finite number of values/observations. For example, five customers, 17 points, 12 steps, etc.

What is continuous data?

It is quantitative data with value/observation within a range/interval. For example, sales in the last year.

What is nominal data?

Nominal data is qualitative data that can not be put into a meaningful order (i.e. ranked). For example, {Blue, Yellow, Green, Red, Black}

My best selling books on Digital Analytics and Conversion Optimization

Maths and Stats for Web Analytics and Conversion Optimization
This expert guide will teach you how to leverage the knowledge of maths and statistics in order to accurately interpret data and take actions, which can quickly improve the bottom-line of your online business.

Master the Essentials of Email Marketing Analytics
This book focuses solely on the ‘analytics’ that power your email marketing optimization program and will help you dramatically reduce your cost per acquisition and increase marketing ROI by tracking the performance of the various KPIs and metrics used for email marketing.

Attribution Modelling in Google Analytics and BeyondSECOND EDITION OUT NOW!
Attribution modelling is the process of determining the most effective marketing channels for investment. This book has been written to help you implement attribution modelling. It will teach you how to leverage the knowledge of attribution modelling in order to allocate marketing budget and understand buying behaviour.

Attribution Modelling in Google Ads and Facebook
This book has been written to help you implement attribution modelling in Google Ads (Google AdWords) and Facebook. It will teach you, how to leverage the knowledge of attribution modelling in order to understand the customer purchasing journey and determine the most effective marketing channels for investment.

About the Author

Himanshu Sharma

  • Founder, OptimizeSmart.com
  • Over 15 years of experience in digital analytics and marketing
  • Author of four best-selling books on digital analytics and conversion optimization
  • Nominated for Digital Analytics Association Awards for Excellence
  • Runs one of the most popular blogs in the world on digital analytics
  • Consultant to countless small and big businesses over the decade