Learn Excel 2010 Advanced - Bitly

Learn Excel 2010 Advanced

Table of Contents Chapter 1 – Basic Steps for Charts & Graphs Introduction to Advanced Excel 2010 .....................................................................4 Four Basic Steps When Creating Charts/Graphs .....................................................5 Additional Features to Help Create Charts/Graphs .................................................8 Additional Features to Help Create Charts/Graphs Part 2 .....................................12 Chapter 2 – Layout Tab Detailed Formatting for Charts/Graphs .................................................................16 Formatting Legends within Charts/Graphs ............................................................20 Formatting Axes and Gridlines within Charts/Graphs...........................................25 Chapter 3 – Trends Trends within Charts and Graphs ..........................................................................30 Complex Trends within Charts and Graphs ...........................................................34 Trends over Time within Charts and Graphs .........................................................39 Chapter 4 – Differences Showing Differences Using Bar Charts .................................................................43 Showing Differences Using Pie Charts..................................................................48 Limitations with Pie Charts & How to Correct .....................................................53 Alternative Ways to Show Differences..................................................................58 Chapter 5 – Relationships Using Charts & Graphs to Show Relationships .....................................................62

Using Charts & Graphs to Show Relationships Parts 2 .........................................66 Chapter 6 – Stock Charting & Graphing Financial Information .........................................................71 Charting & Graphing Financial Information Part 2 ...............................................76 Specific Charts for Finance Information................................................................79 Setting Up Live Charts...........................................................................................83 Chapter 7 – Sparklines Using Sparklines for Data Visualization................................................................87 Chapter 8 – Data Bars Using and Formatting Data Bars, Color Scales & Icon Sets .................................91 Chapter 9 – Pivot Tables Setting Up Pivot Tables/Charts ..............................................................................96 Setting Up Pivot Tables/Charts Part 2 .................................................................101 Filtering Pivot Tables/Charts ...............................................................................105 New Features for Pivot Tables/Charts .................................................................109 Chapter 10 – Graphics Graphics Tools .....................................................................................................113 Graphics Tools Part 2...........................................................................................117 Chapter 11 – Exporting Exporting Charts and Graphs ...............................................................................121 Exporting Charts and Graphs Part 2 ....................................................................126

Learn Excel 2010 – Advanced

Chapter 1 – Basic Steps for Charts & Graphs Video: Introduction to Advanced Excel 2010 Toby: Hello, my name is Toby. Welcome to this advanced level course on Excel Graphs and Charts. During this course, I’m going to be taking you through the very much extended features of Microsoft Excel 2010 and showing you how to create great graphs and charts. The Excel graphing and charting engine was completely re-written in Excel 2007and if you’re used to Excel 2007, you’ll find that 2010 is very similar in the way that it operates; although there are some significant additions, such as the introduction of Sparklines. If you’re used to an earlier version of Excel, 2003 or earlier, then the changes are really quite significant. Apart from the changes associated with the introduction of the Ribbon Interface the graph types, the way they work, and so on have all significantly changed. So, you’re going to need to allow yourself quite a bit of time to get used to the new graphing and charting in Excel 2010. Now, before we start, I need to point out two or three things to you. First of all, I’m going to assume on this course that you are familiar with the use of Excel in general. If you are used to Excel 2003 or earlier and you haven’t used either Excel 2007 or Excel 2010, then getting used to the Ribbon Interface and so on does take a little bit of time. So you may want to just have a little bit of practice on that before you follow this course in detail. Having said that, I will explain in detail what I’m doing as I go along and where I think particular aspects of the use of Excel may present you with problems, if you’re not a particularly advanced user, I will try to explain it in a little bit more detail. The next thing to point out is that I created this course so that the best way to follow it is to start at the beginning and work through in sequence. It is a modular course, but it’s a good idea to follow the modules in the sequence in which I’ve created them. Having said that, the approach I’ve taken is not just a work through each of the different types of graph and chart and explain how to create each one. I’ve looked at it very much from a functional point of view and said if I want a graph or chart to achieve this particular affect or to explain this particular concept what’s the best type of graph or chart to create and how do I go about creating it. And the last thing to point out is that while we’re primarily dealing with graphs and charts on this course, I’ll also be looking at some of the associated techniques that can really help in your representation of data. These include particular graphic techniques, data visualization and some conditional formatting and so on. So, I think it’s time we got started.

© Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


Video: Four Basic Steps When Creating Charts/Graphs Toby: So, we’re first of all going to create a straight forward chart in Excel 2010. And I’m going to use this first example as a way of demonstrating the basic sequence of steps with all the types of graph or chart that we’re going to create. Now there will be some variation from this sequence and there actually sometimes a couple of steps to add on the end, but basically it is a four-step process. The first step is to select the data that we’re going to use. The second step is to choose the graph or chart. Step three is to choose a layout and design for the graph or chart. And the fourth step is to customize that accordingly. Now, as I said just now, we may need to one or two additional points but basically we’re always going to follow this four-step process. So, now let’s work through those four basic steps with a very straight forward example of a simple chart. Now, I have a workbook here relating to the cost of building a house. The worksheet on it is called Earned Value and I’m going to select some of the financial information and create a chart from it. I select the data in this case in the way that they will usually select it, that is by dragging with the mouse and selecting the area that I want to use. Now, within this area I have the financial information, the numbers here and also what are basically the Variable Values relating to the two axes on the chart that I’m going to draw. I’ll explain that more a little bit later on. With my data selected, I now want to actually insert the chart. So, go to the Insert tab, click on Insert, and there is a Charts group on the Ribbon. There will normally be seven icons in there and you may see these seven icons in a row or squashed up together like mine because of the resolution on the screen that I’m using. Just choose the first one for the moment which is the Column Chart and then choose 3-D Column. We’re going for the first option. Click on that and there we are. We straight away and in a very simple and straight forward way have a chart representing this data. Now, one very important thing to appreciate early on is that Excel 2010 will make a good stab at guessing what each of the values in your selected data represent. And it has a set of rules that it follows by default to interpret your data. And that’s exactly what it’s done in this case. Now, going back to the question I raised earlier on of what all these things mean, let me just talk about them now a little bit. The categorization here in terms of cost, these are dollar values or pound values, it doesn’t matter which here, the numbers. And without understanding at all what these values actually mean, Excel 2010 has used those on the horizontal axis of our chart. Now, in fact, ACWP, for example, stands for Actual Cost of Work Performed. But Excel doesn’t really need to know what it means in order to do a chart on this basis. Similarly, it’s used the other labels here: Project, Prework, and Prepare and used those as basically the dividers, the components in which the costs are split to create our blocks. Blue for Project, red for Prework, green for Prepare. Again, it doesn’t really need to have any idea of what these terms mean. All © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


its done is to use those to create the individual blocks and the numbers in the body of the selected data are plotted against this vertical scale. We put zero to 6,000. Excel 2010 has no idea what these numbers are, they could be degrees Fahrenheit, they could be dollars, they could be the number of people that are living in a certain town. They could be anything. It just knows that they are numbers and it just up to us to put the interpretation on what each of these things represents. Now, the third step in this process is to actually change the layout or the design of the chart that we’ve created. And in this case, once you’ve created a chart and the chart itself is selected a new set of tabs appears on the Ribbon under the overall heading Chart Tools and we have Design, Layout, and Format tabs within that. Let’s start with the Design tab here. Now, on the Design tab we have a number of options and we’re going to look at all of those as part of this course. But let’s start with the Chart Layouts group. And if I click on the control at the bottom here, the More control, I’m given a number of options for the chart layout. Now, this basically determines where things like the Legend appear. If I click on the option here, the first option, I get a title with the Legend on the right. But if I wanted the Legend to stay at the bottom I could click on this option; this is layout three. And clearly it’s straight forward to experiment with the different options. We’ll come back to talk about the Chart Title in a little while. So, as the name implies, Chart Layout deals mainly with the layout of the items within the chart; so position of the title, the Legend, and some basic layout items. And we’re going to look at Chart Layouts in more detail later on. The other major option on this tab is the Chart Styles Group and as you may know with Office 2010 there is an integrated set of themes and styles that crosses the various components of Office 2010, including Excel 2010, but also Word 2010 and Access 2010 and so on. And we can choose from the available standard themes and styles. Now one very quick way, a simple way of doing this is just to choose one of the icons on the Ribbon here. So, the one I’ve got selected is the one that’s in force, if I click on the next one along we can see how the color scheme changes and, of course, apart from color scheme changes we can have built in font and other stylistic changes as well. And as we’ll see later on if we go to the dropdown here, the More gives a whole range of alternative styles that we can use, including some more dramatically different ones. Now, as you can see from this, there are some Tools provided in Excel 2010 that make graphing and charting very straight forward and if you need to quickly make a graph or chart and match it to a particular style, you can produce pretty good graphs and charts very quickly with Excel 2010. If you are new to graphing and charting, one of the mistakes you may make early on is to actually get the variables that you are graphing or charting the wrong way around. Here I saw the breakdown Project, Prework, Prepare and the three different ways of analyzing costs. If I’d wanted those the other way around I could obviously go back here and switch my data table around, but there’s a very straight forward button on the Design tab, Switch Around Column which is sort of provided really for situations where people do get things the wrong way around .



It is very straight forward, just click it once and as you can see it’s interchanged the positions of the two main variables that are used in this chart. So, now we come to the fourth step in the basic creation of a straight forward chart and this is the one where we customize the chart. Now, the Design tab basically gives us the simple straight forward, fairly high level ways of changing a chart or graph. The Layout tab, on the other hand, let’s us go down into much more detail. So, for instance, we could select something like the Chart Title and we could say there’s none or we could say it’s a Centered Overlay Title, which is pretty much what it is right now more or less. Or we can go into more title options and with more title options we have a full set of formatting options for our graph title and we’re going to look at those later on. Other things we can change are Axis Titles, Legend, Data Labels, and so on and we can also insert other objects such as Text Box, Shapes, Pictures; again, more on those later on. But for really detailed customization and formatting of a chart, we have the Format tab where we can go into individual fonts; we can use word art style, shape styles and so on. And actually really go to town on formatting a chart exactly the way that we want it to appear. Some of these actually also allow for live previews, so as I hover over the shape styles here you can see that the Chart Title, the style of it, the border on it is changing and if I go into Word Art Styles. I can get different Word Art Styles on the title as well. So, we have a lot of Preview options as well. So, there we have it. You should now have some idea of the sequence of steps we’re going to follow when we are creating graphs and charts in Excel 2010 and from the next section, we’re going to look at a more complete example in a lot more detail and look at some more of the features of these various Excel 2010 graphing and charting tools. I’ll see you then.



Video: Additional Features to Help Create Charts/Graphs Toby: Hello and welcome back. In the previous section, we looked at a very simple example of a chart in Excel 2010. In this section, we’re going to go through pretty much the same procedure, but this time we’re going to go through it in much more detail and look at some of the additional features that you should be aware of when you’re starting to create graphs and charts in Excel 2010. So, let’s get started. Now, as before, we’re going to choose an adjacent or contiguous set of cells. We’re actually going to plot a chart for this group of cells here. The particular worksheet I’m looking at corresponds to some monthly sales figures for some employees for 3 months of the year. Now, in the body of this data we have the actual sales figures themselves, the numbers themselves. But apart from that we have in the top left hand corner an empty cell and that will quite often be the case that we have an empty cell in the top left hand corner. Along the top we have what we will refer to as the Categories. In this case three months of the year: January, February, and March. And down the left hand column we will have what we call the Series Values. Each of these employees has a Series of monthly sales figures corresponding to the three categories: January, February and March. So, the names Anne Carnegie, Scott Denvers, and so on are our Series Values. The months Jan., Feb., Mar. are our Category Values. Now, with the required data selected, I can go through to Insert a Chart in exactly the same way that I did before, but there’s actually a shortcut way. In earlier versions of Excel, there has always been a shortcut whereby with the F11 key you can create a Default Chart from selected data. That’s actually still available in Excel 2010. But Excel 2010, in addition, offers the option of using Alt plus F1. And the Alt plus F1 keystroke is a time saver, which will enable you to create a chart that corresponds to a default that you can change. First of all, let me press Alt and F1 now and straightaway you can see the Default Chart is created basically with one click. Now, there we have each of our Series Values, that’s each of our employees. We have their sales by Category, the three months of the year that we’ve selected. Let’s see how to change this Default Chart. Let me use the undo command to remove the chart that’s there now. And let me go to Insert tab and if I click on the button in the bottom right of the Charts group. The Insert Chart dialog opens and you can see at the bottom of this dialog, there’s a button Set as Default Charts and in fact, that Column chart, what’s called the Clustered Columns Chart is actually selected at the moment and that’s set as my Default Chart. Let me look at the 3D version of that, 3D Clustered Column, and click on Set as Default Chart for that and then click on OK. Now you can see that my chart has been inserted. You can see what it looks like: the five employees and the Categories, the months. Let me undo the addition of that chart. But let me now do Alt plus F1 and see what happens. And there you can see that with Alt plus F1, my Default Chart type is now seen to have been changed.



Okay, so let me undo that chart as well, the one that I’ve done with the shortcuts and let’s look at the conventional way of inserting a chart of our choice. Click on Insert and as I pointed out previously, in the Charts group we have a choice of basically seven options here: Column, Line, Pie, Bar, Area, Scatter and Other Charts and there are several in each category. There are, in fact, 19 types of Column chart and under Other Charts, if I click on the dropdown next to Other Charts we can see that we have Stock Charts, Surface Charts, Doughnuts, Bubble Charts, Radar and most of these we’re going to look at in detail later on. If you know in advance which of the Categories you want, so for instance if you know you want to do a Pie Chart, you can click on the dropdown next to Pie and you can see the Pie options that are available. All of these dropdowns as well have at the bottom All Chart Types and if we click on All Chart Types, we get basically the same Insert Chart Dialog that we saw before with the full list of all of the available chart types. Let me just Cancel that. As an alternative to going via one of these specific icons, we can use the dialogue box launcher in the bottom right of the Charts group. Click on that. Note the tip there, Create Charts and we can straightaway bring up the Insert Charts Dialog, which has the full list of available graphs and charts in it. Now there are so many graphs and charts here that it may seem really quite confusing, but it’s probably something you shouldn’t worry about too much to begin with. For many of the types of chart we’re going to look at there are only a small number of options, although they may need quite a lot of customization. It’s mainly for the more common types, such as Column charts where we do have a lot of entries. But in fact, the differences between them are quite slight and in many cases with a bit of practice you will be able to hone in on exactly which one you want pretty quickly. So, let’s just look at these options for Column charts. If we take, for instance, the Cylinder options here, we have a Clustered Cylinder option and next to it are three other cylinder options. Now they’re all basically all going to show the same information, but with a different arrangement of the cylinders. The Clustered Cylinder shows the sales figures side-by-side. The Stacked Cylinder shows the sales figures stacked. The 100% Stacked shows the sales figures in each case proportionally divided up to 100% of sales for each of my Series, i.e. my employees. And then here we have the 3-D Cylinder. Let’s just do the first two of those to see what they look like. Let’s start with the Clustered Cylinder. If I do Clustered Cylinder you can see there are my Series, my employees. Categories are obviously the months, the sales figures there. Let me undo that one. Now let me do Insert, Dialog Box Launcher and this time I’m going to do the Stacked Cylinder. Basically it’s the same information arranged in different ways. And it’s very common amongst the Column options on the charts there to have these options grouped together, the straight forward Cylinder, Stacked, 100% and so on. So, they’re not quite as confusing as they might look at first. Now, as we saw in the previous section once you’ve inserted a chart, you enable the Chart Tools group of tabs, Design, Layout, and Format. And on the Design tab at the extreme left there is Change Chart Type. If I click on the Change Chart Type button, I bring up the Change Chart © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


Type dialog, which looks exactly the same as the Insert Chart dialog. The chart type I have at the moment is selected and if I wanted to change the type, which I’m going to do, I’m going to change it to a 3-D Cylinder now. I select 3-D Cylinder, click on OK, and my chart type is changed. Now, I mentioned right at the beginning of this section that the data we chart doesn’t need to be contiguous on the spreadsheet. So, let’s just look at how we deal with noncontiguous data in general terms now. Let me first of all remove the chart that I inserted before, just use the Undo button. And here, what I’m going to do is, I am going to select the same amount of data that I did before. So that’s my five employees, Jan., Feb., Mar. and then all I need to do to include the next three months is to hold the Control key down and select the next three months worth of data. Note that I’m avoiding the Quarter totals that I’d included here. Now, this is generally how to select noncontiguous data in Excel so there’s nothing surprising or new there. And, of course, if I wanted to say try this out with my Default Chart type I could just do Alt-F1 and now you can see that I have a rather crowded looking chart. But it now has for my five employees six months worth of data for each employee. One of the other basic things that you need to know about when you’re working with graphs and charts in Excel 2010 is how to move them around. Now, I’ve created here a straight forward chart with a small amount of data and it’s quite often the case that when the chart appears, it’s not in the right place; you need to move it to see what’s behind it or you want to move it into a report. We’re going to look at actually moving the chart around between worksheets and into other documents later on. But in terms of moving it around on the sheet itself, there’s a few things to be aware of. First of all, the area of the chart where you can actually grab it and move it is a selected part of the chart. For instance, if I click here in this sort of white area, I get the cross-hair which basically tells me that I can move the chart around. I can drag it with the mouse. But if I click say here, within the Legend, I would not be able to move it. I’d be selecting either individual Series names or the whole Legend. And I could actually use that to resize it. I could resize the Legend like that. Or I could edit one of the individual Series names. But I couldn’t move the chart like that. So, it’s important to get the hang of how to actually drag the chart without doing what I did just then, which is to actually drag the chart itself within the whole chart area. Don’t forget if you do accidentally move a part of a chart just use the Undo to put it back where it was. Now, one or two other things to be aware of in terms of moving a chart around on a worksheet, if you do find a part of the chart area where you can drag it, which I’ve got here. Once you get towards the edge of the sheet, dragging can continue but you might have a lot of trouble seeing in relation to the rest of your data, the rest of the content of the sheet where the chart is. Again, let’s just do an Undo. One option, when you’re dealing with a very large amount of data and you want to move a chart into position so that you can either look at the data or position the chart for reporting purposes, is to use the Zoom Slider which is often quite useful with charts. If I Zoom out here, for instance, although I’ve only got a small amount of data here at the moment. If you © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


imagine that with a lot more data, we’ll see more data later on, you can see how much easier it is to position the chart when I’m zoomed out like that. So, take advantage of the Zoom facility. And finally in this section, let’s just look at a bigger move of a chart with the chart selected so that we have it Highlighted. We can see the Highlight box around the outside. Cut it, either using the Cut button there on the Ribbon or by using the Control-X key sequence. Then we can click on any cell and Paste. And there the chart appears based on the cell that we’ve selected. And similarly, if I go to another worksheet in my workbook, select the cell there, C3, do Paste again, and my chart now appears on that new worksheet. So, that’s the basic way of moving a chart between worksheets. So, we’ve looked at quite a few of the most important basics of graphing and charting in Excel 2010 in this section and we’re going to continue on the same theme in the next one, so I’ll see you then.



Video: Additional Features to Help Create Charts/Graphs Part 2 Toby: Hello and welcome back. In the last section we looked at a straightforward way of creating a chart from the data you can see in front of you again here. Let me just do a quick recap. If I select that data, I can either do Insert chart or I can do the shortcut Alt-F1 to get a Default chart and there we are. And just to recap, we have Series of Jan, Feb, and Mar and Categories corresponding to our five sales people. It’s worth noting that when you’ve created the chart, if you right click in the chart area you bring up a Context Menu which offers a number of options; not just Cut and Copy options but also a Move Chart option which gives an alternative way of moving the chart so you can actually specify that it needs to be moved to a selected sheet or you can create a new sheet and say move it to that one. Also on this Context Menu we have options to do 3-D Rotation, Format the Chart Area, change the Font that’s used and so on. And we’ll be looking at some of those Formatting options later on. Now let’s look at the chart itself again. We have these three Series: Jan, Feb, Mar and five Categories. Let me just move the chart slightly out of the way. Let’s do what we did before. I’m going to select the same data area, Jan, Feb, Mar, hold the Control key down and select the next three months, and I’m again now going to do an Alt-F1 to create a Default chart. Now, note what happens because now my Series are no longer Series. My Series are now the five sales people and the Categories are actually the six months of the year. Now, I mentioned earlier on that it’s quite easy in Excel 2010, or in fact earlier versions of Excel, to get the Series and Categories the wrong way around. And one of the reasons for this is that Excel doesn’t actually always use columns for one and rows for the other. It actually works on the principle of which is most numerous when it’s actually taking a stab at creating a chart. And so it’s actually quite common to need to be able to switch rows and columns. Now, in this case, the reason it’s chosen six Categories and five Series is because, generally speaking, whichever is more numerous it treats as its Categories. And if we need to change them we need to change them manually.



So, with this particular chart selected on the Chart Tools Design tab, if I go to Switch Row and Column, if you watch what happens to the chart as I do it we finish up with the six months as the Series and once again the sales people as the Categories. I could, of course, choose the other chart and do the reverse to that chart. So, let’s revert back to the chart with the six months worth of data in it and let’s look at one of the other commands on the Design tab, because this is also a command button we can use to switch columns and rows, but we can also use it to do quite a few other things as well. It’s the Select Data button. Now with the Select Data button I’m just going to move its dialogue up here so we can see a little bit of what’s going on. This actually enables us to switch rows and columns, but for each of the Series, which are currently on this chart, the months, and each of the Category labels, it lets us move them around, change the text and so on. So, let me show you one or two examples of the sort of things you can do with that. First of all, within the Select Data source dialogue, at the top we have Chart Data Range and this actually lists the range that the chart refers to. Now in this case our range is actually made up of two noncontiguous ranges of cells, both on sheet one. The first range is A1 to D6. The second range is F1 to H6. I could, if I wanted to, actually type the range of cells I wanted to use in here. It’s not something I do very often, but there’s no reason that I shouldn’t and this is obviously a way that I could change the range if I wanted to. So, for instance, if I wanted to lose Junes figures, I could change the H in H6 to G6, click on OK and see what happens. And there we are, the June figures have gone. Let’s look at one or two of the other things we can do usefully with the Select Data dialogue. We have on the left, the Legend entries for the Series. As you’ve all ready seen, we can switch around row and column, but we can actually go in and Edit these entries. So, for instance, we could Edit this, change the Series name, and also change the Series values. The actual range that’s specified for Jan is basically from B2 to B6, so it’s this part of the column, you just about see it there. And the Series name is taken as the Content of B1, which is Jan. So if I wanted to put in Jan 2011 as my Series name, click on OK. You can see that Jan 2011 now appears there in the Legend.



And there are in fact other things that I can do here such as if I say didn’t want to include the figures for May, I could just click on Remove and I can also change the sequence if I click here for March I could move March up, if you note at the moment. Look at the Legend; March is the green column in the chart. I can use the up and down arrows here, move March ahead of February and you’ll now see that the green column is second in the list. Obviously it will be a bit nonsensical with month dates, but if in fact my Series values were different from this, then it might be appropriate to change the sequence. I’m just going to move that one back down again and there we are. Click on OK and I’ve made quite a few substantial changes to my chart. So, having used the Select Data dialogue to make some changes, let’s now look at the chart type that we have selected and see in a little bit more detail the choices we have for changing the chart type. Before I do that I’m going to do one other thing, I’m going to select the chart itself and then I’m going to use one of the sizing handles in the corner to make the chart a little bit bigger and as it gets bigger you’ll see that Excel 2010 uses its own intelligence to realign things like the Categories, the Series labels, and so on. I’m going to leave that one saying Jan 2011, that really doesn’t matter. Now, with the chart type that we’ve got, which is a Column chart type, with the chart selected within the Chart Tools group of three tabs, on the Design tab we have an option to choose between the layouts. Now we have scroll buttons here that let us go down the available chart layouts for the Column chart type and, in fact, the More button here we can click to give us access to the whole lot in extended gallery view. Now, in the extended gallery view it’s possible to see how the main elements of the Column chart type are arranged for each of the available options. So, for instance, with option 1 we have a Title at the top, Legend on the right, and otherwise the chart appears pretty much as it does here. Here’s an alternative where the Title is at the top, the Legend is below the Title, and then the chart appears and so on. So, let’s go for option 1. And really all that’s changed there is that we have a Chart Title in place and we’ll see in a little while how we can actually add a Chart Title. It’s very easy then to try any of the other options that we want to try. Some of them involve having Titles on the individual Axes without a Chart Title, some of them involve having these grid lines which make it easier to read heights, depths, etcetera of the chart columns. Now don’t forget, and as we saw before, we could, of course, completely change the Chart Type if we wanted to. We have the button over here on the left in the Type Group on the Design tab © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


where we can basically select from the whole range of available charts, but I’m not going to do that at this stage. Let’s go back to the Layouts gallery again and on the More I’m going to choose this type, Layout 9. This has Title, Axis Titles on both, Legend, and so on. And in the next section we’re going to look at how to set up the Chart Title, Axis Titles, and so on. So, finally in this section, let’s take a look again at the Chart Styles. We saw this before; we know that with this whole gallery of Chart Styles here on the Design tab, we can very easily change the color scheme for our chart. Again, there is a More button we can press and in fact as you move your way through the gallery options here for style they get more and more extremely different from what we started with. So, for instance, if you choose the last option, the chart really looks quite spectacularly different from the way that it looked at the beginning. Now, apart from this choice of style and bear in mind you can actually create your own styles as well, the basis of the Color Scheme, Fonts, and so on is the theme that’s selected at the moment. And if I go to the Page Layout tab and then click on Themes, I can see the available Themes and I can actually change the Theme for this particular chart. So, for instance, the Aspect theme, and note that it’s a live preview, would make the chart look like that. The Austin theme makes it look like that. Black Tie theme makes it look like that and so on. Now, built into each of those themes is a set of Fonts, Font sizes, colors, and so on. And depending on the theme that we choose, it can have quite a dramatic impact on the look and feel of the final chart. So, there’s the Office theme, here’s the Adjacency theme. I’m going to choose Angles. So, as you can see, choosing the Theme can also have a dramatic affect on the appearance of a chart and I’m really quite pleased with the way this one is starting to look now. And in the next section, I’m going to look at putting a Title on, putting Axis Titles on, and some other more detailed Formatting that I’ll normally need to be able to do. Before I end this section though, just one word of warning when you choose a Theme, that Theme will apply to all of the graphic elements in a Workbook. So, the Theme will also apply, not only to other charts and graphs, but also to Smart Art graphics and so on. So, be a little bit careful when you choose a Theme. You might get some surprises. A chart perhaps you’d thought you’d finished, changes when you’re working on another one. So, that’s just a word of caution really. I’ll see you in the next section.



Chapter 2 – Layout Tab Video: Detailed Formatting for Charts/Graphs Toby: Hello, and welcome back. We’ve now seen how to create straightforward charts and graphs from data on Worksheets and to make some straightforward decisions about Style, including the use of the Office 2010 Themes. In this section, we’re going to go onto the next stage in this whole process. We’re going to use the same example we used in the last one, but this time we’re going to look at what’s available on the Layout tab. So, let’s get started. So, first of all let me select the Layout tab and I can see a number of commands available there and we’re going to start with this group here in the Labels group and as part of that we’re going to start looking at the terminology that’s used in charts and graphs in Excel. First of all, the whole of the area of the chart is referred to as the Chart Area and everything else appears within the Chart Area. The part which is actually the rectangular area containing the data Series, the data markers, and so on is called the Plot Area. And above the Plot Area, normally, will have a Chart Title, but as we’re going to see now it’s possible to do quite a few different things with the Chart Title. So, first of all, let’s click on the Chart Title to select it and note the little border and selection handles that appear around it. Now, we’re going to spend a little bit of time looking at options for formatting the Chart Title because it’s pretty typical of some of the other elements that appear on charts and graphs. So, we’re going to use it as an example of what can be done. One very simple thing you can do is if I click inside the Chart Title area you notice that the surrounding border changes from a continuous border to a dashed border and once that happens I can actually edit using the keyboard, the Chart Title pretty much in the way that I edit text normally. But more of that later. First of all, let’s delete this chart all together. Select, press the Delete key and let’s go back to the beginning and we will make sure we’ve got our data selected. Select the table, hold the Control key, select the next one, Alt-F1, brings up my Default chart type. Now, if I drag that chart, slightly different position, make it a little bit bigger. Make it a little bit easier to work on. By dragging the sizing handle, there’s my chart. Note that when Excel 2010 created a © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


