Charts are one of the most powerful visual features of spreadsheet software like Microsoft Excel 2010, if you have never used Excel 2010 to create a chart then you are missing out on a key feature of Excel. Get to grips with the basics of charts with this overview guide…
Numbers are extremely useful, they form the basis of a spreadsheet so you always need them but charts add an extra dimension to a spreadsheet as they provide you with a visual representation of the data.
The ability to see the data plotted neatly on a chart helps you to identify trends and patterns in the data which you might miss if you are just scanning a series of values on a sheet, in addition it also spices up what can sometimes be a rather boring looking report, unless you are a hardcore numbers person…and those people do exist!
When to Chart?
Adding a chart for the sake of it is a bad habit to get into for any Excel analyst. Excel has a vast array of features all at your fingertips but knowing when best to use them is where your experience as an Excel user comes into play. You can build that experience over time but it also requires a degree of common sense on when and when to not add a chart.
Charts take up a lot of real-estate on a spreadsheet; real-estate is another way of saying available space in your report. With Excel reporting, and in particular Excel dashboarding, real-estate is always at a premium because your aim should be to avoid having your users scrolling up and down sheets and across lots of sheets. You want to get into the habit of where possible keeping everything in front of the user so they can see the important information easily. Remember not every end-user is going to be an Excel whizz, some don’t even know about the scroll bars!
With that in mind be careful on when it is worth adding a chart, consider only those occasions when a chart brings something more to the report or makes it easier for your end-user to understand something, for example if you want your users to see that sales have increased over time then a chart is a great way to do that.
Different types of chart and when to use them
Excel 2010 has over 60 variations of charts available along with a variety of formatting options that make each one heavily customisable. Charts in Excel fall into 11 main categories of chart type:
The column chart is one of the most commonly used charts in Excel. Data that is arranged in columns, or rows, can be plotted on a column chart and typically the data is shown with categories along the horizontal axis (such as date) and the values (such as sales) plotted along the vertical axis. You will find a lot of uses for this type of chart in a business environment where tracking something, like sales, over time is a constant requirement of the business.
Bar charts are similar to column charts, so similar in fact that the term becomes almost interchangeable with most people, but in Excel you will notice a key difference with how a bar chart is displayed. The bar chart is presented on a horizontal basis (Left to right) whereas a column chart takes a vertical format (up and down), this works better for some data although a lot is in the eye of the beholder.
Another commonly used chart, line charts, are useful for presenting trends over time. For example sales by date would be a good fit for a line chart. You can present a line chart with data markers or as a smooth line depending on how you feel the end-user would need to interpret the chart and you can have multiple lines representing different categories, for example showing multiple years of sales by month. Just be careful not to display too many categories on a line chart as they can become difficult to read if you have something like ten lines all criss-crossing each other.
Pie charts are something everyone remembers from math class at school; slices of pie or pizza were mainly used by my maths teachers to explain them to bored teenagers! They are particularly useful when you need to display categories as a percentage or total of the whole, for example if you want to show the sales each business area has contributed to the total amount of sales then a pie chart is perfect for that.
XY Scatter Chart
Scatter charts have two value axis and are used when you have two sets of numerical data that need to be plotted on a chart, for example ages and heights. Scatter charts are often used in statistical scenarios when you might want to see if there are correlations between the two sets of values.
Additional chart types
These are the additional chart types that are available in Excel but less commonly used. In many cases they are used for specialist data or as a way of displaying data in a more fanciful way than the above charts. Take care if you plan on using them for the latter though as many end-users will not be familiar with them so might have trouble interpreting the results correctly. As always your aim should be to make life easy for the end-user and not try to wow them with something just because it looks cool to you.
An area chart in very similar to a line chart with the main exception being that the area underneath the line will be shaded in using colour. This does add an extra dimension to your charts in terms of presentation as it can help distinguish the variances between the lines more clearly however in my experience of them you need to be careful when implementing as you can end up with lots of shaded areas that get hidden because the one at the front takes up most of the view. The 3D version of this chart is almost always the one used to get around that problem but again this can have its flaws with overlapping data.
Stock charts are mainly used to display the movement of stock prices, I know that’s obvious but there are other uses in the science or statistical industry where they come in handy. It is important that your data is arranged in the correct way when using a stock chart option as you have a variety of values that are needed, such as the opening and closing price along with the days high and low values for the stock.
Surface charts look cool, they are Excel’s only truly 3-dimensional chart whereby data is plotted across 3 points. Generally scientists and engineers will be the main user group for these type of charts as they are used to chart combinations such as rainfall, depending on longitude and latitude, or tensile strengths of components depending on length and diameter…if that doesn’t mean a lot to you then the chances are you won’t have a need for this chart despite how interesting they look.
The doughnut chart has a similar format to a pie chart, except it looks more like a doughnut than a pie…this sounds like it is turning into a foodie blog and sure is making me hungry! The main difference with a doughnut chart is that you can display multiple data series, such as different years of sales splits on the same chart. This can quickly get confusing so in my experience you rarely come across them and adding labels etc. while helping to understand the chart can clutter up the visuals so custom formatting is usually a must if you intend to use this chart.
The bubble chart is used when you have three distinct values to plot. It works similar to a scatter chart with the exception that a scatter chart only relates to two values and in the case of a bubble chart the data points are represented by bubbles. One example of its use is if you wanted to plot sales value by staff members, along with % of total sales then you would use a bubble chart. The bubble is placed as a position on the X axis (value 1) and the Y axis (value 2) and the size of the bubble relates to value 3.
Radar charts, also known as star or spider charts due to their appearance plots the values of your categories along an axis that starts in the centre of your chart and extends to the outer ring, for example sales would start at zero in the centre of the ring and extend to 5,000 on the outside ring. The data is plotted around the circumference of the ring by its series, i.e. months. Again this chart falls into the one where there are certain occasions where this might be useful but in general more often than not your users will find this sort of chart hard to read.
Considerations when Charting
Along with the main consideration of do you even need a chart in the first place you should also consider the following when adding a chart:
- Does the chart type best display what I am trying to show?
Ask yourself are you trying to show a trend, in which case a bar or line chart is usually the best option, a split in which case a pie chart may be the way to go or correlation where a Scatter chart is the one to opt for. Save the fancy charts for when you really have no alternative.
- Where should I position the chart?
It’s commonly known that end-users are drawn to the top right of a webpage and Excel dashboards work no differently. Look to place a chart in a key position on your report so that the eyes are drawn to it.
- Have I selected the best colour style to use?
Excel makes it very simple to change colours and styles in all types of charts and it is very easy for the analyst to get carried away and go a bit crazy with the style here. In the majority of business situations there may be a standard to adhere to but when not then basic is generally best. It’s all very well having a shaded 3d looking bar with yellow into orange into green but ask yourself if the end-user will be looking at the chart for what it tells them or just because the colour looks intriguing!
- What formatting extras are needed to explain the data?
Again Excel makes it very easy to format your chart, adding titles, legends, values are all common options that you will play around with but always consider what you actually need. The main focus should be on the chart itself but make sure that if anything is not obvious in the chart then use formatting to explain it.
Everyone who uses Excel should have a basic grasp of charts and be able to create one, otherwise you are missing out on one of the main features of Excel. The best analysts work out when and where they are best deployed, and what type of chart should be used.
Take extra care when using some of the non-standard charts. Most people will have come across bar, column, line and pie charts but the rest are not always that familiar to end-users. Remember that they may look good to you but they must achieve the objective of telling a story to the end-user and if your users struggle to interpret them it won’t matter how cool they look they will be ignored, or even worse misinterpreted.
Always remember that formatting is critical in Excel, this applies even more so with charts than solely numeric worksheets. Put yourself in the shoes or better yet the eyes of your end-users and ask yourself whether the chart is clearly interpretable or requires some formatting to make it so.
Try not to get carried away with the variety of colours and chart backgrounds that are available to you, the aim is to present the data visually but clearly, not to blind the end-user with an array of bold colours on a fluorescent background!
Hopefully with this knowledge in hand you should now have a better grasp of Excel charts along with an understanding of their uses and power in Excel reporting and dashboarding. The next stage is to get into actually creating some charts yourself and gaining an understanding of the various options so that will be covered in the next post.