Excel Charts: A Complete Overview

Get FREE Advanced Excel Exercises with Solutions!

Charts in Excel are used to represent data graphically. There are many types of charts in Excel that you can use based on the data.

In this Excel tutorial, you will learn everything about charts in Excel. You can learn to create a chart, format, move, copy or filter it.

The image below shows a 2-D column chart in Excel. It represents the year-wise sales of the first six months of ABC company.

excel charts


What Is a Chart in Excel?

A chart is a graphical and visual representation of data. In Excel, there are different kinds of charts. You can insert the data in a worksheet and Excel can provide you with beautiful and meaningful charts.

A chart can help viewers to understand the data, trends of the data, and future values easily. Column chart, pie chart, line chart, and bar chart are the most commonly used charts.

Types of Charts in Excel

1. Column Chart

The chart that contains the categories on the horizontal axis and values on the vertical axis is called a column chart. It is also called a vertical bar chart. It helps to get an easy comparison between different categories and values.

column chart

2. Line Chart

The chart where category data is evenly distributed on the horizontal or X-axis and values are evenly distributed on the vertical axis or Y-axis is called a line chart. A line chart is basically used to show the trend over time.

line chart

3. Pie Chart

A pie chart displays one series of data through slices of a pie to understand the proportion or percentage of each item compared to the sum of all items. It is useful when you have only one data series and none of them are zero or less than zero.

pie chart

4. Bar Chart

A chart where categories are plotted along a vertical axis and values are plotted along the horizontal axis is called a Bar chart. The Bar chart allows users to understand the comparison between individual items. It is available in 2-D and 3-D format in Excel.

bar chart

5. Area Chart

An area chart is basically a combination of a bar chart and a line chart. It illustrates the trends over time and total values across a trend. It allows us to compare the connection between parts of a whole. The 3-D format is also available for an area chart in Excel.

area chart

6. Scatter Chart

A scatter chart displays the data point for two variables in a two-dimensional plane. One variable is plotted along a horizontal axis and another is along a vertical axis. The data points are represented by dots in a scattered manner. You need to add more data to your scatter chart to get a better comparison.

scatter chart

7. Map Chart

A map chart represents the values and categories across geographical regions. But there must be regions/countries/states or postal codes in your data.

map chart

8. Stock Chart

A stock chart displays the data of stock price like opening price, closing price, high price, low price, and volume. It helps to understand the fluctuations in stock prices.

stock chart

9. Surface Chart

A surface chart is a three-dimensional chart that represents the relations among three sets of data. It uses a topographic map, colors, and patterns to highlight areas for the same range of values.

surface chart

10. Radar Chart

A radar chart is used to compare multiple series of data. It uses several axes to highlight data originating from a central point. And connects data points for each variable using lines and areas.

radar chart

11. Treemap Chart

A treemap chart gives a hierarchical view of your data to compare different levels of category. It uses different colors for categories and is helpful for lots of data that is difficult to highlight in other charts.

treemap chart

Note: Available on the newer versions since Office 2016.

12. Sunburst Chart

The sunburst chart is also used for displaying hierarchical data. A ring or circle represents each level of the hierarchy and the innermost circle is considered as the top level of the hierarchy. For one level a sunburst chart will look like a doughnut chart.

sunburst chart

Note: Available on the newer versions since Office 2016.

13. Histogram Chart

A chart where data is plotted to show frequencies within a distribution is called a histogram chart. Each column of a histogram chart is called a bin.

histogram

Note: Available on the newer versions since Office 2016.

14. Box & Whisker Chart

A box and whisker chart is used to display the distribution of data into quartiles and highlight the mean. The vertical lines through boxes are called “whiskers”. The variability of quartiles is represented by the size of the lower or upper portion of the lines.

box and whisker chart

Note: Available on the newer versions since Office 2016.

15. Waterfall Chart

A waterfall chart displays the running total of financial data. It helps to understand the effect of any addition or subtraction of data in the total. To differentiate between positive and negative data, color codes are used.

waterfall chart

Note: Available on the newer versions since Office 2016.

16. Funnel Chart

A chart that displays the values of multiple levels in a process is called a funnel chart. As the values are represented in descending order that’s why the shape of this chart looks like a funnel.

funnel chart

Note: Available on the newer versions since Office 2016.

17. Combo Chart

When a chart combines different charts for a wide range of data for better understanding then it is called a combo chart. It includes a secondary axis.

combo chart

Note: Available on the newer versions since Office 2013.


How to Create a Chart in Excel

We can create a chart in Excel by following some easy steps. Suppose, we have a dataset in the range B4:D10 that contains the yearly sales history of a company.