new chart the Default is to create it without any Title at all. And the first thing you may want to do when you’ve created a chart is to click on the Layout tab and if you use the Chart Title button in the Labels group, the Default is the first option, None – Do not display a chart Title. And the option we saw just now was actually the third one, Above the Chart – Display the Title at the top of the chart area and resize the chart. Now note what happens when we actually add a Title like this. The chart itself, the Plot Area, inside the chart area is resized and the Chart Title appears. Let me undo that, go back to the Chart Title button again, click on the dropdown and this time choose the middle option, which is Centered Overlay Title. Watch what happens in this case. Now in this case, the Chart Title appears but it’s overlaid on the whole of the Chart Area. In fact, it goes over part of the Plot Area and the Plot Area itself is not resized with that option. So, now that we have a Chart Title partly overlaying the Plot Area, let’s go back to that Chart Title button again and look at that bottom option which is the More Title Options button. Now this is quite important because it’s going to illustrate some formatting that we can do on charts, which is going to cover many different parts of a chart or graph. And this is the Format Chart Title dialogue, specifically aimed at the Chart Title and on here we can format the Fill, Border Color, Border Styles, Shadow, Glow and Soft Edges, and so on. And I’m going to demonstrate a couple of those now and then we’re going to be able to extend this into other areas of charting and graphing later on. Let’s just start with Fill. At the moment the Fill is set to Automatic and that will pretty much cover the style of chart that we’ve chosen. But we can change that into a non-automatic set of options. So, for instance, if I wanted to choose a Solid Fill, I could choose that here. I could choose a color, in fact there’s a yellow color there. You can see how that is all ready appearing in the title. So I can change that color. Let’s say I want to go for that color for my fill and then I can also change the Transparency, make it more transparent to show through. So, as you can see even with this straightforward example we have a lot of control over how that can appear. Similarly with Border, Border Color is automatic. I could for instance set the Border Color to a Solid Line. I could set the color to, say, a red color and again change the transparency. And then Border Styles, I can have a wider border. A 3 point border, you can see it getting thicker as I preview it there and then I can change Alignment, Soft Edges and so on. So we’re not finished working on that, I can pretty much format the Chart Title any way that I want.



And one other point to note before we move on is if you point at the Chart Title Area and right click, you get not only a Context Menu, but a mini Tool Bar here where you can actually format the text in the Title, choosing Font, Font Size and so on. And then if you click within the Context Menu on Format Chart Title, you bring up that same dialogue again. And, in fact, amongst the other options on that Context Menu is this group here, which actually relate to the whole of the Chart Area. So if I, for instance, click on 3-D Rotation, I bring up the Format Chart Area dialogue that lets me make changes to the Chart Area itself. So, for instance, the 3-D Rotation one, if I actually reset that value now, you can see it’s affecting the whole of the Chart Area. And there are a number of options available there as well. Okay, so we’ve looked quite a bit at the Chart Title and some of the ways of changing the layout and doing some more detailed formatting. Let’s now look at the second button in the Labels group, which is Axis Titles. Now, there are two axes. There’s the Primary Horizontal Axis and the Primary Vertical Axis. And both of them at the moment have the Default Titles of none. So, if I wanted to put on a Vertical Axis Title for a start. Let’s look at Rotated Title first. If I select that what happens is a title is added with a Default name of Axis Title and the text is actually rotated sideways and I can edit it that way or I can actually turn it around. But I’m going to leave it that way at the moment and edit that Axis Title. Now, some people find it a little bit unnerving to type apparently on something that’s lying on its side like this, but in fact it’s no different to working on the Chart Title. When you’ve got the Axis Title selected, click again to change the shape of the border to a dash line, which means I can now just type. So, let me delete the word Axis. I’m just going to put in the word Dollars as well as Title at the moment. And that’s absolutely fine. If you find that a little bit unnerving what you can do is, if you don’t all ready have the Formula Bar shown in Excel, you can switch that on and you could actually type in the Formula Bar. I’ll just put the word Dollars and then click on the Axis Title and, in fact, Dollars appears as my Axis Title. So, back to the Layout tab, Axis Titles, Primary Vertical Axis and I’m now going to put in Vertical Title instead of Rotated Title. Watch what happens to the Plot Area when I do this. The text now reads horizontally, although it’s arranged vertically and the Plot Area was resized to allow for that. Back again to Axis Titles, Primary Vertical Axis, I have the option of Horizontal © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


Title. Again, watch what happens to the Plot Area and the Title when I do that. It now reads as Dollars horizontally with both the individual characters and the whole title arranged horizontally. And finally, as you can probably guess, if I again go Primary Vertical Axis, More Primary Vertical Axis Title Options, click there and I have a Format Axis Title dialogue where I can do things like Border, Fill, 3-D Formatting, Alignment changes, and so on. So, finally in this section let’s look at the Horizontal Axis and do a couple of different things with that. What you can do to the two axes is pretty similar in terms of formatting. But if we look at the Primary Axis, example; let’s first of all put a Title below the Axis. I then click in there, change the Axis Title to Months, and then I’m going to go back to the same button and this time choose More Primary Horizontal Axis Title Options. It brings up the Format Axis Title dialogue, and just quickly for Fill; I’m going to make it Solid Fill. Border Color, I’m going to say it has a Solid Line. Border Styles, I’m going to give it quite a wide Border and, of course, as I’m doing this you can see it all happening in the chart itself. Give it a 3 point Border. And now this time I’m going to say Shadow. Yes, let’s give it one of the Shadow presets; perhaps, that one. Note the affect. Glow and Soft Edges, yes, let’s give it one of the presets; what about that one. And then 3-D Format, I can give it a bevel effect and so on. So there’s a whole range of options there that I can use on Titles, Chart Titles, and so on. It’s best really if you experiment with these. It would take me quite a lot of time to go through all of them, but you know how to access them now and you’ve got a general idea of what each of them does. In the next section, we’re going to carry on looking at the Layout tab. We’re going to look at formatting the Legend and so on. I’ll see you then.



Video: Formatting Legend within Charts/Graphs Toby: Hello and welcome back. In the previous section, we looked at adding a Chart Title, Adding Axis Titles and formatting these. In this section we’re going to look at the Legend. Now, the Legend here lists the sales people whose sales are represented in the chart, and there are various ways that we can both position the Legend and format it. As with the Chart Title, there are various options for positioning of the Legend. And if we look under Chart Tools on the Layout tab, the third button in the Labels group is Legend. Click on the dropdown arrow there and we see there are a number of these Default options. Now, this group of four will enable us to position the Legend right, top, left, or bottom. And as with Chart Title, when we move the Legend around by choosing one of these, Excel 2010 will automatically reposition the Legend, of course, but also reposition the Plot Area within the Chart Area to accommodate that position. So, for instance, if I select Show Legend at the left, you can see what happens to the Legend and the Plot Area. Now, as with Chart Title, it isn’t necessarily the case that Excel 2010 will resolve all of the positioning issues when you make a change to the choice for Legend position. So, if I now choose Legend bottom, note what happens. It’s quite nicely positioned, but not only does the Legend have a clear space at the bottom of the chart, but the orientation of the entries in the Legend is changed to make use of the width of the Chart Area. So instead of the sales people’s names being one above the other, they’re side-by-side. But watch what happens if I put the Legend at the top. Now, in this case, it actually clashes with the Chart Title. There is a limit to how automated Excel 2010 can be with these things. And sometimes it is necessary to manually intervene to overcome this sort of problem. So, now we’ll look at this question of overcoming some of these positioning issues by making a different choice. Still with the Layout tab selected and Legend, click on Overlay Legend at right and see what happens. Now we now finish out with the Legend as it was originally with the sales people’s names one above the other, but the whole Legend now overlays the chart. And there are two main ways that we can overcome these overlay types of problem. Now the first way is to change the positioning manually. You have to do this a little bit carefully, but once you know how to do it, it’s pretty straightforward. If you click once within the area while the crosshair cursor is selected, if you look at it now you can see I’ve got the crosshair selected.



That means the Legend is selected and when I click I have the sizing handles and so on. And now, I can drag the Legend to any position I like within the Chart Area. So I’m going to actually drag it up there and you can see I’ve placed it away from any of the blocks or any other text. The other thing I can do to help to make the Legend readable in any position is also pretty straightforward once you know what to do. If you move the mouse cursor somewhere within the Legend Area and right click, you actually come up with a Context Menu and within that you have a Format Legend Option. If I click on Format Legend I can actually click on Fill, choose a Solid Fill, and then choose a Solid Fill color. Now I’m going to choose a Solid Fill color of white at the moment, just to show what happens. Click on Close again and you can see that I now have the Legend with a white fill and again if I go in there, get the crosshair cursor, I could actually drag that anywhere on the chart. I’ll put it somewhere particularly unsuitable and, of course, the white fill means that I can now read all the entries in the Legend and whatever is behind the Legend is obscured by the white fill. Now, of course, with that Format Legend dialogue I have all the full range of formatting options. So, for instance, if I wanted to put a Border around the Legend I could choose Border Color, say, Solid Line, and then choose a color for the line. Let’s stick with that red color; perhaps change the width the line a little, say, put it up to 1.5 pts. Click on Close and I’ve got all of the normal kind of formatting that I would need. Now, we saw just now that we can choose a position manually for the Legend. There’s one other tool, one other facility that Excel 2010 offers us with Legends, which is particularly useful and particularly useful when you’re a bit tight for space. If I select this Legend again, note all the sizing handles, if I say choose this sizing handle and I’m going to drag this Legend to be wider, but not quite so tall, so I’m going to drag that up in that direction, so I’ve got a wider Legend, but not as tall and watch what happens now when I release the mouse button.

Excel 2010

intelligently changes the orientation of the entries in the Legend. And as you probably guess, if I were to drag this right up to here I eventually finish out with the side-by-side arrangement, which then means that I can in fact, if I wait until I get the crosshair, I can move the whole Legend and choose a space where it either doesn’t obscure the chart or it doesn’t obscure it in a way that causes a problem.



There are one or two other features of Legends that we’ll look at later on, but we’ve covered really all of the basic things that we’re going to need, so let’s move on now to the actual data in the chart. One thing you may have noticed if you’ve been following along with this on your own chart, with your own data, is that if you hover the mouse over a particular entry, say this one, the tip that appears actually tells you about the data for that particular bar in the chart. Series “Pawolski, Dav” Point “Jan” Value: 518. So we get the Category Value, Series Value, and the actual Data Value. And that’s pretty useful. It’s not there, of course, if you’ve got a printed version of this chart. So, somebody perhaps looking at something in the middle of this chart might actually find it quite difficult to know exactly, say, what the value, the heights of that particular bar is. Now, there is a way of showing those values and of showing them quite selectively. And that’s what we’re going to look at next. Now, with the chart selected, on the Layout tab, in the Labels group, go to the Data Labels button and there are two main options for Data Labels. None, which is the current situation and Show and if we click on show, what basically happens is that Excel 2010 adds a Label to each bar showing the value in dollars of the sales per month corresponding to each bar. Now, one of the things that’s immediately apparent in the example here is that there are quite a lot of numbers now on this chart and generally speaking, Data Labels don’t work particularly well when you’ve got a very crowded chart like this one. And it’s always worth asking yourself whether you really need those Data Labels or whether you would expect users to be able to read these values off from the axis scale on the left. You can actually add the Labels selectively as we’ll see in a little while, but just be careful that you don’t put too much information in the body of this sort of chart. Now, to make this a little bit easier to follow, what I’m going to do is I’m going to switch the Data Labels off again; so switch to None and I’m going to select just one of my sales people that, Petra Henderson, and if I right click on one of the bars of Petra Henderson I get a Context Menu and I have an Add Data Labels option there. If I click Add Data Labels what happens now, we just have the Labels for that one person, now I’m doing that this way here just because it will be a bit easier to see what I’m doing rather than having all of those numbers. Once I’ve added Data Labels for that one person, if I right click again, bring up the Context Menu again, I get Format Data Labels and I can go into that dialogue to do a little bit more work on the Labels for this © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


chart. I’m going to just pull the dialogue over to the side so that I can point out a couple of things as we go. Now, within the Format Data Labels dialogue I have a number of options to choose between. For instance on Number it currently at set Number with zero decimal places. If I actually wanted to show this as currency, my Default currency is the UK Pound, so that’s the Default symbol that appears. But if I wanted to say, put it in Euros, I could choose, for instance, Irish Euros and the Euro symbol appears the way it is formatted in that country, with the Euro symbol in front. Obviously I could change it to U.S. dollars or any other currency that’s available. And I can also choose from Label Options, I can add Series Name or Category Name and so on. And if there are more values to show, for instance, if I wanted to include, let’s say I wanted to include the Series Name, then the name appears and I can choose to separate it between the name and the currency amount here. So, plenty of options to experiment with in terms of giving the right amount of information on each of the blocks in my chart. And finally, one other very useful option for Formatting Data Labels is this one down at the bottom, Alignment. We can change the Vertical Alignment of the Labels, but we can also change the text direction. So in the case of this one, where the text is basically crossing over all of the bars, we can make things a little bit easier to work with by choosing, for instance, Rotate All Text 270 degrees and then you note how the Labels appear aligned with each of the bars or with the selected bar. So, I’ve removed those Labels again now and I’m back to my chart as it was before. We’ll quickly look at other very useful option here, which can overcome some of the issues we get with Labels and how crowded they can make a chart look. And that is to use the last option in the Labels group on the Layout tab and that is the Data Table Option. Now, the Data Table Option currently set at None which is the Default, offers us two options. One of them is Show Data Table and one of them is Show Data Table with Legend Keys. Let’s start with Show Data Table. Show Data Table actually includes a copy of the spreadsheet data that has been used to draw the particular chart underneath the Plot Area and within the Chart Area. So you can actually see all of the number directly as they were in the spreadsheet and in addition let me change now to the other option on Data Table, which is Show Data Table with Legend Keys. Using the Legend Keys, so basically the colors for the blocks, in this case, you can directly relate © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


these numbers to the content of the chart. So, for instance, if I look at this month and I look at that colored chart I can see that’s Petra Henderson. I can see it’s 364, so I don’t actually need 364 drawn on the block. One of the advantages of using the Data Table with the Keys is you can pretty much eliminate the need to have a Legend all together, which can save you a bit of space. So, the use of a Data Table is a very useful option when you’re looking at how to present a chart or graph. Well, that’s it on this section on Layouts. In the final section on Layout, we’re going to look at Axes and Gridlines. So, I’ll see you then.



Video: Formatting Axes and Gridlines within Charts/Graphs Toby: Hello and welcome back. This is the third section on Layouts and in this section we’re going to look at Axes and Gridlines. Now, Axes are particularly important because they form the basis of the message that your chart and graph is putting over to the user. And as such they give a way to deceive and be deceived really. The careful, clever, deceptive use of axes can fundamentally change the way in which a graph or chart is interpreted. So, it’s very important to understand both how to present your own data and how to interpret data that’s being presented to you. Now, we’re going to carry on with our sales per month spreadsheet for the moment and the chart we’ve got has two axes: a vertical axis and a horizontal axis. And in fact, most of the charts and graphs in Excel have two axes, but not all of them do. There are charts and graphs with three axes and there are some, such as Radar Charts, which only have one, and in fact, if you take, for example, a Pie Chart, a Pie Chart has no axes. So, we’re going to look at the case of two, which is the most common one and we’ll look at some of the others as we work through the applications of graphs and charts later on. Our Horizontal Axis here is a time axis and each interval is one month of time. If we, on the Layout tab click on Axes, the dropdown gives us a choice between Primary Horizontal and Primary Vertical. Let’s look at Primary Horizontal. We see the options are None, which is easy enough to show. We can click None and we have no axes. Back then to the dropdown, show a Left to Right Axes, which is basically what we had and is basically the Default. We can also have Show an Axis without Labeling. In this case we have the axis line there, but there are no Labels on it and we can show a Right to Left Axis. This actually shows the same data, but the data run Jan to June from right to left. Let’s go back to the traditional one, the normal Show Left to Right Axis and then I can either click on the Axis, right click to get a Context Menu or here I can use the dropdown, Primary Horizontal Axis and say More Primary Horizontal Axis Options and, of course, I get a dialog. Now the dialog is of similar style with a set of formatting options, but also specific Axis options and that’s what we’re going to look at next. So, let’s look at one or two of the options we can choose here for Axis Options. Let’s start with this one, Interval between Tick Marks. At the moment, the Tick Marks that you should just be © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


able to see on the Horizontal Axis here are at single units. If I were to set that to say, three, it’s actually updated live as I do it, and if I Close the dialogue now, you can see that I just have a Tick Mark every third month. And as I said just now, one option for bringing up that dialog, if I right click here, Format Axis takes me straight into that. Let me change that three back to one. And the Label Distance from Axis, I’m going to change next. At the moment you can see where the label is in relation to the Axis. I’m going to change that from 100 to 500, just to make it quite a big change, and you can see the actual Labels themselves stay where they were, but the Plot Area is slightly rescaled to allow for that increase in size. Let me put that back to 100 again. So, there are a number of options you can experiment with there to change the look and feel and properly use, make better use of the space you’ve got for your graph or chart. So, now let’s look at the Vertical Axis. This Vertical Axis is actually in dollars and if I click on the Layout tab Axes, Primary Vertical Axis, I have a few more options here. I still have the Default axis which is the one that’s shown and I’ll have Formatting. Let me quickly look at the formatting options here. Don’t think they’ll hold particularly many surprises in terms of things like Line Color, Line Size, Shadow, etc. But interestingly on the Axis Options, we have a choice here for Minimum and Maximum Values. Now, on the Vertical Axis this is really usually the main way to deceive and be deceived. If I take it, for example, here, the number of dollars on the sales per month data varies over a very small scale up to just over $500 from a very small number. Now quite often, if we’re dealing with very large numbers, people will use the Vertical Scale to manipulate the impression of how much something has increased or decreased. And, in fact, in one or two of the examples we’re going to use from business applications later on, I’ll show you in much more detail how this is done. But if you wanted to change the basis of Vertical Values here, you would do it using one of those options. Let me again go back, I’ll do a right click here, Format Axis for Vertical Axis. But apart from that, you have the same sort of things, you have Major tick mark types, you can vary how frequently the marks are and so on. And you can also use Logarithmic scale. Again, that’s something I’ll show you in an example later on. And one final point about Vertical Axis. I’ve chosen this particular example because the numbers are fairly convenient, small numbers up to about 500 or so. Very often we’re dealing with very large numbers and Excel 2010 deals with these pretty well. On the Layout tab, if you © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


click on Axes, Primary Vertical Axis, you can actually show the axis in thousands, millions, or billions, which means you haven’t got to have numbers on there with loads of zeros in them. And, in fact, if you go into More Primary Vertical Axis Options, as before and again look on the Axis Options, Display Units, roughly in the middle here, we can have hundreds, thousands, tens of thousands, right up to trillions now. So, if you have very large numbers to show, Excel 2010 can deal with those automatically. We’re now going to look at Gridlines. And on this particular chart gridlines don’t really help very much in great detail. In fact, the Horizontal Lines we’ve got for hundreds of dollars are pretty useful, particularly if we haven’t Data Labels or a Data Table underneath. We can see, for instance here, easily read across that this value is about 360, something like that, it’s actually 364. Vertical Gridlines add very little for us. If we click on the Layout tab under Gridlines and select Primary Vertical Gridlines, the Default at the moment is selected, which is None. If I put in Major Gridlines, see what happens. We finish out with Vertical Lines corresponding to the months. In some of the later examples, Vertical Gridlines are actually going to be very helpful but they don’t really add very much here. Now, let’s look at Horizontal Gridlines on this one as an example. If I right click on the Vertical Axis and select Format Axis, I’m going to go back to Axis Options again and one thing we didn’t comment on before was that the Major Units on the Vertical Axis here are hundreds, which of course you can see. But Minor Units are also defined. Excel 2010 when it looks at the data automatically selects both Major and Minor Units and it’s actually set them at $20. The reason you can’t see anything is apparently if you look further down this page on the Format Axis dialog, and that is that Major tick mark type – Outside, Minor tick mark type – None. So, the reason the 20s aren’t apparent is that there are no tick marks. If I put the tick marks also outside, you can now see, you should just be able to see the marks there. So, let’s now go back into Gridlines again. Primary Horizontal Gridlines. Currently we have Major switched. We could switch on Minor or we could switch on both. Let’s switch Major and Minor Gridlines. Now what we can see now is both sets of Gridlines and the Major ones are actually slightly heavier than the Minor ones. And, in fact, if you click on a Gridline, right click on it, you actually have a Format Gridlines dialogue and depending on whether it’s Major or



Minor, you can change the Line Color, Transparency, Style, and so on. So, you can make a very flexible arrangement of Horizontal Major and Minor Gridlines. We’re now going to finish off this review of the main Layout options in Excel 2010 by looking at the group under Background. The chart we’ve got here does actually have a Background and in order to demonstrate these main options I’m going to use one of them, which is the 3-D Rotation Option. Now this is a 3-D chart and if I click on that and look at 3-D Rotation, it’s currently got an X Rotation of 110 degrees, a Y of 15. Let me just move this dialog over here and you can see what happens now. I’m going to change the Y Rotation and I’m going to increase it by just a few degrees at a time. Get out to 90 and if I Close that you can see a much better way the 3-D nature of this chart. Now, what you can see here are the Floor and the Walls. Now formatting the Floor is pretty straightforward, but it is one of those where you have to get the mouse in just the right place. So, roughly in the middle of the floor here, right click and I get an option Format Floor. And I can, for instance, now say I want the Solid Fill and I can choose a Floor color. Let me choose a color you’ll be able to see, but not too dark. There we are, a green color. And then on the Layout tab there is a Chart Floor Option with a dropdown, which is None, that clears the Chart Floor Fill, or Show Chart Floor, which basically shows the Chart Floor, but having cleared the fill I need to go back in again now, Format Floor, Solid Fill, and it puts back in the color that I had there before. To Format the Walls what I’m first going to do is to go back to Gridlines, Primary Horizontal Gridlines, and change back to just having Major and then it’s easier to right click, click on Format Walls, and choose a Fill for the Walls. Now, I think on this occasion just to show you how this is done I’m going to go for Gradient Fill and I’m going to choose Preset Colors. Let’s have something that’s not too overwhelming, say that one. And click on Close. Obviously I’ve got some minor settings here where I can more fully customize this selection, but if I click on Close you can now see how that Gradient Fill gives my chart a much better overall appearance. So, having used the Gradient Fill in the Plot Area I could, for instance, now move the Legend to a slightly better position and if I right click on the Chart Area, click on Format Chart Area, and select Gradient Fill there as well. Close that. Perhaps move the Title a little and the Legend a little. Then my chart is really beginning to look a lot better now, and I have, of course, a huge © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


amount of control over how to present everything else to do with this chart. I could, for instance, clear the Fill here so that the background of the Horizontal Axis Label becomes Transparent again. I can change Fonts, Point Sizes, and so on to achieve exactly the affect that I want with my chart. So, under Chart Tools the next tab we have is Format and if I wanted to I could use some of the features on the Format tab to further improve the appearance of this chart. But I’m not going to do that now because we’re going to move on to looking at specific charts and graphs to deal with specific requirements, and as we do that we’ll use the features of the Format tab to deal specific issues and specific needs. So, we’re going to start with that in the next section where we’re going to deal with ways of showing trends in Excel 2010. So, I’ll see you then.



Chapter 3 – Trends Video: Trends within Charts and Graphs Toby: Hello and welcome back.

In this section we’re going to start to look at specific

applications of charts and graphs in Microsoft Excel 2010. We’re going to pick up some more tools and techniques as we go along and we’re going to start with showing Trends in Excel 2010 charts and graphs. Now, the figures that you see on the worksheet in front of you are the sales of iPods over the period 2002 and 2010. We have the year number on the left and we have the four quarters for each of the years and a total sale for the year. What I’m going to do first of all is to just plot total sales against year to show the Trend in sales over that period. Now, if I just select year and total you would think that I could do a very simple chart automatically from that, the problem is that if I did that because both of these columns have headings, they’ll both be interpreted as variables that I want to plot. And, of course, the fiscal year is affectively my independent variable. So the way to get the chart that I want is to actually remove this fiscal year heading here. So I’m going to just delete that, reselect year and total and then if I press Alt and F1 to get my Default chart type, I actually get straightaway a pretty representation of how those sales have varied over the period. So, let’s go through the steps that we’ve looked at so far and do some improvement straightaway to this particular chart. Let’s start by making it a little bit bigger. Now, if I wanted to change the Chart Type, I could do that very simply using the Type group. Here we have the Change Chart Type button which gives us access to the full gallery of Chart Types. I’m going to stick with the Column Chart on this occasion, but I’m going to look at some other options a little bit later on. So we’ll stick with that as it is for now. We are only dealing effectively with only one figure for each of the years, which is a total figure, so we don’t need to worry about choosing one of these different sort of composite types of chart, just a straightforward Column chart will do the job here. So when we talk about a chart that shows Trend, we are showing Trend almost always this is a Trend over time. And the time here is represented by the year number on the Horizontal Axis. © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


The sales are basically going out to figures in the tens of millions. I think you could reasonably argue that on the Horizontal Axis, the year number here pretty much explains itself, although we are going to add something there just to help the user in a little while. And the total number of sales probably needs a little bit of explanation and somebody looking at this chart from cold would have no idea what these bars represent. So we do need to put some sort of Title on here to explain things. So, let’s now choose our Chart Layout. Now if we look at the Gallery of available Layouts for this type of Column Chart, we have a number of options. I really don’t believe we need a Legend here as we have a simple variable, basically the number of sales over the course of the years. And it’s questionable that we really do need a Label on the Horizontal Axis because we can put something in the Title to indicate that these years, although I think anybody using this chart would deduce that they’re years anyway. So, I think probably the ideal choice here would be this one where we have a Title, a Label on the Vertical Axis, and that’s about it. So, let’s choose that option and see how we get on. So we have a Chart Title, we have an Axis Title that we’ll obviously need to correct. We’ve actually got a Data Label switched on here, so if I just select that, go to the Layout Table and switch off Data Labels. That sorted that problem out. And we now have a chart with just the bear information that we need. So, we’ve got our Layout, let’s now try Chart Style. Again, click on the More button to look at the Gallery of Chart Styles and lets choose this one. We have a nice 3-D look and feel to that chart and it’s very straightforward because we’re only really measuring one thing, the number of sales. So, let’s go with that type for now. Okay, we’ve dealt with all of the design elements for the moment, let’s move on now to Layout and starting from the left Chart Title where we’ve got a Chart Title, let’s go in and edit it. We can just click inside the Title until we get the dashed line. Let’s, think that’s probably a suitable Title, and if I wanted to I could now straight away format that Title for presentational point of view. I’m going to leave that for just a moment though because I want to get the Axis Title sorted out as well. So let’s now look at Axis Titles. We’re not going to use an Axis Title on the Horizontal Axis. I don’t think it’s necessary because the fact that these are years is now implicit in the Title of the © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


chart. If I click now within the area of the Vertical Axis Title I can once again get the dashed line and I can start typing in there. I’m going to make my Title “iPods Sold” and what I’m going to do, I’m going to leave that as it is for the moment. I may come back and reformat it a little bit later on. Now, a combination of what’s in the Title, what’s on the Title on the Vertical Axis, and the numbers that appear on the chart anyway, I think is enough to make this chart completely understandable by somebody coming at it from cold. So, let’s just run through the other things we might want to set. Legend is one. I don’t think we need a Legend. Data Labels, as you may recall, put Labels on the bars. Again, I don’t think we need them on this occasion. And I’m not going to include the Data Table within the chart. But when it comes to Axes, I am going to make a change on the Primary Vertical Axis because as you may remember earlier on we talked about working with very large numbers, and we now have a first case of working with very large numbers and what I’m going to do is I’m going to say that the Axis, in this case, is numbered in millions. I’m going to choose the option Show Axis in Millions and see what happens. Now what happens, in fact, is that we have the word Millions added here to indicate that the scale used here is in fact millions of units. So we’re going from zero millions of units to 60 millions of units. Now I said I might come back to reformat this Axis Title. I’m going to actually do that because I don’t particularly like these things lying on their side and I’m going to make this Horizontal. One of the reasons I’m going to do that on this occasion is that this particular chart, if you look at the Plot Area itself, is not particularly crowded. The message about the sales will be clear if I squash the whole thing up a little bit more there, the huge amount of information on here. If there were a lot more information in the chart itself, I might be reluctant to squash it up, but on this occasion I don’t have a problem with that so I’m going to right click here. I’m going to say Format Axis Title and then I’m going to go to Alignment and I’m going to say make it completely Horizontal, not Stacked but Horizontal. Click on Close and as you can see, it now read iPods Sold. As I said the Plot Area is a little bit more squashed up than it was, but very readable and I made more significant improvements to this chart. So, finally, let me review my Gridlines. The Gridlines actually, in this case, are perfectly suitable as they are. I’m not really intending to use this chart for people to read off accurately the heights of the bars. Remember if this is printed, you won’t have the option of these Tips © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


