Tips and tricks relating to Excel Pie Charts including the Pie of Pie and Bar of Pie charts
A Pie Charts is a good way to represent certain types of data. Let’s take this data, which shows some numbers and the amount spent on the calls in February. I go to Insert and under Pie you’ll see there are a whole bunch of Pie Charts. We’ll get on to a few of them just now but let’s just do a standard 2D-Pie. When I click on it, you get the following chart. What this chart is showing is which phone calls are being made or which is the most expensive phone number – the number we call most often. Remember you can customize what it looks like with the Chart Layout so let’s look in here and perhaps you can find something that looks like what you want – so maybe that one – click into it – perhaps that is not quite right – maybe that one. You can see what it has done – in this case it has put the percentages within the series. If you need to customize that you can click and in this case on the data labels – right click – go to Format Data Labels and decide what you want to show, so perhaps you want to show some other items – where you want to put it. The majority of these items are fairly standard. What is different with the Pie Chart. I am just going to right click on the series and I say Format Data Series. You’ll see the Fill, Border Colours, etc – these are all standard. But the series options for each chart type will be slightly different. So in this case you’ll see they talk about the angle of the first slice. The first slice is this item here and you’ll see it starts at the very top pointing upwards. I can say let’s rotate it a bit. So I am going to click on here and you’ll see as I move it along the rotation changes. So that first slice now perhaps we want it to appear over there, at 900. So you can customize where the slices should appear.
You can also see we have an option relating to the pie explosion. At the moment it is set as “Together”. When I click on it and maybe move it along, what it does, is it explodes outwards. You can go to the absolute extreme and we can put it all back together so we have got a single slice.
Because of the nature of Pie Charts – in this case you don’t have an option to split the axes. I am just closing it. Be aware as well that you can click on a particular series, so we will click on all the series. I click again and only that series is highlighted. You can click and drag that particular one out – you can explode the chart manually. So perhaps these two items you want to expand on. Let’s undo those two.
Let’s look at some of the other Pie Charts available. So I am going to go to Change Chart Type. And with the Pie you’ll see we have got a 3D version, and then we have got some interesting ones. There is something called a Pie of Pie – There is Exploded Pie, and there is this other one called Bar of Pie. The Bar of Pie and Pie of Pie are very similar, I am going to choose this one. Say OK. What you’ll see now is we have a Pie chart, but what it has done is taken some of the items out of the list, put them into a common area and then expanded them in a Bar Chart. Now if we right click on the Series – so I go to Series and I say Format Data Series, you’ll see I have a number of new options. The first thing it says – Series Options – it says Split the Series by Position and the Second plot contains the last four values. What you’ll see is because of the way the data is set up the main Pie will contain these numbers. And the last four items have been combined into this segment and split up over here. You can change that so let’s say I want it to contain only the last three, so watch what is happening there when I change that to three and you’ll see the chart changes. I am just going to change it back to four. You’ll see that you don’t have to use the Position, if we click here we can tell it to do it by Value. Now in this case the values are going to be the same because the Value is in ascending order and you’ll see here the option here is Second plot contains all values less than:- and you can now decide, so let’s say we want all values less than 50, for example. When we do that any number, or any value less than 50 – in this case coincidentally the last four will be extracted. Maybe just do values less than 100. So you’ll see there is only one value more than 100, all the rest are now shown in the bar chart. Let’s go back to 50.
Let’s see what else we can do. There is an item here called Percentage Value- the same basics. It looks at the percentage and decides what belongs where and we can say the Second Plot contains all Values less than 10%, depending what the percentage is, the numbers will be pushed backwards and forwards through here.
The last option to Split the Series by, is something called Custom and it will tell you to “Select a Data point to move between plots”. I am going to say Close. Let’s say, based on this, we actually want this item to be back in the main area. I click once, you’ll see we are now in the data point and I right click – I say Format Data Point and you’ll see it now says it has split series by Custom – where do you want to set this point to belong – at the moment it is belonging to the Second Plot and you can say, no, please move that to the First Plot, and you’ll see it is pulled out of the Bar and into the Pie. Perhaps this bigger one, you actually want to move out of the Pie and into the Bar – same logic – click on the Data Point, so it only activates that data point, right click say Format Data Point. In this case the point is going to belong to- I am going to put it into the Second Plot and you’ll see the chart adjusts itself.
Just getting back to the overall formatting, if I click on the Series, right click and say Format Data Series – there are some options lower down – so you’ll see we can, again, control the pie explosion and as we move the pie explodes or doesn’t. The new option here is something called Gap Width, which is the width between the pie and the bar. At the moment it is set at 150, if I move it, you’ll see the gap increases – or go to the extreme – let’s make it zero- you’ll see they are basically on top of each other. Go back to Format Data Series – Gap width – let’s just make it a bit more reasonable again at 150.
You can also decide what the Second Plot Size must be- at the moment it is set at small. We are going to click on it and just move it along and you’ll see we can make it grow. Perhaps you want to emphasize that particular plot – you can see you can keep going to a fairly significant amount.
So you can control these pie charts to the nth degree.
Occasionally when you have a spreadsheet that will be used for presentation you may want to neaten it up by either showing zeros in Excel or not showing the zero in cells that have a zero in them. We typically … Continue reading
If I understand the query below correctly, the one idea is to type some words in a cell, and then, while holding the ALT key down, click ENTER. Within the cell you will get another line. Hope this helps. Original query Hi, … Continue reading
AuditExcel (aka Miricle Solutions) provided the Sasol Chlor Vinyls financial team with professional excel training whilst fully understanding the accounting landscape which is highly recommend.– Sasol Polymers