Let’s follow the steps below to create a chart in Excel:

  1. Select the range B4:D10.select chart data
  2. Then, go to the Insert tab.go to the insert tab
  3. In the Insert tab, you need to go to the Charts group. You can find different types of charts in the Charts group.charts group in insert tab
  4. Now, click on the Insert Column icon. A drop-down will appear.
  5. Select Clustered Column from the 2-D Column section.selecting 2-d column chart

As a result, a Clustered Column will appear on the worksheet.

2-d column chart

Create a chart using Recommended Charts option:

The ‘Recommended Charts’ option gives different types of charts based on your data. To create a chart using the Recommended Charts, you can follow the steps below:

  1. Select your data > Insert tab > Recommended Charts from the Charts group.using recommended chart option
  2. Select the desired chart from the Insert Chart window and click on OK.inserting recommended charts

How to Change Chart Layout and Style in Excel

Changing the chart layout will change the arrangement of different chart elements. To change the layout, follow the steps below:

  1. Select the chart to get the Chart Design tab on the ribbon.
  2. After that, click on the Quick Layout option. A drop-down menu will appear.
  3. Select the desired layout from the menu.change chart layout

After selecting the desired layout, the chart will automatically change.

chart after changing layout

To change the chart style in Excel, you need to follow the steps below:

  1. Select the chart and go to the Chart Design tab.chart styles under the chart design tab
  2. Select a style from the Chart Styles group.
  3. After selecting a style, the chart will automatically update.chart after changing style

How to Add, Change or Remove Chart Elements in Excel

There are different kinds of elements in a chart in Excel. A chart in Excel contains Axes, Axis Titles, Chart Title, Data Table, Data Labels, Error Bars, Gridlines, Legend, Lines, Trendline, and Up/Down Bars. You can easily add, change, or remove these elements in Excel.

To add a chart element, you can follow the steps below:

  1. Click on the chart to get the Chart Design tab.
  2. Select Chart Design > Add Chart Element > Data Table > With Legend Keys.
    You can select different elements. Here, we have added a data table with legend keys.selecting element from chart design tab

After that, you can see the chart with a data table.

chart with data table

When you select a chart, a plus icon appears on the top-right side. If you click on the plus icon, you will see the chart elements. You can select or deselect the elements to add or remove them.

add or remove chart element

If you double-click on the Data Table, you will get a Format Data Table pane on the right side of the worksheet. You can select different options from there and format the data table.

format chart data table

To remove a chart element, select the element and press the Delete key from your keyboard.


How to Switch Row and Column Data in Excel Chart

Sometimes, you may need to change the way a chart groups the data. In that case, you can use the Switch Row/Column option. For example, our chart shows year-wise sales data. To see month-wise data in a year, you can switch row/column.

To switch row/column, you can follow the steps below:

  1. Select the chart and go to the Chart Design tab.
  2. Now, select Switch Row/Column from the Data group.switch row/column of chart

After selecting the Switch Row/Column option, the chart will look like the image below.

chart after switching row/column


How to Change Chart Type in Excel

After creating a chart, you may need to change the chart type based on the requirement. To change the chart type follow the steps below:

  1. Select the chart and go to the Chart Design tab.
  2. Select Change Chart Type from the Type group.
    The ‘Change Chart Type’ window will appear.changing chart type
  3. Select the desired chart and click on OK.selecting different types of charts

How to Format Charts in Excel

After creating a chart, you need to format it to make it visually attractive. You can format different chart elements to give it a better look. Here are some basic chart formatting in Excel:

1. Format Chart Axis

A chart generally has two axes: X and Y. You can format the axes by following the steps below:

  1. Right-click on the Y axis and select Format Axis from the context menu.opening format axis option
  2. The Format Axis pane will appear on the right side of the screen.format axis pane
  3. Change the Display units to Hundreds and the Tick Marks to Inside.changes in axis option

As a result, you will see the changes on the Y-axis. You can follow the same steps to format the X-axis.

chart after changing axis option

You can also change the position of the labels and number system of the axis from the Format Axis pane.

changing axis label position and number system


2. Format Chart Title

Chart title is an important element as it gives an overview of the chart. To add or edit a chart title, you can follow the steps below:

  1. Click on the Chart Title box and type your desired title.adding chart title
  2. After adding a title, the chart will look like this.result after adding chart title

To format the chart title, you can double-click on it. It will open the Format Chart Title pane. Here, we have added a solid fill color to the chart title box. You can also explore different types of Title and Text options from here.

filling chart title box with solid color

3. Format Legend

To format the legends, double-click on the legend. It will open the Format Legend pane. You can select different options from there.

formatting legend in chart

For example, if you select the Legend Position as Right, you will see legends like the image below.

result after formatting legend in chart

4. Format Data Label