popping up here to give the actual height of each bar. So, really I’m giving fairly broad information about how these sales have gone. So, I’m going to leave the Gridlines as they are with just this major 10 million sale Horizontal Gridline. No need for Vertical Gridlines at all and finally I’m going to format the Chart Area itself. So, right click, select Format Chart Area, and I think I’m going to use a Gradient Fill here. Let’s try that one. That looks okay. Close. And there we are. I’ve got a pretty smart looking chart there, showing the information about the sales of iPods. Not only can I use that now as a presentation, I could perhaps put it in a Power Point presentation, transfer it to a Word document, or put it on a website or whatever, but I’ve got a pretty smart looking chart here to use when I present these figures to other people for whatever purpose I’m using these figures. Okay that’s a basic example of showing Trends over time. In the next section, we’re going to look at some more advanced techniques that we can use to show Trends in some more complex situations. I’ll see you then.



Video: Complex Trends within Charts and Graphs Toby: Hello and welcome back. In the last section, we created this chart that shows, in a fairly straightforward way, the Trend in iPod sales between 2002 and 2010.

Now, the source

information we used for these sales figures actually contains a lot more information than the straightforward yearly totals and we’re going to look at how to deal with Trends in data where there is more complexity in the data in this section. Now, on this worksheet I have the source data covering the same years, 2002 to 2010 for quarterly sales for iPods. So I’m going to select all of that data and once again I’m going to create the Default Chart Type and once again I get Column chart covering the whole period, but this time, of course, I’ve got a lot more points in the chart because it takes me right up to the end of 2010 a quarter at a time. Now, apart from the obvious need to put a Chart Title on and Label things and so on, there are two main problems now with this sort of Column chart showing a Trend. First of all, once you get above about 10 or 12 columns, the Column chart itself tends to look a little bit crowded and it’s worth considering whether you would be better off using some sort of Line Chart and we’re going to return to that point in just a moment. The other point about this one is that every year there is a peak of sales in one quarter and that peak. Although it doesn’t completely mask the overall pattern of the sales, it is often confusing having peaks and troughs. And sometimes, in order to see the overall Trend in something and it’s after all Trends that we’re talking about here, you need to be able to see past those peaks and troughs, and that’s one of the things we’re going to look at in this section as well. Now, let’s just look at a couple of ways of showing that overall Trend. And we’re going to start by turning this Column Chart into a Line Chart, so change the Chart Type and we’ll go for a straightforward Line Chart here. And when we’re drawing the Line Chart, you can see that it doesn’t really give a very good overall impression of how things are going. This sort of spiky affect, apart from making it difficult to see an overall Trend, also implies that there is a continuous variable that we’re dealing with here, which there isn’t. We’re dealing with specific sales for quarters of a sequence of years. But having drawn this Line Chart what we can then do is add a Trend Line. © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


Now, we have a number of options with Trend Lines, but if we right click on the Series itself there is an option there on the Context Menu, Add Trend Line. And there are a number of Trend Lines that we can draw. Let me just pull this dialog out of the way for the moment. By Default, when you add a Trend Line, you get a straight line which is the best fit line for the existing data. And on this case, it gives a straight line which seems to be continuing upwards. We know by looking at the data all ready that the sales are not just going up in this uniform way, but by Default we get a straight Trend Line. In order to get a better picture of how the sales are actually going we can choose from one the other available options. And I’m going to start with one that’s very popularly used, which is the Moving Average Option. Now you might be familiar with Moving Averages, they’re particularly used when we have a Trend over time which is masked by periodicity. This is a good example here where we have quarterly sales, we have four figures each year, one for each of the four quarters, and there is one quarter each year where we tend to get a peak. Now by averaging the quarters, we can reduce the impact of that peak and get a clearer picture of overall Trends. By Default, if we choose Moving Average in the Format Trend Line dialog, the period we’re dealing with is a two-period Moving Average. And again if I move the dialogue slightly out of the way, you can see that the affect of a two-period Moving Average is that you can still see the peaks, but they’re reduced by being averaged over two periods. If I want to reduce them further I can change that to three periods and further still four periods. And the four-period Moving Average actually gives me a very good picture of how the sales are going over time. Now obviously the reason the four-period Moving Average works very well is because we have one peak per year, i.e., per four-periods, so averaging that out over four actually gives me the smoothest overall Trend. If I include that to increase that to five-periods I start getting peaks again because in some of the periods of five-periods, so some of the periods will have five quarters, I’m getting two peak quarters. So I really need to stick with four-periods here and that gives me this pretty useful four-period Moving Average Trend Line. Now, I’m going to do a little bit of work on formatting my Trend Line. So, I take the Trend Line itself, select it, select Format Trend Line, and as with many of the other formatting options, I can change the Line Color. So I’m going to go for a Solid Line. I’m going to stick with that red color. Line Style, I’m going to make it a wider line. I think I’m going to make it, say, 3 points. No other effects for the moment. So that will do for the line itself. And then for the actual quarterly sales, instead of showing this jagged line, I’m going to select that, go into Format Data © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


Series, and I’m going to change the Marker Options. At the moment there are no actual Markers for the Marker Points. I’m going to use built in Markers. I’m going to change the type from these little blue squares to crosses, reduce the size a little bit, say down to three and then for the line I’m going to remove that blue line altogether. So let’s see how that looks. What I have now, as you can see, is the quarterly sales figures as crosses on my graph and the Trend Line as a thick red line. I think the one other thing I’m going to do, I’m going to make those crosses a little bit bigger. A little bit easier to see and then click on Close and that now shows my four quarters Moving Averages pretty well against the existing data. So, now let’s do a little bit of work on formatting this particular chart. Some of the things we’ve done before, and I’ll do those again offline in a moment, such as using a different scale on the Vertical Axis, but let’s try one or two things that are new. And one of them is that when you have a Trend Line like this, if you go back into Format Trend Line, the Trend Line name is automatically assigned by Excel 2010, but you can put a custom name in as well. And I’m going to just change that name to Four Quarter Moving Average. Click on Close and there we are; I’ve changed the name of the Trend Line. Now, I’ve changed the Vertical Axis Scale and I’ve added a Title to the Vertical Axis. We’ve done both of those before. I’m now going to look at the Legend here to change one of the terms in the Legend. One easy way of doing it is to go back to the Design tab, click on Select Data, and here where it says Q1, Q1, of course, is being picked up as the header on Column B on my original worksheet. I can edit that and change it to Quarterly Sales, click on OK, and now all I’m left with is I’d like to put a little bit more information in the Title. Now for most purposes, the Title facility on graphs and charts in Excel 2010 is fine but it’s actually quite restricted in some ways. And if you want to put in an informative Title, sometimes you need to know quite a bit about the limitations of Titles, but also how to overcome these limitations. Now let’s suppose I wanted to put a more informative Title here. We’ve all ready seen I can click inside there until I get the dash line and then I can edit, put in my, so I get a bigger, longer Title; a more informative to explain what my chart is about. But, in fact, if you would have stopped at that point and then, I’m just going to select the Title again, what looks like exists around here is a Border with Sizing Handles, but in fact these aren’t sizing handles at all. And although Excel 2010 automatically resizes this Title Box for you as you type into it, it’s © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


actually very difficult to Format the content independently yourself. You can certainly when you get the crosshair cursor, drag the Title around, but you can’t actually resize it yourself and when it comes to formatting individual element. So let’s suppose I want to put some more text in there and I want to actually say, “Showing Four Quarter Moving Averages.” If I wanted to say, break it before Show, I could put carriage return in there. I could select the lower text and then on the Home tab, perhaps reduce the point size. So I can achieve different effects, but in some ways, if you do want an elaborate Title and particularly if you say, want to put some Word Art, some sort of graphics in the Title, what might be a better idea is to revert to the idea of actually removing the Title altogether and instead inserting a Text Box or some other graphic element that you can use. So here, for instance, having got that Title, which I’d actually be quite happy with to be honest with you, if I go back to Design and either choose a Chart Layout which doesn’t have a Title or literally while I’ve got the Title selected, just press the Delete key, I can then go into the Insert tab and say, do Insert Text Box. Then I can draw a Text Box. I can reset my Plot Area and so on accordingly. And, of course, with this Text Box, I have the full normal capability of editing, formatting, and I can use Word Art in it and so on. Now, I’m not going to go into that in detail now. You’ve seen the basics of what you need to do, but we will be doing some more with Word Art later on in the course. So, I’ve gone back into the Design tab and chosen a different style and I’ve now produced a chart that I’m quite happy with showing both the sales, the quarterly sales, and the four quarter Moving Averages and I’m quite confident that somebody looking at this chart now will get a pretty good impression of what’s happening with iPod sales. As you can see they seem to have sort of reached a peak here and just tailing off at the moment, probably because of competition from things like the iPhone and the iPad and so on. But also, you can readily see these peak sales, certain quarters of the year, and so on. So the messages I want to get over are now pretty clear to me and I think somebody looking at my chart will get those messages as well. In the last two sections, we’ve been looking at Trends and as I said right at the beginning of these two sections, Trends are almost invariably in relation to time and it appears here that we have a good example of a Trend over time. But as we’ll see, in fact, with Excel 2010, you may think that you’re working with time but sometimes you aren’t. And, in fact, in the example we’ve just



done on the iPod sales, we’ve not actually been working with time at all and I’m going to explain this to you in the next section. So I’ll see you then.



Video: Trends over Time within Charts and Graphs Toby: Hello and welcome back. In this section, we’re going to round off our look at how we show Trends using graphs and charts in Excel 2010 and I’m going to start with an example showing stock prices based on U.S. dollars over a period of time of just over a year. Now if you look at the data, it’s in two columns. The first column has the date and the second column has the stock price and if I look at the date column you’ll see that first of all that the dates are in UK notation, so it’s day, then month, then year. And secondly you’ll see that although they’re basically about a month apart, there are some big gaps, so we leap from April to November there and sometimes the gap isn’t really a month. We’ve got one from the 3 rd of November to the 27th of December. So let me select my data, create a Default chart with Alt-F1, then I’m going to enlarge that chart a bit as usual, and what you can see is that it’s made a pretty good attempt at an initial chart, as you would expect it to do. But the irregularities in the dates mean that the chart itself has columns that are not evenly spaced, which is exactly how it should be, of course. Now, there are a couple of very important things to recognize here because particularly in relation to showing Trends in Excel 2010, there is a very fundamental difference between a Horizontal Axis that is based on date and a Horizontal Axis that is based on text. If you look at the Horizontal Axis here for my stock prices, you’ll see that Excel 2010 has actually created an Axis for me. It’s actually made the interval one calendar month, so it starts at the 25 th of September 2009, goes through to the 25th of December 2010 and it has placed the columns accordingly on that date scale. Now, let’s go back to the previous example that we looked at, which is quarterly sales of iPods. And, in this case, although Excel 2010 appeared to be treating these as quarters, in fact it wasn’t treating these as dates at all. It has no real idea that these are dates it just treats them as text. They’re really just names, they’re items with names. There’s an item called Q1 2002, there’s another one called Q3 2002, and so on. So, Excel 2010 does not recognize that these are dates, even though it has done the Trend processing as though they are. So it’s important to recognize when you’re dealing with any kind of Date Axis or scale in Excel 2010, whether Excel is actually treating it as a date or not.



Now, let’s look at some of the differences that occur in this situation. And let’s look at our quarterly sales of iPods again and if I right click on the Horizontal or Category Axis and click on Format Axis. Axis Options, Interval between tick marks is one, it’s just one unit really, one of something. And then we can set distance from the Axis and so on. But the important thing here is that the Interval along the Axis is one unit. Now, let’s go back to our prices. Do the same thing on the prices, Format Axis.

What we get now under Axis Options is pretty much

completely different because Excel 2010 has recognized that we’re dealing with dates here. And it’s given us a minimum dates for the Horizontal Axis of the 25th of September, but we could change if we wanted to. So we could choose an earlier date or even a later date for that matter. It’s given us a maximum date; again, we could change that if we wanted to. And then on the Axis it has selected Major Unit of one month, Minor Units of one month, and a Base Unit of a day. Now these have all been done automatically and you can manually change them. But it’s important to recognize the difference here in terms of how Excel 2010 has interpreted our starting data and created the chart. So, let’s stick with our stock price for the moment and let’s see something else that we can do with Trend Lines. If I change the Chart Type for this chart to Line, something we’ve done before, and then click on Add Trend Line. We’ve all ready seen that there are a number of options for the type of Trend Line and we did a Moving Average for the iPod sales figures. For these figures, I’m going to choose Polynomial as my Trend Line Type and you can have second up to sixth order Polynomial. I’m going to choose a third order Polynomial and with Polynomial chosen, you can actually use Excels 2010s built-in ability to predict to forecast future values. So, given that my figures here went from late September 2009 virtually to the end of 2010, let’s suppose I want to try to predict that same stock price for a couple of months. Now, my Base Unit, as we saw just now, is a day, so two months would be about 60 days. Choose Polynomial, third order. Trend Line Name I can change if I want to, I’m going to. I’m going to change it to “Price Forecast” and I’m going to go forward 60 periods, that’s two months basically. Click on Close and let’s see what happens. Now what you can see, first of all, is that the line has been extended beyond the actual data, which is the blue line there, but also when Excel 2010 extended the line it also extended the Labeling on the Axis by adding two more months worth of Labels on there.

And I’ve not got a forecast for this particular stock price based on a third order

Polynomial forecast up until about the end of February 2011, which I know by the time you see © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


this is going to be in the past, but you get the general idea. Be interesting to actually see what happened to that stock price over that period. So, that’s a first look at the forecasting capabilities within Excel 2010. Now in terms of now formatting the rest of this chart, we’ve seen things like changing Labels, Legends, Title, and so on. One thing we haven’t looked at so far is the right hand Axis and what I’m going to do now is I’m going to basically Undo the Trend Line and I’m going to put the chart back to the Column form. Now, one of the most effective ways to mislead people with a graph or chart is to change the Vertical Axis and sometimes you might use this to your advantage, sometimes you have to do it for practical reasons. In this particular case, we’re showing a stock price which has basically been priced just over a dollar, it’s gone up to about at 1.3 at its highest and because the price variation hasn’t been very great, the movement in the top of the bars is not very significant. If you wanted to show that this stock price was very steady, a presentation like this actually makes it look quite steady. If you wanted to show that it was varying quite a lot, that it was either increasing greatly or for that matter decreasing greatly, what you would do is to change that Vertical Scale. Now, if I go to the Vertical Scale, right click, click on Format Axis. Note again, Excel 2010 has automatically assigned Minimum, Maximum, Major Unit, and Minor Unit Values. If I change the Minimum to Fixed and instead of the chart starting at zero dollars, supposing I started it at 0.8, now watch what happens. The whole chart appearance is greatly changed by the fact that it appears to vary very markedly over the period we’re looking at. And if you wanted to basically present this as a price that was increasing well, that sort of value would really help your argument. And, in fact, go back into Format Axis again and change that Fixed to $1, Close, and you have an even more markedly growing price. So, let’s now return to our iPod sales spreadsheet and have a look at that again from the point of view of dates versus text.

Now as we saw before, Excel 2010 does not treat these values as

quarter dates. In fact, it treats them as straightforward text. So, if you wanted to properly interpret this data as dates, you would need to convert these to dates. And with quarters there are a number of ways of doing it. I’m going to use what I think is a particularly straightforward way in this case. First of all, I’m going to Insert another Column and quarter dates basically begin at beginnings of January, April, July, and October, and all I’m going to do is I’m going to replace that first entry, Q1 2002 with a date of the 1st of January 2002 that denotes the beginning of the © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


quarter. Now can immediately see that Excel has treated it as a date because it’s put zeros in for me. And if I now select to the bottom of the data area and then on the Home tab click on Series in the Editing group and I want a Series in Columns with dates growing three months at a time, click on OK and it fills down with all the dates for the starts of my quarters. If I then select, back up to the top again, Alt-F1 for Default Chart and in fact I can straightaway check if I right click and go into Format Axis, I can see straightaway that Excel 2010 is now treating my Horizontal Axis as a Date Axis. So it’s put in Axis Options, Minimum 1st of January 2002, Maximum 1st of October 2010 and it’s putting Major/Minor Units that aren’t particularly helpful but I’ll sort those out in a short while. Let’s just make the Chart Area bigger, a little bit easier to work with. Okay. Now, I’ve still got exactly the same data I had before, I could go through all the same formatting options that I did before. The advantage now is that these are actually being treated as dates. If I don’t like this particular notation, it doesn’t say quarter anywhere; I could actually do some formatting. One very simple way of reformatting this, if I were to change the Axis to do as Major Units, Fixed, say, 1 years and the Minor Axis, Fixed at 3 months, I’d have markers corresponding to quarters, so that’s a good start. And then if I go back into Format Axis again, this time choose Number and Custom Setting, and for my Custom Setting let’s see if I’ve all ready got one set up, I have. Choose just the year number. I now see that on my Horizontal Axis, I just have the year numbers, which may be enough for me, considering the message that I want to give. Now these are currently aligned with the beginning of each year. I could actually move these along and align them, say with the center of each year by changing the start point on the Horizontal Axis and as I said earlier, I can change the scale vertically, Title and so on. And I can then put in Trend Line, do Forecasting, put in a Moving Average in, and so on. So, if you need to convert something which is text into date this is one kind of approach. There are various other approaches, you can use functions like the Excel Date Value Function and various other sorts of String Handling Functions to convert variables that are meant to be dates, but don’t actually look like them into dates. Okay, so that’s it on showing Trends using Excel 2010 charts and graphs. In the next section, we’re going to start looking at comparisons using Excel 2010. I’ll see you then.



Chapter 4 – Differences Video: Showing Differences Using Bar Charts Toby: Hello and welcome back. In the last few sections we’ve been looking at Trends, showing Trends over time in particular and one of the types of chart that we used there was the Column chart. Now, in this section we’re going to start looking at Comparisons between entities. Comparisons, or if you like, looking at the differences between entities. And in many ways Column charts are good for that as well. However, when you start to use Column charts, people almost invariably get the impression that in the Horizontal direction you are looking at time. So, when you get a group of columns, such as in the Gallery here, people tend to think that from left to right, we’re moving over time. Now, the sort of comparisons and differences we’re talking about now are not directly related to changes over time, although some of them specifically will be in some cases. And one way of getting around this tendency to think of Columns as Column charts as time based is to use the type of Column chart on its side that’s called a Bar Chart, and basically Bar Charts really are just that. They’re Column Charts on their side and they’re specifically used as the Tool Tip here says to compare values and to look at differences between values. Now, we’re going to start to look at Bar Charts with this particular worksheet where I have the lengths of some of the major African Rivers. And I’m going to select the data that I want to chart, which is going to be first column which has the name of the river and the second column which has the length. And instead of going for my Default chart type, which of course, isn’t Bar Chart. Let’s just refresh our minds on how we insert a chart normally. Go to the Insert tab, choose the type, Bar, and I’m going to choose a 2-D Bar to begin with and just take the first option, which is Clustered Bar. And we get a pretty straightforward chart straightaway. Again, I’ll make it a little bit bigger to easier see what it is. So we have the name of the river down on the left hand side. We have a Legend saying length, kilometers. We have an Axis here with a Scale and then we have a Chart Title. That’s all pretty straightforward and as you can probably guess, the Title, Legend, Axes, and so on, you should all now be able to edit and set up exactly the way that you want without any trouble.



Now one thing that takes a little bit of getting used to in relation to Bar Charts is that the entries in the chart are in reverse order in comparison with the spreadsheet. So in the spreadsheet Congo was first, on the chart Congo is last. You can actually change that. If you go to the Layout tab and then click on Axes, Primary Vertical Axis, Primary Vertical Axis Options. There’s an option here which enables you to, as it says here, present them in reverse order. So if I just check Categories in Reverse Order, click on Close, and now you can see that on the chart they’re in the same order as they are on the worksheet. Now, for this type of comparison one of the big advantages that the Bar Chart has over the Column Chart is that on the left, on the Vertical Axis here, there’s plenty of room to show, in this case the names of the rivers, horizontally, and even if the names here of the variable values are very long, there’s no problem in fitting them in, generally speaking. And you can actually sort the data and present the data in whatever way you want. So, for instance, I’ve got these basically in alphabetical order of name of river, but if I were to go back to the original source data and sort it on length, the rivers will be shown in order of length. Now here’s something new to make a note of, let’s suppose I want to show these in order of length. I don’t actually need to redraw the chart or to delete it and start again. If I just go back, select my two columns, that’s the river name and the length, go onto the Data tab, select Sort, and say that I want to sort by length, click on OK and my chart is updated automatically. Now, I’m going to draw another Bar Chart now. This is for another selection of African countries and their male literacy rates. So, Insert Bar again, go for the 3-D option this time. Let’s move the chart over here, make it a little bigger.

As you can see, it’s pretty

straightforward. The name of the country, male literacy percent, the Chart Title is probably almost enough for itself. But I’ve actually got data here which also shows the female literacy figures and we can actually put two bars on the same chart. Now, of course, for the chart that I’ve got selected, I can see if I click on the chart the data that’s been used to draw it and it’s bounded by these blue rectangles with the handles at each corner. And, in fact, if I wanted to show both male and female literacy rates on the same Bar Chart, although I could delete this chart, select more data, and start again effectively, there are a couple of other ways of doing it. And one of them is to grab that corner, drag it over here, and then © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


automatically I get an updated Bar Chart with two bars, a red bar for the female literacy rate and a blue bar for the male literacy rate. Now, of course, I’ve lost my Title, but you know how to put that back and if I wanted to add Axis Titles or change the Axes that’s straightforward enough. Now, of course, I mustn’t lose sight of the information that’s been given to me by these charts. This type of Bar Chart where we have two bars for each of the countries, showing the two literacy rates makes interesting reading in itself. We can see how the female literacy rate is always lower. In Namibia the two are pretty close, whereas in Morocco and Ghana there’s a very significant difference. And really this is what this sort of chart is all about. Now, let’s return to this question of selecting data for this sort of chart. I’ve just shown you how to increase from one bar per country to two. These little handles in the corners here are pretty useful because I could very easily go back to just having one bar. I can also drag the bar upwards and I could lose the Egyptian figures all together. I’ve dragged and moved out words; now watch what happens to my chart when I release the mouse. Egypt is now gone and similarly if I drag it left, I’m back to a single Bar Chart, male literacy without Egypt. Now as a general rule, and this will vary to a certain extent between the different types of graph or chart, as a general rule, that’s one way of changing the data that is actually addressed by a chart. And, in fact, there is indeed another way of doing this, which I hinted at much earlier on. If you go back to the Design tab under Chart Tools, and go to Select Data, just move that dialog over slightly. You have the Chart Data Range here and at the moment it says Equals Literacy, that’s the name of the worksheet, A1 to B5. If I were to change that to C5, I would get female literacy as well. And if I changed it to C4, I’d not only lose Egypt, but I’d lose Ghana as well. So just check those figures so it would only go from basically Namibia to Libya, click on OK, see what happens to my chart. And there we are. I’m back to having female and male literacy figures, but for just three countries. Now as a general rule, there are two good methods of changing the Data Range covered by a chart or graph. So, let’s now look at all of the main types of Bar Chart. Here I have some data relating to land use in five African countries and if we start with a straightforward these two columns, country, and area in square kilometer, Insert, straightforward Bar, we’ll go for a 3-D Bar and it’s a very straightforward chart, as you can see, area on the Horizontal Axis and the names of the five countries. © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


Now, let me do a different type of analysis altogether. If I select the countries again and hold the Control key down and select these four columns, these four columns represent the ways in which the land area for each country is divided. Got four Categories – Forest and Jungles, Crop Land, Meadows and Pastures, and Other – and the total of each of these will be the total land area of the country. Go to Insert Bar, hover over the tip there, the first one is Clustered Bar in 3-D. And what this Bar Chart does is to compare the Categories of land use in this case for each of the countries and also, of course, between the countries. Click on 3-D Bar and there we have our chart. Now as we can see from this chart, for the larger countries, the largest of them all is Congo, we can see that a very large part of the land is Forest and Jungles, a small part – red – is crop land. If we look at Namibia, there is much less Forest and Jungle, but an awful lot of Meadow and Pasture. So, we get some clear messages from this Bar Chart about the different distributions between these countries. And of course the total size of all the bars for each country represents the total land area of that country. So, we’ve got the additional information of getting an idea of how big the countries are in comparison with each other. So we can see that Ghana relatively speaking is a very small country. The Congo is actually a very large country. In fact the Congo is about the size of Western Europe. So, let’s now change this Chart Type. Sticking with the Bar Chart and going to the next Category, which is the Stacked Bar in 3-D, let’s see what that does. What happens here is that for each of our countries, the total area is represented by the length of the single bar. But each bar is divided according to the Categories of land use. So, for instance, from Namibia a smallish amount of Forest and Jungle, a tiny amount of Crop Land in there, a lot of Meadow and Pasture, and then a smaller amount of Other. For Libya there’s effectively no Forest and Jungle and a huge amount of Other, which of course will mostly be Desert. For Ghana we have a reasonable amount of Forest and Jungle, very small amount of Crop Land and so on. Congo is mostly Forest and Jungles. Now of course this is showing the same information that the previous one showed, but it’s showing it in a different way and obviously it’s a matter of personal preference, which one suits your way of presenting your argument and, of course, the argument you’re trying to present. The bars give us a good idea of the relative size of the countries and the land use within the countries are represented basically by the color divisions, the stacking within the bars. So there’s one © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


more of these to look at. Change Chart type again and the third type of 3-D Bar is the 100% Stacked Bar in 3-D. Now this one is really quite fundamentally different because this one does not give us any impression of the relative sizes of the five countries. We’ve lost one piece of information. And what we have instead is that for each country we have a straightforward percentage split really of how much land is used in each of these four categories. So, again, we can see that within Namibia we’ve got a very small amount Crop Land in there, within Libya we have no Forest and Jungles at all, within Congo most of the land is Forest and Jungle. So we’ve lost one piece of information, but on some occasions this type of Percentage Chart can be the sort of message that we want to put over. So, there we are. In this section, we’ve looked at using Bar Charts to see differences in comparisons. We’ve looked at different ways of changing the Range of Data that’s covered by a chart and some more ideas about formatting. In the next section we are going to look at the Ubiquitous Pie Chart.



Video: Showing Differences Using Pie Charts Toby: Hello and welcome back. In the last section, we started to look at charts and graphs which show comparisons and differences. And in this section we’re going to continue on the same theme and we’re going to look at the ever popular Pie Chart. The Pie Chart is one of the most often used ways of demonstrating or presenting statistical information. It’s actually an extremely good tool, but unfortunately it’s quite often misused. In this section we’re going to start by looking at the basic facilities for building and formatting Pie Charts in Excel 2010 and then we’re going to look at its uses and abuses. Now I’m going to start with a very simple example of a Pie Chart. I’m going to use the data I used earlier concerning land use in a few African countries. I’m going to select the Congo and then select the four categories of land use and then on the Insert tab, Insert Pie, and I’m going to choose the first of the Pie Chart types, which is a 2-D Pie and there we have it. And, in fact, that particular, very simple example tells you a lot about what a Pie Chart is used for. The primary use of a Pie Chart is to show how a particular amount – entity, quantity – is divided up into categories. So we have a whole, which is land area of the Congo and it’s divided into four parts. And the four parts in this example are Labeled 1, 2, 3, and 4. There’s a Title at the top and for each of the four categories we have a slice of the pie. So, number one, which in this case is Forest and Jungles, has the biggest slice of the pie; number two Crop Land, smaller slice of the pie and so on. When a Pie Chart is being drawn by Excel 2010 it always starts with the first quantity at the top, at 12 o’clock and then proceeds clockwise around in the sequence in which those quantities are presented. Now we’re going to look at the formatting of this Pie Chart in a couple of minute’s time, but first of all let’s just go back. I’m going to Undo this chart actually and I’m going to create it again, but this time I’m going to include the Header Row from my sheet. So I’ve now got Congo again with the four categories, but the Header row. Again, if I do Insert, Pie, and just choose the 2-D Pie, see what happens and of course Excel 2010 now knows what those four categories are and you can see according to the color scheme, according to the sequence, that the categories are presented in the spreadsheet, you can see how they go around the Pie itself.



