How to use charts and graphs in Microsoft Excel

Often data is best represented in graphical format, so it’s great to know that Microsoft Excel provides numerous options. Charts and graphs are usually placed at the top of your spreadsheet grid where they are inserted automatically. In the interests of displaying everything you need, they may need moving or resizing.

Excel offers so many charting possibilities that it is easy to be confused. Here are the basic chart options and what they are best used for:

•    Column Charts are used to compare values across categories by splitting each column up.
•    Line charts are generally used to display trends over time, such as how politicians fare in the run up to an election. Use these carefully as points will be spaced equally on the horizontal axis.
•    Pie charts are used when you have a single type of number data split into categories. There must be no negative numbers and they must add up to something meaningful.
•    Bar Charts are simple charts used to compare different values. Pie Charts and Bar Charts can often be used on the same data.
•    Scatter Charts – So-called because the data often looks as though it has been scattered, the Scatter Chart is used to compare two (possibly related) sets of data.
•    Area & Other Charts – These types tend to be used less frequently, but worth mentioning is the ‘radar’ chart which is popular in benchmarking performance of entities with several characteristics.

The problem with pie charts

Pie charts come in for a fair amount of criticism, yet they are probably one of the most frequently used types of chart. The main criticism is the difficulty of judging the values that the slices represent. If you want to compare the ‘parts of a whole’ to each other, then a column chart does a better and clearer job.

However it would be harsh to claim that pie charts are never useful. For example, if you want to see the proportion of each part to the whole, then the pie chart does a decent job.

3D charts
No, you won’t need 3D glasses to see them! However you should be aware that using 3D charts often makes it more difficult to work out the values represented. For instance, it’s easy for the perspective to give the misleading impression that some figures are higher than what they actually are.

3D charts: not always easy to understand data

In addition, when they are used to cope with an extra dimension of data, perspective is again an issue, and it can be hard to avoid obscuring some of the data.

AAT members have free access to similar Excel tips through their MyAAT account. Excel with Business online course is designed to teach the features of Microsoft Excel that are useful to AAT members, saving time and offering a tailored learning experience.

Interested in learning more about the powers of Excel? Read about the use of formulas to maximise data and work out simple sums.

Paolo Lenotti is the Head of Marketing & PR at Filtered.com.