Charts are a great way to visualise your data. Sometimes a worksheet may contains thousands of rows of data which would be nearly impossible to make sense of. Creating a chart in Excel will make your data set more visually interesting to your audience and be able to pick out key points from it. This tutorial will teach you how to create charts in Excel.
Types of Excel Charts
Excel provides a number of charts you can choose from. It is important to choose the chart which will present the data the best possible way.
Column charts have vertical bars to present the data. They are very versatile and can be used for most types of data.
Bar charts are like column charts however the bars are displayed horizontally rather than vertically.
Surface charts are useful when you have a large data set. They are useful when you want to find the optimum combinations between two data sets.
Line charts are useful when you want to analyse trends. The lines are connected to data points so you can easily see if the trend is going up or down.
Area charts are very similar to line charts. The difference is that the area under the lines are filled in.
Combo Charts (Only available in Excel 2013 and 2016)
Combo charts are useful if you have a data set arranged in columns and rows. Combo charts are like combining two charts into one hence the name.
Pie charts are useful if you want to compare portions. Each value is shown as a slice of a pie. If you have data which is arranged in a row or column then a pie chart will be a good option.
Scatter charts are typically used for scientific, statistical and engineering data to show and compare numeric values. Data that’s arranged in columns and rows can be plotted in a scatter chart.
Radar charts compare the aggregate values of several data points. If data is arranged in columns and rows then you can use a radar chart.
Stock charts can show fluctuations in data such as stock prices, daily rainfall or annual temperature. You have to ensure the data is organised in the right order and arranged in columns and rows in order to use stock charts.
How to Create a Chart in Excel
In this example I have a list of sales people with their sales in each quarter. If I try to analyse the data by looking at the table of figures it is difficult to spot the trends. This is where charts come in useful.
To create a chart in Excel using this data set of sales people follow this step by step guide.
Highlight the data and then click on the Insert tab and under the Charts group select the chart you want.
In this example I selected a 2-D column chart. The chart is now inserted to the worksheet.
Excel has a Recommended Charts command button if you are not sure which chart to use.
You can make the chart bigger by clicking the chart to activate it and then hovering your mouse over any of the size handles until the mouse pointer turns into a left and right arrow. Click and drag the sizing handles to make the chart bigger or smaller. If you move either the left or right size handle the chart gets wider or narrower depending on whether you move your mouse left or right. If you click either the top or bottom size handles the chart gets taller or smaller depending on whether you move the mouse up or down.
Formatting the Chart
Adding Chart Axis
To add an axis title click on the chart to activate it and then click on the Design tab. Under the Chart Layouts group click on Add Chart Elements and then select Axis Titles. You can select whether you want to add horizontal axis, vertical axis titles or both. In this example I have selected the vertical axis option.
Click the axis title to rename it. In this example I have named the vertical axis "Sales".
Creating a Chart Title
To rename the chart title highlight the default chart title called “Chart Title” and then type the name of the title.
In this example I have renamed the chart title “Sales by Sales Person per Quarter”.
Changing the Chart Style
You can change the style of the chart. Click on the chart to activate it and then click on the Design tab. Under the Chart Styles group click on the design you want.
To get more styles click on the drop down arrow on the bottom right.
You can also change the chart style by using the chart formatting shortcut. Select the chart to activate it and on the right hand side of the chart you will see a chart formatting shortcut. Click on the paintbrush button. You can now select the chart style you want from the various options.
If you click on the Color tab you can change the colour of the chart series. On the chart formatting shortcut key you can also select various chart elements and filter the information.
Switch Rows and Columns
If you feel the chart is not showing the information in the way that you would like then you can switch the data over the axis. The data on the x axis will be displayed on the y axis and vice versa.
Click on the chart to activate it and then click on the Design tab and under the Data group click on the Switch Row/Column command button.
The columns and rows have been switched over.
Add Colour to the Chart
Sometimes you may want to add a bit of colour to the chart to give it more impact and make it more visually pleasing to the eye.
Select the area of the chart you want to colour. In this example I have selected the plot area. Click on the Home tab and under the Font group click on the Fill Colour drop down menu to choose the colour you want.
To Change the Chart Type
If you decide the chart you created is not the right one for your data set you can change it.
Click on the chart to make it active. Click on the Design tab and under the Type group click on the Change Chart Type command button.
The Change Chart Type dialog box will appear. Select the chart type on the left hand menu and then select the layout on the top menu and then press the OK button.
In this example I have changed the chart from a column to a bar chart.
To Move the Chart
You can also move the chart to a different location in the workbook.
In this example I want to move the chart from the current sheet to another sheet called Sheet2. To do this select the chart to activate it. Click on the Design tab and under the Location group click on the Move Chart command button.
The Move Chart dialog box will appear. In the Object in drop down box select the sheet where you want to move the chart to. In this example I want to move it to Sheet2 so I select Sheet2.
The chart has now been moved to Sheet2.
I hope you enjoyed this tutorial on how to create charts in Excel. If you have any questions or feedback then please leave me a comment below.
Excel Chart Tool Add-in
There is an excellent Excel add-in you can buy which will allow you to create visually stunning charts with a simple click of a button saving you time and effort. The add-in is called the Excel Chart Tool. To learn more about this add-in then please click here.
To see the Excel Chart Tool in action then please watch my video below.
To buy the Excel Chart Tool add-in then please click here.