So let’s now look at some of the other options for Pie Charts. If I go to Change Chart Type and in the Gallery look at the different sorts of Pie, there are actually six here. We’ve chosen the straightforward 2-D Pie. Let’s try the Pie in 3-D next. Basically presents the same information, but you can probably just about see that it’s slightly rotated into 3-D. And we’re going to look at obviously increasing that rotation a little bit later on. Go back to Change Chart Type again and now we come to one which is Pie of Pie. Now Pie of Pie we’re going to look at a little bit later on, but here’s a very very simple example of how that works. Basically what Excel 2010 does is to take a part of the Pie, normally the one with the smallest categories in it; the smallest sized categories in it, and explodes that into another Pie, showing how the smaller categories are divided. And as we’ll see later, that’s particularly useful when we have a large number of categories. And if I go to Change Chart Type yet again, there is a final category here which is Bar of Pie. Let me just choose that, which works on pretty much the same principle, but this time the smallest slice of Pie is expanded into a Bar. As I said, we’ll come back to those later on. So finally let’s look at two other options here. One of them is the Exploded Pie and what happens here is that some of the slices are actually taken away from the main Pie. Show this, what’s call this Explosion here. You can actually do this manually from a complete Pie anyway, but this is sometimes a useful option if you want a quick way of showing this explosion effect and again, I’ll come back to the uses for that a little bit later on. And then very last one we have is the 3-D Exploded Pie, which looks like that and again you can just about see the 3-D effect on that. So, these are the types of Pie Chart that we have available to us in Excel 2010. So, I’ve reverted to the 2-D Pie Chart and under Chart Tools on the Design tab, as usual, I’m going to look at the available Layouts. Now with a Pie Chart there are no Axes, so all of the Labeling we use has to be chosen carefully and where there is some sense in which there are quantities we need to be careful about how we show those quantities. Now, if you look at this example, this 2-D example here, you can see that although we can see the relative sizes of these four categories – Forest and Jungles, Crop Land, and so on – there’s no information about the actual numbers on the chart itself. Obviously we can go back and look at the spreadsheet, but there’s nothing on the chart. Now, if you look at the available Layouts and there are actually seven of them, you can see that, for instance, number seven, if I select that, I have no Title; I still © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


have a Legend and I still have my chart. If I choose number 1, have a chart but no Legend, but with number 1 I also get the Labels pointing directly at the slices of the Pie and with each Label with the category name I get a percentage. So, I’ve got both the nature of the land use and the percentages. Number 2 gives me the percentages, the categories along the top and I still have my Title. Now, obviously you can experiment with these and see the one which suits your particular situation, but it’s quite important to recognize whether you actually need to show those values or not and whether you need to show the actual Labels pointing at the slices or using the color key system. And then finally if I choose Layout 4, I actually get the numbers on it and there may be some situations where as in this case you actually want to show the numbers. Now if that were the case I think you’d need here also a Title or something to explain what these numbers mean because 1829174.1 could be just about anything and you need to point out somewhere that this is the area used for that purpose, Forest and Jungles in square kilometers. So, there we are. I have my chosen type for the Pie Chart. I’ve got the Layout I want and now I’m going to look at the Styles, of course I have all of the usual selection of Styles available to me and I can choose from the available ones including, for instance that one. And once I’ve chosen the Style that I like, obviously, I can bear in mind things like fitting in with themes from other documents and so on. So, I’ve now selected my style and I’m going to move onto Layout. So click the Layout tab and you can see that some of the options that are normally there such as arrows related to Axes and Gridlines are grayed out. Of course, there are no Axes or Gridlines with a Pie Chart and we don’t have things like a Chart Wall and a Chart Floor in this type of chart either. So, Chart Title, we can certainly change. We could remove it; we could put it overlaying and so on. And of course just to change the words I click in there as usual and I can directly type in a better title. And for Legend, we currently have no Legend. It’s turned off. I could switch it on. I could, for instance, put the Legend on the right. Probably unnecessary in this case because I’ve all ready got good category Labels on my Pie Chart all ready. So I’m going to switch that off again. And what we’re going to look at next are Data Labels. Now, if I click on Data Labels, I have a number of options here; straightforward ones first. We have, for instance here an option for Inside End for Data Labels. Let’s just see what that does. If © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


we do Inside End what it actually does, as far as it can, is to put the Labels inside the slices of the Pie. Now if you take the example of Meadows and Pastures here, the text of that Label is just a little bit too big to fit in there anyway without making the text very small. So it does as good a job as it can, but there won’t necessarily mean that it will completely inside, whereas of course with Other and Forest and Jungles it can. Outside End means that the Labels are completely outside which is pretty much what we had before. But in fact there is a good level of control over all of the Labeling of a Pie Chart and we do that by using the More Data Label Options there, bringing up a Format Data Labels dialog. Now, in order to show the affect of the options in the Format Data Labels dialog I pulled the dialog over to the side here. You can only see part of the Pie Chart but it will be enough. And basically the Label Options choice here, the page here, the very top one has got three sections. The top section determines what’s actually in each Label. The second one determines position and the third one defines a Separator and I’ll explain what that means in just a moment. So the Contents first of all, you have a number of check boxes here and the ones that are checked are Category Name, so for instance Meadows and Pastures, Percentage, which gives the percentage and Show Leader Lines. Now, let me explain Show Leader Lines first. If you move a Label around, so for instance if I were to grab this Label, particular Other Label here and move it, the Leader Line is the little line that it leads pointing from the Label to the slice of the Pie and I generally speaking always leave Leader Lines on because if I later move or need to move one of these Labels to make some space, the Leader Line is very useful. If you don’t need to move it, then as here you don’t actually see the Leader Lines anyway, so they’re not really causing you a problem. So, we have Category Name, Percentage, and Leader Lines specified. If I uncheck Percentage, note the percentage is now gone and check Value. I get the actual Values in there. Now, this is the point at which I can use a different Separator. If I want to separate the Category Name and the Value, I can choose a Separator here, say, semi-colon and as you can see I’ve now got the Category Name, semi-colon, and then the land area. So I’m going to go back to putting Percentage on. I can, in fact, have Value and Percentage. Note how they now overlap and I need to do a little bit of moving around there to tidy my Pie Chart up. But if I switch off Value again, everything should be okay. © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


So finally within the Format Data Labels dialogue I’ve moved the chart over so that you can see the whole of the chart. We just need to look at this middle section here, Label Position. The options we have are Center, whereas far as possible Excel 2010 places each of the Labels centrally within its slice of Pie; Inside End, where basically it puts each Label inside the end of the slice, so near the edge of the Pie; Outside End, pretty much the same idea, but outside; and then Best Fit which is effectively the Default where it looks at the overall picture and tries to work out the best place to put each of the Labels. So we’ve now seen all of the basic features of using Pie Charts. In the next section, we’re going to look at the special cases and some of the formatting options available to us to deal with more complex and challenging situations. So I’ll see you then.



Video: Limitations with Pie Charts & How to Correct Toby: Hello again and welcome back. In this section, we’re going to continue to look at Pie Charts and specifically to look at some of the issues that we find with Pie Charts and some of the alternatives. So, let’s get started. Now I mentioned before that when a Pie Chart is created, Excel 2010 starts at the top, at 12 o’clock, starts with the first Category and works through the Categories in your data set. Now, you can actually change this arrangement and if you right click on the Plot Area and select Format Data Series, one of the options you have in Format Data Series is one that allows you to change the Rotation. Now the option here, right at the top, Series Options, Angle of first slice starts at zero. You can actually have any rotation you like, but supposing I put 180 degrees. Let’s see what we get. And what happens now, of course, is that we’ve rotated the start to 180 degrees, it still goes clockwise so we have Forest and Jungles first and then the others are down here. Now, of course different people have different opinions as to which arrangement looks best, but what sometimes happens when you do a Rotation is that it means that the Labels that you have on the Categories finish up in a more convenient location. Sometimes of course, as in this one, I think the whole thing looks a bit unbalanced as it is. So I’m going to Undo that and put it back the way that it was. But it’s very much a case that you might want to experiment with this, depending on the data and the particular application that you’re looking at. Now, there’s something else that’s been bothering me about this Pie Chart in particular, and that is that one of these Labels is really quite a way away from the chart itself. Excel 2010 does a pretty good job of positioning these, but sometimes it just doesn’t seem to get it quite right. If you click on one of the Labels, I’m going to click on this one, Crop Land, note what happens. If you click one, they’re all selected. If you click that one again or I’m going to click Meadows and Pastures now, what happens is you finish out with just that individual one selected. And once you have a single Label selected, then with the crosshair cursor, you can move it. So I can move this one, say to here. That’s not quite right, I’m slightly losing the Percentage figures, so get the crosshair back, just nudge it out that way a little bit and that looks quite a bit better to me. Now the next particular feature of Pie Charts is one that takes a little bit of getting used to, but it can be very effective. If I click once within the general area of this chart and then holding the © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


mouse button down, I pull away from the center, you see that ring that’s moving? What’s actually happening is one of the slices, in this case the larger slice, has been exploded, expanded away from the Pie Chart in general. Now because this is the biggest slice, it looks as though the little parts have left it. But if I do an Undo and you watch what happens, you see it’s actually the bigger slice that’s moved. I’ll do a Re-do. So, I can actually select one, or in fact, any number of slices and highlight that slice by Exploding. Now, this is exactly the same effect that we get if we change the type of Pie Chart to an Exploding Chart and I mentioned before that you can actually achieve the Exploding Chart manually yourself. If you right click there and click on Format Data Point, you will find that the Point Explosion Control within the Format Data Point dialog shows the percentage by which it’s been exploded. It’s 24. Supposing I put that 24 back to 10 and then Close again, you’ll see that the explosion is actually quite a bit smaller. So, let me Undo that and Undo again. I’m going to click in the general Chart Area again. This time I’m going to do the same, but I’m going to do it directly without first selecting one slice. So, move the cursor to the middle, hold, pull out, and now watch what happens. What happens now is that each of the slices is exploded away from the center. It may look a little bit like it did before, but in fact all four slices have moved and you can actually see that these three have separated as well. So you can actually explode the whole thing out. Again, if I click, right click now, now because I’ve got all of the slices selected instead of Format Data Point on the Context Menu I get Format Data Series. And if I wanted to say change that from 19% to 10%, I’m actually going to be reducing the Pie Explosion on all four slices. So Close that and you’ll see that they’re all pretty much closer back to where they started from. Now, you can in fact achieve some more extreme effects is I right click Format Data Series again and change the 10% to say 100%, you can see the effect of that. I finish out with slices that are very considerably separated away from each other. You can actually go up to 400%, which you finish out with tiny little wedges a long way away from each other in the chart. So, I’m going to Undo that and there is our Exploded Pie Chart. Now there is another very useful formatting option with Pie Charts, which is used quite a bit and quite affectively. In the style that we’ve selected for this chart, we have all four of the slices with different Fill colors, and in fact if we right click and select Format Data Series and then look at the Fill Option. You can see the check box marked here, Vary colors by slice, if I were to © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


take that off, which would stop the variation by slice and choose a Solid Fill Color, I’m going to choose say that fairly pale color there. I finish out with my four slices all the same color. You can just about read the text in white on the black background, the light blue background for these two slices. Now what I’m going to do is I’m going to click in the Meadows and Pastures slice alone. See the selection points which basically means that at the moment all four slices are selected. If I select, just click once in Meadows and Pastures, note the points there, three points indicate that it’s just this slice that’s selected and if I right click on that slice and go Format Data Point, go to Fill, and change the Fill Color for this point to say that very dark color. When I’ve now effectively done is to highlight one slice in the Pie. Now, I could remove the Borders around the slices, which may make this look slightly more effective. If all I wanted to show really was that Meadows and Pastures has 4% of the whole of land use of the Democratic Republic of Congo, I could do it by this idea of visually highlighting one slice. And, of course, you can visually highlight more than one slice if you wish to. Now, I’m finished looking at that land use data for just a little while. I’m going to look at it again in the next section. What we’re going to look at next is some of the limitations of Pie Charts. And generally speaking, it’s probably unwise to use a straightforward Pie Chart when you get beyond about half-a-dozen Categories. And in order to show that, what I’m going to do is I’m going use this data, which is actually UK Export Data showing the country of destination for UK Exports and the percent share currently and I’m going to Insert just a two dimensional Pie Chart and see what happens. Now, as you can see, one of the problems with this is that there are so many slices that the chart is virtually meaningless. You can see some of the major contributors, such as the fact that United States, which starts at the top, remember we start at the top and go clockwise, is the biggest single export partner with Germany second, France third, and so on. But when you get to the smaller ones it’s almost impossible to see them. Now if I make the chart a little bit bigger, note that at the moment the last listed country is UAE, United Arab Emirates. I can get further down the list, but there are still so many of these countries that it’s impossible really to get a clear picture from this Pie Chart. Now, there are a number of alternative ways of dealing with this and that’s what we’re going to look at next. Now, one way of dealing with this is extremely straightforward. If I go back to the original data again. Look, I have a list of our 20 top export partners from the UK and the final figure, Other © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


20.6% represents a very very large number of other countries. Now, in order to make my Pie Chart more manageable, I could simply roll up more of these countries into the Other figure and perhaps only show the top 6, 7, or 8 countries as export partners. But that’s not really what I want to do in this case. That may solve the problem in particular situations, but here I really want to keep the access to the other countries figures. So what I’m going to do is to use what is called a Pie of Pie Chart. Now, to create the Pie of Pie Chart I go to the Design tab in Chart Tools, click on Change Chart Type, and Pie of Pie is that one. Click on OK and there we have it. Now, the Pie of Pie Chart basically produces two Pie Charts. The first one, this one on the left here, shows the largest share slices, starting with the United States here, going next onto Germany, actually as I hover over each of them you can see the name by the Data Point that pops up and it’s got the top ones right down to Hong Kong and everything below Hong Kong, including the Other Category has been made into this second Pie Chart. Hence the name Pie of Pie. So the Pie, this slice of Pie is made into another Pie, which is then sliced up as well. Now, Excel 2010 uses some Default values to create this. But as you’ve probably guessed by now, you can highly customize this. And let’s start by changing which countries appear in which chart. If I right click on the main Pie Chart and click on Format Data Series, you can see that the split is such that the second Plot contains the last seven values. Now I want an easy way of changing this. If I increase that number, so I go eight values, nine values, ten values and so on. I could perhaps stop at that point, Close, and you can see I’ve got a lot more of these in the smaller Pie now. If I go back into that dialog again, there are a number of ways that you can split the Series with a Pie of Pie. Here I’ve done it by position that is position in terms of ranking in my table of 20. But you can split it by Value, Percentage Value, or indeed choose a Custom way of splitting it. You can change the Pie Explosion Percent, so I could Explode the Pie, say, make that 10% and the Gap Width can also be changed. I’m going to change it from 150% to 50% and the size of the second Plot as a proportion of the first one can be changed as well. It’s set at 75%, let me change that to 50% and let’s see how that looks with all of those changes. You can see that I’ve got Exploded Pie, the Gap is reduced, and the size of the smaller Pie compared to the bigger Pie, it is itself smaller. So, there are plenty of opportunities there to customize the Pie of Pie and it’s a much better alternative when you really do want to use a Pie Chart, but you have a very large number of © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


Categories. The other type of chart that can achieve this sort of effect is the Bar of Pie here. Click on OK. It’s exactly the same principle but this time you get a Bar Chart on the right. Now of course you can put Labels, Percentages, Values, and so on on here to format this. And one other outstanding point, it’s quite often the case when you have a large number of Categories and you’re trying to use a Pie Chart or a variation on a Pie Chart like this, you can’t actually list all of the Categories. Sometimes you have to experiment with rearranging the size of the Legend and in fact here if I drag the top of the Legend up and the bottom down a bit, you can see I can actually just about fit all of those country names, including Other at the bottom in. So, that’s it on Pie Charts for themselves at the moment. In the next section, the final one where we’re looking at differences and comparisons, we’re going to look at some of the alternative ways of representing this sort of information. I’ll see you then.



Video: Alternative Ways to Show Differences Toby: Hello and welcome back. For this last section on differences and comparison, we’re going to look again at the data on land area use in some African countries and we’re just going to quickly recreate the Pie Chart that we had for land use in the Congo. Now, I’ve selected the data and Insert Pie 2-D and there we are. Now, one of the alternatives that we looked at before for showing this sort of information was the Stacked Chart. So, let’s go back now, change the Chart Type, go to Bar, and choose 100% Stacked Bar in 3-D; click on OK. Now in order to get the correct affect here we need to switch rows and columns and on doing that we see that this type of Bar Chart shows us pretty much the same sort of information that we could see with the Pie Chart, the distribution, Forest and Jungles, Crop Land, Meadows and Pastures, Other. And obviously this is an alternative to a Pie Chart. It is an alternative, but it also has some advantages as well. And one of the most obvious advantages I can show you by grabbing the edge of the Data Area, dragging it down, say, to cover up three countries and as we saw before, we can actually have any number of these bars and not only for each bar can we see the percentage of land use in each of our four categories, but we can see three countries at the same time so that we can draw a comparison between them as well. Now, in effect this is not possible with a Pie Chart because with a Pie Chart we show the composition of one entity, the breakdown of one entity. We can have many categories within it, but we’re still talking about one entity. We could, of course, in a report put a number of Pie Charts side-by-side but it doesn’t have the obvious way of doing that that this use of a Bar Chart does. Now there is an alternative with Pie Charts, which I’m going to show you next. But it’s worth baring this Bar Chart option in mind when you have more than one entity whose breakdown into components you want to show. Now, the particular chart that can be used for this purpose is a Doughnut Chart. And if we change the Chart Type, scroll down, we find Doughnut. There are actually two variants; there’s the Exploded Doughnut and the straightforward Doughnut. So, I think you’ve probably worked out by now what the Exploded Doughnut is. Let’s just do the straightforward regular Doughnut. Click on OK and you can see in fact that we have four Doughnut rings, which are clearly the land use categories. They’re not the three countries. And then within each of the four rings © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


we’ve a colored bar for each of the three countries, which is really not what we want at all. Now although there are no apparent Axes here, we have the usual problem that we get which is we need to switch the rows and columns to get the affect we need. So, switch them over and okay, we’ve now got three rings. That’s one for each of the three countries and then for each of those countries we have four bands in it corresponding to the four types of land usage. Now, I’m not a particularly great fan of the Doughnut Chart, but I do use them from time to time. And you will see them and use them from time to time yourself probably. So let’s just go through some of the fundamentals of formatting one of these. We have Chart Layout Options, of course. We can choose to have Legend, Title, and so on. So, I would suggest that in this case we stick with the basic layout we’ve got, but perhaps include a Title. I’m going to edit the Title to say “Land Use by Category” and we have the percentages to support the visual size of usage for each of the three countries. We can’t currently see what the three countries are, so let’s right click on the Doughnut itself and say Format Data Series. Now then, within the Format Data Series dialog we can specify a number of options that we’ve seen before. Basically we can change the angle of the first slice from zero degrees or 12 o’clock. We can, of course, explode the Doughnut and change the Percentage explosion here. And we also have an option here to increase or decrease the size of the hole in the Doughnut. If you make the hole quite a bit bigger, let’s say put it at 75%, click on Close. The bars, of course, get a lot thinner. But depending on the number of rings in the Doughnut, you may or may not need to make the hole bigger or smaller to make it an effect. And sometimes people actually put some sort of graphic in the middle of one of these holes. You may need to change the size to make up for that. I’m going to set that back. Again, right click on the Doughnut itself now and this time we’re going to say Format Data Labels. Now in terms of formatting the Labels on this Doughnut Chart, it’s actually quite tricky. If I move the dialog a little bit, you can see that we have the Legend with the types of land use, which is fine, corresponding to the Color Key for the parts of the Doughnut rings, but the Labeling of the countries is quite difficult. We could have a system of pointers and so on coming in here, but the facilities that are built into the Doughnut Chart in Excel 2010 do sort of work after a fashion, but they usually take quite a bit of work to get them to look reasonably good. At the moment, the Doughnut rings we’ve got only show the percentages in each of the © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


sectors. If I put in the Series Name, which will be the country in this case, let me just show you what happens. For the ring that’s selected, which is currently the middle one, the one for Egypt, I now see Egypt followed by the percentage for each of the rings. I could remove the percentage. It’s a little bit easier to see Egypt because it says Egypt everywhere around the ring. There isn’t a really particularly convenient way of overcoming that problem. And I could select each of the rings in turn, so the outer one, and again click on Format Data Labels, Series Name, Remove Percentage, and I could actually set all of the countries up like that, but I’m not sure that whatever I do is going to make it look particularly helpful. But in a particular situation one thing that can help is if you click on Format Data Series and reduce the hole size, say to 20%. Let me now close that. You can sometimes arrange things as I’ve got here, so that a combination of the Legend, the names of the Series – in this case the country name, possibly with the Percentages as well, and a bit of judicious sizing of the central hole. You can sometimes make something that’s reasonably readable, but it usually takes quite a bit of work with a Doughnut Chart to make something that gets over a clean, clear message. So, we’ve looked at Pie Charts and Bar Charts in relation to differences and comparisons and right at the beginning of this look at differences and comparisons I mentioned that we sometimes steer clear of Column Charts because of the general tendency for people to associate Column Charts with progression over time. But in fact from a validity point of view, of course, they’re perfectly valid way of doing things and pretty much you’re going to use the same ideas and approach with Column Charts that you do with Bars. So I’m just going to finish off this section with a quick look at some of the Column Charts. Let’s stick with our land use by category Doughnut Chart where we have three countries and the land use in the four categories. Let’s go to Change Chart Type, to show just how straightforward this is. Let’s go up to Column and let’s choose just a 3-D Clustered Column, click on OK. Now, again, we need to interchange our rows and columns and we have Congo, Egypt, Ghana. We have some Data Labels on there that are going to confuse things. We’ve got a Legend as well. So let’s go to Layout. Let’s say no Data Labels; makes it a bit easier to read. The Gridlines are probably fine to give us a good idea of the actual values, although we don’t have any units shown here, so maybe we should just change the Design. Let’s choose one which has got a Title, a Vertical Axis Title, and a Legend. That’s fine. Now click in the Axis Title. I’m going to actually put an Axis Title in there. I’m going to say “Land Area” and then in brackets, square kilometers, tick, and that’s pretty much it really. © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


I’m quite happy with that. There’s a Horizontal Axis Title there that I don’t want, so I can select and Delete. So that’s a perfectly good Column Chart representing my information. Let’s once again change the Chart Type. We used the 3-D Clustered Column. Let’s try this one, the Stacked Column in 3-D. Now, this one has the added advantage that we have the total area for each of these three countries and then for each of the individual countries we can see a visual split of the percentages of land use by each of our four categories. Now for the final one of these three standard options, go back into Change Chart Type and this time we’re going to look at 100% Stacked Column in 3-D. What this option gives us is the percentage breakdown of land area use for each country, but it shows 100% for each country in the vertical direction. Now there are a couple of things to be careful of here because this is the sort of chart where people can easily be misled. First of all, if you looked at this without being careful, you might think that all three countries have the same land area, which you need to be careful of. Obviously that’s not the intention here at all and at the moment, in effect, we’ve got an arrow here because on the Vertical Axis, which is clearly marked with percentages. We’ve still got the Axis Title saying “Land Area (square kilometers),” so click to select that, press the Delete key, and it’s gone. And then at least we don’t have that element of confusion going on. Somebody reading this carefully will see we’ve got three countries. We’ll see we’re only really telling how for each of those countries the land use is divided, but it’s one to be careful of. So, that’s the completion of this review of the approach to showing differences and comparisons within Excel 2010. In the next section, we’re going to start to look at relationships. So I’ll see you then.



Chapter 5 – Relationships Video: Using Chart & Graphs to Show Relationships Toby: Hello again and welcome back. In this section, we’re going to look at charts and graphs that show relationships and the data we’re going to use to begin with are some data on the sales of insurance policies for the Las Vegas branch of a particular insurance company. The data here in this sheet, I have two years worth of data covering 2008 and 2009 and I have a season column that I’ll talk about later on. A column that contains the number in enquiries and a column that contains the number of policies sold. And we’re going to start to look at the relationships between these variables. Now, the first thing I’m going to do is to create a Scatter Chart for this data and the data I’m going to use begin with are the months and the number of enquiries. So having selected those two sets of data, click on Insert, click on Scatter, and we’re basically going to draw a Scatter with only markers. And there we are. Now, of course, the Scatter Chart I’m drawing here is really showing a Trend over time and I can format this Scatter Chart using all the normal tools available to me. Let’s have a quick run through of those. On the Design tab we Alternative Layout, so we can choose to have Title, both Axis Titles, and Legend with that first option and then the other options gives us different combinations of those things. Obviously we can go in, change our Axis Titles, and so on. And as you can tell from the points in the Scatter Chart, there seems to be a general upward trend in the number of enquiries at this branch over the period that we’re looking at, which is a period of two years. But, in fact, it’s not Trend that I want to look at now, it’s Relationship and what I’m trying to do is to identify the pattern that there is in these enquiries at the insurance office. Now clearly they’re tending to increase over time, but what are the other dependencies? What are the other ways in which these vary? Now what I’m going to do is select the chart, just Delete it, and go back to the data and just look at the data again. One of the things that I suspect is that there is a relationship between the number of enquiries and the time of year. So we’ve all ready seen that there’s a general tendency for enquiries to increase over time, but is there some kind of pattern © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


within a year which makes a particular part or parts of year more busy than others. So what I’m now going to do is I’m going to draw a new Scatter Chart, but this time I’m going to Plot season of the year against enquiries. Now, season of the year can be defined in a number of ways. The way I’m going to define it here is I’m going to define it by month number. So, I’ve written a little formula using the function Month, which for instance here says the month for Jan 08 is 1 because it’s the month in cell A2, which is Jan 08 and then I’ve filled that formula down the whole of this column to give me the seasons, which obviously go from 1 to 12 right down to the end of 2009. So let’s select all the data, Insert, Scatter, we’re going to do a simple Scatter Chart again and let’s see what we get this time. Now what we get this time is a very different picture because we’re now plotting what is effectively month number from 1 to 12 against number of enquiries. It’s a bit more difficult to see a general pattern, although we can see that in December, for instance, we tend to get more enquiries than we get in January. Now, I’m not going to format this chart just now, we’ll come back to that a little bit later on. What I am going to do though is I’m going to go to the Layout tab and select Trend Line because I’m going to put a Trend Line onto this Scatter Chart and I’m going to start with a Linear Trend Line. Now, what Excel 2010 does is to use a mathematical formula to place a Trend Line onto my graph. And it will always put a line. What that line is trying to do is to show me whether there is a relationship between the two variables that are plotted. And the two variables I’ve got plotted are, of course, the number of enquiries and the season of the year, specifically the month of the year. And if the line goes from down on the left to up on the right, it is very likely that the two variables are related in a positive way. What that means is that as one increases, the other increases, which loosely speaking means that the lowest number of enquiries is early in the year, the higher number later in the year. If the line went down from left to right, then we would say that the variables are inversely related. So, we do seem to have what is called a Direct Relationship between the number of enquiries and the month and in order to quantify that we can go back to the Trend Line button again and look at More Trend Line Options. Now on this, we have two options at the bottom, which we’re going to look at in a moment, but you may recognize part of this. It’s very similar to the forecasting we had when put a Trend Line on much earlier on and indeed we can with Scattered Charts use these for forecasting, which we’ll look at later on. We can name the Trend Line and © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


so on. But the two things we’re interested in at the moment, particularly are the bottom two. One of which is Display Equation on chart and one of which is Display R-squared value on chart.

Now, these two actually define the relationship between the two variables.

Now

unfortunately it’s going to refer to our Vertical Variable as Y and our Horizontal Vertical as X and what it says is that the relationship, such as it is, is that Y is just over 6 times X plus about 105. The R-squared value is referred to as the correlation coefficient and that correlation coefficient tells you how closely related the two variables are and if the correlation coefficient is close to one or minus one it indicates that the two variables are actually very strongly related. In a case of a direct relation it will be positive, so we’ll be looking for a value near plus one. In a case of an inverse relationship it will be negative, so we’ll be looking for a value near minus one. Now a value of 0.315 is not a particularly strong correlation, so although there is a relationship, it’s not a very strong or convincing one. Now once again I’m going to Delete this chart, go back to my source data and this time, again, I’m going to draw a different chart. And this time the chart that I’m going to draw is going to be a chart comparing the number of enquiries with the number of policies sold. So select that data, Insert Scatter, same sort of Scatter Chart, increase it in size, and again go to the Layout tab, choose Trend Line. Trend Line Options; I’m going to choose a Linear Trend Line. I’m going to name the Trend Line this time and call it “Policies vs. Enquiries” and I’m going to display the equation and display the R-squared. Let’s see what we get. Now, again, I’ve got a formula, Y is 0.1869x + 9 and the R-squared value indicates that there isn’t a particularly strong relationship between the number of enquiries and the number of policies sold, which is probably quite worrying from a selling point of view because you think that if you deal with your enquiries efficiently, you should be making more sales when you get more enquiries. So that would be quite disappointing. Now, while we’ve got this particular Scatter Diagram on screen, let’s look at the alternatives as far as Scatter Diagrams are concerned. Go back to the Design tab, Change Chart Type. With Scatter the options we have are the one we’ve got all ready, which is Scatter with only markers, Scatter with smooth lines and markers. Now in the case of this sort of data, the smooth lines don’t really add anything much for us. These don’t imply any kind of sequence or relationship between those. Again, Change Chart Type again. We can have a Scatter with smooth lines and no markers. We can have Scatter with straight lines and markers, Scatter with just straight lines. So just to show you the last one to see what it’s like. Again, in this particular © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


