How To Pick The Best Chart Type In Excel

Sun 29th May 2011

Have you ever wondered which chart type to choose in Excel to best show your data? How you present your information in a chart can be crucial in creating the best possible visual effect. This article looks at the most commonly used chart types available in Excel and summarises the best uses for each type.

Have you ever wondered which chart type to choose in Excel to best show your data? How you present your information in a chart can be crucial in creating the best possible visual effect. This article looks at the most commonly used chart types available in Excel and summarises the best uses for each type.

Suppose you create a table of data in Excel, for example showing the sales of three different products in four areas. You might create the table by typing the names of the products in separate cells down a column, then type the four area names as headings across a row, and then add all the sales data in the table. So your table has data in three rows by four columns. You then decide to create a chart based on your data. The most commonly used Excel chart types are Column, Line, Pie, Bar and X-Y Scatter. We'll look at each of these in turn.

Column Chart

Column charts tend to be used to compare sets of data. So if you create a column chart from your table, Excel will display the results in groups of Area columns horizontally with each Area group showing four vertical columns, one for each product. So the chart lets you easily compare different product sales within each area. In your data table, whichever is the greater between the number of columns and number or rows, becomes the horizontal grouping in the resulting chart, but the chart sense can be easily changed by switching the row/column choice.

So in our example you change the chart details to show Products by Areas rather than Areas by Products. To do this, ensure the chart is selected. Then in Excel 2003 choose Chart, Source Data, then choose Series in Columns, rather than Rows, and click OK to finish. In Excel 2007/2010 choose the Design Tab, then click on "Switch Row/Column".

Line Chart

Line charts tend to be used to show how data changes between items. The data display is similar to a column chart but with markers showing values rather than columns. Within the different Line Chart types you can choose markers only or lines drawn between the markers for visual neatness. A useful example of a line chart might be in showing trends or forecasts, if one of the row or column headings contains dates.

Pie Chart

Perhaps the most popular of all chart types in Excel is the Pie chart. This chart is used to show only one set of data only, for example the Area1 sales for the four products. The resulting chart shows a circle with the product sales shown proportionally within the chart.

A pie chart needs labels and data to display correctly, so you need to highlight only two columns of your original table, such as the product title and names, and the Area1 title and sales. You then use the chart wizard and create your pie chart. If you want to create a pie chart showing data which is not in two adjacent columns, for example a pie of the Area3 sales, then you need to first select the column with the product label and all the product names, then press and hold the Control key and select the Area3 label and all the area3 values. Selecting data using the Control key lets you select groups of cells from different parts of a spreadsheet. Then use the chart wizard to create your pie chart.

Bar Chart

A Bar Chart, like a column chart, tends to be used to compare sets of data, but with the bars displayed across horizontally rather than vertically, and bar groupings can be changed as described in the column chart details. However because the bars display horizontally, the bars in the upper part of the display tend to catch the eye first, so bar charts are often used to present a case, with the preferred data in the upper set of grouped bars.

XY Scatter

This chart type may sound rather confusing, but if you've created graphs at school or college using x axis and y axis data, then this chart type is based on exactly the same principles. As an example, in an empty worksheet type two columns of numbers with five numbers in each column. Label the columns X and Y. Just for exercise make the numbers in the second column twice the values of the numbers in the first column. Then highlight both columns, choose the chart wizard, and create an XY Scatter chart, choosing the type showing markers and lines. The resulting graph shows a line of data markers with each marker representing the X and Y values for each of the five sets of data in your two column table.

Hopefully this article has given you a brief insight into how to effectively use the different chart types available in Excel. To find out much more about Excel and its many features you might like to consider attending a training course. This can be a really effective way to boost your Excel skills.

Author is a freelance copywriter. For more information on excel training london, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-1712-pick-best-chart-type-in-excel.html