Excel Charts: A Complete Overview

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.
2. Then, 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.
4. Now, click on the Insert Column icon. A drop-down will appear.
5. Select Clustered Column from the 2-D Column section.

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

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.
2. Select the desired chart from the Insert Chart window and click on OK.

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.

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

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.
2. Select a style from the Chart Styles group.
3. After selecting a style, the chart will automatically update.

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.

After that, you can see the chart with a 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.

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.

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.

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

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.
3. Select the desired chart and click on OK.

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.
2. The Format Axis pane will appear on the right side of the screen.
3. Change the Display units to Hundreds and the Tick Marks to Inside.

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

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

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.
2. After adding a title, the chart will look like this.

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.

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.

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

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.

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.
4. After that, the data labels will change according to the new format.

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.
3. After applying a new color, the chart will look like below.
4. You can also change the gaps between the series. To do so, change the Series Overlap value.
5. After increasing the series overlap value to 0, you will get a chart like the image below.

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.

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

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.
3. Select New sheet in the Move Chart box and click on OK.

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

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.

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.
2. After that, go to your desired location/file and press Ctrl + V.
   Here, we have pasted the chart into a new worksheet.

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.

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.

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.
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.

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

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.

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.

As a result, the chart will also show the 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