case, there isn’t really going to do anything for us. So, once we’ve got our Scatter Chart and we’ve got our formula and coefficient, we can actually put those somewhere else on the chart if we want to and then we could actually set about formatting this chart. So, we’re going to the Title. I’m going to change the Title to Policies Sold Compared to Enquiries. I’m going to look at the possible Chart Layouts up here because I actually want everything on here. I want the Title, both Axes Titles, and a Legend. So that’s the first option for me. I’m going to change the Vertical Axis Title to “Policies Sold.” Tick there. Select the Horizontal Axis Title, “Enquiries Received.” And what I’m going to actually do now is say this is not a time based chart. I’m not talking about time going that way. I’m saying that as the number of enquiries increases, generally speaking, the number of policies sold increases, but I can still use the principle of the Trend Line to do some forecasting. So, for instance, suppose that I wanted to see based on the Best Fit Line here, how many enquiries I would need to get to say, sell 100 policies in a month. What I can do is, I can go back to the Layout tab, put the Trend Line on there. I’m going to stick with the Linear Trend Line and I’m going to go into More Trend Line Options. Now, I’m going to change the Trend Line name, which I’ve typed in there, “Policies vs. Enquiries.” I want to see the equation. I want to see the R-squared value, but I am going to forecast forward, say, let’s say 200 periods. So that’s 200 enquiries. Let’s say 250 enquiries and see how it looks. Now when I do that, notice what happens, the Trend Line is extended. It’s still based on the existing data, but it’s extended forward and, in fact, quite fortunately for me, with 500 enquiries the Trend Line indicates that I will sell 100 policies. And one of the things I need to bear in mind with this is how confident I am in that figure. Now an R-squared value of 0.26 is not particularly convincing and not something I’d put an awful lot of weight in, but it’s all I’ve got at the moment. So, that would be a reasonable target to aim at and give me a good idea of how many enquiries I would need to get in a particular month to sell 100 policies. So, in this section we’ve looked at how to build a Scatter diagram, how to format it, and how to use it to identify relationship between variables, and to use it to forecast. In the next section, we’re going to continue to look at relationships and we’re going to look at some of the other major options available to show these via graphs and charts in Excel 2010. I’ll see you then.



Video: Using Charts and Graphs to Show Relationships Part 2 Toby:

Hello and welcome back.

In this section, we’re going to continue looking at

Relationships, but this time we’re going to look at ways of presenting Relationships based on the techniques that you’ve learned but being a little bit more inventive. We’re going to start with the information about insurance policies sales again, but I’ve got a simplified version of the data and all I have for the two year period that we had data for is by month – Jan, Feb, Mar – the number of enquiries, and the number of policies. And we’re going to put together a simple graphical representation of this relationship. Now, the first thing I’m going to do is to make all of the enquiries figures negative. Now the easiest way to do that is to create a formula. So I’m going to put a formula in here of =-B2, which puts a value of -241 in there. Select and fill down with the shortcut Control-D. Copy those values and then do a Paste Special, which is effectively a Paste Values into there to make those numbers negative and then I can get rid of the column that I used. So what I’ve now got is a negative number for each of the enquiries. Now, I’m now going to give you some idea of what we’re trying to achieve with this particular exercise. If I select all of the data now and do Insert Bar and then do a Stacked Bar, the chart that I get, I’ll just make it a bit bigger for you, the chart that I get effectively shows the enquiries pointing that way, the policies pointing that way, and apart from the fact I’ve got these negative numbers, it’s a representation showing for each month of the year the relative numbers of enquiries and policies. And it’s quite a good representation. It shows that in May we’re pretty busy on both fronts and then particularly in December we’re busy, although we get more enquiries in December but not quite so many policies. So, the balance of business in May is maybe not so good, more policies for the number of enquiries. So it’s only really these negative numbers that create us a problem. So, let’s look at how we can deal with that. An important thing to realize in Excel when you’re formatting cells is that no matter how you format the cells you won’t actually be changing the underlying values in the cells. So if I take these negative numbers here under the enquiries, go into Format Cells, and instead of using Conventional Format for Number, go into Custom and use a Custom Format of 0;0. That will actually present the numeric values, but without any leading negative sign, any minus sign. © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


Click on OK and my values, the underlying values are still negative, but they appear as positive, and in fact, the chart gets updated accordingly as well. The Scale pointing out from zero on the left now shows positive numbers because the chart is based on the numbers as they are seen here rather than the underlying values. Let’s do a little work now on the chart itself. Go into the Design tab, look for Chart Layouts. Let’s go for a chart with a Title and Legend at the top. And Axes, yeah, I think that one will probably do the job. Chart Title there and I’ve got the Legend at the bottom and I still show my numbers down here. Chart Titles is “Enquiries and Policies by Month.” Okay. So I’m very nearly happy with this, but there are still a couple of problems with it. One of them is that the Horizontal Scale to the left of the zero is much longer than the one to the right on the zero. Excel 2010 has automatically decided on Minimum and Maximum points for both of these. So, I notice that the largest figure under enquiries is 402, so I need to go out to 500 but I need the right to out to 500 as well. So if I go into Axes, click on Primary Horizontal Axis, go into Options, and change the Maximum to 500, Fixed, and the Minimum to minus 500 fixed. Close that. That gives me a much better representation of the relative sizes and the other thing that I might find a little bit annoying is the fact that the months are in the bars and actually they’re visible, but the coloring means that they’re not quite as visible as I’d like them to be. So let’s fix that as well. This particular problem is also solved using the Layout tab Axes button and then we go to Primary Vertical Axis, go down to the Options, and within the Options you can see that the Axis Labels are next to the Axis. I have an option of setting them at the low values, which for this is obviously -500 and there you can see the month numbers are now over here. Just click outside and the Vertical Gridlines are a good way of reading off the numbers. I can put in an Axis Title here if I want to. I think it’s unnecessary to point out that they’re months. And there we are. That’s a pretty good visual representation of the relationship between enquiries and policies by month. For the rest of this section we’re going to look at one or two other examples of charts that can help with the purpose showing relationships. And the one we’re going to look at next is the Bubble Chart. Now the Bubble Chart is one which is sometimes misused. It’s important to © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


understand situations in which a Bubble Chart can be used. The basic situation in which a Bubble Chart is used is where you have three values of three variables. And because you’re drawing a two dimensional chart, in order to show the relative values, the relationships for the three variables, the third variable becomes the size of the point in the chart. It’s also important to recognize that none of these variables are actually Categories. They are all values. Now I’ve got some data here relating to demographics in five African countries. For the five countries I’ve got, I’ve got the unemployment rate in percent, the population in millions, and the GDP per person in U.S. dollars. Now generally speaking to draw a Bubble Chart, you need to select the data without any of the headings and then we add the headings, Chart Titles and so on afterward. So, having selected the data, the next step is to do Insert, Other Charts in the Charts Group, and Bubble. Now there are two sorts of Bubble Chart. There’s the Bubble Chart itself and then there’s a Bubble with a 3-D effect. Let’s go for the one with the 3-D effect. And we get this rather smart looking chart with two axes and bubbles. Now, the first axis, the X axis always corresponds to the first column of data or the first range of data that we’ve chosen. Now in this case that’s the unemployment rate along here. The Y axis will be the second column, which is the population in millions. And the third column, the GDP per person is used to determine the size of the bubbles. Now a simple analysis of this Bubble Chart would say that the highest GDP per person corresponds to countries with the highest unemployment rate, but the lower populations. Now obviously that’s not universally true because this one here is an exception; this is actually Morocco. But as a general rule we get the larger size bubbles here where the unemployment rate is higher and the population is lower. This largest one here is actually South Africa. So, let’s make that chart a little bit bigger and then let’s start to format it. Now for formatting we can select a Chart Layout on the Design tab, so we’ll go for Layout 1, which gives us a Title, both Axes Labeled, and a Legend over here. So I’m now going to put in a Chart Title of “GDP per Person Based on Unemployment Rate and Population Size”; quite a big title there. Then down here I’m going to put “Population (millions)” and here “Unemployment Percent.” Okay. So I’m just going to Delete the Legend because it’s not really going to help us. We’ve got the nature of the information in the title, GDP per Person. One other thing to point out here, if you look at the size of the bubbles, if you go into Format Data Series, there is an entry in there about © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


what the size represents, Area of bubbles or Width of bubbles. Now, generally speaking, you should always choose Area of bubbles because the relative values for this third variable, in this case the GDP, will if you do them as Width of bubble basically be squared compared with the actual relationship. So, for instance, if you had one countries GDP twice as much as anothers, if you based the width of the bubbles on that you’d finish out with a bubble that was actually four times as big instead of twice as big. So you should always base it on Area. And the other thing is if you find that the bubbles are either too big or too small to make the point, you can Scale them. So, I could say set that at 50 and then all the bubbles come out smaller. Now, generally speaking, you wouldn’t use a Bubble Chart with more than maybe half a dozen values anyway. So overlapping shouldn’t be a big problem, but you might need to use that in some situations. So finally let me go back to the Design tab and look at the available Gallery of Styles. If I say choose this one and I finish out with a pretty good looking chart showing GDP per person based on unemployment rate and population size for five countries. Now, of course, I could put Labels onto show the five countries and so on, but as you can see it’s quite an impressive sort of presentation. Now, the last type of relationship chart I’m going to show you is a Radar Chart and the example I’ve got here is a pretty straightforward one. The Radar Chart is generally used where we are comparing a set of values with either another set of values or often with some sort of target or benchmark set of values. Now, here I’ve got a situation where a student has a set of target grades on a scale of 1 to 5 that the students been set and the scores in the latest tests are over here. So the target grade on Mathematics was 4, the latest score is 5. Now if I select all of the data, so that’s the list of subjects, the target grades, and the scores. Click on Insert, click on Other Charts, and then Radar. I get this rather interesting Radar Chart. Now what the Radar Chart shows is the Categories are around the outside, so you can see all the subjects – General Studies, Modern Languages, English Language, and so on. And then as shown by the Legend, the target grade in each case is the blue line and the other line shows the actual score. So we can basically say where the student has gone outside the blue line, they’re performing well because they’ve exceeded the target score and when it goes inside the blue line, such as in History and Social Studies, they haven’t matched their target score. So, that’s a Radar Chart.



So we’ve now seen most of the types of chart. There’s still a few to go, but the main ones we’ve looked at. And in the next section, we’re going to start to look at specific applications in relation to the stock market. I’ll see you then.



Chapter 6 – Stock Video: Charting and Graphing Financial Information Toby: Hello and welcome back. In this section, we’re going to start to look at charting and graphing of financial information and in particular stock and share prices. We’re going to start by getting some data to actually work on and I’m going to show you how to get that from one of the most straightforward sources that you’ll find on the internet. Now we’re looking at one of the pages on the website of the Financial Times based in London, probably the best known source of financial information in the world. We’re not actually going to use the FT site, part of it is only accessible by subscription and, in fact, in some ways the FT site can be quite a complicated one to find your way around. There are one or two little interesting things here just to point out. For instance, here, Sector Performance here; this little graph in the middle here is an interesting one and we’re going to be looking at this later on in the course when we look at things called Sparklines. And these are a fairly recent innovation and they’re cropping up all over the place, particularly on this sort of financial site. The site we’re going to use for our data to actually demonstrate the various stock and share price charting options is actually Yahoo. Now is you access Yahoo and click on the link to Finance, you’ll get a page something like this. Now, in fact, depending on where you are, you may have a local Yahoo page. This is actually Yahoo for UK and Ireland. The URL is uk.finance.yahoo.com. I’m going to use what’s probably the most widely used one of all, which is really the core Yahoo finance page, it’s basically the U.S. one and I’m going to go to that page. So it’s finance.yahoo.com and that takes me straight into Yahoo Finance. Now on this particular occasion all I want to do is get some prices to show you how the various charts and graphs work and how to build them. So I don’t really mind what the prices are for. In the base page here there is a box here where I can enter a ticker code. If I start to type, I put in the letter A, everything beginning with A starts there. Let’s put in B. I’m now looking at the ABs. Let’s try Alliance Bernstein Holding L.P., which is in Equity, as I can see on the right there, traded on the New York Stock Exchange, NYSE. Click there and I have now information © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


about Alliance Bernstein Holdings. Now, there’s good summary information down the page. The thing I’m particularly interested in is over here on the left. It’s Historical Prices. Now I’m going to look at those Historical Prices in just a moment, but first of all when we’ve done this charting, make a point yourself of coming back and looking at some of the charting that’s available on Yahoo. I think it’s fair to say that when Excel started offering charting and graphing facilities specific to finance market quite a few years ago now, the charts that were produced were deemed to be a pretty authoritative and good quality and useful charts. Now that so much information is available on the internet, in many ways, some of the graphing and charting you get on websites such as Yahoo, somewhat overshadows the charting and graphing facilities in Excel. So I’m not saying there’s anything wrong with the graphing and charting facilities we’re going to look at because they’re still perfectly adequate and they can do a good job. But what’s been done on the internet, in some ways, is soaring forward, as with most things on the internet. So, when we’ve actually produced some of these charts, come back and have a look at these yourself. There’s even interactive charts where you can actually tune the chart to your exact requirements as you go along. But here we’re interested in Historical Prices, so that’s where we’re going to go next. So what we’re interested in are the Historical Prices. When we get to that page we can see we have a table of prices. On each row of the table there is a day and on the day we have an opening price, high price, low price, closing price, and volume traded. On the right, we have adjusted close which is basically the closing price adjusted for dividends and splits. And on some, in some cases, such as this day on May the 11th we have a dividend payment. Now just above the chart, we have the date range covered by this list of Historical Prices and we can set it to any date range that we want. I’m going to change those dates in just a moment. We also have a choice here of showing prices daily, weekly, monthly and we can also show only dividend payments. At the bottom of the table we have confirmation that currency is in U.S. dollars and we also have these buttons that let us take us through successive pages. Now for this particular equity we’ve got prices going back to 1988, so you can imagine on a daily basis there are going to be quite a few of these pages. Now we’re not going to work with over 20 years worth of data, to begin with here anyway. We’re going to take a smaller sample and just start to work on these graphs and charts on a smaller sample. But if you wanted to work your way through, you’ve got a Next button, obviously takes you to the next page in the table and then you’ve got First, © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


Previous, Next, Last. I’m sure you’ve used that sort of facility before. We’re much more interested though in this one, which is Download to Spreadsheet where we can actually basically take any selected amount of this data and download it to a spreadsheet. That’s what we’re going to do, but first of all let’s go and choose our data. And I’m going to get daily prices and I’m going to download to a spreadsheet. Now when I click to Download to Spreadsheet, it actually gives me the option of either opening the spreadsheet file or saving it. Now, I could save it. It will be saved as a .csv file, Common Separated Values file, and I could give it a suitable file name and then it would be available to me to work on later on. On this occasion rather than save it, I’m just going to open it and it will in fact open it in Excel because my installation of Excel is set up to treat .csv files as Excel spreadsheets. So I’m going to Open and once it’s downloaded the data I should see it all available for me to work on in Excel. Okay, well here we are. Excel has opened. I normally get it in some not quite maximized form, so let me just maximize the window and maximize the sheet within the window. It will quite often be the case when you’ve downloaded data like this that the Date Column, the A column will be too narrow, so let me just make that wide enough to see the date. And the other thing to note about the date is depending on which country you are working in or set up to be working in, you may or may not have the dates in the sequence as me. Now the dates that were in the data I downloaded were in the U.S. notation of month, day, year, but Excel is clever enough to know that because I’ve got them set on UK settings, they say day, month, year. And provided you’ve got Excel set up properly, it should adjust them to whatever is good for you. So, if you’re looking at this from a U.S. site and you’re looking at these dates and thinking they’re the wrong way around, well that’s the way we them in the UK and it really. What I’m going to show you from now on, it makes absolutely no difference which format you’ve got it, as long it’s the right one for you and it makes sense to you. Now, we have a couple of other things to do this data. One of the things we’re going to do is to freeze the top row so that we can always see that as we scroll through the data. So, go onto View. Go onto Freeze Panes, Freeze Top Row. And then I’m going to sort the data. Now, if you’re familiar with sorting in Excel, if I want to sort it on date, I can click that column, go onto data. If I just click on AZ, which is sort oldest to newest, A to Z, as you’ll know, Excel warns © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


me, “Do you want to expand the selection to include all of the columns rather than just sorting that column?” Of course, I do want to expand the selection and there we are. I have my data and a very large number of records for all the days, for all of those years and I may choose to Delete some of this data to make it a bit easier to work with later on. But for the moment you can see going right back to the 15th of April and right up to yesterday I now have my prices available and ready to work on. Now, I need to explain a little bit about dividends and splits before we go any farther. Partly to explain the meaning of adjusted close. If a company pays a dividend on a particular date, then closing prices before that date are reduced by the amount of that dividend because it’s deemed that the value of the particular stock is actually reduced by that amount because basically that amount of money is being given away by the company. So, what happens then is that the adjusted close is changed to account for that dividend payment. Now, in fact, that has no real effect in terms of how you proceed with graphing and charting. It’s important to understand though that it happens, because it partly explains the difference between close and adjusted close. In fact, if I go back to Yahoo there is an option to show dividends only and if I do that for this particular equity, we can see that they generally pay the dividend about once every three months. And that partly explains the difference between closed and adjusted close. Now, let’s take a look at Stock Split because that is also important. Again, I’m looking at the same equity and back in 1998; this company did a 2 for 1 stock split. What that means is that people who held shares in this company, the number of shares that they held was doubled, which in effect halved the value of each of them. And if you look at the closing price, you’ll see that it pretty much halved between March and April, I’ve got monthly figures shown here. So, if we actually plotted that price, it would appear that they suffered about a 50% drop in price, which was purely because of this Stock Split. Now, what happens here is that the closing price is adjusted to make it appear that the split appeared much earlier in time. Then in fact there was no real split in stock and the adjustment is made to the earlier closing prices to compensate for what is an apparent 50% drop in value. So, the company basically halved the values of the shares, but the closing prices halves that because, of course, the overall value of twice as many shares at half the price is pretty much the same. © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


So, we’re just about ready to start now. I’m going to only plot the last 10 years worth of this data. So I’m going to Delete quite a bit of it from the spreadsheet and then I’m going to check to see if there are any splits in the range that remains. I can do that by going back to Yahoo and just having a quick scan through the prices over the last 10 years from 2001 to 2011. Okay, I’ve checked over that range of 10 years. There were no Stock Splits in that range. So now I’m going to do one final thing, which is the other problem with the data we’ve got at the moment is that the high and low prices are against close. They are not adjusted. And I need to adjust those as well, because it’s the adjusted close price that I’m going to use for most of the charting and graphing that I’m going to do. Now, the recommended way to adjust these is to adjust each of them in the ratio of adjusted close to close. So, for instance, if I Insert a column here, put a heading in of adjusted high, the formula I want in here is equals the actual high, which is C2 times, and then it’s the ratio of adjusted close which is H2 to close, which is F2. And that gives me my adjusted high, which obviously I can format, fill down, and then do the same to produce an adjusted low. So, there we are. I’ve now got my adjusted high, adjusted low, to go with the adjusted close, and I’m just about ready to start working on these graphs and charts. I’m going to start that in the next section. So I’ll see you then.



Video: Charting & Graphing Financial Information Part 2 Toby: Hello and welcome back. In the last section we downloaded some data from Yahoo and we processed it to adjust the high and low prices to go along with the adjusted close prices and we’re now going to start to produce some graphs and charts to illustrate the fortunes of this particular equity. Now, when we downloaded the data, it came down in .csv format and what I’ve done is I’ve actually saved it in Excel 2010, .xlsm format. So make sure you save your spreadsheet, if you’re working along here, perhaps using a different stock yourself. And at the moment the data is arranged in ascending date order, which is how I need it. Now the first thing I’m going to do is to plot the closing price as a simple Line Graph, a very straightforward way to start. So, we select column A, which is the date column. Hold the Control key down and select adjusted close, which is column I at the moment. Onto the Insert tab, Line is one of the other options and from the available Line Charts, let’s just do a straightforward 2-D Line. And there we are. Now let me just make that a little bigger, move it very slightly, and there we are. That shows the adjusted closing price for this particular stock over a 10 year period. Now, I want to change a few things about this chart. We really don’t need that Legend. The Legend only says Adjusted Close, which we know that that’s what that’s plotting and let’s change the Title. And we could, if we wanted to or needed to, change the Scale on the Vertical Axis so that we didn’t include parts of the Scale above and below the Max and Min. Now in this case the Minimum does go below 10, the Maximum does go above 70. So, the values for the Scale, which Excel 2010 has worked out are actually pretty good in this case, so I’m not going to make any changes on the Vertical Scale. However, I would like an Axis Title on the Vertical Axis, so let’s just go to Layouts and I think this layout would probably be more suitable. That’s right. That gives me the Title I need. Let’s just type on there, we’re going to put “Adjusted Closing Price in U.S. Dollars,” click on that. Probably don’t actually need “Adjusted Closing Price” in the title, so let’s get rid of that. Over here, can go again. And now the only thing I want to change now is the Labeling on the Horizontal Axis, so right click there, Format Axis. At the moment, I’m quite happy with the fact that we’ve got a tick every year and so on, but I don’t particularly want this date the 11th of May © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


roughly in the middle of each year. So, I’m going to go to the Number page and the Format Code I’m going to use, I’m just going to put the year on there. Let’s try closing that. Now that’s nearly right. That actually gives me two per year, which is not really what I’d intended, so let me just change that again. And in terms of the Major Units, I want that to be Fixed at 1 year and the Minor Units, I don’t want those at all. So, I’m now pretty happy with that chart, but I’m going to do one more thing to it and this is quite a common and popular requirement, which is that I’m actually going to also show the volume of trade on the same chart. And there’s a particular way of doing that that gives a pretty good effect. Now what we’re going to do effectively is to draw two charts on the same graph. So, what we’re going to do is we’re going to plot the adjusted close again, but we’re also going to plot the volume. Now, clearly, these are two completely different sorts of entity. One of them is a number of shares, the other one is a price in dollars, so there’s going to be a certain amount of adjustment to make that work. The easiest way to demonstrate this is if I Insert a couple of blank columns to the right of the date and then I’m going to copy, first of all the close column, adjusted close column I should say and then secondly the volume. Then I’m going to select those three columns and I’m going to do Insert Line and then all I’m going to do is insert a 2-D Line. Now, Excel 2010 has created this effectively pair of lines for me, but because the scale it’s used is the one here on the Vertical Axis here relates to volume, the numbers of course are vastly greater for volume than they are for price. So you can’t actually see price; it’s basically a line along the bottom. We’re not going to need that Legend, so let’s get rid of that Legend. That’ll give us a little bit more working space and let’s do a little bit of work on the volume chart itself. Now, to do this we’re going to need to first of all, select the volume chart. So, if I hover over part of the chart, actually see the selection markers on there now, right click, and I get Format Data Series. Now at the moment for the volume chart, the Series is being plotted on the Primary Axis, this is the Primary Vertical Axis, change it to the secondary axis. Straightaway you can see the affect that that has. Now I’m going to while the volume chart is still selected, I’m going to change the chart type and instead of a line for the volume, I’m going to choose one of the



Column Charts. So I’m going to go to Column and just click the first one, the Clustered Column; click on OK. And now you can begin to see how this is going to work. Now it’s worth bearing in mind here that what we do next will largely depend on exactly the stock and the history that we’re dealing with. In this particular case the prices varied from very low value, below $10 up to over 70, we could by introducing basically a negative range for price over here, move the whole of the Price Line up so that it doesn’t clash so much with the volume chart here. That might look a bit strange though and actually be quite misleading. So, in order to make a bit more space and to separate these two out a little bit more, the best bet is to make the volume chart a little bit lower. And the easiest way to do that is to change that Vertical Scale, so Format that Axis. We currently have a maximum of 3.5 million, supposing I make it 5 million as a maximum just to show how to go about this, click on close. By changing that to 5 million, obviously, the volume goes down; you get less of an impress. Now obviously it looks less dramatic when we’ve got these peaks, these spikes, but as I said before, how you adjust this, how adjust each of the scales will depend on what you actually discover when you, when you actually put this chart together. So, I’ve done the same sort of changes that we did last time. I put a Chart Title on, Axis Title, and changed the Labeling on the Horizontal Axis. To put a Title on the Secondary Vertical Axis, on the Layout tab under Axis Titles, you may recall from much earlier on, we can have a Secondary Vertical Axis Title and currently there’s none. We can make it a Rotated Title, so click on there and we can just put that as Volume Traded. Tick that and there we are. And, of course, as usual, once I’ve got the content I require in my chart, I could go back into Design and change perhaps the Style. Pretty straightforward one there and that’s it really. That’s how to put two charts on the same chart, in this case showing financial information related to a particular stock. So, that’s it for this section. In the next section we’re going to look at a couple of types of chart that are very specifically related to finance information and that is their prime purpose. I’ll see you then.



Video: Specific Charts for Finance Information Toby: Hello and welcome back. In this section, we’re going to look at one of the most popular charts related to finance and that’s the OHLC Chart and we’re going to sort of work our way towards a complete chart via a couple of intermediate steps. And the first thing we’re going to do is to produce a High-Low-Close Chart. Now in order to that what I’m going to do is I’m going to use Adjusted High, Adjusted Low, and Adjusted Close. I’m going to move them into the Columns B to D here. I’m not going to show you that, these sort of moves again in this course because we will be doing quite a few of them. I’ll be back when I’ve done that. So, there we are. I’ve copied those three columns of data. I now select the first four, which includes the date, obviously. Click on Insert, Other Charts, and the Stock Charts are at the top. The first one is the High-Low-Close Chart, click that, and there we have our High-Low-Close. Now if I make it a little bit bigger, it becomes a little bit easier to see what a High-Low-Close is about. Then, in fact, what you have for each of the data points, that is each of the days, you have a high, a low, and a closing value plotted. Now, with so much data in this chart over such a long period, it’s actually just about impossible to see what the High-Low-Close Chart is doing. So, let’s change the amount of data that we’re showing and you may recall if we go back into the Design tab and go to Select Data. If instead of going from to A1 to D2515, let’s change that to A2501 and let’s just bring the chart back into view. And what we see now is the High-Low-Close over the last two weeks, roughly about 15 days and you can see exactly what that’s doing. Note the gaps for the weekends, of course. Now, that’s really what a High-Low-Close Chart shows you. For each day it shows you the range at which the stock traded over the course of that day. Now, the High-Low-Close Chart that we have is actually very difficult to follow. The Vertical Lines are fine in that they show the range of trading price, but the little marker on there which is supposed to be the marker for the closing price is tiny. And, in fact, if you look here you can just about see that little dot against Series 3, which is the closing price; it’s the third column of data, Series 3. So, that’s not really helping us at all. Now we can overcome that quite straightforward way. We Delete the Legend. Then if you right click on the Vertical Axis and bring up Format Axis, you can actually work on the individual elements on the Axis. Now, to select a particular © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


one of the Series, on the Layout tab there is a dropdown up here and when you select Format Axis on the Vertical Axis, the dropdown becomes Vertical Value Axis. If you actually go onto that dropdown and go to Series 3, which is the closing price value, you get a particular version of Format Data Series dialog related to the closing price. And, in fact, the positions of the closing price are now marked, just highlighted on the bars. And if we want to make those markers a little bit easier to see, we can go to Marker Options. They’re currently marked with a little dash at size 3, now that dash is basically too small. So let’s make it quite a bit bigger, say, go up to 7 and then we can also say let’s go to Marker Fill. It’s currently set to automatic, let’s make it Solid Fill. We can keep that dark color. Now, let’s go to Marker Line Color; it’s currently automatic. Let’s make sure it’s solid and black and then Marker Line Style, we’ll just make it a little bit thicker I think; a little bit more noticeable. Click on Close and then finally click elsewhere on the chart and we’ve actually got now a much more readable HLC Chart and it’s showing us the range on each day and highlighted with the marker is the closing price on each day. Now within Excel 2010, there is no OHLC – Open-High-Low-Close Chart as such by Default. So, we can actually make one, although there are some problems with making one as we’ll see. What I’ve done is I’ve now introduced an additional column here, which is Adjusted Open, which I’ve created from the Open Column in exactly the same way I adjusted the others. And I’m going to now add that adjusted open value to the existing chart. Now to do that, if I go back to the Design tab, go into Select Data and instead of A2501 to D2515, if I change that to E, I introduce the additional column. Now, just pull that back up so I can see the chart. Now if I right click on the Axis, Format Axis, and as before on the Layout tab select this time Series 4, which is the open values, the adjusted open values and Marker Options. Currently there’s no marker. I’ll use a built in marker, choose one of those. I’ll choose this dash I think. Make it a little bit bigger. Marker Fill, we’ll make it a Solid Fill in that color and Close. Click away and I now have an Open Marker as well as a Close Marker. Now, the Close Marker actually shows it on the right hand side of the line and that’s the point at which we exit on the day. The broader one shows the open. Now the sensible and standard thing to do would be to put the little open marker on the left, but for some reason Excel 2010 doesn’t offer that as a © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


