Level 2 - Lesson 27 - What chart to use
Category: [General,Excel] Demo Available
What Chart Type to Use
Before we even consider creating a chart we need to give thought to what it is we are trying to show. Put another way we need to decide on the picture we are going to paint. Let’s go through the different types of charts and see a brief description of when you would use them. The descriptions for each chart type are certainly not cast in stone and there are far from any hard and fast rules.
Column
A column chart might be used to show changing data values over time or by category eg; departments, regions etc. Categories should be shown horizontally with values shown vertically.
Bar
A bar chart could be used to illustrate a comparison of different categories or items by their associated values. Categories should be shown vertically and values shown horizontally. A bar chart is not usually used to place an emphasis on time.
Line
A line chart is best suited to demonstrate the trend of a set (or sets) that have related data.
Pie
Pie charts are used to show the proportion of particular items in comparison to the total sum of all items. Each slice of a pie chart is often shown as a percentage of the total. It can only show one data series.
XY (Scatter)
An XY (scatter) chart can show relationships between data that is set up in more than one row or column. In other words it can be used to show relationships among two or more sets of values.
The data for an XY (scatter) should have it’s X values set up in the top row or left column and it’s Y values set up in corresponding rows or columns.
Area
An area chart can highlight the extent of a change over a set time period of one or more set of values. It can also show its values as a proportion of the total in a similar way as a pie chart.
Doughnut
A doughnut is very similar to a pie chart. The difference is it can contain more than one data series. A pie chart can only contain one.
Radar
A radar chart can compare the total of values from a multitude of related data series. Each category has its own value axis stemming from a centre point. Lines are then used as connectors for all values in the same series.
Surface
A surface chart can be used to find the best combination of two sets of data. Colours and/or patterns are often used to show areas that are from the same range of values.
Bubble
A bubble chart is a type of XY (scatter) chart and pie chart. The size of the data marker (bubble) is used to show a proportion, while its position can show another factor. As with an XY (scatter) chart the data for a bubble chart should have it’s X values set up in the top row or left column and it’s Y values set up in corresponding rows or columns.
Stock
Stock charts are usually used to show stock prices, although they can be used for scientific data as well. Stock charts can show high-low closing and high-low closing and volume. A stock chart is highly dependent on the layout of it’s data. It is best to consult Excels help file before setting up stock data you wish to show in a stock chart.
Cone, Cylinder and Pyramid
These types of charts are exactly the same as bar and column charts in their content. They can add a dramatic effect to bar and column charts.
We would like to state again that the above is only a guide and not a set of hard and fast rules. The most important thing to keep in mind is the best chart for the job is the one that best shows what you want to say. It can be very easy to fall into the trap of basing your ‘Chart type’ decision on the cosmetics of various chart types. Try not to let this influence your decision or at least make it a low priority in the decision process. You can apply an endless amount of formatting to your chart at any time. You can also change the chart type at any time by right clicking on the outer chart area and selecting Chart Type. In short what we are saying is never lose sight of the reason for creating a chart - they are supposed to supply us with visual information not visual pleasure!
Creating a Chart
We will create a basic chart for a pet shop showing their sales for the first four months of the year. The data will be as follows:
In cell A1 type the heading Animals and on rows 2 – 5 you have the animals, dogs, cats, rabbits, birds
In cell B1 you have the heading January, in B2 type 20, B3 type 15, B4 type 17, B5 type 21
In cell C1 type the heading February. In C2 type 25, C3 type 12, C4 type 22, C5 type 19
In cell D1 type the heading March. In D2 type 9, D3 type 8, D4 type 10, D5 type 8
Lastly, in cell E1 type the heading April. In E2 type 12, E3 type 10, E4 type 13, E5 type 14.
The first thing we will do is highlight the range that we wish to chart. To do this, select the range A1:E5 with your mouse.
Now go to the Insert, Recommended Charts. Click on any chart type and you will see the available. Right click on the chart for additional chart options.
Embedded versus Chart Sheet
Whether you place your chart on a standard worksheet (embedded) or on a chart sheet is purely optional.
If you will be doing frequent printing of your chart(s) then you may want to lean towards a chart sheet. This way Excel will default to the most likely settings for a chart whenever you select Print. However having said this you can get the same effect by simply selecting your chart before selecting Print, this way Excel will know that it is a chart you want to print and default to the appropriate settings.
At the end of the day though it really makes no dramatic difference so use the option that you are most comfortable with.
Summary
So in summary, charts can be a very effective way to demonstrate and/or highlight a particular point. The purpose of a chart is to stress a point and not have attention drawn away from the point they should show by the over-use of formatting. In short, decide on the chart type that would best demonstrate your point, create it, then apply a sensible amount of formatting. Remember, the trick is being able to paint a true informative picture.
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets and Index to Excel VBA Level 1 Free Lessons and
Index to Excel Level 2 Lessons and Index to COVID-19 Charting examples