I think charts are one of the nicer things about Excel, because you have have an awful lot of fun presenting your data in a graphic way. Just like anything in maths, there are some basic rules that all charts should obey; and as well as various tricks to getting Excel to do what we’d like it to, we also have to bear in mind that in the exam you’ll be following very specific rules about what your charts should have, do and look like – so I’ll try to balance all three of these aspects.
What should charts actually do?
This seems like a silly thing to ask, but in fact a lot of people don’t actually consider why they are including a chart in the first place and almost use them as a space-filler. In fact, they can be an incredibly powerful tool to show the patterns in the data. So I think it’s worth going over the basics:
The different chart types:
If you need a reminder, there are plenty of online guides to help you choose the right type. There’s a brilliant one here, for example, which graphically divides charts into four basic types (distribution, comparison, relationship and composition) – well worth a look.
Don’t get stuck in a ‘my favourite chart’ kind of rut. Every time you have to create one, think critically about who it’s for, what you need to display and why. Be prepared to try out a few different versions – you might surprise yourself by detecting a pattern you’d no idea about, and if you can translate that into real business insights your managers will be sorely impressed.
All charts should have:
- A title. This should explain what the chart is telling you.
- A means of identifying the bars/slices/dots/lines. Don’t assume it’s obvious.
- What the different axes represent. Are we talking how many chickens crossed the road here, or something a little more useful?
- An idea of scale. Are your numbers tonnes or milligrams? Percentages? Are we looking at pounds or thousands of pounds? Is this data over a week or over a decade?
- A source for the data. This is only really applicable if the chart is being used in a report outside of its originating spreadsheet but people should still be able to inspect the raw data should they wish.
Setting up Excel to make your chart
All these features and instructions are covered in this PowerPoint file – please remember to download it to view, otherwise it won’t animate.
Firstly, and most fundamentally, ensure that all your data is contiguous: that is, all in a block with no gaps or rows/columns missing.
Aside from the gross insult to data integrity by having blank rows and columns willy-nilly, you’ll make Excel get ever so confused and you’ll have to work harder to get it to do what you want it to. So just save yourself the faff and get into the habit of never having blank rows and columns.
What data is going into your chart?
You have a choice: all of your data, or some of it. In the above example, I could include both years if I were making a bar/column; but I would not be able to use a pie chart unless I decided to show just the 2016 data or just the 2017 data.
- To select all: click somewhere in the data. That’s it, no selecting, no dragging – just click somewhere in your data.
- To select a portion: click and drag over the first range, then hold the Ctrl key down and click/drag over the other range(s). DO NOT ‘CLICK OUT’.
It’s worth noting that if you need to select only a portion of the data, the ‘height’ and ‘width’ have to be the same for each section you pick. Examine these examples below:
Can you see that in the top image, A1 hasn’t been selected? That means that the ‘height’ of the two ranges isn’t the same. In column A, it’s only four rows, but in Column B it’s five rows. Make sure they’re the same, even if A1 is completely blank.
In these two images below, much the same thing applies. Because A1 hasn’t been selected, the first range (B1:C1) is only two columns wide whereas the second range (A3:C4) is three columns wide.
In the second image, A1 has been selected, which means the two ranges are now the same width.
If you ever forget and you end up with a weird chart, it’s probably because of this! But don’t worry, you can change the data once you’ve created the chart – we’ll look at how shortly.
Inserting the actual chart
The next decision you have to make is what type of chart you want. There are a few different ways you can actually insert a chart:
Via mouse:
- click the Insert tab, then click either Recommended Charts to see suggestions Excel is making OR click directly on the type of chart you’d like. Move your mouse over the suggestions to see a version of your chart.
Via keyboard shortcut:
- press F11 – this will insert a column style chart on a completely separate tab
- press Alt+F1 – this will insert a column style chart in the same tab.
If you accidentally insert the wrong type OR you have it in the wrong location, don’t worry – you can change this.
Changing the type and/or location
If you realise you actually prefer a different type or location, make sure you’re actually ‘in’ your chart, that is, you’ve clicked in it. You get an additional pair of tabs become available when you’re in the chart which disappear when you’re not in it, so if you can’t see them, double-check!
Click on the Design tab if that doesn’t show, and inspect the final four options:
- Switch Row/Column means Excel will change from, say, presenting each year as a separate bar to each country as a separate bar. This can often throw up surprising outcomes, so don’t forget to have a peep at this option and see what you get.
- Select Data obviously allows you to go back to your raw data and reselect it if you need to add or exclude anything.
- Change Chart Type is self-explanatory. Again, you get a preview of what your data would look like rendered as that chart, so it’s a really useful feature.
- Finally, Move Chart allows you to switch from having your chart in its own worksheet (and you can even choose what name to give the worksheet – see where it says Chart1 in the dialog box below?) or as an ‘object’ (moveable chart) in the same tab as your source data.
All happy with the location and type? Let’s dive into making it look better!
Formatting your chart
Firstly, you will need to add a chart title, and, if you have a bar/column/line chart, axis titles as well.
Remember to click somewhere in your chart if you haven’t already. From the Design tab, click on the very first option in the ribbon: Add Chart Element. Here you can see the options for Chart Title and Axis Titles (Primary Horizontal / Primary Vertical).
Choose whichever option, type away, and press Enter when done to confirm your typing.
Help! Is the x-axis vertical or horizontal?
Should the exam ask for an x or a y axis title and you can’t remember which is which, use a bit of Mark 1 Brain. In our example here, which of the two axes is going to have a title of ‘Sales in £000s’? It’s not going to be the horizontal one, is it?! So don’t worry so much if you can’t remember. Use the text to guide you.
If you’re curious, though, the horizontal axis is always the x axis.
Editing the titles
Click once, then once again. This is not the same as a double-click! Use your backspace/delete as required and correct your title. This time, do NOT press Enter to confirm, or you’ll put in a line break! Just press the Esc key. Editing the vertical axis can be a bit funny as you’re working sideways.
Changing the colours
Essentially, it’s as simple as pointing to whatever you want to change, right-clicking, then choosing a colour from the little pop-up with Fill and Outline. However, you might come a cropper if you do this in all circumstances!
Have a look at the two images below. They both show charts formed from the same data; the column chart is from all of the data, whereas the pie is from the first column only (Year 1).
As you can see, I clicked on the very first bar of my column chart. Can you see that every UK bar has a blob around it? This is called a Data Series, and refers to an entire row in the originating data. A single cell within that data series is called a Data Point, as you can see from the panel that appears when I point to one of the UK bars.
Now, let’s have a look at a pie equivalent. Obviously I can only do one column or row at a time in a pie, so here I’m looking at Year 1. When I click on it, can you see that every single slice has a blob on its corner?
In just the same way as the column chart, an entire column of data is known as a Data Series, and an individual cell is a Data Point.
When you first click on an element of your chart like this, you get the whole Data Series. If I were to right-click at this point and change the colour, every bar would change colour. This is great for the column chart… but not for the pie chart, because every slice would change to the same colour! Uh-oh!
So here is A Really Important Thing To Know!
- IN A COLUMN CHART, SELECT THE DATA SERIES before changing the colour. You need to make sure EVERY COLUMN has blobs on it! Click once, then right-click to access the Fill and Outline pop-up.
- IN A PIE CHART, SELECT A DATA POINT before changing the colour. You need to make sure JUST THAT SLICE has blobs on it! Click once, then ONCE AGAIN to select the individual slice (this is NOT the same as a double-click!). Now you can right-click and change the colour!
Changing an individual bar in a bar/column chart
Essentially, what you need to do is to select the Data Point, rather than the Data Series. Just as you would for a pie, click once then once again. You will see first that every single bar has a blob on its corners, then when you click again you will see only your one bar has blobs on it. Now you’re good to go with the right-clicking.
Extra chart features
Pretty much everything aside from the above is controlled via right-click, Format. As you can see from the images here, the Format option changes depending on what you right-click on, but is always at the very bottom of the menu.
Whichever you pick will open a Task Pane down the right hand side of the screen with various options you can access from it. You’ll find three or four symbols at the top of each Pane which give you options relating to the appearance (Fill/Line, Effects, Alignment) and an option specifically relating to aspects of the chart you’ve chosen, such as how to change the numbers on the axes, how far apart the bars are and so on.
Having numbers next to the slices/bars
You may want to show the actual numbers relating to each slice or bar – or even, in the case of a pie chart, show percentages instead of numbers. These are called Data Labels.
In order to add them, right click and select Data Labels. Once you have them on your chart, right click on any one of them and select Format Data Labels, whereupon you will have the following Task Pane which allows you to include things like the Category Name (in this case, the country names) or percentages – or several at once. Obviously if I chose to show the Category Name, I could dispense with having a Legend (key) altogether.
Bear in mind that the percentage option isn’t available in bar/column charts, and sometimes the numbers are so tight up against each other it’s hard to read them. But does your chart really need to go into that much detail anyway?
Changing the values on the y-axis
Looking at our countries data above, you can see that the lowest value is 425 and the highest is 720. That means all the bars end at more or less the same level, and therefore there’s not much detail to be had. However, you can reset the lower limit so instead of starting at zero it starts at, say, 400. Compare these two charts where you can see the difference starting at 400 has made. The charts are the same height (i.e. the same actual dimensions).
To access this, right-click on the vertical axis and choose Format axis, then from the Axis options in the Task Pane, reset the Minimum and Maximum bounds.
Exploding a pie chart
What? Is this really a thing? Oh yes indeed, and what a fun thing it sounds! Before you go getting visions of yourself indulging in your favourite food in pie form, I’m sorry to inform you that it’s actually just a pie chart where one or several of the slices have been pulled out from the centre.
There’s no secret to it. All you need to do is click and drag from the centre outwards, and the same in reverse to bring all the slices back together again.
Setting a trend line
I noticed that one of the previous mocks which don’t seem to be available any longer asked for a trend line across a bar chart.
In order to add one, think quite carefully about exactly which Data Series you would like your trend line to apply to – if you have one for every single Data Series it could get a bit cluttered and hard to read.
Having decided, right-click on any one of the points in the series and choose Add trend line. Simple!
Remember, all the examples above are animated in the PowerPoint file you can download here.
Practising
To be honest, you can play around with pretty much any data and learn about charts from there, but if you would like to use the example shown here, click here to download it. Try out the different ways to insert a chart, then just simply right-click here and there on what you’ve created and see what you can work out just from seeing where the whim takes you! Have fun!
If you’re following the suggested Learning Plan, please click here to go to the next post.
next to each bold line. Suddenly your data analysis is becoming really powerful!
But you’ll also have noticed these bar thingies going on down the side. They allow you to view your data in different ways – so you can ‘drill down’ very quickly to a specific region and just display that.
and it will fold your data up, displaying only the row with the Subtotal on (and it’ll turn into a 
), or click one of the numbers at the top to fold up everything at once.
it just means that your cell isn’t wide enough to display all the numbers correctly (and remember, by ‘numbers’ I also mean dates and times). Just widen the column.
Now you need to look for Paste Values. Each version of Excel has a slightly different Paste Special layout, so the image here is a guide only – pause over each option and you’ll see a small panel appear which tells you what type of special pasting option it is, browse around until you find the button with a clipboard with 123 on it then click it. This will then transfer the results of your TRIM without the underlying function behind it.
Excel for Accounting Students 