Default marker. Now, you can actually make a marker of your own. You could even adapt the close marker and that way you could get something that’s more like a conventional OHLC Chart. But for the purposes of demonstrating how to add the open values, that’s fine; we just can’t do the marker with Excel as it stands. So, that’s our OHLC Chart. Obviously we could Format it with Legend, Title, Axis Titles, and so on, but now we’re going to look at one of the other standard types of chart, which is the Candle Stick Chart. Now to draw a Candle Stick Chart in Excel, you need the data in a particular sequence. The particular sequence you need is the dates on the left and then you need Open, High, Low, Close. I should point out that while I’ve been setting up the data for these various charts, I’ve chosen to use these columns on the left. You could, of course, provided they’re in the right sequence, you could select the individual columns in situ over the rest of the spreadsheet, but I do find it easier to follow what’s going in each case if we keep all the columns together. So, having moved the columns into the correct sequence, let’s create that Candle Stick Chart. Now, I’ve limited myself again to a couple of weeks worth of data for the purpose demonstrating this and what I’m now going to do is select the five columns that I need, Insert, Other Charts. Now on the Stock Charts, the second one is the, described as the Open-High-Low-Close Chart. It’s actually a Candle Stick Chart and if I click on that I get my Candle Stick Chart, which I can then resize and you can probably see now where the name Candle Stick comes from because they are like candle sticks. Now, for each day that’s covered we have some markers that indicate what happened on that day. And the way that these markers work is that the white ones, by Default these are created in monochrome by Excel. The white ones indicate an increasing price. So effectively if you take this white candle here, the bottom was the opening price, the top was the closing price, and the little wicks in there are basically the highs and lows that occurred during the day and the black indicates a lowering price, reducing price. So, the top would be the entry point, the black would be the exit point; so open and close. Now, there are actually a number of options in terms of coloring. By Default, as I said, these are created with white for the increases and black for the declines. Where you are going to use © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


color, the convention is to use green for the increases and red for the declines. If you look on the Chart Styles here, you can see a number of alternative color schemes here; for instance, this one. This is showing a sort of slightly white color for the increases and a red for the declines. But if you wanted to stick with this Layout you can individually change these. For instance here, if you go to the Format tab under Chart Tools, use the dropdown to select Up Bars 1 and then click under there on Format Selection. You can say Fill, Solid Fill and then choose a suitable green color. So say something like that and obviously you could do the same with the black bars, showing the declines to show those in red. So, finally in this section I’m going to draw a Candle Stick Chart with Volume. Now, I know that I need to have the volume available and it needs to be the second column on the sheet. So I’ve got date, volume, open, high, low, and close. If I select those six columns now, Insert Other Charts, the last Stock Chart is Volume-Open-High-Low-Close, which just reminds me the order that the columns have to be in. Click there and I have a Candle Stick Chart with Volume as well. So we can, of course, do the normal formatting to this. We have the problem that the bars for volume overlap the candle sticks. We know how to fix that. We can click on Format Axis and for the Maximum, which is automatically set to 9,000, I could make that Fixed. Sorry, 900,000, I could make that Fixed at say 2 million and that whoa, we’ll probably need to bring that one down a little bit more, but that’s okay, you get the general idea. And then, of course, I can go to the Chart Layouts in the Gallery, choose one perhaps with a Title and then I can put the Volume on this Axis. I can put “Closing Price” or “Price” on this Axis and give myself a Chart Title. So there we are. That’s a Candle Stick Chart with Volume. You’ve now seen most of the charts that are particularly relevant to finance, to stock prices and so on. There’s one more section now on stocks and that is, we’re going to look at how to set up a Live Chart, representing and showing Live Data and we’re going to cover that in the next section. I’ll see you then.



Video: Setting Up Live Charts Toby: Hello again and welcome back. In this section, we’re going to look at some Live Charting from data provided on the internet concerning stock prices. What we’re going to do is to create a very simple chart which will just show us Live the performance of two of the main induces on the stock market. We’re going to look at the FTSE and the Dow Jones Indexes and create a Live Chart showing their progress through a period of time. So, let’s get started. So, first of all, I’m going to create a dummy version of my Live Chart and this is really just to give you some idea of what the end result is going to be. I’m going to start both of the indices off with a dummy value as well, which will just be 0.5 and what this value is going to be generally is the percent change on the day. So, a value of 0.5 would imply that there is 0.5% increase on the day. Now if I just take those few sales, go to the Insert Menu, and Insert a straightforward 2-D Column Chart. Now I’m going to go to the Vertical Axis and I’m going to Format the Vertical Axis. It’s got Auto Value set up by Excel 2010 at the moment. I’m going to change those to Fixed. I’m going to change the Minimum to -1. Now as you’ll see, in reality if you set this up, you’d want it significantly lower than -1, but I’m putting it at the moment so that you can see the effect of the chart. And I’m going to put the Maximum at +1, close that and I’m actually allowing for a variation of between +1% and -1% on the day. Now, as I say, you’d probably want to allow a significantly higher figure than that. And then we’ll choose a Chart Layout. I think we’ll choose that one. We don’t need that Legend there. We’re going to change the Title to “Index Performance Today” and we’ll put a Vertical Axis Title on, “Percentage Change.” And then we’ll move the chart up there only really to keep an eye on the percentage change over the day for those two. Now we come to what is in effect the tricky bit. And the tricky bit about it is that in reality if you wanted to set up this sort of dashboard type of arrangement where you can look at Live Feed information. It’s straightforward enough to set it up, but you probably want the workings of it hidden away somewhere because it can tend to look a little bit messy. It’s not that difficult to understand, but there’s quite a bit of it. And you may want to put all of these working on a different sheet or hidden down this sheet and just leave the chart in view. Now, so that you can © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


follow what’s going on, I’m going to show all the workings as well, so it will look quite messy. But I hope you’ll get enough of an idea from it that you can make a more elegant job of it yourself. So, first of all, I’m going to choose one cell on the spreadsheet away from the chart, just down here. This is one of my working cells. I’m going to the Data tab, Get External Data from Web, and I get a new web query dialog and the web query dialogue by Default goes to my homepage, which is the MSN UK page. And I’m going to type in here as my URL finance.yahoo.com and that takes me back to the Yahoo Finance page and you’ll see a number of little black on yellow background arrows. They’re very important in all of this, as you’ll see in a moment. Now I’m going to look for FTSE. The FTSE 100, get quotes. You very often get this script area. Don’t worry about that, that’s happening on the page beyond the point that we need it, so just click on Yes and carry on. And once the FTSE values have been found, if I scroll down the page, I’ll start to see these little black on yellow arrows. Now, wherever I see a black on yellow arrow, I can copy that data as a live feed onto my spreadsheet and there are two particular areas which are going to be the basis of my work. First of all, this area here gives Live the Index Value for the FTSE 100 and you can actually see it updating as we’re looking at it here. And just to the right is another table that shows yesterday’s close, today’s open, the day’s range, and so on. Now, if I click on the first of those, after a moment the black on yellow arrow becomes a tick. If I click on the second one and then click on Import; it will actually Import both of those tables for me. It asks where I want to put them and I’m going to put them where I’ve all ready selected, which is A18. Click OK and in fact the pair of tables now begin on A18. Now, notice that the first table is here and the second table is here. Now, what I want is a Live Value which tells me the percentage change in the FTSE since last night at this moment in time. And that’s what we need to work out how to calculate. Now to calculate that is actually not very difficult and what I need to do then is to assign that value to this cell, B1. The one that shows the percent change in the FTSE. So, let’s do that calculation. Well, we know it’s a formula; so it’s going to be equals. And the change in the FTSE is the current value, which is B18 less last night’s closing value which is B22. I then divide that by last night’s closing value which is B22 and then to make that into a percent change I just need to multiply it by 100. Now that gives me a current change of -0.99, which is basically © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


what the website told me it would be. Let me just format that as Numeric with two digits and I’ve got a -9.9. Now notice that the -0.99 there immediately obviously updates my chart. So what I’m now going to do is to do exactly the same for the Dow, so position the cursor, Data, Get external data from web, and then up here type the URL of finance.yahoo.com. Yahoo Finance page comes up again and this time I’m looking for Dow, so it’s DJI, there we are. Again, I get an arrow on the page, but that won’t affect this. And again, once the arrows on yellow backgrounds appear I can find the corresponding data for the Dow, there’s one, there’s another. I can click on Import and those will go, get this funny text here, but ignore that, and those will go into the equivalent positions here. Now the formula, in this case, is going to be equals the current value which is E18 minus last night’s close which is E22 divided by last night’s close which is E22 and times 100. And that gives me a drop of 0.35%. So, that’s pretty much the whole thing in place. Now let’s look at the clever apart of it. If I click, right click inside one of these areas and click on Data Range Properties, there are a couple of very important settings.

There is a Refresh control and the Refresh control dictates how frequently the

information is refreshed and I’m going to set this at one minute. And I can also say that I can Refresh the data when I’m opening the file. So whenever I open this file containing this structure, the data will be refreshed. Now, on a minute interval it doesn’t really make a lot of difference because within a minute of me opening it, it’s going to refresh anyway. But I can also specify here that when I close the worksheet, I can remove the external data. So if I click on OK, I’ll now find that I’m getting a one minute update. Now keep an eye on the FTSE box. If I go to the Dow one and make the same change, Refresh every one minute and Refresh when opening, click on OK. Once we make that change it applies to all of the boxes within that range. So, both of these are now on a one minute refresh. So it really is as simple as that. Now of course, you can include any stock values that you can find by that sort of procedure. It works very well with the Yahoo Finance site. It could also work very well with other finance sites as well. And for the reasons you may now see, you probably would want to put all these mechanics, all these tables of data hidden away somewhere, maybe even further down this page, out of sight and just leave the particular chart showing. You’ll notice already that the updates have started to happen Live while I’ve been talking. FTSE’s changed. The Dow-Jones has changed and so on. And, of course, there’s absolutely no reason that you shouldn’t use any of the other values here as well. So you could just have the Index values shown; perhaps on a little Dashboard. © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


Now, a couple of other important points here, as you can see from the changes that are happening Live as I’m talking, the FTSE is all ready gone down more than 1%, so our Scale Minimum of -1 wasn’t a particularly good choice, so you need to be careful about how you set that up. And a second point to note is that whenever an update occurs it will interrupt whatever you’re doing on this sheet, which can be rather annoying. There are a couple of alternatives to this and for some people if this kind of Live update is important, they’ll even have it running in a separate instance of Excel. Not just another workbook, but running a second instance of Excel, possibly on the same PC, which is just doing these updates and feeding the charts. In some cases, another extreme, people will have a PC just dedicated to taking these Live Feeds and so on. But that’s a little bit beyond the scope of this course, so I’ve given you the basic ideas and I’m sure with that you’re going to be able to access Live Data if indeed you need to. So, that’s it on stocks for the moment. We’re now in the next section going to look at a couple of the newer features of Excel 2010 and particularly at Sparklines. So I’ll see you then.



Chapter 7 – Sparklines Video: Using Sparklines for Data Visualization Toby: In this section, we’re going to start to look at some of the other types of data visualization technique which are not strictly speaking charts and graphs as such, but are very closely associated with those. We’re going to start with Sparklines, a new feature introduced in Excel 2010. Now, I’m going to introduce you to the idea of Sparklines by an example. This table shows an extended version of the data we looked at earlier on relating to monthly sales by a sales team. And what I’m going to do is I’m going to introduce a new column B and I’m going to include in that Sparklines. So, having inserted the empty column, I now select the data that’s going to be used for the Sparklines. Now, on the Insert tab there is a Sparklines group and there are in fact three sorts of Sparklines; there is Line, Column, and Win-Loss. And we’re going to start with Line. If I click on Line, insert Line Sparkline, what Excel 2010 asks me for is the data range, which I’ve all ready selected, C2 to N6 and then it asks me choose where I want the Sparklines to be placed. Now I can select those like this, click on OK, and there are my Sparklines. Okay, what are the Sparklines? Well, the Sparkline is really a little line graph. If you look at the first sales person, Anne Carnegie, and look at the sales over the period of a year, just squeeze the last one or two in. I’ll just zoom out a little. You can see that basically if you look at the Sparkline, you can see that Anne’s sales have fluctuated over the year with a slightly downward trend. So, 321-64 was a dip, 414 was bit of a peak, went up to 503, which is in fact the best month she’s had and then it sort of slightly oscillated down. Now, for each of my sales people I have a little graph showing their performance over the course of the year, that’s a Sparkline. Now, once you’ve inserted Sparklines and you’ll normally insert Sparklines in a group like this, you have access to the Sparkline Tools Design tab and on that there are a number of commands and functions that we need to look at. One of them is that we can change between the three different types of Sparkline. Now, to help with this a little bit, I’m just going to zoom in a little. We won’t be able to see all of the data, but we’ll be able to see the Sparklines a little better. And © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


one of the things I can do is to change the type of Sparkline. There are actually three types; the Line that we’ve seen, the Column, which basically shows the same data, but as a Column Chart, and then there’s a Win-Loss. Now in relation to this sort of data, the Win-Loss is not really a relevant type. So let’s go back to the Line. Now, by Default the Axes and Scales on which each of the Sparklines are drawn are independent of each other. So, for instance, if we look at this lower performing person here, Ang Lee in January, the scale that’s used to draw this little Sparkline for that person will be a different scale to the one that’s used to draw that person. Now, we actually created these Sparklines all at the same time. When we do that they’re actually created as a group and if I select the group, one option I have on the Sparkline Tools Design tab is to Ungroup them. And this breaks a set of group Sparklines into individual Sparklines. While they’re grouped, if I go into Edit Data, the first command on that Tool Bar, I find Edit Group Location and Data, and what that lets me do is to select the source for the data for this group. So if I wanted to actually now take this from a different data set, so for instance, instead of going B2 to B6, I wanted to put the Sparkline somewhere else, I could reposition them. I could also change the basis on which the data I selected. So, I could choose from a different selection of data. Similarly if I were to Ungroup these into individual Sparklines and then perhaps select that middle one, when I go into Edit Data I can Edit a single Sparklines location and the data that the Sparkline is built from; in this case, C4 to N4. So, let me now group them back up again, select, and then group and they’re back as a group. Now let’s look at some of the other formatting options here. Sparklines are actually designed to be included in a small space, often at a single cell on a worksheet that may itself be zoomed out from. But you can actually add levels of information to the Sparkline. So, for instance, using the Show group here, you can show high points, so basically the peak for each of our five sales people and indeed you can show the low points, last points, and so on. And with each of these you have options here to change the marker color. So you can change the marker color for negative points, high points, low points, and so on. And in fact, there’s another control here for changing Sparkline color itself. There is actually a Style Gallery here, although it only really affects the color of the lines themselves, which you can set separately using that Color Control. So it’s not something I personally have used very much. But one very important control is this one, the Sparkline Axis © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


Control. Now if you click on that you have a number of options and particularly the lower two groups it’s worth being aware of, Vertical Axis Minimum Value Options, Vertical Axis Maximum Value Options.

At the moment, by Default, they’re set to automatic for each

Sparkline. So each Sparkline is treated on the basis of the values, in this case of sales for the individual sales person, as I explained before. But you can actually say that it needs to be the same for all Sparklines and similarly you could say the Vertical Axis needs to be the same for all Sparklines and then everybody is being measured on the same level and you can see that generally speaking there’s quite a difference in the pattern of their sales over the course of this year. You can also set Custom Values. So you can actually set the minimum and maximum values for these scales, which means that it makes it easier for you to compare them say with a performance of another group or something like that. Again, back at this control, you have a general Axis Type. You can actually change the Axis Type depending on the range of values that are featured in the data you’re using. Now, one very special thing about Sparklines which is not at all apparent when you first come across them is that they’re actually built on a separate layer from the main content of a worksheet. And you can actually put other data in the same cell with a Sparkline and this can actually be surprisingly useful. Now, I’m going to demonstrate this by increasing the height of each of these rows in the spreadsheet. I’m just going to basically approximately double the height. Obviously I could do this accurately if I wanted to, but for the purposes of this I’m just going to roughly double the height. And then I’m going to select this group of Sparklines, click on Axis, and I’m going to set a Custom Value for the Axes of 1,200 pounds or dollars depending on what we’re taking these sales values to be. Just see what happens now to the Sparklines. The Sparklines still definitely show the shape but in each case there’s a bit more space in the cell. Now let me take one of these cells, say, this one and I’m actually going to type in there now, Anne Carnegie. Now you may think that in the way that this normally happens with Excel, as soon as I type in there I delete what’s there already. Well because the Sparkline information is a different layer, that’s not the case and if I tick this note, the Sparkline is still there and so is the name Anne Carnegie. So, let me now format this cell; right click, Format Cells. I’m going to change the Alignment in the cell, Horizontal, it will be center and Vertical will be top, and note what happens. Now it has © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


absolutely no impact on the Sparkline, but it does change the Alignment of Anne’s name. Let me go into Format Cells again. This time I’m going to the Font. It’s currently Arial Bold 10 point; I’m going to make it Ariel Regular 8 point. Click on Ok again and there we are. You can see we have a Sparkline with a Label in the same cell. It’s actually pretty straightforward to do and you’re pretty safe to experiment with it as well because the Sparklines aren’t affected by those same formatting options. So, let’s just look at a couple more aspects of Sparklines that are quite important. I’ve got here some other economic data relating to an unnamed country and it shows for that country over a period of eight years, three key economic indicators – GDP, unemployment rate as a percent, and population. Now as before, if I insert a column to hold the Sparklines, just zoom out so that you can see the whole data selection. Select the data, go to the Insert tab, and I’m going to Insert Line Sparklines. Select the group of cells where the Sparklines are going to go, click on OK, and there are my Sparklines. Now while the Sparklines are grouped, they’re going to all be of the same type. Let me just zoom in there. Although, of course, considering the data that they represent, the Scales are extremely different. GDP is measured in, this will probably be dollars per individual, the unemployment rate as a percentage and the population in people which actually runs into millions. So they’re definitely on different scales, but they’re all lines. While I’ve got the three here together with the selection box around them, I can now Ungroup and now each of the Sparklines can take on its own life and if I go to a particular one, say the first one, the GDP one, I could change its type of Column and then that alone will be shown as a Column and as you can see I can get a good visual representation of how GDP has changed. I can also apply, of course, a Style, change the coloring on that. And I could, for instance, change the population as well, also to Column. And choose a style as well on that, perhaps that one. So, once you’ve ungrouped them you can treat them all separately. And clearly, of course, as we saw just now I could put additional Labeling information in here as well. So there we are; that’s pretty much it for Sparklines. In the next section, we’re going to look at Data Bars. I’ll see you then.



Chapter 8 – Data Bars Video: Using and Formatting Data Bars, Color Scales & Icon Sets Toby: Hello again and welcome back. In this section, we’re going to look at Data Bars and some of the associated conditional formatting associated with Data Bars. Let’s start with Data Bars themselves though. A Data Bar is basically a piece of color that occurs inside a cell and it shows something about the size of the number in the cell.

Now Data Bars really relate to

numbers and to show you a very simple example of Data Bars in use, I’ve got the sheet you saw a while ago which has got eight African rivers, their lengths, and their drainage basin sizes. And I’m first of all going to use Data Bars on the lengths. So, first of all, to apply Data Bars, select the numeric cells that you want to apply it to and generally speaking as we’ll see in a moment, these need to be numbers that in some way are related to each other. So in this case, we’ve got the lengths of the rivers but we’re not including the drainage basin sizes, but the lengths of the rivers. Click on the Home tab on Conditional Formatting and Data Bars and there are two main choices here; there’s a Gradient Fill choice and a Solid Fill choice. I’m going to use the Solid Fill choice. And what has happened, as you can see, is that Excel 2010 has looked at all the values in these cells, assigned a full length data bar to the highest value and then to all of the other values, it’s assigned proportionately shorter Data Bars. So, you immediately get a visual impression of the relative sizes of those eight rivers. Now, it’s important to recognize that Excel doesn’t realize what’s the meaning of the numbers that we’ve selected is and if we were to choose numbers that were measures of different entities Excel would be unaware of that. So, for instance, if I Undo the Data Bars on those river lengths and instead select the river lengths and the drainage basin areas, do the same thing, Conditional Formatting, Data Bars, Solid Fill. What I actually get is that. Now, might look to you as though it’s ignored the lengths, well it hasn’t. The point is that the drainage basin sizes in square kilometers are so much hugely bigger than these, so the drainage basin size of the Okavango is 3.7 million square kilometers, compared to a number of less than 2,000 for the actual length of the river. So when it chooses the largest number to work out the length of the longest bar, it scales it down for the river lengths and, of course, none of them even show up. However, if I separately select these, so for instance, if I select Okavango down to Nile length, put on Data © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


Bars, I perhaps use these blue bars for the river and then I select the drainage basin separately, apply the bars and perhaps use green for the deltas. Then I have two separate sets of Data Bars that each have their own meaning. Now when I have a group of cells with Data Bars in use, I can actually manually manage the way that those bars work. I’ve selected the cells for the river lengths and if I click on Conditional Formatting again, I can bring up the Manage Rules dialog. Now, in fact, for the selected cell or group of cells, there are two rules in force. There’s the one I set up originally covering the range B2 to C9, just to show how not to do it and there’s the one that superseded it covering only B2 to B9. Now, don’t want that original rule, it was no use because it was mixing two different entity measurements. So I can just delete that rule and all that remains now is this one. Similarly I’ll just click on OK. If I select the drainage basin figures and again bring up the Manage Rules, I can see that there is only that one rule now applying there, the original one having been deleted. Now with each rule, I can actually edit the rule. And there are quite a number of options. If you look at the top Select A Rule Type, Format of cells based on their values. Now, that’s basically what is happening in this case, the format is purely based on the value in each cell. There are other options such as I can format only the top or bottom ranked values, which means that only the largest and smallest will be formatted. When I’m doing the formatting, the rule description appears down here. We’re doing Format All Cells based on their values. The format style is a Data Bar and we could, if we wanted, show the bar only. If I click Show Bar Only and then click on OK and apply the rule now. Watch what happens, the numbers disappear. Going to Edit the Rule and take that off again, click on OK and reapply. Okay, so let’s look at some of the other options we have in Edit Rule. We can specify the Minimum and Maximum values for which formatting will apply. They’re otherwise automatic based on the Maximum value available, as I said before. We could, for instance, for the Minimum value, specify a lowest value; so the lowest one becomes the zero, if you like. Or we could say that the lowest value, there will be a number that gets assigned. So, we’ve the Zambesi drainage basin 785,000, supposing a minimum number in there of a million, click on OK, apply the rule now and you’ll see that below a million it doesn’t get a Data Bar. This one is slightly over the million, but it’s such a tiny bar I can just about see it, you may not be able to. So let me go back into Edit Rule again and I’m going to change that back to automatic and then © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


one final piece of formatting you can do here. We will be looking at one or two other things later on, but just for now, when we’re looking at the Fill, we can say either Solid Fill or Gradient Fill and we can also decide whether we’d like a Border on it or not. We can have a Solid Border and perhaps a Solid Border in say a slightly darker color. That isn’t going to look particularly nice, but never mind. Apply that and what we now have is Gradient Fill, we reverted to the automatic sizing of the Data Bars, and each of the bars has a Border. Now the use of Data Bars is a very useful piece of data visualization, a good tool for that. I’m now going to look at another one which is similar in some ways. Back to the sales data for our five sales people. I’m only going to look at six months worth and I’m going to Delete the quarter figures and what I’ve got now is a table, six months. I’m going to select the sales figures for the six months and then again on the Home tab under Conditional Formatting, I’m going to select Color Scales and I’m going to choose the first option there. And what this does is to look at the lowest and highest figures over the period of the six months and the best results are the greenest ones and the worst ones are the reddest ones. So as you can see, you can pick out the high scores very easily here because of the color; you can pick out the low ones as well. Now what you don’t want to do is to include the sales figures in that, the total sales figures in that, because if you do, obviously the total figures will be all the green ones. So again, it’s important to make sure that you’ve selected comparable entities. So let me take all of those back, go back again here, Conditional Formatting, Color Scales, and there we are. Now, one of the reasons for showing this particular set of data is that there are actually two very extinct ways of looking at this data and at the moment I’ve chosen the figures over a six month period and the color scale works on all of those figures. But in fact, what might be of more interest is to look at what happens in each individual month, which is a different story. So, for instance, if I select just the January numbers, Conditional Formatting, Color Scales, and there, I find out what the story is for January. Similarly I can do it for February, March, April, May, and June. And what I’m now looking at is very much a different Color Scale because what we’re seeing now is which are the strongest people. Now, for instance, if you look at Anne Carnegie, she’s only got one green month, one month where she was relatively strong, two red months, couple of ambers, and a sort of orangey colored one. And if you look at each individual, you’ll see that Scott Denvers, for example, has three strong months. Now, that one 396 is not a © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


particularly high score overall, but it was the highest score in that month and when we had all of the months selected that didn’t come up as a very green score, where an in fact in the month of June that was quite a strong sales performance of 396. So there’s a very strong difference between these ways of using these Color Scales and it’s always important to look at the meaning, the way you’re interpreting them and the fact that in this example using the Color Scales in two different ways, actually you’ve got two very different meanings. Now, as with Data Bars, we can customize the Color Scales. If I, for instance, select the January ones again, go into Conditional Formatting, Manage Rules, you’ll see that there are two sets of scales applying here. There’s the set that we applied originally to all six months and a set that applies just to the one month. So I’m going to actually get rid of the six month rules and just stick with that one and if I now OK that, select February, of course, and having deleted that from January, it’s gone from February as well, that rule is gone. Let’s look at the February rule, we can edit the rule and in a similar way to the way we saw with Data Bars, we have a number of rule types we can apply. We also have a number of choices. We have a two color scale and a three color scale and another option, which is Icon Sets that I’ll come to in a moment, and with this Color Scale we have ways of defining a Minimum, a Midpoint, and a Maximum. Now if you look at the Color Scale from red to green, the reddest red is defined here in this rule as the lowest value, so whatever the lowest value is, which is 96 in January, but 64 in February, so that’ll be 64 in this case, will get red and the highest value, 457 will get green. A 50 percentile point, so something midway between those would get the middle amber color. Now, of course, as I pointed out just now, the way we’ve got this set up, this rule is being applied to each of the six months separately. Okay, so we have one more of these data visualization approaches to look at and we’re going to look next at Icon Sets. Let’s stick with this data, we’re going to stick with the January data here, ranging from a low of 96 to a high of 518, going to select those, Conditional Formatting, and this time I’m going to go for Icon Sets. Now, Icon Sets, there’s quite a wide choice now and I’m going to choose the Directional Set here, three arrows, colored, click there, click away, and you’ll see that basically the Color Scheme is still in place, but arrows are added as an extra visual clue as to how well performance is going in January for each of our five sales people. If I select that again and go into Manage Rules, if I get rid of the Graded Color Scale rule, Apply, and what © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


I’m left with is just the Icons. Now sometimes if you don’t want all the colors in place, the Icons actually quite a nice feature because it’s not quite so intrusive on the eyes and yet gives you a good clue of how well somebody is doing. Now, again, back into Conditional Formatting, Manage Rules, I’ve only got this Icon Set Rule. I can Edit the rule and again, based on whichever rule type I’ve chosen, there’s a number to choose from here, the Icon Set choice I can change if I want to. I could, for instance, change it to these Traffic Lights and then each icon will be displayed according to these rules. Now, the way these rules work is that they use percentage values, in this case, to decide which color to show. But I can, in fact, change from percent to actual numbers. So I can actually define the values where they change from red to amber and so on. And so there’s a lot of flexibility in the way that I set these up. If I click with these, click on OK, and I’ve now got my Traffic Lights in place. So, there we are. That’s it on those visualization techniques. In the next section, we’re going to move on to look at Pivot Tables which many people consider to be one of the strongest features of Excel in general and Excel 2010 in particular. I’ll see you then.



Chapter 9 – Pivot Tables Video: Setting Up Pivot Tables/Charts Toby: Hello again and welcome back. In this section, we’re going to start to look at Pivot Tables and Pivot Charts, which many people consider to be about the strongest features of Excel. I’m going to start with a fairly straightforward example just to get some of the basic concepts and terminology over and then I’m going to go into a much more complex example and to show you just how powerful Pivot Tables and Pivot Charts are in Excel 2010. So, let’s get started. Now before we actually get started, a couple of words of warning about earlier versions of Excel. Pivot Tables created in Excel 2003 are really not compatible with Excel 2010 and if you have a version 2003 Pivot Table, your best bet, the one that will probably involve the least problems for you would be to use the data and to re-create a Pivot Table in Excel 2010 from the data. The second point to bear in mind is that although in many ways Excel 2007 and Excel 2010 seem very similar in many things, Pivot Tables are one of the areas where there are quite a few changes. Some of them are more changes of terminology, but there are some substantial changes as well. So if you’re just moving over from 2007 to 2010, you might want to look at what I’m doing here quite carefully as I’ll try to point out some of the changes in terminology. So, let’s get started. Now Pivot Charts relate to transactional data and in order to prepare data for a Pivot Chart you really need to work on a couple of basic principles in terms of the data. The first principle is that each transaction should correspond to one row on your sheet. Now, these transactions really are very straightforward transactions. They’re details sales in a number of stores. The data of each sale is in the first column, the name of the store branches in the second column, the value of the sale, which is actually in dollars, is in the third column, and then the department, the type of produce is in the D column. So we have flat bread, breads, and so on. Now with each of these we have just that basic information. The examples we’re going to look at later on will have quite more complex transactions. If in fact you have information, transactions the other way around, so if each column were a transaction, that’s not really suitable for building Pivot Charts in this way and you need to in some way transpose that data. And a couple of other very important points about Pivot Charts in order to make them reasonably straightforward, yes, Headers are a © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


good idea, but keep the Headers simple, ideally the Header will be in one cell. And secondly, you shouldn’t have any blank columns or blank rows in your data. So, the way I’ve got this data presented here is pretty much spot on right for a Pivot Chart. And here’s a useful tip when you’re creating Pivot Charts. It’s always a good idea to actually put all your data into a table or rather to create a table containing all of your data. The reason this is helpful is that if you subsequently add some data, or even take some data away, Excel 2010 will automatically refresh and redraw the Pivot Chart on the basis of the contents of the table. So, if it comes to changes to data later on, you’ll find this actually saves you some time. It’s quite easy to put your data into a table, just click somewhere within the data and then use Control-T. In my case, I know that I’ve actually got an awful lot of data here, so my range is A1 to D34109. As you can see I have a lot of transactions, check the box My Table has Headers, click on OK, convert the selection to a Table, Yes, and there we are. And I’ve now got a Table in place. Now, in order to create the Pivot Chart, we just click anywhere within our data and then on the Insert Menu there is a Pivot Table button. Now this button has a top and a bottom and we’re going to start by clicking the bottom, Insert Pivot Table, and we’re going to select the Pivot Chart Menu item. So, we see the Create Pivot Chart Table with Pivot Chart dialog and within that we have, first of all, a definition of the data that’s going to be analyzed. Now, if I had just selected a range, the range would appear here. Because I put this into a table, it’s actually linked this to what it’s called Table 2. You can see up here Table 2, just confirms that that’s the table that’s outlined here. And I can actually link to an external data source, now I’m not going to do that, I’m going to stick with the data I’ve got here and by Default, the chart is created on a new worksheet. You can put it on an existing worksheet, perhaps one you’ve all ready prepared for that purpose, but I’m going to choose New Worksheet. Click on OK and there we are. My new worksheet, which is actually called Sheet 2, which is created, and now let’s look at all these various Fields and Controls that are put on that sheet. So, now we have our new worksheet. The first three columns or this area in the first three columns is actually reserved for the Pivot Table, columns A to C for the Pivot Table. The Pivot © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


Chart has columns E to M reserved for it and on the right we have the Pivot Table Field List Box. Now this contains a list of the Fields and then there are four drop zones at the bottom. Now, the arrangement here, what you can actually see, is selectable by this little menu and you could, it currently says Filled Section Area, Sections Stacked. You can actually change that, the second option is Filled Section Area, Sections Side-by-Side, so you have a different arrangement, Filled Section Only and so on. Now as we start to develop the complexity of the data we’re dealing with in Pivot Tables and Pivot Charts, you’ll see the use for those alternatives for the Pivot Table Field List Box. Now apart from these new areas on the worksheet, we have a set of four tabs on the ribbon under the heading Pivot Chart Tools. We have a Design tab, which is basically the same as the Chart Design tab we’ve seen before. We have a Layout tab, and then we have a Format tab. Again, these are pretty much the same as we had before and then an Analyze tab that we’ll look at a little bit later on, which is where we do Analysis on the data in our Pivot Table. So, as a first step in creating a Pivot Chart, let’s enable one of the fields to add to our report and we’re going to choose Department. Now once we choose department a number of things happen. One of them is that over here in the Table area we get a list of all the different departments that Excel 2010 recognized in our data. And apart from being able to select one of the individual departments, we can also use the Filter Control to choose any combination of them. So, for instance, we could choose breads and flatbread.

In the chart area we see

department with a symbol here showing that there’s a filter on the departments and we can actually change the departments there as well. And again, so it works both ways between the table and the chart. One other things to note is that department appears in the Axis Fields down here and it’s all ready identified as one of the Axis fields for my Pivot Chart. Now, one variable is obviously not going to be enough, so let’s do an analysis on Value of Sales by Department. So, the next thing we do is to check the Value Box. Now what we now have is two fields selected and Excel 2010 has identified that the Value Field is going to be our Vertical Numerical Field here and the Department Field is on the chart horizontally. Now note, Value is actually in the sigma drop zone and when we’re dealing with more than two fields, there will be occasions when we will



need to move things between zones. But for the purposes of this exercise, Excel has actually put everything in the right place for us, so we don’t need to move anything on this occasion. Now although this is a very straightforward example, it’s a good place to stop because it enables us to look at some of the main features of Pivot Tables and Pivot Charts. Let’s look at the Pivot Chart, first of all. It’s created as a normal Excel 2010 Chart and we can do things like Change the Title, Format the Axes, apply different styles to the chart, and so on. So, we can pretty much do anything we would do to a normal chart, we can do to this chart, including putting it somewhere else for presentation purposes and so on. But we’ll come back to that later on. Let’s look at some of the powerful features that we can use here with Pivot Tables and Charts. One of them, of course, is the Filtering Facility. So, if I actually didn’t want to include the figures for Coffee and the figures for Gift Certificates, I only want breads, flatbreads and, say, oatmeal. Click on OK, and everything gets updated. Now on occasion when you’re working with Pivots you may find that the Field List on the right disappears, quite easy to get it back. Normally, if you just click within the Pivot Table or the Pivot Chart itself you’ll see the List reappear. So, just one more thing on Filtering, we always have the Select All feature. So if we do a Select All, note not only that we see all of the Departments, the sales departments shown, but Excel 2010 does its usual good job of fitting everything in by changing the way that the Axes are Labeled, the Gridlines, and so on. And now let’s look at one of the other very important basic features here and that is if we go back down to the drop zone for sigma here, note that it says Sum of Value. By Default it actually plots the sum of the values of all of the transactions and as we know here, categorize by department. But we don’t actually have Sum of Value, we can, for instance, go into Value Field Settings, brings up the Value Field Settings dialogue and we can change it to, for instance, Counter Value. And what then happens is that Excel 2010 counts the number of transactions instead of summing the total value. So we find how many transactions we have in each department. Now, they’re actually many options here, back into Value Field Settings. We can have the Average, the Maximum, the Minimum. We can have the Standard Deviation, and we also have options to do Show Values As. So, for instance, we could have the sum and then showing the values as a percentage of the grand total, click on OK, and basically for each of the



departments we can see the percentage that its sales represent out of the grand totals. So, smoothies nearly 50%, gross sales just over 40%, and all of the others are actually very small. So, if you’ve not really seen or used Pivot Tables and Pivot Charts before, by now you’ll be looking at that and probably be quite impressed and probably see why people see that it’s such a powerful feature of Excel. But in fact we’ve only really scratched the surface so far and in the next section we’re going to dive into a lot more detail using initially this example and more detail from this example, but then building up the overall complexity. So, I’ll see you then.



Video: Setting Up Pivot Tables/Charts Part 2 Toby: Hello and welcome back. In this section, we’re going to continue looking at out straightforward example of sales in a convenient store or a number of convenient stores and what we’re going to do first is just to look at formatting the Pivot Chart that we created before. First thing I’m going to do though is to change the data selection. I’ve actually now got a number of places I can that. I can do it here; I can click on department on the chart and filter from there. So if I remove Select All and just select breads, flat bread, oatmeal, and snacks, click on OK, and as you saw before, everything is updated. The selection shown in the Pivot Table and the Chart are all selected. The Sum of Value I have here is still a percentage of total sales, but of course, this is the percentage of total sales for the selected departments. So, just over 50% of the total sales in these four departments is down to breads. Now, let’s once again take a look at formatting the Pivot Chart itself. We’ve seen ways of selecting data for it and if you want to actually format it, we may want to use it in a report; you may want to move it within your workbook. It’s very often the case that it’s a good idea to get the Field List out of the way while you work on it. I mentioned before that usually if you click within Table or Chart, the Field List will normally reappear. But actually if you’ve deliberately closed it, you need to open it quite deliberately as well. There are two main ways of doing that. If you currently have the Chart selected, then on the Analyze tab there is a button that says Field List, click that and the Field List is shown. And close it again. If you’re within the Pivot Table, then you have a Pivot Table Tools Group with Options and Design in it. If you click in Options, over on the right there’s a Field List button there that shows the Field List for you. So, I’m going to close this again and now let’s start working on this chart. With the chart selected we get the group of tabs up here – Design, Layout, Format, Analyzer. Let’s go to the Design tab. I’m going to change the Chart Type. I’m going to change it to a Pie Chart. I’m going to change it to a 3-D Pie Chart; click on OK. It all works exactly the same as it did before and I can even go into my Filter here and say decide, well I don’t really want snacks included, click on Ok and everything is updated.



Now there are certain types of chart you can’t use as Pivot Charts, including Scatter Charts and Bubble Charts. But for the purposes of analysis they’re not the sort of charts we’d be using anyway, so that’s not really a restriction. So, let’s change the Title here. We’re going to change it to “Sales by Product,” okay. And we’re going to remove the Legend. And finally, we’ll add some Data Labels. So, let’s go for More Data Label Options. We’re going to put the Category name and the Value and let’s not have leader lines. Click on Close. There we are. So flatbread and oatmeal are fine, let’s move breads just over to here and there we are, “Sales by Product.” That’s pretty good. Now, of course, we can still use usual facilities to increase the size of the chart, for instance. Excel may move some of the Labels to less convenient places, but that’s fine. And we can, back in Design, we can choose from available Chart Styles. So we could go for something like this. And the one thing you may have noticed, which is different, is this, Sum of Value up here. Now this is one of the buttons that the Pivot Chart and Table mechanism in Excel 2010 puts on the charts. If you go to the Analyze tab there is a button here, Field Buttons and if you click the top of that to switch off the Field Buttons, then any of those buttons will disappear. You can, of course, put them back on again if you need to. So, now we are going to return to the original Field List and we’re now going to go to the next stage with this Pivot Table because we’re going to introduce one of the other variables. We’re going to introduce Branch. So, in the Field List click on Branch. Excel 2010 works out that Branch should be one of the Axis fields. It’s not going to try to do any kind of calculation on it as it did with values. And what you can see has happened here is that for our selected group of departments, so starting with breads, flatbread, oatmeal, snacks; it has then Categorized or Subcategorized by Branch of Store those percentages. So, for instance, for breads, of the 56.63% of our total that is contributable to breads, this is now broken down by our five stores. Now at this point you can probably start to see the power of Pivot Tables and let’s now do a few things to show how easy it is to change this particular slice of the data that we’re looking at. Let’s go down, first of all, to the sigma drop zone down here. We selected earlier on here as our Value Field settings, we changed to Sum shown as a percentage of the grand total. Let’s just © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


return it to Sum as it was before, click on OK, and we’ve now got a total value of sales for each of those. The other thing we can do down here, if you look at the Axis Fields Department and Branch, I can actually click on Branch and drag it above department and watch now what happens. We now have the Branches as our first level and then within that the Departments, the types of food stuffs within the Branches, and obviously everything else is updated in line with this. So, of course, I can filter on Branch now. So I could Undo Select All and say just look at Boston and Chicago. Again, everything is updated accordingly. Now there’s one other very important thing now and that is that as we look at the chart here, we can see that it affectively we have eight values. That’s four different departments, two different branches, and the structure relating these. Although it’s shown in the way that the axis is marked, doesn’t really reflect the nested nature of this information. It’s as though these are eight separate departments. Now, of course, there may be situations in which that is exactly the way that we want the data to look. But if we actually wanted a structure whereby it’s got a more two dimensional feel, the clue to what has happened here by Default with Excel 2010 is that both Branch and Department are shown as Row Labels. And in fact you can see they are Row Labels. They are structured, there are as we move these around, departments within branches, but they’re both Row Labels. If we wanted to get a two dimensional structure all we need to do is to take department and drop it in the Column Labels drop zone. What happens then is that we get a completely different look to our data because now the columns are the departments, the food types, and the rows are our two branches that are shown. Both of them are Filtered, so on the Column Labels, that is the department types, we could perhaps put in hot teas and on the Row Labels, that is the branches, we could include another branch, so we could put Denver in there. And, of course, we have this type of Clustered Column Chart representing all of that data and looking pretty similar to the Clustered Column Charts that we looked at before. The next thing we’re going to do is a very important feature of Pivot Tables and Charts, but it needs to be treated with a little bit of care. We have then our Branches as rows and we have our Departments as columns. What I’m going to do is to remove Departments altogether.



So, to get rid of Department, I can just double click on it to bring up the Menu and I can just say Remove Field and that takes it out of the Legend Fields drop zone. So all I have now are my three branches with the total sales for each. What I’m now going to do though is I’m going to add Date. Now, this is a bit of a dangerous thing to do with a very, very large amount of data because the dates are by day and there are over 30,000 transactions, but Excel 2010 copes with it okay. And if I now drag Branch over to the Legend Fields, that’s the column ones and I’ve now got something a little bit closer to a readable chart. Now the problem is with daily data that’s extremely difficult to follow. So what I really want to do is to report by say month, and that’s what we’re going to do next. Now, one of the differences when you’re dealing with dates, for example, is that although you have a Filter here, so if I click on the Filter, the Filter covers every day over a long period of time and it still only does whole days. So, I could do as I did with Branch and Department at different times, and select a subset, but what it doesn’t do is to total up by date to larger time denominations like month. The way that I do that is I go to the Options tab within the Pivot Table Tools and there’s a function there, Group Field. And if I click on Group Field, Excel 2010 recognizes it as a Date Field. It gives me my Minimum starting date as a Default ending at as another Default. I can change those if I wanted to, but it also lets me group. And I’m going to group this data by months. My choices are everything from seconds to years, but months will I think will be good. Click on OK and what you now see is much better. I’ve got a by month list of sales for my three selected branches with the total value of sales and I also have the Clustered Column Chart to go with it. So, that’s how we do grouping. Now, dates are very often the types of field we’ll use for groupings and this is a pretty clever feature of Excel 2010’s Pivot Tables and Charts, which can really help in reporting a lot of data over a long period of time. So, we’ve covered many more of the basic features of Pivot Tables and Charts and we’re going to return to them again in the next section where we’re going to step up again and look at some of the more advanced features, including the use of Filters and Slices. So I’ll see you then.



Video: Filtering Pivot Tables/Charts Toby: Hello again and welcome back. In this section, we’re going to continue looking at Pivot Tables and Charts and we’re going to concentrate in this section on Filters. But before we do that we’ve got a couple of other things to finish off from last time and a couple of potential problem areas really. When we finished the last section, we had just grouped all of the transactions into months, so we’d created groups and we’ve got a Table and Chart showing for six months the sales in three selected branches, totaled value over those months and showing that the Boston branch, generally speaking, has by far the highest sales. Now when we did that we used a facility on the Options tab of Group and we used Group Field and we selected Months as the group. Now, what that would actually do is to look at all of the transactions in our Pivot Table and put together all of the ones that were in each of the months; so, all of the April transactions are in this row. Now that’s not April in any one year, it’s April in all years. Now, in fact, the transactions I used here were over a relatively short period of time, although there were over 30,000 of them, so they were actually in the same year, but that was more by accident than design. And if I really wanted to cover a long period of time and show a chronological sequence, I’d have to change this grouping to be months and years. So I can select two items. And if I do that, watch what happens in the table and the chart. And what you can see is that Excel gives me a year category and within the year category, the months. It also adjusts the chart accordingly. Now, in fact, we can take the concept of grouping like this one step farther and we can apply it to either rows or columns and we can even create groups of our own, for our own convenience. Now I’ve got data on five branches, three of them are shown at the moment. What I’m going to do is to switch all of the branches on for the moment. So, I’ve got all five there; I’ve got Boston, Chicago, Denver, Miami, and a New York branch. And what I’m going to do is I’m going to choose the Boston, hold the Control key down, select Miami and New York and I’m going to click on Group Selection. Now, that actually creates a group and if you now look down at the chart you’ll see that what it says on the chart is Group 1 – Boston, Group 1 – Miami, Group 1 – NY. If I select Group 1 and then in the Formula Bar I’m going to give it, this is just to demonstrate the principle, I’m going to give it the name of “Eastern” and I’ve now effectively grouped those three branches into what I’ve called my Eastern group. Now, of course, there was nothing about Eastern in the original transactional data. © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.

This is a group name that I’ve


introduced for my own convenience and, in fact, within Pivot Table and Pivot Chart processing in Excel 2010 there are many opportunities to add concepts and entities of our own to our analysis of the data. So, next I’m going to choose Chicago and Denver, group those, and I’m going to select that group name, Group 2 and I’m going to call it “Western.” There we are. And now all five of my branches have a prefix name shown here, which is the group followed by the branch name. Now, you will notice on the chart, you can probably just about see it, that I’ve still got a full set of five columns for each of the months in 2010 for which I have transactions. Now watch what happens if I collapse the Eastern groups and next to the Eastern group I have a little minus sign to collapse the group and what is in fact on the chart now shows that all of my Eastern group figures have been combined into a total for Eastern, the Western ones are still separate. And, of course, I can do the same for Western. I can collapse the Western group down and now I just have in my Composite Bar Chart, I just have two bars, an Eastern Bar and a Western Bar. And in this way I can form any combination of which ever entity it is, in this case branches, but I could do the same with the departments or any other entity that I have in my table and I can do analysis at that grouping level. Now it’s worth looking at what’s happened in the Field List as a result of doing this. In the upper section, we have two new fields appeared. Obviously these are not fields which we identified at the beginning, but they’re ones we’ve introduced for the purposes of analysis and reporting. We’ve got years and we’ve got Branch 2. If we click on the dropdown against Years, we’ve effectively got a Filter there and in fact all of the transactions are in the year 2010, so that’s not really a big issue in the case of the transactional data I’ve got here. But if I had transaction over a very large number of years I could Filter accordingly here. Branch 2, which is effectively the next level of branch, if I click on that, that lets me Filter on the two introduced groups: Eastern and Western. And as far as my Pivot Table is concerned, when I introduced these groups they became one row in the table, so they operate at the level of Eastern, where I can collapse or expand and the individual branches come at the next row level down. Similarly, I can expand Western.



And I can, of course, take this one step further because I could select Eastern, hold the Control key down, select Western. Click on Group Selection again and I’m going to name this group U.S.A. and I now have a new level. It’s appeared in my Column Labels. It’s appeared on my Chart. It’s appeared in my Field List and I can, of course, collapse it which gives me a total for U.S.A. sales. And of course if I’ve got an international operation I might be able to then combine nations into continents and so on. So, I have a multi-level grouping facility which can further add to the power of my Pivot Table and Pivot Chart reporting. Okay, let’s start with the Filters that we can access from the chart itself. Now we’ve all ready got buttons here from which we can filter, so let’s take Date for example. If I click on Date I can do Select All or I could say I just want January and February. Click on OK, there are of course no records in those dates, so let’s try June and July, where, of course, there are records. Now when ever I’ve applied a Filter, the little Filter sign shows here. There’s also, of course, a Filter here for the year. I only have 2010 values. If I selected less-thans, I wouldn’t find any, more-thans I wouldn’t find any, so it’s only 2010s, so that won’t make any difference. If I look over here for Branch 2, which is basically the group which will be Eastern or Western, I can Filter on that. So, for instance, if I just click there, De-select, and say Eastern, although that grouping is effectively a value that I’ve introduced myself, the Filters still work on it and I only get the Eastern branches. Similarly if I now remove that by doing Select All, you can see the names of the branches – Boston, Miami, NY, Chicago, Denver. On branch I have, clearly, the set of selections here, but I can also use Label or Value Filters. For instance, Label Filter, I could say Begins with and I could say that the Label begins with B. Click on OK and I only get Boston because the branch name begins with a letter B. If I put on a Filter like this there’s a little tick mark here that shows that a Filter is in place. If I want to clear it, I just say Clear Filter. So apart from being able to use these Filters that are on the chart any way, I also have the equivalent of an earlier Filter facility which is this drop zone here, Report Filter Zone. I can take any of my fields and drop it into the zone. So, if I take the Branch Field, for instance, and drag that into there, this fundamentally changes my report from the point of view of it now only has the group level shown here, although I obviously I can expand it out again. But the Filter now is positioned up here on the top left and is really read as a Filter on the whole report, so it’s going to achieve the same affect, but I can click and choose the branch or branches that I want, © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


including selecting multiple options. So I could say I just want the Boston and Denver and they’re the only two that are included, one being an Eastern and one being in Western. If I remove that Filter, you note the relative sizes of the blues and reds. Remove that Filter by clicking Select All again, click on OK, and they’re back to the values that they were. So we have several tools to help us with Filtering. We’ve seen most of them now and just going back to the Filters we’ve set so far, if we remove these, for instance, the Date Filter here, Select All again to get all dates back in. We have the Column Labels here, we still have both Eastern and Western included and the only Filter we’ve now got is one on department, but department is not shown. So a quick scan around we can check that nothing else is Filtered and that gets us ready for looking at the major new inclusion in Excel 2010 on the working of Pivot Tables and Pivot Charts which are slices and we’re going to look at slices in the next section. So, I’ll see you then.



Video: New Features for Pivot Tables/Charts Toby: Hello again and welcome back. In this section, we’re going to] start by looking at Slices which are one of the really interesting new features in Excel 2010. Now, I’m going to make a couple of changes to our current table and chart. I’m going to ungroup the branches, so select Eastern, on the Options tab, click on Ungroup, and select Western, on the Options tab, click Ungroup and then I’m also going to introduce department again and department is actually down in the Row Labels. So I think I’m going to put it up in the Column Labels. So I’ve got branch and department, so that’s fine. Right, what I’m going to do now is to remove the various Filters that are currently applied. So on department, the Filter there, I’m just going to put that as Select All again and branches I will make, it’s all ready all. Date is going to be all of them and that’s fine. So, having removed the various Filters that were on my data, I’m now back to a situation where I can see all five branches over all, seven months for which I have data. Now what I’m going to do is in order to make space to see what’s going now, I’m going to cut the chart and I’m going to position it further down the page and then I’m going to scroll down there and I’m going to work right here where I’ve got a little bit more space. And what I’m going to do is to, if I click on the Analyze tab, I’m going to remove the Field buttons, so there’s my chart and then on the Insert tab I’m going to Insert and then Slicer. And I come up with the Insert Slicers dialog and I’m going to choose the first four fields for Slices and click on OK and what I get are the four slices, one for each of the fields and I’m now going to position them as well as I can. There we are. Now these Slices, will actually work as Filters. At the moment everything is included, but if I say just wanted the figures for Chicago I could select Chicago alone just by clicking on Chicago. Now when I make a selection in one of the slices, the button in the top right corner becomes enabled. That’s one where I can actually just click to remove that filter again. It sometimes takes a few moments for it to happen. Remember it’s got over 30,000 lines of data to process every time I click a button here. So we’re back to having all included and of course I can include two. So, for instance, if I wanted breads and flatbreads I’d hold the Control key down and click on flatbreads and I’ve got two selected. Again, it takes a couple of moments for that to take effect. © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


Now note that when I applied that filter Excel worked out that I only have any data for those departments, breads and flatbreads in April, May, and June and so it narrowed the data down to show just April, May, and June. If I clear that filter again, I’m back to having the full set of six months in place. Now it’s quite often the case that Slices are used in presentations such Dashboards. They’re a lot easier to use than the little Filter controls that we’ve seen elsewhere in Pivot Tables and Pivot Charts. And so they can be a really nice user interface option for applying Filters. There are a number of things you can do to make their presentation more efficient and better looking. You can, for instance, change the style of the Slices. So let’s say the Branch one, if I select the Branch one you’ll notice it’s selected I get highlight around it, change the Style and Colors for that, then perhaps click on the department one, wait until it’s selected. Bear in mind that particularly when you’re working with large volumes of data, anything to do with Pivot Tables and Pivot Charts can take a few moments to actually execute properly, so when you’re doing this sort of thing wait until the particular Slicer is selected. Let’s have a different Style for that one and the Date one a different Style again. And then the Value one we don’t actually need, I don’t think. So I’m going to click on that, right click, and just say Remove Value. If you have a lot of Slices, you may actually finish up needing to stack them on top of each other and in fact when they are stacked, there’s pretty much a hierarchy. So there are some controls here that enable you to move them around. So, for instance, if I’d got the Department one selected, I could send it backwards and send it backwards again, and I could perhaps bring the Date one forwards and whichever one I’ve got may appear at the front, I can then work with it. Now having got my Slices, the other thing I can do is, I can change their sizes to make better use of the space available to me. So that one, for instance, could be made quite a bit smaller and there’s also a Slicer Settings dialog over here. Now here I can do things like, for instance, I can change the Caption, I could change it to “Month of Sale” and I can also change the number of columns. So, for instance, if I’ve got a Slicer like this with very short fields, basically just abbreviations of month names, I could say, let’s have two at a time and actually save space by having more on the individual dialogs. So there’s quite a few things you can do there to optimize the space usage of your slices.



Now we’re going to finish this look at Pivot Tables and Pivot Charts in Excel 2010 by a quick review of two or three other important features that I haven’t mentioned so far. One of them is that when you have Pivot Table like this, let’s take this cell here. This is a cell for all of Denver’s sales in June. If you double click on that one cell, Excel creates a new worksheet containing all of the data for that particular cell. So, it’s got a list of all of the transactions for Denver in the month of June which is a pretty useful facility as well. Another very useful facility is to introduce a Calculated Field. Now we don’t have a particularly good example with this data, but I can illustrate it perfectly adequately. Click anywhere inside the Pivot Table and you get the Pivot Table Tools, two tabs, one of which is Options and within the Options tab there is a Calculations group. Click on Calculations and then choose Fields, Items, and Sets and select Calculated Field. Now this lets us create a Calculated Field. Now I think what I’m going to do is I’m going to invent something here. I’m going to say “Value Inc. Tax” and I’m going to make out that there is an amount called Value Including Tax that is relevant to the Value to each of these sales and it’s calculated by a formula. And the formula is based on the Value Field, so it’s equals Value and then we’ll say times 1.10, so add 10% for tax. Click on OK and that creates a new field called “Value Inc. Tax.” Now once I’ve done that Value Inc. Tax is now included in both the Pivot Table and the Pivot Chart in pretty much the same place that the Value Field is, so down here under the Field List in the drop zone in Values I’ve got Sum of Value Inc. Tax and Sum of Value. If I look at the Pivot Chart, I can see that wherever I’ve got Sum of Value for each of the months for each of the branches, I’ve now got two total values and then I’ve got in the Pivot Table itself Sum of Value and Sum of Value Inc. Tax. If I wanted to only see Value Inc. Tax, I could actually switch it off here and it automatically gets removed from the values list here. So I’m now looking at something which is Value Included Tax. So you can make up pretty complex formulas here and if you’ve got a Pivot Table with numbers in it where you can do useful calculations and use those calculated values in the Pivot Table, it’s a pretty good facility. So we’ve looked at Pivot Tables and Pivot Charts and we’ve seen that there are various tabs and ribbons associated with them. So let’s just finally go through a few of the things we haven’t touched on yet. We have looked at some of the options for Pivot Table, including Calculated Fields, as we just did. We can also, if you look at Design under Pivot Table Tools, we can © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


actually change the Style of the Pivot Table in the usual way, so we’ve got a number of different Layouts with and without Borders and Shading and so on there that we can choose from. We can choose the Style Options in terms of whether we want Banded Rows, Banded Columns, and so on. And there are various other Layout Options to do with Grand Totals and so on, which you can experiment with. And then when we have the Pivot Chart selected, as we’ve all ready seen, we have the standard tabs for Design, Layout, and Format and on the Analyze tab we’ve already looked at such features as switching on and off the Field List. There’s also an option to Expand the Fields in the chart, so if I expand the entire field I get an expanded display with Boston department by department. And then, of course, I can collapse that back up again. So, that is it on Pivot Tables and Charts for now. In the next section, we’re going to start looking at Smart Art Graphics and Shapes. So I’ll see you then.



Chapter 10 – Graphics Video: Graphics Tools Toby: Hello and welcome back. In this section, we’re going to start to look at the Graphics Tools within Excel 2010. We’ll be particularly interested in how we can use them on our Graphs and Charts, but we’re going to actually approach them from the general point of view of drawing graphics using Smart Art and the various other Graphics Tools within Excel 2010 because Graphics can be quite useful just on an ordinary everyday spreadsheet to either illustrate a point, highlight a point, or just add interest to what can otherwise be a rather dull set of numbers. We’re going to start with a very simple option for drawing a graphic. On the Insert tab, in the Illustrations group, there is a Shapes button and if I click the dropdown next to that, you see a Gallery of available shapes. Now each of these is drawn as a very small icon, but in fact when you draw them they can be pretty much any size that you want.

