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.
Why create charts in Excel
- We convert data into charts because raw data can be hard to analyse visually
- Charts make it much easier to identify trends and garner insights from the data
- MS Office offers many different charts for you to use, located in the Insert tab
How to create charts in Excel
1 Select the data you wish to plot
2 Go to the Insert tab and select your desired chart
3 Ensure the horizontal axis and legend are showing correctly
Changing the chart data
1 When you select a chart, coloured borders appear around chart data
2 With the mouse, move the coloured borders so that only your desired data is selected
3 The chart will automatically update to only show the selected data
Adding a data row manually
- To a data row manually to a chart, simply right-click on the chart and 'Select data'
- Then click 'Add' under Legend entries and select the relevant data row
ALT + N , C: Create column chart
CTRL + SHIFT + →: Select all cells within data region
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.
Let's start off in this first lesson with how to create a simple chart.
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 access 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 your 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 2 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 going to 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 see 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 Ledged 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 Ok.
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 date that you need for your charts.