Before formatting the data label, you need to add them to the chart. To add data labels, you can follow the steps below:

  1. Select the chart > click on the plus icon.
  2. Check Data Labels from there.adding labels in chart

After adding data labels, you can format them using the following steps:

  1. Double-click on the data labels to open the Format Data Labels pane.
    You can select different options for different purposes. Here, we will change the number formatting of the data labels.
  2. To change the number formatting, go to the Number section.
  3. Type 0.0,”K” in the Format Code box and click on Add.changing number format of labels
  4. After that, the data labels will change according to the new format.result after changing number format of labels

5. Format Data Series

We can format the data series to make the charts look more attractive. To do so, you can follow the steps below:

  1. Double-click on the data series to open the Format Data Series pane.
  2. Select a color from there.opening format data series option
  3. After applying a new color, the chart will look like below.result after changing data series fill color
  4. You can also change the gaps between the series. To do so, change the Series Overlap value.changing overlap series percentage
  5. After increasing the series overlap value to 0, you will get a chart like the image below.result after changing overlap series percentage

6. Format Chart Area

You can format the chart area and change its color to make the chart report-friendly. To format the chart area, follow the steps below:

  1. Double-click on the chart area to open the Format Chart Area pane.
  2. In the Format Chart Area pane, select Solid fill and change the fill color.format chart area option

As a result, you will see the color of the chart area has been changed.

result after formatting chart area


How to Move a Chart in Excel

In Excel, there are times when you need to move the chart to a different sheet. You can do that using the Move Chart option easily. Follow the steps below to move a chart in Excel:

  1. Select the chart to get the Chart Design option.
  2. Click on the Move Chart option.move chart option
  3. Select New sheet in the Move Chart box and click on OK.moving chart in a new sheet

As a result, the chart will be moved into a new sheet.

result after moving chart

You can also move the chart as an object on a different sheet. You need to select the second option from the Move Chart box and choose the desired worksheet.

moving chart to a specific sheet


How to Copy a Chart in Excel

While creating dashboards or reports, you need to copy a chart. You can copy a chart and paste it on any location. Follow the steps below to copy a chart:

  1. Right-click on the chart and click on Copy from the context menu.copy a chart in Excel
  2. After that, go to your desired location/file and press Ctrl + V.
    Here, we have pasted the chart into a new worksheet.pasting a chart in a new sheet

How to Resize a Chart in Excel

Resizing a chart is often necessary when you are working with a lot of data. You can resize the chart using the double-headed arrow. To resize a chart, follow the steps below:

  1. Select the chart and place the cursor on the small circle of the chart. The cursor will change into a double-headed arrow.
  2. You can then drag the cursor to decrease or increase the chart size.resizing chart with mouse

You can also resize the chart using the Format tab. Click on the chart and the Format tab will appear. You can resize the chart from the Size group.

resizing chart from the Format tab


How to Filter Chart in Excel

Filtering a chart can help you visualize a specific amount of data in Excel. To filter a chart, you can follow the steps below:

  1. Select the chart and click on the ‘Filter’ icon.filter icon on chart
  2. You can check/uncheck Series and Categories from the filter options.
    Here, we unchecked April and May from the Categories section.
  3. After that, click on Apply.filter options for chart

After applying the filter, the chart will show the selected data.

chart after applying filter


How to Keep Charts Up to Date in Excel

Sometimes, after creating a chart, we need to add new data to the chart. In that case, the chart doesn’t update the data automatically. For example, if we add the data for July to the chart, the chart doesn’t update the data.

chart not updating new data

To keep charts up to date, you can follow the steps below:

  1. Select the chart area and the chart data will be selected automatically.
  2. After that, adjust the chart data range using the double-headed arrow.adding new data to chart

As a result, the chart will also show the new data.

chart after updating new data

Tips: Before creating a chart, you can convert the chart data range into a table and then, create a chart using the data. In this way, the new data will automatically be updated on the chart.


How to Select the Best Chart in Excel

There are many types of charts in Excel. To select the best type of chart, you need to understand what type of data you are working with. You need to keep the following things in mind while selecting the chart:

  1. Understand the data
  2. What you need to show through your chart
  3. Identify the chart type
  4. Determine patterns and trends
  5. Different charts for different data

Download Practice Workbook


Conclusion

In this article, we have described everything related to charts in Excel. You can create, format, and customize using different steps in Excel. You can also learn to move, copy, and resize charts from this article. In Excel, there are many types of charts and each one has different uses. This article will help you to learn everything. If you have any queries or suggestions, let us know in the comment section.


Excel Charts: Knowledge Hub


<< Go Back To Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Mursalin Ibne Salehin
Mursalin Ibne Salehin

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo