1. How to Create Charts in Excel

 
Subtitles Enabled

Sign up for a free trial to access more free content.

Free trial

Charts in Depth

12 lessons , 3 exercises

Preview Course

Overview

Charts can be created in a couple of different ways. In this lesson, I'll explain the pros and cons of each method, depending on how your dataset is arranged.

Summary

  1. Inserting a Chart (00:24)

    The easiest way to create a chart is to select the data you want to chart, then go to the Insert tab of the ribbon, and select the chart you want. Excel offers a wide variety of different chart types. In this lesson, we create a stacked bar chart showing annual revenue for four different regions over a period of 11 years.

    Excel can automatically identify which data should be the labels for the horizontal axis, and which data should be the names for each of the data series, known as the legend entries.

  2. Changing Chart Data (01:23)

    When you select a chart, the horizontal axis labels, the legend entries, and the data are surrounded by colored borders. If you want to change any of these aspects of the chart, you can simply adjust these borders by clicking and dragging so that they highlight the appropriate data. The chart automatically adjusts to show the selected data. 

  3. Adding Data to a Chart (02:13)

    The most common method of creating a chart in Excel is to pre-select the data, then create the chart. However, you’ll sometimes want to create a chart and then add data to it. This can be the case if there are gaps between the rows of data you want to plot on the chart.

    We use this method to create a chart showing revenue and gross profit. First we create a column chart of revenue. To add gross profit, we right click the chart and choose Select Data. In this window, we have the option to add a new legend entry. When adding a legend entry, we specify the series name, and the data that makes up the new series. Once we do this, the new series is added to the chart.

Transcript

In business, we tend to convert raw data into charts because it's much easier to spot trends and patterns in data, once that data has been visualized. Excel offers many different charts for you to use, and in this course we'll learn how to create compelling, professional-looking charts for a variety of data sets. In this lesson, we'll learn how to create a simple chart in Excel. In the Excel sheet shown, I have raw data that shows annual revenue for a company from 2003 to 2013. This revenue has been split into four regions, Europe, North America, South America, and Asia.

The easiest way to chart this data is to first select it and then go to the Insert tab in the ribbon and select the chart you'd like to create.

I'll select the column chart and make it a stacked column.

When I do this, Excel takes the data that I've selected and creates a chart based on that data.

As you can see, Excel assumes that my first row of data are the labels for my horizontal axis, and the four text values on the left hand side, are the names of each of my data series. When I have my chart selected, you can see that the horizontal axis labels, the legend entries, and the data are surrounded by colored borders. To change the data that we have selected, we simply move these borders. So for example, if I'd only like to include the European revenue, I can simply grab this handle in the bottom left hand corner and drag it upwards, so it only includes Europe.

If I only want data from 2008 to 2013, again, I'll grab the bottom left hand corner, and drag across for 2008, and as you can see my chart updates accordingly, and in this way, we can change what data appears in our chart very quickly and very easily.

When you're creating charts in Excel, you'll almost always use this method, pre-selecting the data first, and then simply inserting a chart. However, sometimes this method won't work on its own, particularly if there are gaps between the rows of data that we want to plot on our chart.

Let's take a look at worksheet two, where I have a simple example of revenue, cost of goods sold and gross profit. I'd like to plot on my chart revenue and gross profit, but unfortunately, there are a couple of rows between these two values. I could move gross profit next to the revenue row, but that would affect the structure of my model. Instead, I'm first gonna create a chart that just shows revenue and then add the gross profit line afterwards. So I'll start by selecting my labels and the revenue data, and again, I'll insert a chart. I can use a shortcut with Alt + N to open the Insert tab, and then C for the column options, and now I have a chart that just shows revenue.

To include gross profit, I'll right click on the chart and go to Select data.

And what I'd like to do is add a legend entry of gross profit to my chart, so I'll go to Add, the series name would be gross profit, and the series values would be the gross profit cells which I'll simply select, I'll then press okay and as you can see, gross profit has now been added to my chart. For many users selecting data correctly for their chart, can be a bit of a struggle, but now that you know the two ways of completing this task, you should have no problem selecting the data that you need for your charts.