Sign in or start a free trial to avail of this feature.
1. How to Create Charts in Excel
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.
To explore more Kubicle data literacy subjects, please refer to our full library.
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.
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.
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.
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 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 this Excel sheet, we 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, then go to the Insert tab on the ribbon and select the chart we'd like to create.
We'll select the column chart and make it a stacked column.
When we do this, Excel takes the data that we've selected and creates a chart based on that data.
As we can see, Excel assumes that our first row of data contains the labels for our horizontal axis and the four text values on the left-hand side are the names of each of the data series.
When we have the chart selected, we can see that the horizontal axis labels the legend entries and the data are surrounded by colored borders.
To change the data that we've selected, we simply move these borders.
So for example, if we'd only like to include the European revenue, we can simply grab this handle in the bottom left-hand corner and drag it upwards so it only includes Europe.
If we only want data from 2008 to 2013, again we'll grab the bottom left-hand corner and drag across for 2008.
As we can see, the chart updates accordingly.
In this way, we can change what data appears in our chart very quickly and easily.
When 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 we have a simple example of revenue, cost of goods sold and gross profit.
We'd like to plot revenue and gross profit on our chart but unfortunately, there are a couple of rows between these two values.
We could move gross profit next to the revenue row but that would affect the structure of the model. Instead, we'll first create a chart that just shows revenue and then add the gross line afterwards.
We'll start by selecting the labels and the revenue data and again, we'll insert a chart. We can use a shortcut with Alt + N to open the insert tab and then C for the column options and now we have a chart that just shows revenue.
To include gross profit, we'll right click on the chart and go to select data.
Now, what we'd like to do is add a legend entry of gross profit to the chart.
So I'll go to add, the series name is gross profit and the series values are the gross profit cells.
We'll then press okay.
As we can see, gross profit has now been added to the chart.
For many users, selecting data correctly can be a bit of a struggle.
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.