They’re arranged into

Categories and I’m going to choose something from the Block Arrows Category. I’m going to choose that shape there, which is a pentagon. Now when I click on pentagon I don’t actually see anything except a crosshair cursor and to draw the pentagon I click and hold the mouse down and then I draw the pentagon. Now I can draw it any size I like and what happens when you draw an object like this is that Excel gives it an identifying name and that name is shown here. This is actually called Pentagon 2. Now, I need to point out one or two basic things which apply to all of the graphics that we’re going to be drawing. First of all, the graphics really have nothing to do with the worksheet cells that are underneath them. If you Format the worksheet cells, you put some numbers in there, Format with Color, it doesn’t affect the graphics at all and vice versa. Think of the graphics as something that sort of floats over the worksheet. Secondly, when you draw graphics you normally have an additional tab or sometimes more than one tab highlighted, very often it will be with a Label over the top saying Drawing Tools and then you have a Format tab which you can use to actually Format the graphic. Now this will give you options such as filling the shape, note we’ve got a blue color here now. We can change the color. It will also give you Shape Style options, Word Art Style options, and so on. You can also do things like Rotate, Align, Change the Shape, and so on. And we’ll be looking at those features as we go through. Another point to © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


bear in mind is that when you are working with graphics you very often need to resize them and we’re going to be using the sizing handles quite a bit. The sizing handles on the corners, generally speaking, maintain the proportions, the aspect of the particular graphic. The ones in the middle, this one for instance, reduces the height. This one reduces the width and so on. So that’s a few general things to remember and a couple of general rules. Now some other general rules that we can use which take a bit of getting used to, but you’ll find are going to be extremely useful. Let me just get rid of my Pentagon 2. When you’re drawing or doing certain other things to shapes, there are various keys on the keyboard you can use to constrain what you’re drawing in some way. Now let me show you a couple of example of this. Back to the Insert tab, Shapes, I’m going to choose this rectangle shape and I’m going to draw a rectangle, which is fine. Now if I hold the Shift key down and draw a rectangle, watch what happens. Insert, Shapes, rectangle, Shift key. What happens now is that I get a square and what the Shift key does generally when you’re drawing anything is that it forces symmetry. Let’s try a different shape. Let’s try an oval. Draw on oval, fine. Once again draw an oval, hold the Shift key down, we get a circle. So that’s what the Shift key does when we’re drawing. Now I’m not going to go through every shape and the effect of drawing shape of holding the Shift key down, but just to emphasize this point, the Shift key will affect most shapes that you draw in terms of symmetry in whatever way is appropriate to that shape. Let me just show you one more example. If I try to ensure say an arrow, I could very easily draw an arrow on a worksheet like that. Let me now insert an arrow again, but this time I’m going to hold the Shift key down. I cannot draw an arrow at a strange angle, it will force it to be horizontal, or if I push it hard enough up it will go to 45-degrees and in fact I can move it in 15-degree increments just, but it won’t do anything other than one of those regular increments. Obviously I can do it vertically. So that’s the constraint offered by the Shift key. Now the Control key on the other hand offers a constraint of drawing from the center. So I say wanted to Insert an oval, I’m going to put the cursor right there on the junction between those Gridlines, Columns G and H, Rows 12 to 13, hold the Control key and draw an oval. The oval is centered on that starting point. I can change the shape of it, of course, but it’s centered on that starting point. So that’s what the Control key does. And if I now say try to Insert a rectangle again, you can guess what the Control does, but if I hold Control and Shift down and draw, © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


watch what happens. It’s centered on that point, but it’s constrained to being square as well. So I can use both of those keys, Control and Shift together. And one other important key is the Alt key and what the Alt key causes as a constraint when you’re drawing is it forces, as far as it can, that the drawing follows the Gridlines, the worksheet lines on the page. So I’m going to insert a rectangle again and I’m going to do it holding the Alt key down and watch what happens. It won’t draw a rectangle that doesn’t keep to the cell borders. Now obviously with things like circles, you go about a different set of rules all together, or ovals. Hold the Alt key down and it tries to keep as far as possible, its edges, the extremes on the elliptical shape aligned with the borders. Now apart from the specific points that I’ve just made about using those keys to help you to be more accurate in producing good quality graphics, I’m going to assume from now on that you have some experience of graphics in Excel and so really what we’re going to concentrate on now is producing particular Pit Graphic Effects or Graphic Objects to assist in the presentation of your data. So we’re going to move along reasonably quickly looking at the range of facilities that are available. Now very often when you’re looking for graphics to compliment the presentation of some data Smart Art will be the starting point. The facilities in Smart Art have been improved significantly in recent releases of Excel and in Excel 2010 you’ve got a wide range of pre-packaged starting points. So we click on Insert Smart Art and we see a Smart Art Gallery which is arranged into Categories, Lists, Process, Cycle, Hierarchy, and so on. And we can take each of these and we can Customize it to our particular requirements. So let’s choose an example from the List Category and we’re going to try this particular one here. Now this is a Vertical Picture List and within the Smart Art Graphics in general there are generally two levels of text, a level one and a level two. Some of the Smart Art Graphics don’t have any text at all and some only have one level. But if I take this particular one, we have text at both levels, so there’s level one there. If I just click on it I can type in the text that I want and then at the second level additional text to follow. Now having created this Smart Art Graphic, there are a number of things I can do to change both its content and its appearance to suit my specific requirements and we have a Smart Art Tools © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


pair of tabs here, we’ve got a Design tab and a Format tab. On the Design tab we have a number of choices for Layout and there’s a whole Gallery here. I could, for instance, choose that one and the text that I’ve all ready typed in is, of course, not lost, but the whole Layout and Shape of the Smart Art changes. Apart from being able to change the Layout I can, of course, change the Style. So I could, for instance, choose a 3-D version. The other notable thing here is that we have an opportunity to change colors and there are a number of Color Schemes available. We can go, for instance, for that one and we can use these to match the other elements in our presentation. Now, note that there are several other facilities on this Design tab. One of them is that we can add a Shape. So we could, for instance, noting that Ghana is selected, we can add a Shape before or we can add a Shape after. I’m going to undo both of those. And we have a facility to move the selected item in the list down one, select one again, move it up. We can also convert a Smart Art Graphic to Shapes so that we can actually move, resize, or otherwise change any of the shapes within the Smart Art Graphic. And we have a button here which says Reset Graphic, which basically puts it back to where we started from. Another very important facility for Smart Art is over here. If we click on Text Pane, we get a Text Pane that shows all of the text that we can type. We can actually use this for entering the text and it also shows a facility to enter a graphic. Now, many of the Smart Art Graphics can accommodate an image or some other sort of graphic and you can either enter those by clicking in here, double clicking in here actually or you can click in the area within this Text Pane and it automatically opens a dialog where you can select an image, in this case the flag of Ghana or part of the flag of Ghana and clearly you could put the same image in for whatever countries are going to be in these other three parts of the Smart Art Graphic. If you then change the Layout, so for instance if I changed to that one, obviously the image is maintained as well as the text. So, in this section we’ve had a quick look and a quick revision on some of the basics of inserting graphics onto a worksheet and we’ve looked at the revised list of Smart Art Graphics in Excel 2010 and a whistle stop tour of some of the ways of changing Layout, Design, Colors, and so on. In the next section we’re going to look at Graphics in a broader context and some of the more advanced features of Graphics in Excel 2010. So, I’ll see you then.



Video: Graphics Tools Part 2 Toby: Hello and welcome back. In this section, we’re going to continue looking at Graphics and I’m going to return to the sales figures for the team of five sales people that we’ve looked at a couple of times before. Last time we used some conditional formatting with Color Scales to illustrate how well or poorly each of our five sales people is doing. This time we’re going to use some of the graphics in Smart Art we saw in the last section to do a sort of presentation display for each of these people. Now what I’m going to do is I’m going to pretend that we have a Live Data Feed for sales for each of these people. I’m actually doing this in the month of May and I’m going to get rid of these June figures for the moment and for the May figures I’m going to remove the conditional formatting. And I’m going to assume that for each of the sales people they’ve been given a target for the month of May, which I’m going to put in the next column. And I’m going to record in the H Column their progress towards that target as a percentage. So, in Anne’s case this will be F2 divided by G2. I then Format Cells, change it to a percentage and I’ll say no decimal places. I can then Fill that down and I can see the percentages for each of the sales people, their target number or value of sales, and their actual performance so far in the month. Now what I’m going to do is to Insert a Smart Art Graphic. And I’m going to choose one of the Hierarchy graphics. I’m going to actually choose that one, Table Hierarchy. Now what I plan to do is to have a Heading on the whole graphic, but to have one leg of the graphic for each of my five sales people. So, I need the five legs to be equal, so I’m going to select this one and press Delete and then I’m going to select that one, Add Shape After, and with the newly selected one, Add Shape Below, repeat that exercise twice more. And there we are. Now I have the five legs that I’m going to use in my graphic. Now, let me explain what’s going on. What I’m going to do is to put some effectively Live Data onto a graphic as a means of presenting it other than the graphs and charts that we’ve seen so far, but I want to use the graphic capabilities of Excel 2010 as far as I can. Now I can’t actually with this as a Smart Art Graphic do all the things I need to do in terms of putting the progress © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


indicators and so on on there, but I can do virtually all of the presentational side and then I can convert it from a Smart Art Graphic into individual shapes and then I can the data to the shapes in a very straightforward way. So, first of all I’m going to get the graphic side sorted out then I’m going to put the content into it. So first of all I’m going to just move the graphic a little and then increase the size. Okay. And then on the Page Layout tab I’m going to choose a theme for it. It’s currently the Black Tie theme according to the Default, so let’s try something else. Something that’s not too awful. Let’s try the Hard Cover theme and then having chosen a theme we now click in there again, go back into the Smart Art Tools Design tab and we’re going to choose a style. Now there’s a number of styles available, let’s go for one of these 3-D ones, Polished, Inset, Cartoon. Let’s go for Inset I think. Okay. And then we can change the color and again drop down on there, Primary Theme Colors are these. I think we can probably stick with that or should we try Accent. Accent 2, Accent 5, yes, I think I quite like the look of that one. Okay. So now I’d like to change the shape of the Top Box here. I’ve just got the Level 1 Box selected and on the Format tab under Smart Art Tools, I can change the shape to any one of these available shapes. I’m going to change it to that sort of horizontal scroll shape there and in fact maybe we can change these, what about that sort of shape, yes. Now it’s important to recognize that although I can do all of this shape changing here, working with the Smart Art Graphics, when I actually come to putting the data in here from the worksheet I won’t actually be able to dynamically increase the number of employees or change the shape of the box or whatever. I can sort of put the numbers in, but there’s a limited amount of Live Formatting that we can do in this situation. Okay, we’ve done what we can. Now we go back to the Design tab under Smart Art Tools and we say Convert to Shapes. And what actually happens now is that we have a set, a group of shapes that can receive data from the worksheet but that are no longer a Smart Art Tool. So, let’s choose one of the shapes. Let’s choose this one. Now I’m going to put Anne Carnegie’s name in this shape. Anne’s name is in cell A2 so I can just put equals A2. Now clearly the Font there is too large a size. On the Home tab it shows that it’s 23 point. I’m going to put it at a much smaller size, let’s say 11, then click on the next block © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


and that’s Scott Denvers name, tick that, and again reduce it to 11 point. And of course I can now go through the rest of these and assign the names accordingly. So with a tiny bit more adjustment I managed to make the rest of those fit. Now I’m going to put in the progress indicators, for Anne that is H2, and again I can do the same for all of the others. So there we are. That’s now beginning to look pretty good. Now when you’re presenting data using this sort of graphic approach you probably wouldn’t, generally speaking, have the table of data behind. You’d either be making this on a blank sheet or you’d be moving it out of the way as we did before when we were working on Pivot Charts and doing it elsewhere on the same sheet. I’m going to do one more thing now just to put in the Heading here and I’m going to work on that further down the sheet just so that it’s not quite so visible. I’m just click in anywhere and I’m going to type in something to use in the Heading. So it’s going to be “Team Progress” as at ampersand, now I need to Convert now into a Format of will put the month, the day of the month, and the time of day I think. That should be about right. May 13, 18:22. That seems good to me. And then it just remains for me to put that into that Banner, so that I know that is B32, tick that. I think we need a smaller point size there. What is, 30, 28, 24, 24 maybe. There we are; that’s not bad. So there we are. That’s a presentation of my data not using our conventional graph or chart, but using a graphic that I built using Smart Art and then basically broken into individual graphic elements and my data is now on each of those individual graphic elements. Now I mentioned before about over-lapping the presentation and the source data on the spreadsheet. It’s actually quite easy to move this somewhere else. I could, for instance, Create a new sheet, Sheet 7 there, back to the target sheet that I’ve been using. Make sure that this group is selected because although it’s not a Smart Art Graphic any more, it’s still a group of individual graphic elements, so I can Copy or just use Control-C, keyboard shortcut, go to Sheet 7 and Paste. And of course the groups there and I can move it and process it as a group as before. So if I want to use that elsewhere, including in other documents, then it’s readily moveable as a group. And we’re going to look at transferring this to other documents later on.



So that’s it for this section. But in the next section we’re going to continue with the graphics theme. Although we’ll be concentrating on linking to other types of documents, so we’ll be looking at how we can use our Excel Charts, Graphics, and Graphs in for example Word Documents. So, I’ll see you then.



Chapter 11 – Exporting Video: Exporting Charts and Graphs Toby: Hello and welcome back. In this section, we’re going to look at Exporting Excel Charts and Graphs into other applications and in particular into the other main components within Office 2010. Office 2010 now offers a high level of consistency and compatibility between its various components as all of the major ones use the same charting engine. However, it’s important to be cautious about compatibility with earlier versions of the components of the Office Suite. For example, if you make a chart in Excel 2010 and you’re going to use it in a document that’s maintained in Word 2003, then you won’t have this high level of compatibility and you won’t have many of the features that we’re going to be looking at in this section.

So you need to be a little bit wearier when you’re doing anything in

Compatibility Mode, for example. Having said that, virtually everything we’re going to look at in this section is primarily about Office 2010 and I’ll point out any of the most obvious issues that are going to arise, but basically I’m going to assume that you’re working within Office 2010 throughout. So let’s start by returning to the academic achievement spreadsheet that we looked before. For a particular student we have some target grades, we have scores, and we have the Radar Chart that we drew showing performance basically of score against target grade. Now if I Copy the chart by selecting it and then either using the Copy button on the Home tab or using the Control-C shortcut, I Copy it to the Clipboard and then go to Word where I have created an empty new document and just click the Paste button. The chart is Pasted into the Word document. If I now go back to Excel and change the scores, let me change all the scores to fives and you watch the effect on the chart. We now see that on the Radar Chart, the whole of the perimeter is red, go back to the Word document, the update has happened automatically. Now let me Delete the chart from the Word document, go back to the source Excel document, copy the chart again, back to the Word document, and this time I’m going to do the Paste in a different way. So I’m going to choose from the Paste Options at the bottom, one of which is Picture and I’m going to Paste it as a Picture. Now go back to the Excel document and I’m © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


going to change the scores to 4-5-4-5-4-5-4-5-4-5 and so on. So I’ve changed the scores. You can see the effect on the chart there. Back to the Word document and there is no change. In fact, if you Paste as a Picture then you have a snap shot of exactly how the chart was at the time that you pasted it and that will never be changed by anything you do in the Excel document. Now there are occasions when you would want this behavior. You don’t want a linkage, you don’t want subsequent on the Excel Chart to affect the chart as it’s seen in the Word document and so Pasting it as a Picture is one option that achieves that effect. Now let’s try another option. If we go back to the Excel spreadsheet and Copy the chart again, back into the Word document, but this time we do Paste, Paste Special, and then select Paste Link and Microsoft Excel Chart Object, click on OK and the chart is Pasted again. Note we’ve got the red perimeter with the score of five in each of the subjects. Let’s now go back into the Excel Chart and let’s change the scores to 1-5-1-5-1-5-1-5-1, so we’ve got that sort of star effect. Let’s see what happens in the Word document. In the Word document actually nothing’s happened. It looks exactly the same as it did before. If I right click on the chart in the Word document however, there is an Update Link Option, and if I click on Update Link I get the updates. That gives me the option then of manually updating from the Excel spreadsheet instead of it happening automatically. So we’ve seen options to prevent any updates, to automatically update, and to allow manual updates and they’re the three main options that are available in terms of the data. There are other ways of achieving these by the way, but let’s turn our attention now to Formatting and Style. So we’re returning now to the spreadsheet with the details of iPod sales from 2002 to 2010. If I select the year range and then select the sales figures and use Alt-F1 to create a Default chart, notice that this Default chart has no additional formatting. It’s come off exactly the way that it does as a Default Column Chart. And then if I go into Page Layout and Themes, let me choose a basic theme, in fact let’s choose the Office Theme. Now, if I Copy that chart, go back to Word, and I want to Paste this chart now into Word, what I’m going to do is first of all go to Page Layout in Word, Themes, and choose the Foundry Theme. Now the Foundry Theme is one with a Seraph Font and a very different look and feel to the Office one that was in use before. Now Home tab and for Paste I’m going to choose the first Paste option, use Destination Theme, and



Embed Workbook. Now note what has happened. I’ve Pasted the chart in, but the look and the feel of the chart has been changed by the use of a different theme. Now just to emphasize that point, let’s put in a couple of return characters. Let’s do the Paste again, but this time we’re going to do Paste with Keep Source Formatting and Embed Workbook.

Now we get exactly the same information but now we have the theme, the

formatting in fact, that was used in Excel and that’s really the beginning of the second major aspect of how we Export from Excel into the other components of Office. If we want to adopt the style of the destination document, so if we were say putting this into a Word document using a particular theme, it’s important that we do the Paste using the Destination Theme option and then if we add to that the issue of updating the source document, we can see again it’s important to understand what the affects are. And we’re going to look at that next. So let’s first of all zoom out a little and I’m going to Paste again, but this time I’m going to do a Paste Special and with Paste Special I choose Paste Link, Microsoft Excel Chart Object, OK. It looks exactly the same as the one above it, but note what happened when with regarding the data when I did this Paste Link before. Let’s now go back to the Excel spreadsheet. The charts all ready selected. On the Format tab look at Shape Fill, perhaps go for a Gradient Fill and let’s try one of these Gradient Fills. So note the effect on the chart in Excel. Let’s now go back into Word and you can immediately see the difference between those two. Both of the original Pastes that were done, there’s been no change in them by changing the Excel source document, whereas with the one where we did the Paste Link the update has been carried out immediately. Now, we need to look in a little bit more detail at exactly what’s going on here. When we create a Link, the Link is between the source document, in this case an Excel workbook with some data and some charts in it, and a destination document and we’ve been using a Word document. But we could, for example, have been using a PowerPoint document. For that Link to work both ends of the Link need to be there and what would happen if, for instance, I wanted to prepare a workbook, put some data in it, prepare some charts, and from those charts create say a Power Point presentation or a Word report, what would happen if I separated the two? What would happen if the source document were no longer available? Well the simple answer to that is the Link will be broken and the Linkage would be broken. Any changes I made in the source document would no longer be reflected in the destination document. Now there in itself may not © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


be a huge problem, but there may be a situation where I want to be able to make changes to the chart or the data in the Word document or the Power Point document, but for some reason rather I cannot keep it Linked to the Excel source, so what do I do in those cases? Well what do I do in those cases is I Embed the Excel data and I’m now going to show you how to do that. So let’s once again Copy this chart, switch to Word, and look carefully at the Paste options. The first is Use Destination Theme and Embed Workbook, the next one, Keep Source Formatting and Embed Workbook, Use Destination Theme and Link Data, Keep Source Formatting and Link Data and finally, Picture. Now Picture we know basically means that we’re putting the chart in there as a picture, we can’t actually change the data, link it to the data. We could put a Border or a Frame around it, but that’s about it. But the other four really combine three different things. One of them is Use Destination Theme that we’ve all ready looked at, which is whereby the chart picks up the theme in the Destination document. The other one that two of them use is Link Data, which basically means that the destination document or the chart in the destination document is Linked directly to the source data in the source document and it’s very important to realize that when this embedding takes place the whole workbook is embedded in the destination document; all the sheets, all the charts, everything, so that it’s all available to be edited directly from the destination document. So, what we’re going to do is we’re going to choose Keep Source Formatting and Embed Workbook. And then what I’m now going to do is I’m going to go back into Excel, close the source document, and I’m actually going to rename the source document so that it’s definitely not available to the destination document. So, here I have my Word document with my embedded chart in it. The Menu options on there include Edit Data and if I click on Edit Data what happens is that Word 2010 basically opens an Excel instance here with the source data in it and I can go into that Excel instance, I can change my source data. So, for instance, let me change one of those totals which say 937,000; let me change it to say 20,937,000. Note what happens here in the chart, obviously the chart gets a big column there built up and exactly the same in the Word document. And as you can see that embedded Excel object has all the sheets of data, including the other charts that we drew and when I finished working on those I merely close them down and I’m back at my Word document ready to carry on in whatever way I want to.



So, in the next section we’re going to complete our review of these ways of exporting charts from Excel into other applications. I’ll see you then.



Video: Exporting Charts and Graphs Part 2 Toby: Hello and welcome back. In the last section we looked at exporting charts and the associated data from Excel into other components of Microsoft Office. Notably Word, although pretty much exactly the same procedures operate for PowerPoint. In this section, we’re going to just sum up one or two of the main points and look at another couple of options available to us. One of the main factors we looked at was the choice between Linking and Embedding. With Linking a connection is set up, a Link, between the source document and Excel workbook and the destination document, which may be, for example, a Word document whereby as we make changes in the source document those changes are reflected in the destination document. This is often a very good arrangement, but not always. There are some situations where it either doesn’t work or it’s really not the right way to set things up. For example, we wouldn’t use this situation where somebody is going to take the destination document away, say on a laptop to do a presentation and the source document is no longer available. You might argue well the source document could go with them, but the source document may be feeding various other destination documents as well, so that may not be possible. So, Linkage will work in many situations, but not all. When we achieve this by embedding, we basically put a copy of the workbook in the destination document as an embedded object. That has some advantages because the destination document then becomes self-sufficient and somebody using the destination document can even update the data, reformat the graphs or charts that they want to show and so on. But clearly there are disadvantages with this approach as well, in that if the data that we’re showing in the destination document actually derives and is maintained in a master Excel workbook, breaking the destination document away from that master workbook is probably quite a dangerous thing to do and it would be easy for things to get out of step. The other point about embedding is that sometimes it’s dangerous because when we do embedding, we embed a complete workbook, not just one sheet or one chart that we’re interested in. And there may be confidentiality issues associated with that.



Now one other useful alternative here is to use a feature that’s present in both PowerPoint and Word which can give us a good compromised solution which is suitable for many situations. I’m going to actually demonstrate it in Power Point. I’m going to create a chart to illustrate the iPod sales figures again. So in Power Point I say new slide and in the middle of the new slide area there is a button, Insert Chart. Now watch carefully what happens when I click on Insert Chart. I select the Chart Type. I’m going to select a Clustered Cone, click on OK, and what happens if I don’t already have some data selected is that Power Point creates a Default chart of the type requested with some Default data and it opens side-by-side two windows, one of them a Power Point window with the charts in it and the other one an Excel window with the Default data in it. And the general approach here is that having created that Default chart, I can now substitute the data that I really want to chart for this data and then I’ll finish up with the chart that I want. So all I need to do now is to select the years from my original iPod sales spreadsheet and switch back to the spreadsheet within Power Point.

Okay.

Then again back to my iPod sales

spreadsheet to get to the actual sales data, which I also Copy, Power Point, Paste there. Then I take the corner of the selected data area and drag it to be the right size, but just to tidy up it’s best to get rid of the unwanted data as well. Then I can close that and you can see that my chart has now been created. It is effectively now still an embedded chart, but I don’t have all of the other sheets that were in my original iPod sales workbook. All I’ve done is copy and pasted the contents of one sheet into the sheet that is now embedded within this PowerPoint presentation. Now, of course, what I have in my PowerPoint presentation is pretty much the same chart that I would’ve got if I created it directly in Excel. With the chart selected I have the normal three tabs: Design, Layout, Format. From the Design tab I can, for instance, go into Edit Data and that gives me access to the table of data specifically that is embedded in this PowerPoint presentation. I haven’t copied all of the original data so I haven’t say got the quarterly figures, although of course I could’ve pasted those in and I could’ve carried as much of the data from the original spreadsheet workbook with me as I wanted to, but I only copied the column with the years and the column with the totals.



Now I can, of course, use all of the other standard facilities under Chart Tools. I’ve all ready changed the Title here. I can Format the Axes. I can change the Chart Layout if I want to, choose a different Style, and as I mentioned before, the advantage now is that all of the major components within Office 2010 share the same Charting Engine, so all the things we saw earlier on about formatting this chart still apply but from within PowerPoint. We now have another important Export option to consider and that is the option of Exporting a Chart or Graph for use on the web. And the way we do this in Excel 2010 is as follows. First of all, given the chart or graph I’m going to use the chart we had for our insurance sales organization showing inquiries and policies by month over a 12 month period. First of all, select the chart and we will get the three tabs: Design, Layout, Format. Select the Design tab and one of the options on the Design tab, right over on the right is Move Chart. Now we’re given an option of moving the chart to a new sheet or to one of the existing sheets, so it becomes an object in that sheet. Now I know for a fact that Sheet 3 is currently empty so I’m going to move it to Sheet 3. Click on OK and there it is on its own on Sheet 3. Now I go into Backstage View, select Save As. I have a little folder set up on the desktop called Excel Temp and I’m going to save it in that Folder. I’m not going to save it as a workbook though, I’m going to save it as a webpage. Now when I select webpage in the Save As list, I’m given the option of saving the entire workbook or just the selection, which is actually a single sheet. Now having selected the sheet already I then click on Publish and I’m given the options of Items to Publish, choose Items on Sheet 3 or Contents of Sheet 3. There’s a drop down there. I could, of course, choose a different sheet if I wanted to here, but I’m only going to go for what’s on Sheet 3 and click on Publish, and there we are. If I now switch to that folder, Excel Temp, there is an .html file which is basically insurance sales to htm. Go into the insurance sales files and one of the items on there is a .png, a Portable Network Graphics file, a .png file showing my chart. And of course I can use that .png on a webpage in the usual way. And there are a couple of other things to bear in mind in terms of exporting and using Excel 2010 graphs and charts. One of them is associated with a new feature in Office 2010, which is the Screen Shot feature. If I’m working on say a Word document, one of the options on the Insert Menu is Screen Shot and with the Screen Shot feature when you click on it available © Copyright 2008-2012 Simon Sez IT, LLC. All Rights Reserved.


windows are shown, so the other windows that are available and open are shown; in this case I’ve got the window with that chart on it. And you also have a Screen Clipping facility where you can not only Paste in what’s on one of the available windows, but you can actually clip it to exactly the right size for your requirements. So if I click here on Screen Clipping, I get a slightly grayed out version of that screen and using the crosshair cursor I can select the actual chart that I want and then that is pasted into my Word document. Now of course the effect of this is pretty much the same as if you’d pasted from Excel as a Picture, or in fact created say a .png file and pasted it in. None of the associated data is there, all you get is the chart and of course as we’ve seen already there are advantages and disadvantages to that approach. And sticking with the other options for Exporting a chart like this one, again into Backstage View, File, Save As, one of the other Save As options we have is the option to save as an .xps document.

This is, of course, the Extended Paper Specification Extension introduced by

Microsoft and we have some optimization options here, Standard which is usually for publishing or printing and then Minimum Size if you wish to Publish online. Again, I’m going to choose the same folder on my desktop and I can save this as an .xps file if I’m not worried about minimizing size I can just export it as a Standard, Insurance sales.xps. Okay. And then another option is pretty much the same really, File, Save As, and of course we have the .pdf option, the Portable Document Format Option that pretty much everybody is familiar with. You’ll see here actually that there are many options on here. Many of them are associated with various versions of Excel, but some of them like .pdf are actually particularly useful and again you’ve got a range of facilities in terms of size and so on. Save as usual; let’s go to our specific folder, do a Save, and now if I switch back into the folder I can see a .pdf file which opens in Acrobat Reader and I can see an .xps file that opens in Microsoft’s .xps Viewer. So that’s pretty much it from me on this Excel 2010 Charts and Graphs Advanced Course. I hope you’ve enjoyed working your way through it as much as I’ve enjoyed delivering it to you. Don’t forget the Excel Help which is very useful and by all means check out the various forums, the Office online material, the MSDN sources and so on for more information and help on these topics. I hope to talk to you again soon, in the meantime, goodbye.
