Microsoft Excel provides a great way to summarise and analyse data using charts. The terminology used can differ between referring to them as charts or an Excel graph. Either way, it is the charting feature within Excel which will create this eye-catching visual representation of your data. Charts allow you to highlight important information or trends that occur.
I’ve said this before and I’m sure I’ll say it plenty more times but I’m a visual person and have always found it easier to identify and understand data presented in a graph rather than reading rows and rows of data.
A common issue I have heard over the years is that users find that the chart they have created does not correctly display the information in the way it is intended. Generally, this is caused by the wrong type of chart being used and so with some minor changes, you can adjust your chart to ensure the data is presented in the most suitable and effective way possible.
Excel provides many different chart types to choose from and within those chart types, you have many different layout options including 2D and 3D charts.
Chart types include:
- Column or Bar Charts
- Line or Area Charts
- Pie or Doughnut Charts
- Treemap or Sunburst Charts
- Histogram or Box and Whisper Charts
- Scatter or Bubble Charts
- Waterfall, Funnel, or Stock Charts
- Combo Charts
In this post I want to show you how to create a quick and easy chart using Excel, in fact, you can insert a chart into Excel in less than 30 seconds. This never ceases to bring a smile to the face of those who dread charts during a training session. I will also give you some tips on ensuring your data is set up correctly.
Set up your data
Often the problems that are encountered when working with charts are caused by incorrectly formatting the data which is being used to create the chart. A few common mistakes are shown below along with explanations:
- Issue 1 is the blank row underneath the headings. Excel will interpret this blank line as the headings for your axis. Always ensure there are no blank lines between your headings and data.
- Issue 2 is a blank cell within the data area. Excel will interpret this as part of your data and will include a value of zero in your data for this row. This also applies to blank columns in between your data.
- Issue 3 is an empty row. Ensure all data is completed and is not left blank or this again will be included as a zero value in your chart. Alternatively, Excel may also interpret this empty row as the end of your data area and will not automatically include data from row 12 onwards in your chart.
Create a chart in under 30 seconds
Now it’s time to create a chart in under 30 seconds. Thankfully the process to create a chart has been dramatically improved with each of the latest versions of Microsoft Excel and I’m going to show you how to create a chart with 1 mouse click and 1 press of a key on your keyboard.
- Firstly open Microsoft Excel
- If you have some data you wish to use for this exercise press Ctrl + O on the keyboard and locate/open the file
- If you do not have a file to use simply create some sample data such as the content shown below:
- Many users believe they need to highlight the data they want to include in the chart first, but Excel has gotten smarter over the years and you do not need to do that anymore.
- Simply select any cell within the data area.
- Now on the keyboard press the F11 key.
- Voila a column chart is now displayed on a new worksheet within your workbook.
- You can now use the Chart Tools to modify the chart where necessary.
This method of creating a chart is quick and easy but obviously, if you want to use a different chart type this may not be ideal but it’s a great start and certainly gives you a quick result.