## Learn Excel 2013 Advanced - Bitly

Let's go back up to the top of this page though. Right at the top here, there's a note that says Important and that's exactly what it is. Some of you will be using Excel on devices with ARM processors. So you may be running ...... speed of your internet connection, you may have to be a little bit more patient than usual but it.

Table of Contents Chapter 1 – Introduction Pre-requisites of Excel 2013 Advanced Course.......................................................6 Chapter 2 – Functions List of Functions; Common Types of Arguments; Basic Use of Functions ..........10 Advanced Aspects of Functions; Searching for a Function ...................................15 AutoSum ................................................................................................................19 Chapter 3 – Personal and Professional Financial Functions Payment..................................................................................................................21 Future Value...........................................................................................................25 Principle Payment ..................................................................................................29 Depreciations .........................................................................................................33 Chapter 4 – Date and Time Functions How Dates and Times are Stored...........................................................................37 Dates and Times Calculation .................................................................................42 Exercise on Date and Time Functions; Date and Time Settings ...........................46 Chapter 5 – Statistical Functions for Description, Prediction and Interface Average ..................................................................................................................49 Percentile................................................................................................................54 Regression ..............................................................................................................58 LINEST Function...................................................................................................63

Example of Functions for Inferential Statistics .....................................................67 Chapter 6 – Text Functions Text Functions Case Study – 1 ..............................................................................72 Text Function Case Study – 2 ................................................................................77 Chapter 7 – Lookup Functions Lookup and Reference Functions ..........................................................................81 Chapter 8 – Logical Functions Logical Functions...................................................................................................86 Chapter 9 – Connecting to External Data Connecting to Other Workbooks ...........................................................................91 Connecting to Access Data ....................................................................................97 Connecting to Web Data ......................................................................................102 Chapter 10 – Tables Creating a Table and Changing Table Name; Filter; Removing Duplicates; Total Row ..............................................................................................................................106 Inserting Columns and Rows; Resizing Tables ...................................................111 Table Reference ...................................................................................................116 Table Styles ..........................................................................................................119 Table Selection.....................................................................................................122 Chapter 11 – Pivot Tables Basic Principles of Pivot Tables ..........................................................................124

Presentational Side of Pivot Tables .....................................................................130 Filtering in Pivot Tables; Slices ...........................................................................136 Chapter 12 – Data Analysis What if .................................................................................................................141 Scenario Manager ................................................................................................146 Goal Seek .............................................................................................................151 Solver ...................................................................................................................154 Chapter 13 – Charts Area Charts ..........................................................................................................159 Surface Charts ......................................................................................................163 Stock Charts – Part 1............................................................................................166 Stock Charts – Part 2............................................................................................171 Radar Charts.........................................................................................................176 Bubble Charts.......................................................................................................178 Chapter 14 – Graphs Regression and Scatter Chart ...............................................................................182 Chapter 15 – Pivot Charts Creating Pivot Charts ...........................................................................................185 Chapter 16 – Sparklines Using Sparklines ..................................................................................................190 Chapter 17 – Web App

Creating Workbooks using a Web App ...............................................................196 Sharing your Workbook .......................................................................................202 Chapter 18 – Conclusion Closing .................................................................................................................206

Excel 2013 - Advanced number of different pieces of software that I use. But some of the common keyboard shortcuts like things for cut, copy, and paste, creating new workbooks, saving files, and so on which are pretty much common across certainly all of the components of Office 2013 I do use. So I have my own combination of keyboard shortcuts that I use, although the majority I don’t. I won’t be going into things like the keyboard shortcuts of everything in detail on this advanced course. Via the online Help you can get a list of those. If you prefer using keyboard shortcuts, then I’m afraid I’m going to have to leave that to you. Although, as I say, I do use them in certain situations and I will mention them from time to time. The second important point to mention is that the content of this course is quite technical in places and some of the applications that we cover, statistics, math’s, engineering, finance, and so on, are really quite specialist applications. This is a course on Excel. It’s not a course on statistics. So although there is a certain amount of statistics in the course I won’t be spending a long time explaining to you what regression and correlation are. So if we look at a particular topic and it’s not a topic you know anything about, I will try to cover the basics but you may need to do a little bit of separate reading or investigation to find out more about that topic. Having said that, I’ve tried to choose case studies, applications, and examples that are as far as possible in contact with most people’s everyday lives. So when it comes to things like financial applications, we’re looking at things like house loans or car loans and repayments, that sort of thing, not some sort of specialist financial application. So there we are. That’s all the preparatory material I intend to cover. In the next section, we’re going to get started on doing some work with Excel 2013. So please join me for that.

Excel 2013 - Advanced I’m just going to add those up. I put in the Sum function. That’s a function you should certainly know. Then always there are parentheses. Even for functions where you don’t need to do anything more than know the name of the function, you will always have parentheses; open parentheses, close parentheses. And as soon as I type the parentheses there, I’m given a sort of hint which basically gives me a list of arguments. Now I’m going to talk a little bit more about arguments in a moment. But basically in this case, you can see you’ve got a bold argument which means there must be at least that first one, and then a comma. Arguments are separated by commas, and then the later ones which are in brackets are then optional. Now I could if I wanted to in that particular function list of arguments there I could put something like 3.0, comma, 5.2, then close the brackets, and tick to enter that function name and there we are, 8.2. Of course, it’s got nothing to do with the five numbers above it but you can put literal arguments in, actual values in there. It’s not something you’ll often do but there’s nothing to stop you doing it. So let’s now work towards summing those five numbers. I’m going to dwell on this a little bit because it’s important to understand some of the rules about arguments. Let me click back into that Sum function again, into the list of arguments, and this time I’m going to delete the arguments that were there before and instead of those I’m going to put in the cell references and I can always use cell references. As I put in the cell references, note the coloring of the cell references, the coloring also relates to the coloring on the border of each cell in the worksheet. So I’ve got a blue C2, a red C3, and so on and I can list the cell references. Again, tick it and now I’ve got a much more sensible use of a function. So the function arguments can certainly be cell references. Now let’s suppose that I really want to do is to put in a range, and a range is very often what I will be summing. So let me suppose I put in sum C2, as you should know, colon, C4 specifies a range. Notice how when I do that I have the range highlighted in blue covering those three cells. Also note the little selection handles at the four corners of that cell range. If I hover over the bottom right hand one, I can adjust the cell range using the mouse and you’ll see that reflected in the formula in the entry bar. And that’s probably something that you use regularly already. Again, when I’ve finished I’ve got a sum C2 to C6, tick in the entry bar, and now I have the arguments expressed as a range.

Excel 2013 - Advanced Let me just demonstrate this once. I won’t keep doing this but I think it’s useful for those of you who are using touch to see that it works pretty much the same way. If I switch into touch mode and then just select the cell there with the formula in it, you can see how I get those round handles for touch mode. Keep an eye on the formula in the entry bar. I’m using my fingers now to change that selection and as I change the selection, you’ll see it change in the entry bar as well and you can see the markers where my fingers are on the screen. So I’ve shown you there the three most common types of argument that you’re going to use in a function, so actual values, individual cell references, ranges. Of course, you can also have references to other sheets or even other workbooks within a function. Let’s look at a couple of special cases here. Can you think of an example of a function with no arguments? Well, here’s one, pie, tick, and that gives you the approximate value of pie. And also one of the main syntactic rules is I don’t have a space between the name of the function and the parentheses. So if I type in here Equal sum, space, open brackets then I can type in something similar to what I did before but I just get an error message because that space stops Excel 2013 from seeing that that is one of its functions, so I’ll just get an error message. Another very important point with functions is that you can nest functions. So, for instance, in the sum function here, I could say comma, pie and what that would do is to say calculate the sum of cell C2 to C6 and pie. Note I must have the parentheses with pie for it to be recognized as a function. Tick that and that’s the sum of those five numbers and pie. So that’s a pretty quick recap of the basics of using functions. If you haven’t used formulas and functions a lot before, there are a couple of very useful Help pages that you might want to work through. One of them is Overview of formulas and that gives you some good examples of the use of formulas, explains some of the things that I’ve done about using functions, constants, cell references, and so on. If you’re not comfortable with those, I think it’s a really good idea to go through that. Another very useful page is this one and I suppose that to some extent this page is aimed at the opposite end of the function spectrum in terms of knowledge. This is really meant for people who’ve used functions a lot in the past and your question is so what’s new in Excel 2013? Well, there is a Help page dedicated to which are the new functions in Excel 2013. So that’s another one that’s well worth looking at.

Excel 2013 - Advanced For those of you who are really hot on functions, this is also a very important page in Excel 2013 Help as well because it lists compatibility functions. These are functions that have actually been superseded by newer functions, better functions, but they’re still provided in Excel 2013 for compatibility reasons. So if you want to make sure whether any of these functions maybe you use already, there’s a better alternative than your alternative or just to understand what’s changed then that’s an important list for you as well. As the reference says here, For more information about the functions that are replacing these compatibility functions, see Statistical functions reference. So particularly in relation to these types of statistical function, then looking at what they’ve been replaced by is pretty important. So that’s it on the basics of functions and what we’re going to look at in the next section is some of the more advanced techniques for working with functions before we move on to some case studies and applications. So I’ll see you in the next section.

The question mark denoting the conventional Help in the Office 2013

applications, if you click on that, the Help that you get relates to how to use this dialogue. In fact, slightly more generally how to create a formula that uses or includes a function. So that’s the general Help on working with formulas and functions. Whereas at the bottom left of the dialog given a selected function, let’s suppose I select this one Cube set, Help on this function gives me access to the Help specifically on the selected function. So if you’re not sure about a particular function, what it does and how to use it, you’ve got direct access to the relevant Help there. Now in terms of finding a specific function, if you know the name and you don’t really want to go around the function library on the Formulas tab, you can select the category here and look for that particular function within any one of the categories. If you choose a particular category and © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced bar, Equals, and start typing. So A, and what it comes up with is a list of the functions that begin with the letter A. Now notice that depending on which one is selected by default, in this case ABS, a little tool tip appears on the right. Returns the absolute value of a number; a number without its sign and you can actually move that tool tip. If you hover over it, it becomes a crosshair cursor. You can put that tool tip somewhere else. One of the reasons for being able to do that is that when you’re working in a particular part of a worksheet, you may well find that that tool tip is in the way. Well, it’s as easy as that to move it. And, of course, if I type a second letter, third letter, and so on it’s going to narrow down the choice of available functions. So say I type an M next. There are only two functions that begin with A-M and again I’ve got tool tips that I can move. So that can be a very helpful way. If you’ve got a reasonable idea of what a function name is a combination of that, and using the tool tip, you can normally find the one that you want quite quickly. Now once you’ve chosen the function, let’s suppose in this case that we’re going to choose the first function there, not only do we get the function with the open parentheses but we get an improved tool tip which includes both the function name and the list of arguments. And in fact if you hover over the function name in that new tool tip, you notice it becomes underlined and that’s a hyperlink through to the relevant Help page. Now as I mentioned earlier on, any arguments that are in square brackets such as Basis here are optional arguments and unless you can remember exactly what the options are in a particular case, it’s useful to be able to just flick through using this link here to the Help. So in the case of the function we’re looking at here, the AMORDEGRC function, if you look at the optional argument there which was the last one, the basis argument, Basis optional, the year basis to be used zero or omitted. So that is that if we don’t put any value in there at all, then the basis is 360 days using the NESD method. I won’t trouble you with what that means now. One means the actual year basis, three is 365 days in a year, and four is 360 days in a year, the European method, and I’ll leave you to look up exactly what each of those mean. But particularly if you’re using a function you’re not familiar with, you may well need to just flip into the Help there for any optional arguments to check what the options are or indeed what happens if you omit the argument.

Excel 2013 - Advanced So that’s a pretty useful way of finding out about optional arguments or indeed any arguments using the Help facility, but there’s another really great way of doing this in Excel. Let’s go for that same function again. So I’m going to just select that function but instead of clicking on the Help, I’m going to press Control and A and what I get is the Function Arguments dialog which lets me enter a value for each of the arguments but then will also give me an explanation of what each of the arguments means.

If like me you have trouble remembering those keyboard

shortcuts, if you’ve identified a function such as this one, selected it, and just got as far as the function and the first parentheses, there’s an Effects button here on the left and if you click on that, that also gives you the Function Arguments dialog. Now the Function Arguments dialog is a non-modal dialog so you can keep it open while you work on the function. And not only can you type in values such as cost, date, purchase, but you can also refer to cells or ranges within your workbook. So for instance, if I wanted to pick up the cost from cell A4, I realize that A4 here is empty. But if I wanted to pick up it from A4, if I just click in A4, then the cell reference there is copied straight into the cost field in the Function Arguments dialog. We’re going to be using the Function Arguments dialog a little bit later on when we start working through the case studies, so I’m not going to dwell on it here. But hopefully you can get a good idea of how to use the Function Arguments dialog already; as I say, more on that later. So having looked at some of the more advanced facilities available, generally for functions we’re nearly ready to start using them in some specific case studies but before we do I’d like to look at a particular range of functions that have a special status within Excel really, and that’s the AutoSum functions. They’ve even got their own button here in the function library. So in the next section we’re going to take a quick look at some special things to do with AutoSum and then after that we’ll start working on our examples and case studies. So please join me in the next section.

Excel 2013 - Advanced won’t be liable for the interest on that period. Whereas if you make the payment at the end of the period, at the end of the month for example, then you will be liable for interest on the period to which the payment refers. So that’s our five arguments. The last two of them are optional ones. Bear in mind that if you’ve got two optional arguments as you have here, if you need to specify, say, the last one, the fifth one, Type in our case, and not the fourth one Fv, then the way you would actually indicate that when using the function is you’d have to successive commas. So you’d have two commas with nothing between them. But what we’re going to do is to pick up the values for the arguments for our use of the function from the worksheet. So what I’m going to do now is to basically fill out the names in column B where I put things like Rate and Nper and Pv and so on, I’m going to type in the correct names and it’ll make it a lot more easy for somebody to understand what we’re doing with this worksheet. So I’ll do that and join me again in a moment. So what I’ve done here is to lay this out a little bit more neatly and what I’m going to do is to now put in that payment function. So it’s Equals, PMT, click on Function. Now notice that apart from setting up the five cells that I’m putting the arguments in here, C2 to C6, I’ve put a little explanation in the D column for each of those five items. So somebody using my little worksheet is going to understand what’s got to go in, in each case. Now the Rate now is the C2. So while I’ve got Rate selected in function arguments, I click on C2. The Number of Payments, that’s that one. That’s going to be the contents of C3. Pv, Present Value, that’s that one. Fv, Future Value, that’s that one. And then Type, that’s C6. Now the only one of those that needs any separate treatment is the Rate one because the rate here to be consistent in terms of dealing with months we need to divide the annual interest rate by 12. So we literally just type in Divided by 12, and there we are. We have all of our function arguments setup correctly. I haven’t put in any numeric values in there yet. I’m going to come to that in just a moment. But if we click on OK although we get obviously no sensible answer, you can see how the PMT function looks in C8. It’s Equals PMT, C2 divided by 12, C3, C4, C5, C6. So let’s now see what happens when we put some numbers in. Okay. My loan is going to be \$5,000 over five years at 5% interest rate. I’m going to pay off the full amount of the loan. So the rate goes in as 5%. The number of payments in months, five years so it’s 60 months. The principle of the loan is going to be \$5,000. And as soon as I get © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced that far, I get an answer basically. Now the answer is in red in brackets. That means it’s minus. That indicates that it’s a payment. It’s a negative amount of money. So if I just under \$95 a month, I can borrow \$5,000 over five years at 5% interest. Now one of the things to be very careful of when you’re dealing with financial functions is the use of plus and minus amounts, credits and debits. At the moment, with this particular loan we’re paying off the loan principle of \$5,000, coming up with a cash balance of zero. If I wasn’t required to pay off the full loan, so I was only going to pay off say \$4,000 out of \$5,000 for some reason, the future value would not be \$1,000. It would not be amount that I was in credit as such. It would be -\$1,000 because basically I would still be \$1,000 in debits, still owing \$1,000. So if I put in as my future value -\$1,000, let me just enter that, then my monthly payment to achieve a situation where I still owe \$1,000 would be less than it is now. So if I enter that, it’s a payment of \$79.65, but then at the end of that I still owe \$1,000. If on the other hand as part of this loan I also wanted to finish up with cash in hand at the end, then I would need to put that amount as +\$1,000. Again, enter that and the monthly payment in order to finish up with cash in hand would be more than the original amount, \$109.06. But for the moment, I’m going to assume it’s a straightforward loan, that the cash balance is zero, go back to the figure that I started with, and now let’s look finally at Type. Now as I said before, the Type really reflects whether you need to pay interest on the payment period to which the payment refers. So if I want to change from the default zero or omitted or payment at the end of the month, if I specify payment at the start of the month would the payments per month go up or down? Now you should realize that the payments will actually go down if I specify a one there because I’m paying at the start of the period and therefore I won’t be liable to the interest. So I’ll tick that and the payment goes down by about 40 cents. It goes down to \$93.96. So there we are. That’s an example of one of the financial functions in Excel 2013 that can be used for personal financial calculations. In the next section we’re going to look at a couple of other examples of those types of functions so please join me for that.

Video: Future Value Toby: Hello again and welcome back to our course on Excel 2013 Advanced. In the previous section we looked at the PMT function and in this section we’re going to start by looking at one of the other very commonly used personal finance functions which is the Fv function or Future Value function. This is the one that’s typically used to work out the value of an investment at the end of a period of time. The example I’m going to use here is the idea of saving for retirement. So, of course, if I want to locate a particular function, I can do it in the way that I did in the previous section or, of course, if I go to the Formulas tab and look under financial, one of the functions down there Fv. Note that if I hover over that I get the tip that tells me what I’m going to need to put in. So that’s Rate, that’ll be interest rate, in this case savings rate, Nper, the number of periods, the payment, the amount of money that I’m going to save each time, the Present Value, Pv, and the Type. So what I’m going to do on this occasion is I’m going to put all those headings in first and then we’ll talk about the values that go in there. Now I’m going to assume, first of all, that the annual savings interest rate here is going to be 4% and that it’s going to be constant throughout the period of saving. I’m also going to assume for the moment that I’m going to be saving, this is perhaps a pension plan, I’m going to be saving over the course of, say, 40 years. So in months that’s 480 months. I then specify how much I’m going to save each month. So let’s suppose that each month I’m going to save \$200nd let’s assume that in present value terms I’ve got nothing in the savings plan at all, so it’s empty. Now on this occasion what I’m going to do is to put the future value function in here and use the Function Arguments dialog but we’re actually going to see what happens as we go along in this case. So I’m going to say Equals Fv, open brackets, and then click on the Fx button, and let’s have a look at the function arguments. Now, first of all, the rate’s already entered at 4% so all I need to do while the cursor is in the rate box in the Function Arguments dialog is to click on C2 and note that the calculation is actually done on the right there. So I’ll actually see the number as it appears. But, of course, that’s the annual rate and I’m talking about monthly saving so I’m going to divide that by 12 and you can now see the monthly interest rate. Right, on to the next field which is the number of periods, that’s C3, the monthly payment which is currently at \$200, so that’s C4, and Pv, that’s currently © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced C5. Note as I’m doing this I can see how each of the calculations is being done on the right here and then the type is just going to be the default, so that’s C6. Click on OK. You can already see the calculation has been done here and on that basis of saving \$200 a month over 40 years, the balance in my pension savings pot will be \$236,392.27. Now one of the flexible and powerful points about the Fv function is that we can include both payments and present value because if we already have some money that we paid into this particular savings or pensions plan, then we can include that in the calculation and it needs to be included as a negative amount because it is money that paid out into this plan already. So we would type it in as minus say, let’s suppose there’s \$10,000 in already. Enter that and you can see that the value, the final value now becomes nearly \$286,000. The \$10,000 that’s already in the plan will, of course, accumulate interest at 4% as will the payments that are going in and so that’s actually over a period of 40 years significantly increases in value. In fact, it almost multiplies by a factor of five. So it significantly increases the value of the final plan. Now as I’m sure you can imagine by now it’d be really quite interesting to set out a sort of grid here to show what various combinations of present values and payments will achieve but that kind of thing is what we’re going to look at later on in the course when we look at data analysis in more detail. But what I’d like to do now is to just develop this particular worksheet a little bit but in a slightly different way. So what I’m going to do here is to select Row 2. I’m going to insert a couple of rows and I’m going to put here, Age now and here, Retirement age. Now let’s go back to our Fv function and we’ve got Fv if you look in the entry bar there is C4 divided by 12, C5, C6, C7, C8. Now what I want to do is to remove the reference to C5 which is the number of payments and instead work out the number of payments based on the number of years till retirement. So what is C5 going to be replaced by? Well, let’s go into the Function Arguments box and instead of C5 for the number of periods what I’m going to do is replace it with C3 minus C2, so that’s the number of years till retirement, multiplied by 12, so that’s the number of months till retirement. Now at the moment I haven’t put anything in there so it’s not going to do me much good. All I’m going to get is the present value of the retirement pot. But if I’m talking about somebody who’s now 23 and is going to retire at age 55 what’s that going to do? And the retirement pots going to be

Excel 2013 - Advanced \$191,229.44. I now no longer need the number of payments, so I can delete that row. I’ve now got a more flexible calculator for the value of this retirement plan. Now I still want to do some more work on this in a later section because I’d like to work on the basis on somebody’s retirement age as related to their date of birth and the date now. So to make this a really flexible retirement planner, you need to know a little bit more about the date functions. So I’m going to return to this example a little bit later on. For now I’ve saved it as Fv Demo in my scratch folder. And it’s time now for you to have your first bit of work on this course and we’re going to turn our attention to one of the new functions in Excel 2013. I’m going to set you an example, a piece of work to do and we’ll see how you get on with that based on what I’ve covered so far in this section. So I’ve chosen one of the really straightforward finance functions for you to have a go at to begin with, just to get warmed up, and it’s the PDURATION function. The PDURATION function returns the number of periods required by an investment to reach a specified value. So it’s quite straightforward. You just need to make a worksheet where you enter an initial value for an investment, a present value, a future value, the value you’re targeted it at, the interest rate, and we just want to know how many time periods it’s going to take to get from Pv to Fv at that interest rate. That’s a pretty straightforward one I hope you’ll find. My answer to that is example-01 in the provided files. Before we leave the personal financial functions I’d just like to talk about one or two of the others that you may well find useful. The RRI function is one of the other new functions in Excel 2013 and with this you take a Pv, a present value, a future value, Fv, and whereas with the PDURATION you specify the interest rate and it tells you how many time periods it will take to get from Pv to Fv this one gives you the number of time periods and tells you what the investment return is. So basically what it tells you is what the interest rate would be to get you from Pv to Fv in that number of time periods. You would typically use this when you’re looking at an Fv, when perhaps savings you have had grown from Pv to Fv and you want to work out what the equivalent rate of return is. Now don’t forget this is a new function in Excel 2013 so if you try to use it in an earlier version, Excel 2010 or earlier, you’ll just get an error message I’m afraid.

Excel 2013 - Advanced The Pv function in Excel 2013 is also one that’s effectively turning one of the functions we saw earlier on inside out. The idea of this one is that you’ve got a good idea of how much you want to pay per month on a loan or a mortgage and you know how many periods you want to do it over, say, over 15 years, 20 years, 25 years, and you know what the prevailing interest rate is and based on those you want to work out how much money you could borrow. So the Pv function is also one that you probably will find pretty useful if you need to borrow some money and just want to know how much you can borrow. That’s it for our review of financial functions that can be used primarily in a sort of personal context. That’s it for this section. I’ll see you in the next one.

Video: Principle Payment Toby: Hello again and welcome back to our course on Excel 2013 Advanced. In the previous section I left you with a little exercise to do. I’m going to take a very quick look at that now and then we’re going to look at some of the more advanced financial functions, some that may be of more interest say to somebody who was involved in financial services professionally. But let’s start with that example. Now the reason that I want to briefly look at this example here is to just emphasize a point that I’ve made a couple of times already and that is that in this particular case I’ve worked in years. So I’ve got an annual interest rate there in C2 and the answer in C6 is the number of years, 10.34 years. That’s not a particularly convenient way of expressing the number of years but it is the correct number of years.

If you worked in months, then your formula, including the

PDURATION function which is in cell C6 and currently displayed in the entry bar, would have the annual interest rate which in my case is C2 divided by 12 and then what would be expressed here in C6 would be the number of months. So if I’d say had, let me just type in there now divided by 12 and click on the entry and I’d find that it is 121.84 months, 122 months which is about 10-1/3 years which is the same figure obviously but expressed in months. So if when you were working on this example you worked in months then, your formula would be something like this. Now, of course, you may have chosen different present values and future values to mine, you may have put in a different interest rate, but your formula if you were working in months would look like that and the function, the PDURATION functions arguments would be used in a similar way to that. So now let’s take an example of a more complex kind of analysis of payments on a loan and we’re again assuming that this is a loan to buy a house. I’ve setup the sort of argument values that we’ve seen already. So I’ve got an annual interest rate of 4%. The number of periods is 180, 180 months, so that’s 15 years. The amount of money borrowed, \$150,000. And Fv zero and the type is zero as well. I’ve entered in cell C8 the monthly payment that’s calculated by the PMT function. You’ll see there with the PMT function that the arguments are C2 divided by 12, C3, C4, C5, C6. Now when you take out a loan of this sort and you start making regular payments on the loan, of course, each payment will generally comprise both a repayment of the

Excel 2013 - Advanced principle and a payment of interest. In a payment like the one we have here which is \$1,109.53 per month, part of that is principle and part of it is interest. The interesting question is in the first month how much of it is principle and how much of it is interest? So what I’m going to do now is to put in a month heading and let’s start with month one and I’m going to calculate for the month one payment how much of that payment is principle and how much of it is interest. Now the function that tells me the part of the payment which is principle is the PPMT function. So I’m going to type in equals PPMT, there it is. Let’s look at the arguments. The function arguments we need are the rate which is that one, of course, divided by 12 because we’re talking about monthly payments. P-E-R is the period and so it will be in the range 1 to Nper, the number of periods. So it’ll be a month number in the range 1 to 180. Now, at the moment that is E2 because we’re looking at month one. Now the number of periods is C3. Pv, that’s the loan amount. That’s in C4, and Fv there. Now in this function it does not distinguish based on type. So I’m not going to put in a type value. Now having done that I click on OK and what I discover is that on that first month the amount of principle is \$609.53. Now from that I’m pretty sure you can work out what the interest is going to be, but there is, of course, an interest function and we’ll look at the interest function next. So the interest function is IPMT. There it is. It requires pretty much the same arguments as the PPMT function. So again, I can say it’s the rate there divided by 12, the period number is month one, total number of periods there, Pv there, Fv there, click on OK, and you won’t be surprised to learn that the interest on that first payment is \$500. So obviously it can be really useful to know what that split of principle and interest payments is and what’ going to happen generally speaking with any loan is that as you pay off the loan each month or each other period depending on how you’re paying it off, the interest will diminish because you’ll be paying off some of the principle. So each month there’s a little bit less principle left and therefore a little bit less interest to pay. So you pay a bit less interest and a bit more principle each month in this case. If we wanted to see the split over time and how it changes over the duration of this particular loan, we can actually now extend this from month one to month 180. So let’s start by getting the month numbers from 1 to 180 in column E. Now, of course, I’m assuming you’re familiar with © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced these kinds of techniques but quickly covering this we’re going to use a series fill here. So in the Editing Group on the Home tab we go to Series. It’s a series in columns. It’s a linear series. The step value is one. The stop value will be 180. Click on OK and of course we’ve filled down to 180. Now what I’m going to do next is obviously going to be a mistake and what I’m going to do now is to just say try doing a fill down of those principle and interest values. So what I’m going to do is select those two cells, the cells below, and just do a regular fill down. Now of course, the answers obviously wrong and hopefully you know why the answer is obviously wrong and that is that if I look at cell F3 and the PPMT formula in it, look at what it says. It doesn’t say for instance C2 divided by 12. It says C3 divided by 12. And the important thing there is that we haven’t used absolute cell references where we needed to use them. Of course, the key point there is that you still have to use absolute cell references in the appropriate places. So let me just undo that again and let’s look again at the way we’ve setup the arguments in the PPMT function. What I’m going to do there is to click on Functions to bring the Function Arguments dialog back up and let’s look again at each of those arguments. So let’s look how we defined this in the cell F2. So this is the principle payment, PPMT for month one. Now the rate we put in as C2 divided by 12. Now whichever period we’re dealing with, the rate is always going to be in C2. So the correct argument here would be dollar-C, dollar-2. The period E2, that is going to vary because as we work down the 180 months then that will vary. You could in fact use dollar-E to say it’s always column E and then it’s only the row number that varies. But in fact in this case E2 would be fine because we’re never going to put a month number somewhere else, having setup the series from 1 to 180. Total number of periods, Nper, C3. That’s always going to be C3 so we ought to have there dollar-C, dollar-3, and then similarly for Pv and Fv they also ought to be absolute references. So what I’m going to do now is to correct those other couple of entries there and then I’m going to do the same for the interest payment definition, the IPMT function use in the formula in G2 and then we’ll see if our fill down gives us a better result this time. I’ll join you again in just a moment.

Excel 2013 - Advanced So let’s try a fill down now. Let me choose say the first three or four months. Fill down I can do from the Ribbon or I can use the keyboard shortcut Control-D, so let’s do a Control-D, and let’s see. Now that looks a lot more like it. We have interest payments going down, \$500 month one, \$497.97 month two, and so on. And if you look at the IPMT function arguments as I click on these different cells, you can see that the only thing that changes is the row number there on the second argument which is the month number. Similarly, as the interest payments go down, the principle payments go up. So let’s select right down to the end, let’s do a fill down to the very end, and just need to make that column a little bit wider and you can see that just about month 180 the interest payment in the final month is only \$3.69. Of course, if I just wanted to check that the total principle paid looked right, if I select the cell below the last entry in column F, the principle column, and do an AutoSum, what do you think it’ll say? It says \$150,000 which is exactly right. So having built up a more advanced model of the use of financial functions, in the next section we’re going to look at a different aspect of the use of financial functions and we’re going to look at the approaches we can use to depreciation. That’s in the next section. Please join me for that.

Excel 2013 - Advanced number of years over which we’re depreciating this piece of equipment, so that will be C4. Okay. There we have the depreciation in year one. Now one thing you’ll realize, of course, is that all three of those are going to need to be absolute cell references if we’re going to use them for subsequent years. So I’m going to make that change now. Now one thing to note is that the value of the depreciation in each year is not a function of the year. It doesn’t depend on the year. It’s straight line depreciation so it will be the same each year. Note how every reference in the arguments for that function is an absolute reference. So if I do a fill down what I’m going to get, that’s Control-D, is exactly the same depreciation in each year. Now the next thing I want to do is to build up a chart that shows me the value of this piece of equipment at the end of each year. So for instance, if you take year one with an initial value of \$100,000 and a depreciation of \$16,000, it’s going to have a value of 100 minus 16. That’s \$84,000 and so on. Now I realize that’s rather simple arithmetic at this stage but as you’ll see in a while it isn’t always quite that straightforward. But let’s build up that chart and I want to do that chart in a couple of ways. I want to do is I want to put in here total depreciation and then value at year end. Now the total depreciation at the end of year one is going to be F2. What’s the total depreciation going to be at the end of year three? It’s going to be Equals, the total so far which is G2 plus the depreciation in this year which is F3. Now notice the formula we’ve used there doesn’t have any absolute values in it. We could, of course, say dollar-G and dollar-F but since we’re not working across the columns anyway, the column numbers are not going to vary. So if I select G2 and do a fill down that’s fine. As I can see at the end of year five, my total depreciation is \$80,000. And then it’s pretty straightforward to put in a formula which gives me the value at year end. So in this case, it’s going to be equals to the original cost which is, let’s put these straight in, dollarC, dollar-2, minus, and now this will be G2. Fill that down and again I finish up with the correct final value. So that’s a pretty straightforward calculation using straight line depreciation.

Excel 2013 - Advanced So what I’m now going to do is to do away with column G and I’m going to incorporate into the formula in each cell in H the whole calculation that I need to do. Now in the case of H2, I don’t actually refer to column G at all. I say that the value at the end of year one is C2 which is the initial cost minus F2. F2 is the total depreciation at the end of year one. If I wanted to express the formula in H3 to not specify column G at all, so instead of G3 what would I put? What I really mean by G3 is this. By G3 I mean the sum of the total depreciation so far which is the total from F2 to F3. That range. If I fill that down to the next cell, so I do a Control-D, that gives me an incorrect value for the value at the end of year three because this now says that the value at the end of year three is the initial value, C2 minus the sum from F3 to F4 which is clearly wrong. I really need the sum from F2 to F4. So in fact, this formula should also specify as the start of the range an absolute value. So this should be the initial value, C2, \$100,000 minus the sum from F2 to F3. F2 is fixed. It’s absolute. F3 varies. So let’s now do a fill down for all of the rest here and see what happens. That now gives us correctly the values at year end for each of the five years and I can now just delete that column. I don’t need the original column G anymore. If I even wanted to do this in a slightly more elegant way, if I go back to the value in G2 instead of having just C2 minus F2, I could put here the sum dollar-F, dollar-2, colon, F2. There’s no reason that I shouldn’t use the same cell for the beginning and the end of a range. In this case, the reference at the beginning of the range is absolute and the reference at the end of the range is a relative reference. It still gives me the right answer and of course if I fill down, everything else works correctly as well. So that’s straight line depreciation. Now I’m going to leave you with an exercise to do now but before I set that exercise I just want to change the layout of this worksheet a little bit. What I would like you to do for this exercise is to build up this table to compare straight line depreciation, the one we’ve done so far, with two other methods of depreciating the value of an asset. There are several provided in Excel 2013 but I just want you to do declining balance depreciation and sum of years digits depreciation. In each case, you need to work out the

Excel 2013 - Advanced depreciation for each year, put it in the first column, and in the second column work out a formula to give you the value at year end. Now let’s take a quick look at the functions that give us those two values. The first function you’re going to need to use is the DB function, the declining balance function. This is not to be confused with the DDB function, the double declining balance function. The principle of the declining balance is that the depreciation is steady but steady in the sense of a percentage. So for example, in the first year a piece of equipment may lose 20% of its value and then in the next year it may lose 20% of that remaining value and then the remaining value after two years, in the third year it loses 20% of that. So although the percentage is steady, the amount that it loses in value each year will actually obviously decrease. Now the formula you need will include the function DB and there you have cost, salvage, and life, the three arguments we’ve looked at already. The period will be the period number. So in the five year period: 1, 2, 3, 4, 5. You’re not going to need that last argument. The last argument is used when you have to allow for a part of a year. So if your company has a fiscal year and if you’re starting this partway through a fiscal year, you have to specify the month number. Now we’re just going to assume that we’re working in whole years, so we bought this thing at the beginning of a fiscal year and in the first year we have a full year of depreciation as opposed to part of a year of depreciation. So that’s the one to use for declining balance and then the second depreciation method I want you look at. The principle here is different. You do get a decreasing amount of depreciation but it actually works on the principle of adding up the total number in if you like the values of the digits in the years. So the first year counts as five units in a five-year depreciation, the second year is four units, the next one three units and so on. Those numbers of units determine the proportion of the overall depreciation that can be attributed to each of the years. Now you don’t really need to understand exactly how that works, although there’s a formula down there and in all cases there are a couple of examples. What you do need to know is the function and the function is pretty straightforward: cost, salvage, life, and period.

Excel 2013 - Advanced So they’re the two functions you need to use. I’m going to leave you with that one to do. I’ve saved the worksheet as it is now in workbook example-02 and my answer to that is example-03. That’s it for this section. I’ll see you in the next one.

Chapter 4 – Date and Time Functions Video: How Dates and Times are Stored Toby: Hello again and welcome back to our course on Excel 2013 Advanced. In this section we’re going to look at what for many people can be the most troublesome area of using Excel and that’s dates and times. Now once you understand how dates and times work in Excel, I really don’t think they’re that complicated and I don’t think it’s that difficult to understand them. The trouble is that if you don’t have a fundamental understanding of how dates and times are stored, then the functions that are available and the things that you can do with dates and times will continue to be a bit of a mystery. So the first thing I’m going to do in this section is to explain to you how dates and times are stored, and then some of the functions that you’re going to need to understand, and some of the things you’re going to be able to do with dates and times I hope will make a lot more sense. So I appreciate that you may be aware of this but I’m going to run through it anyway because it’s absolutely essential that you understand how dates and times are stored. What I’m going to do is just put into this cell here, B2, a date. And of course one of the clever things about Excel is that it can deal with dates expressed in many different formats. Depending on your locale even a straightforward date like that may be October the 9th 2010 or it may be the 10th of September 2013, depending on whether the first number in that sequence is treated as a day or whether it’s treated as a month. In this particular case it doesn’t really matter. But when you’ve entered something like that into a cell and if Excel isn’t told anything otherwise, it looks at the content of that and decides that it is a date. In fact if you right click on that, go into Format cells, you’ll see that it is in fact interpreted as a date. Now note what happens if I change that cell from being a date to being a number. Click on OK and look at what happens in B2. B2 becomes 40460.00 which may seem like a pretty strange number to you but there is a reason that the number is that and that is that the way that Excel stores dates is as the number of days since January the 1st 1900. So it actually stores a date as a count of a number of days. The significance of the 0.00 after it is that it stores time as a fraction of a day. So let’s see what a time looks like.

Excel 2013 - Advanced I’m going to click in this cell here and I’m going to put in a date and a time, 11:47 a.m. Now Excel will realize that that is a date and time. If I go into Format cells again, it doesn’t actually classify it as date; it’s treated as a custom date but it realizes that it is a date because it’s picked out a date format. But let me now change that to number and click on OK and what you can see is that I now have a date with a fractional part, 0.49 is the time. Now 11:47 a.m. is not actually 0.49. Let me go back into Format cells again. When I changed it to number, I left the number of decimal places at two. Let’s go for, say, seven decimal places, click on OK, and up to seven decimal places. That’s what 11:47 a.m. is. So, of course, the fractional part, depending on the time that you specify and based on the fact, for instance, that we have 24 hours in the day and 60 minutes in an hour and so on, that fractional part could actually require quite a few decimal places to express it accurately. Now let’s look at a different situation. Let’s suppose that I’ve written a short sentence like this one in cell B6. The date and time now are 10/9/2010, 11:47 a.m. Excel does not recognize what I in this text string as a date. It just recognizes that the whole thing is a text string so it will store the individual characters of the date 1-0, slash, 9, slash, etc. and the individual characters of the time 1-1, colon in exactly the same way as it stores the rest of this text string. So the whole text string is just a sequence of characters, T-H-E, space, D, and so on. Now one of the key things to be able to do in Excel is to understand which of these types of storage is in force at any time and to be able to convert between text dates and times and what I’m going to refer to as real dates and times, the way they’re actually stored as in the two examples up here. Now there is also another little complication if you like or another little aspect to be aware of and that is let me just go back here and I’m going to right click here on Format cells. I’m going to say that this particular cell is a text cell and I’m going to type in there 10, stroke, 9, stroke, 2010, 11, colon, 47, a.m., just the same as I did before but I’ve already said that that cell contains text. I’m going to enter it and what Excel does is nothing. It doesn’t recognize it as a date and time at all because I’ve already told it that I’m putting text in that cell it just treats it as text. So now let’s take a look at some of the most important date and time functions that you’re likely to need and we’ll begin with today and now. © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced Now you may well get into the situation where you have some text dates and times such as the ones I have here where they really are text, maybe they’ve been imported from another system. They’re not currently formatted in one of the standard ways that you would format dates and times but you can still convert them into the standard Excel 2013 real format using two functions. Dates we process with date value and times we can process with time value. So let’s start with date value here. I can put Equals, start typing the name of the function, it’s date value, put in there the cell it refers to, B2, and what that will give me is the day number, the normal day number as the real date value for that particular date in B2, and then of course I could fill down and do the others. Now for these time values that you see here, there’s a bit of an assortment here. We’ve got a 3, colon, zero, zero. That’s a 3 a.m. We’ve got a 12 a.m. So is that midnight or noon? And then we’ve got a 6 p.m. which is 6 o’clock in the afternoon. The function for those is time value. Let’s put in B6 and 3 a.m. is 0.125. That’s 1/8 of a day. If I fill down the others, you’ll see that 12 a.m. is actually treated as midnight; it’s zero, and 6 p.m. is ¾ of the way through the day, so it’s 0.75. If I clicked in here and changed a.m. to p.m., you’d see that midday is half a day. So those two functions, date value and time value, can be really useful when it comes to converting text values into real date values or real time values. So we had a look there at the key basic functions for dates and times in Excel 2013. What we need to do now is to use some of those functions to perform some of the kinds of calculation that are often needed as you work in a more advanced way with Excel. That’s what we’re going to cover in the next section. I’ll see you then.

Video: Dates and Times Calculation Toby: Hello and welcome back to our course on Excel 2013 Advanced. In the previous section we looked at the most important functions to use for working with dates and times, and in this section we’re going to use those functions plus a couple of others to do typical kinds of calculation involving dates and times. I’m going to start with a couple of really straightforward examples that, for one thing, will test whether you’ve really understood what I covered in the previous section. So let’s suppose that as part of my invoicing system, I need to put an invoice date into an Excel workbook and then put a due date 30 days later into that same workbook. So let’s say I’m invoicing today. So in this cell I’m going to type in Equals today. That’s today’s date. What would I put in here to give a day that’s 30 days later than the date in B2? Well, of course, in reality B2 which is a date is stored in real date format as a number of days so all I should have to do is to put Equals B2 plus 30. That would give me a date 30 days later, and of course it does. There’s 30 days in April so 30 days later is May 25, 2013. Now that’s pretty straightforward because all we’ve got to do is to add a number of days to a real date. Things get more complicated though when we start talking about adding or in fact subtracting different periods of time. If you looked at the date in B2 which is April 25th and you said I want a date three months later than that, hopefully you’d say July 25th. But how do we actually do that calculation in Excel? How do we add three to the month number? And what would we do if we were doing that, for instance, and the date in B2 was the 31st of July? Well, three months after the 31st of July would be October the 31st which is fine. But four months after 31st of July there is no November 31st. So things get a little bit more complicated when we start dealing with other units of time. Now to help us with this type of date calculation, there’s a very clever function in Excel, the date function. The date function basically gives us a real date based on a year number, a month number, and a day number. So as an example we would have Equals date, year number 2008, month number seven, day number eight. Note the order is year, month, day so that’s the syntax down there. There are also rules about if you abbreviate the year number, the conditions under which it will treat the century as 20th century or 21st century, etc. I won’t go into those now but

Excel 2013 - Advanced I’ll just come back to a little thing about that later on. So basically the date function will turn a year number, a month number, and a day number into a real date which can then be stored as a regular serial number. So that’s a pretty useful function, but that function is also the basis for some very useful calculations. So here I’m going to use that function and I’m going to say what’s going in this cell is the date. The year I’m going to use is the year of B2, the month I’m going to use is the month of B2, and the day I’m going to use is the day of B2. So if I tick that, I will get the same date as I’ve got in B2. So at this point you may not be particularly impressed by that but now let’s do something that’s a little bit more impressive. Let’s suppose that I now want to say what is a date 60 days later than the date in B2? All I’ve got to do is to in the day part of the arguments of the function date, I can just put plus 60. So I’m saying I want a date that’s the year of B2, the month of B2, but I want it 60 days further on. Let’s see what I get there. Now what I get is June 24th. What if I said give me a date 600 days further on? Now I get December 16, 2014. And that’s what the date function can do for you. So now let’s put it to the test with the question that I posed earlier on. Supposing instead of today there, I put in a date of July 31, 2013. Okay. I’ve still got the same formula in D2. It’s date, year in B2, month in B2, day in B2. Now what happens if I say plus four, four months later? That would be November 31, 2013, if indeed there were such a thing as November 31st. Let’s see what it does. It comes out with December 1st which is fine because that’s what I’d need to do if I wanted to be four calendar months later, I’d have to go to the first day of the following month. So as I’m sure you’ll discover the date function is really useful from the point of view of doing that kind of date calculation. There is an equivalent time function that is used with times and the arguments for there are hours, minutes, and seconds. So let’s just have a quick example of that. Let’s put the time for now, so in fact we’ll get the date and time with the now function. And then in here I’m going to put Equals time, that’s the function, and then with that I put the hour which is going to be hour B5 and then I put the minute which is going to be minute B5 and then second B5. That should give me the same time that I’ve got now. But now I could say what is the time going to be two hours and 30 minutes later? So I can add two to the hours. I can add 30 © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced to the minutes and say given the time in B5 what will the time be two hours and 30 minutes later? It’ll be 2:54 p.m. Now we’re going to deal with a more complex example of a date calculation in Excel 2013 and we’re going to calculate various aspects of the difference between two dates. I’ve defined a start date there in B3 of November 5, 2010 and then an end date of March 26, 2012. The first question is how many days are there between those two dates? Well, that’s actually pretty straightforward because if they’re stored as real dates which they are, all I’ve got to do is to say that the number of days between them is C3 minus B3. So let’s put that formula in here, Equals C3 minus B3 and what we get is 507 days, straightforward enough. Now then how would you calculate how many weeks there are between them? Well, that’s pretty straightforward as well because all we’ve really got to do is to divide the value in E4, the current one we just did, by seven. Now, of course, the value we come out with is going to be fractional and say it’s either 72 weeks and a bit, and that will be three days or round it up to 73 weeks. Let’s just say that we’re going to deal with it as a rounded down value so what I can do is just go in, format it, and say that it’s going to be a number with no decimal places. So we’re just looking at whole numbers of weeks. Now what about the number of months? Well, this is a completely different kind of question because you cannot just take the time interval, the number of days, and divide it by say 30 or 31 or 29. And this is really where an understanding of how we can look at years and months and days comes in. Now in this case what I’m going to do is to work out the number of whole months between those two dates. So, first of all, if I were to look at the number of years between them first of all then just using year numbers, the end date, 2012, and the start date, 2010, I’ve clearly got two years between them. So the beginning of the calculation would be something like this, Equals and I’m going to put this in brackets and I’m going to say Year. Now the end date is C3 and from that I’m going to subtract the year of the start date, so that’s B3, and then I’m going to multiply that by 12. So I’m going to say calculate the difference in years, multiply it by 12. That will give us the number of months based on years only, and what that gives us is 24 months. Now clearly there isn’t 24 months between them. If they were two complete years, there would be but the month in © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced the end date is March and the month in the start date is November, so it’s not going to be 24. So to that I need to add the month of the end date which is C3 minus the month of the start date which is B3. Now let’s see. And what I get is 16. So the fact that the month number in the start date is later than the month number in the end date means that when I add that difference, I get a negative number. I’m adding a negative number because the start date month is later in the year than the end date month. So I finish up with what is sort of the correct number of months. Now when I say sort of the correct number of months the other factor I need to take into account is the day in each month because if the end date day were later than the start date day, I’d have a full month. If it was less than, I wouldn’t. So should this be 16 or should it be 15? Now the question of whether that’s 15 or 16 we’re going to return to in a later section. But what I’d like to do in the next section is to apply what we’ve just done to the calculations that we did as part of our retirement planning early on in the course. That’s it for this section. I’ll see you in the next one.

Excel 2013 - Advanced Now basically the place to go, first of all, to look at the default settings to do with dates and times is not Excel Options but you need to go into Control Panel and look at your regional settings. Now bear in mind that the settings within regional settings from Control Panel apply across Windows unless they’re superseded in a particular application. So you have format options here that are dependent on your locale. So things like what short date format looks like, what long date format looks like, short time, long time, which is the first day of the week, etc. And then if you click on additional settings here, the additional settings include things like for dates when a two digit year is entered interpret it as a year between and then there are settings there generally relating to how two digit years are handled. Now as I say don’t forget these can be overwritten in individual applications but it’s important to understand what the defaults are setup on your machine in the operating system that you’re using. I’m not going to go into that any further now. If you’re having trouble that’s a good place to start and it’s also a good place to have a look now just to make sure that you know what your settings are at the moment. That’s it for this section. I’ll see you in the next one.

Chapter 5 – Statistical Functions for Description, Prediction and Interference Video: Average Toby: Hello again and welcome back to our course on Excel 2013 Advanced. In this section I’m going to start to look at the statistical functions in Excel 2013. I’m not going to assume that you’re a statistician and have a deep and broad knowledge of statistics, but most people have enough exposure to statistics in everyday life nowadays to understand the kinds of terms and terminology that I’m going to use. Microsoft Excel has been used in statistics and by statisticians for quite a few years now and it’s actually a pretty strong tool. As we’ll see during this section and later on in the course, not only can we do some very useful numeric work but we can also use some of the charting and graphing features of Excel 2013 to look at statistical questions and statistical issues. There are some specialist pieces of software available to do a lot of these sort of statistical number crunching that you might need but the advantage of Excel, of course, is that it does many other things as well and many more people have copies of Microsoft Excel. So if you’ve got a particular statistical problem to solve or a piece of work to do, the chances are that Excel can help you to solve that problem or do that job for you; if it can’t then you probably will need to look at one of the more specialized products. Now for the purposes of this course I’ve divided the application of the statistical functions in Excel 2013 into three groups, and those three groups are: description, prediction, and inference. The first one we’re going to look at is description and by that I mean we take some statistics, in our case, a set of numbers and we look at ways of describing that set of numbers. So we’re really just looking at fact finding. We’re saying what are the features of this set of numbers that will help us, for instance, understand what the numbers tell us or maybe even compare them with other similar or related sets of numbers? I’m then going to look at prediction and in particular at regression and how we can use some existing measurements to predict what might happen in the future.

Excel 2013 - Advanced compare the average number of policies sold there as well. But in the first instance, what we’re going to look at is typically some of the descriptive statistics that we can produce from a set of numbers like these. I’ve got inquiry statistics and policy statistics. I’m just going to look at the policy statistics here. Now, first of all, if I’m going to calculate a number of statistics for the policy sales over that four year period, I’m going to be referring to this range of numbers from C4 down to C51; quite a lot. So what I’m going to do is define a name for that range of numbers. So having selected, right click, define name. I’m going to call it Policy Sales Orlando. Click on OK and now whenever I want to refer to that range, I can use that name. So let’s start with one or two really straightforward statistics. One of the very straightforward statistics would be what is the minimum value in that range? What’s the lowest number of policies that were sold? Now the function we use for that is the min function. So let’s just put that in. And all we need for the min function as an argument is the range of numbers to look at. Well, of course, we’ve got a name and there it is. As soon as I see that appear, Policy Sales Orlando, tick, and I see that in any one month, the smallest number of sales is 57. Similarly for the maximum, I’m sure you can work this out. Now in a very simplistic way, you could compare the sales at two offices by looking at what the minimum number and the maximum number of sales are. Minimum and maximum values can be very useful as descriptive statistics but generally speaking we want to look at two types of statistics which are of particular interest. One of them is the average and one is the measure of spread. Now there are various types of average. Probably the best known one is the arithmetic mean which I’ll look at in just a moment. But other types of average include the median which, if you put all of a set of values into sequence, is the middle one or if there are an even number of values, the average of the two middle values. And sometimes we’re also interested in the mode. The mode is the value that occurs most frequently. Median and mode functions are available in Excel 2013. I’ll leave you to check those out using the Help. But let’s look at the arithmetic average, the mean. So I’m going to put in here Mean and the average function is Equals average, and for that all I need to do is to put in that range again. So I’ve got my name setup and I find that for this particular branch the average number of sales per month over that period is 114. © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced the criterion range and for the range to average. So I could use Policy Sales Orlando in both places. If that were the case, I wouldn’t actually need to include the third argument because by default it’s the same as the first argument. But in this case, the criterion applies to column A so I need to specify the range in A, A4 to A51. I want months that end in 1-2 and let’s see what I get as my average if I check there, and the value is 81. So for months in 2012 the average number of sales is 81. So that’s our first look at averages. In the next section I’m going to take a look at percentiles and then we’re going to look at measures of spread. So please join me for that.

Video: Percentile Toby: Hello again and welcome back to our course on Excel 2013 Advanced. In the previous section we looked at averages for data using the average and average if function in Excel 2013. In this section we’re going to start by looking at percentiles. These changed in Excel 2010. If you’ve been using an older version of Excel you probably used the percentile function, and very often people look at 25 percentiles, 50 percentiles, 75 percentiles and use these of ways of describing data sets.

The straightforward use of the

percentile function, the legacy percentile function if you like is still available in Excel 2013. Basically if you say percentile, define the data set, so in our case that will be our named Policy Sales Orlando data set, and then you define as a fraction in the range zero to one the percentile that you’re interested in. So let’s suppose you wanted the 25th percentile. You’d put in there 0.25 and what that tells you is the value below which 25% of the data occur. So in our particular case, for the whole four year data set the 25th percentile is 86. Now in Excel 2013 you are discouraged from using the percentile function and the function to use

which

has

replaced

the

percentile

function

is

either

PERCENTILE.INC

or

PERCENTILE.EXC. Now largely speaking these two functions produce the same results but there is an important subtle difference between them.

PERCENTILE.INC is an inclusive

function whereby for any value of K in the range zero to one you will get a result. PERCENTILE.EXC actually throws an error if you use a value of K which is outside the valid range for the data set that you’re working with. Now this is actually a little bit tricky to explain but I’ll try to do so. Let’s suppose that you’ve got a data set with just ten items of data in it. If you put them in order, then the first data item will effectively be your tenth percentile because it will mean that 10% of your data, one out of ten, is equal to or below that value. If you try to find a five percentile, it’s a bit of a ridiculous question really because you’ve only got ten data items so you can’t find a value below which 5% of your data exists. Now in that particular case PERCENTILE.INC will just do its best to find you a value, PERCENTILE.EXC will give you an error. The difference is really

summarized

in

this

little

statement

down

here

PERCENTILE.EXC.

Excel 2013 - Advanced values in the array. If it cannot interpolate for the percentile case specified Excel will return an error. So what I’ve done here is to replace the old percentile function here with PERCENTILE.INC. I’ve also actually put in 50th percentile which is the median. Note again the PERCENTILE.INC function and the 75th percentile. Now as I mentioned earlier there are other functions in Excel that have been superseded by newer versions. When you’re doing things like percent rank, there’s now a pair of functions: PERCENTRANK.EXC and PERCENTRANK.INC.

There are quartile functions that now

Excel 2013 - Advanced population. That’s it. You’re not going to use that standard deviation for anything else. You just want to know what the standard deviation is. If you use the S version, you’re calculating the standard deviation of those numbers but you’re going to use it as an estimate of the standard deviation of the population from which those numbers are taken. So if for example you were taking measurements, say, of the heights of people. Let’s suppose you were trying to find the heights of all the women in Orlando. If you stopped 100 women in the street and measured their heights and you got an average height and a standard deviation of heights and wanted to use those to estimate the overall distribution of heights of all the women that live in Orlando, then you would use the S version because you’re going to use that standard deviation as an estimate of the population standard deviation. Now a statistician would probably need a slightly more rigorous explanation than that but that’s basically what it is. Now when I do a standard deviation calculation here, I need to be clear about which of those I’m doing but what I’m going to do in this case is just do this calculation as though this set of numbers of policies sold per month is my whole population. I’m not going to use it as an estimate. So the formula I put in will be Equals ST, it’s STDEV.P. Note the two functions at the bottom there, STDEV and STDEVP which are the old ones. There’s little warning signs on there are saying basically don’t use these, use the new versions. The old ones there are kept there for compatibility with older versions. We’re going to use STDEV.P and then all we have to do is to put in our range which as usual, Policy Sales Orlando, tick it, and the standard deviation is 34. And then finally in this section let’s have a quick look at how we might use these kinds of descriptive statistics. I’ve now put on the figures for the equivalent sales and inquiries for the Tampa branch of the insurance company.

If you look at the numbers, there are massive

numbers. You can generally see that the Tampa ones tend to be quite a bit lower than the Orlando ones. But to get a better handle on exactly how much lower if you go down here, I’ve basically done the equivalent calculations. If you look at the minimum and maximum, you can see that the minimum for the Tampa figures of policies sales is 10 compared to 57 for Orlando, so very much less business in some cases at least in Tampa. The maximum there is 116; again, much lower than the maximum for Orlando. If you look at the mean, you can see that the mean number of sales in Tampa is only roughly half, just over half of the mean sales for Orlando. Similarly, if I look at the mean sales for 2012, they’re only just over half the values for Orlando. © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced So as a straightforward rule of thumb sort of calculation, you can see how that easily tells you that the Tampa figures are just over half the Orlando figures. That’s a much better way of summarizing the situation, then you might be able to achieve by looking at the long list of numbers that we started with. Similarly, if you look at the spread figures, you can see that the percentiles are much lower. The interquartile range is just over half, so just over half the spread but the numbers are only just over half as big and the standard deviation for the Tampa figures is significantly lower than the one for the Orlando figures. So without going into a lot of statistical analysis, you can see how those summary figures give you a really good, clear snapshot of the differences between the performance at the two offices. Okay so that’s it for statistical functions used for description. In the next section we’re going to look at statistical functions used for prediction. Please join me for that.

Video: Regression Toby: Hello again and welcome back to our course on Excel 2013 Advanced. In this section we’re going to look at some of the statistical functions that are used for prediction in Excel 2013 and I’m going to use a very straightforward example to demonstrate this prediction which we usually refer to as regression. Now literally speaking the word regression in English doesn’t really imply prediction at all, but it’s conventionally the name that we use for this general approach to predicting a value of one or more variables from known values of those same variables. So let’s look at this straightforward example. In column A on this worksheet, I have quarters numbered from 1 to 12 and then for a particular company I have the sales of one of the products they produce in thousands for each of those quarters. For quarter one the sales 92,000, quarter two 94,000, and so on. Now you can see there is a general trend, a general tendency upwards in those sales and we could, of course, draw a graph of this. We do various sorts of analysis of how these sales are going. What we’re going to do is to use straightforward regression to predict the sales in quarter 13, and of course using the same principle, we could predict the sales further ahead if we wanted to. Now before I show you how to predict the sales for quarter 13, I’d like to talk to you about independent and dependent variables. Time generally is an independent variable. It isn’t the only independent variable but it’s a very important one. By independent it means it isn’t going to vary in some unpredictable way depending on something else. It’s completely independent. Time goes ahead according to a fixed pattern that we measure using one of the systems that we have, such as measuring in days or weeks or months or years. And it’s independent. We’re not trying to work out what might happen to time. We know what’s going to happen to time. On the other hand with sales, sales we’re sure are going to vary. Now based on the model we’re looking at here, our suspicion is that sales increase over time and the figures we’ve got there suggest that that’s true. But sales may depend on other things as well. So for instance, let’s suppose we introduced another variable. Let me just insert another column in here and let’s suppose that I introduced another variable of rain fall and let’s suppose that these sales relate to umbrellas. We might find that if the rain fall in inches, say, for each of these quarters, if we © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced plotted that we would find that generally speaking the sales of umbrellas were more when there was more rain fall and less when there was less rain fall. Now we can’t control the rain fall. It’s another independent variable, but sales may depend on rain fall and time. So although we might find a general tendency upwards, we may also find that we always sell more umbrellas when the rain fall is high then we do when the rain fall is low. Now if we built that sort of model, we’d be talking about one dependent variable and two independent variables. So this one dependent variable might depend on two independent variables. Now this is what we call multivariable or multivariate regression and in this section I’m not going to look at multivariate. I’m only going to look at one dependent variable and independent variable. But in a more general sense, when you’re dealing with regression you may have more than one independent variable and more than one dependent variable. The next thing I’d like to talk about is what sort of pattern this dependence has. What I’ve done here is to draw a straightforward little graph showing sales against quarters and as you can see there is a general tendency upwards. It’s not quite a straight line but it’s more or less a linear kind of growth. With Excel 2013 not only can you predict the value of a dependent variable using regression methods but you can do that with a number of different assumptions about how that dependence might work. Now again I’m not going to go into all those different types. I’m only going to look at what’s called linear regression in this section, which is straight line regression where we’re assuming that the relationship between a dependent variable and an independent variable is a linear relationship. But you can, for instance, investigate logarithmic relationships where the relationship between a dependent variable and an independent variable is logarithmic. Now as I say I’m not going to look at logarithmic relationships, for example, but you will see in a moment that there are functions that are equivalent to the ones we use in linear regression that you can use to investigate, for example, logarithmic regression. It’s also possible to build up more complex dependencies and deal with more complex relationships using a combination of the various functions available in Excel 2013. So what we’re going to do here is to assume that the relationship between sales and between time is a linear relationship and we’re going to predict using regression on that basis. Now one thing I should point out to you is that as part of doing this we are also going to evaluate how strong the linear relationship between X and Y is. So even though we’re making an © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced now for the rest of this section is a couple of other ways of achieving what we’ve just achieved using a couple of different functions in Excel 2013. The first of those functions is the forecast function and you use the forecast function to predict a value for just one value of X. So supposing I wanted to predict the value of sales where X is 18, so for quarter 18. So I’m going to put in this cell here, B20, a formula including the forecast function. So it’s going to be Equals forecast and the forecast function takes three arguments. First of all, the value of X you want to forecast for which will be A20, the known Y’s that I can specify using selection as before, and the known X’s, again select those as before, tick, and the forecast value same as we had before 142. Now we’re going to look at another approach at predicting these values and we’re going to use for the first time on this course what’s called an array function. This is a function in Excel where we actually generate a whole array of output values of a function all in one go. What we’re going to do is to fill in the values from B15 to B20, the ones that are selected already using the trend function. So having made the selection, I type in Equals trend and the trend function takes four arguments. The first argument is the known Y values. Well, that’s pretty straightforward. We’ve done that before. Do that by selection. Comma. The second argument, known X’s; again, straightforward as before. Third argument, the new X’s. Which X values do we want to predict Y values for? Well, it’s those ones. The fourth argument is a value, a constant value which is either true or false which says Do we want this linear relationship to have a zero intercept? So is it Y = MX+C or is it just Y = MX? Now we already know in this case that it’s of a type Y = MX+C. We already did a calculation which indicated that the intercept in this linear relationship would involve an intercept. If you like a starting value so that when X is zero we don’t have Y zero automatically. Now in that case we can omit the last argument. If you wanted to force it to be a relationship of the type Y = MX, you would not omit the last argument. So having put in all the values we need to, we don’t just tick or press enter. In order to make an array function work, you have to press Control, Shift, and Enter and then watch what happens. With Control, Shift, and Enter you get all of the values calculated in one go for the unknown or

Excel 2013 - Advanced new Y’s and you shouldn’t be surprised to learn that we get exactly the same values that we got when we did the calculation using multiple uses of the linear relationship earlier in this section. So there you are. In this section you’ve seen a few ways of predicting values on the basis of linear regression and you’ve also seen an example of an array function. In the next section we’re going to use the even more powerful array function LINEST and that will also enable us to see how strong the relationship is between an independent and a dependent variable. That’s the next section. I’ll see you then.

Video: LINEST Function Toby: Hello again and welcome back to our course on Excel 2013 Advanced. In the previous section we looked at regression, specifically linear regression. In this section we’re going to finish off looking at this with consideration of a very important function the LINEST or LINEST function in Excel 2013. That cannot only enable us to predict using linear regression but can also give us a pretty good measure of how good any predictions we make are likely to be. Now using the LINEST function is a tiny bit tricky. Once you’re used to it, it’s absolutely fine, but understanding the results of using the function is a little bit more of a challenge. If you don’t have a particular interest in statistics or your statistical knowledge is not that strong, some of the results of this may be a little bit complicated. You may have to do a little bit of research to find out what they mean. However in this section I’m going to look at the meaning of the main ones and the ones that are most likely to help you I think you’ll find relatively straightforward to understand. Now first of all, what we have to do is to select a range on the sheet with the data that we’re using which is of the appropriate size. It doesn’t matter if it’s too big but it’s best if it’s the right size. For linear regression using the LINEST function in Excel 2013, you basically first of all need to count how many independent variables are involved and add one because that’s the number of columns you’re going to need. Now in our particular example we only have one independent variable, X the quarter number, so add one to that. We need two columns. Now in terms of the numbers of rows, we need in the area to select if all you want to do is to get the slope and intercept, you just need one row with those two columns and you’ll get the slope and intercept. But if you want the various statistics that you can use to identify both how strong the model is and what sort of allowance to make for errors, as I’ll explain in a little while, you need an additional four rows. So if we’re going to make maximum use of LINEST, in this example we need two columns and five rows. So let me select a range here, five rows and two columns, and then I type in Equals LINEST, open brackets. First of all, the known Y’s; there we have the known Y’s, then the known X’s. No surprises here. There we have the known X’s. Then we have two constants. Now for the first of the constants, we are again looking at whether we have an intercept or not. If we have the value here set at false, then the assumption is that the

Excel 2013 - Advanced the formula. So the LINEST function is used in the formula, but you have curly braces around the whole thing. Whenever you see curly braces round a formula like that, it should say to you that what we’re dealing with here is an array function in Excel 2013. That’s it for this section. I’ll see you in the next one.

Excel 2013 - Advanced So the function we want in this case is NORM.DIST. So we choose that and it basically takes four arguments. Now the first value is the value of X, the value for which you want the probability of a value less than or equal to that. Now 5 feet is 60 inches, so 60 inches is the value for X in this case. Comma. The second argument is the mean and that is, of course, the value that’s in this cell. Comma. Next value is the standard deviation. That’s in C5. Comma. And the next value is either true or false. It’s a logical value that either gives us the cumulative distribution function or the probability mass function. Now the cumulative distribution function value will be the probability of a value less than or equal to and that’s the one that we want in this case so I’m going to choose true, the default. The probability mass function is a bit of a tricky one, if you’re not very experienced or knowledgeable in statistics. Basically for a continuous variable like somebody’s height, the probability of any particular value is actually zero because it’s an infinitesimally small amount. Let’s suppose you wanted the probability that someone was 5 feet 4 inches tall, 64 inches tall. Strictly speaking, the probability of somebody being 64 inches tall is zero. What you would actually work out is the probability of them being between 63-1/2 inches and 64-1/2 inches tall. That is their height to the nearest inch. Now, that’s the equivalent of a probability mass function value because probability mass functions are generally only defined for discreet values; generally speaking, that means variables that have whole number values. Now, if something had whole number values, it wouldn’t actually have a normal distribution but normal distributions are very often used to describe distributions of discreet variables. When we’re dealing with a continuous variable like this though we wouldn’t generally use the probability mass function, although if we were going to use it we would interpret it as being within a half of whatever the particular continuous variable was, as I just described. So if we were going to use the probability mass function here, we’d say somebody being 5 feet 4 inches tall what we would actually mean is between 5 feet 3-1/2 and 5 feet 4-1/2. For a cumulative distribution function which is the one we’re going to use here, what we’re saying is the probability of being less than or equal to in this case 60 inches, 5 feet tall. So that’s the one we’re going to use and we tick on that and the probability is 0.04. Well, that’s just under 5% will be less than 5 feet tall.

Excel 2013 - Advanced Now one of the things we may well want to do here now is to restrict the number of decimal places we’ve got so I’m just going to go into Format cells and make that a number with two decimal places. So that’s 0.05. That’s certainly good enough for our purposes. So that means 5% of women in Orlando will be less than 5 feet tall. What about more than 5 feet tall? Well, that’s pretty straight forward when you’re dealing with normal distributions because if you’ve got the value less than, all you’re going to need to do is to subtract that from one to get the value of more than. So the formula there is Equals 1 minus C7 and that gives us a probability of being more than 5 feet tall, of course, 0.95. Now the other way in which we can use the cumulative distribution function with the normal distribution is to find the probability of a height between two values. Basically, the way we do that is to find the probability of a height less than the lower value and subtract it from the probability of a height less than the greater value. So for instance, let’s suppose I want the probability that a woman in Orlando is between 5 feet and 6 feet tall. The way I calculate that is to say, first of all, what’s the probability that a woman is less than 6 feet tall? So that’s a NORM.DIST. So 6 feet tall is 72 inches, put in the other values and subtract from that the probability that the woman is less than 5 feet tall. The difference in those probabilities is the probability that the woman is between those two heights, and that comes out to 0.94. If again I format that down to two decimal places, the probability a woman is between 5 feet and 6 feet tall is 0.94 or 94% of women will be between those two heights. Now it’s important to recognize here that we’re looking at a normal distribution and as I’ll show you in a couple of minute’s time there are other distributions that we might need to consider using Excel. Also in common with other distributions not only is there a DIST function which we can use to find the probability of something happening but there is an inverse of the DIST function, an INV function whereby we enter the probability and find out which value gives us that probability. So for example, supposing I wanted to say below what height are 25% of the women in Orlando. So I’m going to set my 25% mark which is my lower quartile for those of you who are familiar with that terminology in statistics. Which height is the lower quartile for the heights of women in Orlando? So in this case, I’ve entered there in column B, lower quartile. The 25% of women in Orlando are going to below this height and then in there I type Equals and © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced the function I need is NORM.INV. I have three arguments to enter. The particular probability which is 0.25, the mean, C4, standard deviation, C5, and the height in inches is 62.97. Well, we’ll call it 63 inches. So 5 feet 3 inches is the lower quartile for the heights of this population of women. And just for completeness, I’ve also included in there the median value; that’s the 50% mark. You can see the formula NORM.INV and then the probability is 0.5, and then upper quartile, 75% mark, then the probability is 0.75. So that’s basically how we can use the NORM.DIST and NORM.INV functions. Now for those of you with more knowledge in statistics or an interest in statistics, there are a number of other functions that you can do similar things with in Excel 2013. Let’s look at a couple of them in this statistical functions list in the Excel Help. You’ll be familiar with the binomial distribution and there is a BINOM.DIST and a BINOM.INV function and also a BINOM.DIST.RANGE function.

You also have a chi-squared function; CHISQ.DIST,

CHISQ.INV, etc. And then various other distributions are represented. So for instance, we have Poisson functions. We have a POISSON.DIST function. Now most of these won’t mean a lot to you if you haven’t got a reasonable grounding in statistics but for those of you that have, there’s a pretty good selection there that you can use. So we’re going to leave the statistical functions at that point, apart from me giving you a little exercise to do. So we’re looking at example-06 here which is one of the provided files.

It concerns the

distribution of the number of passengers using a train service each day. The mean number of passengers is 1,640, the standard deviation is 304, and I want you to work out three probabilities. First of all the probability that less than a thousand passengers will use the service on a given day, secondly the probability that more than a thousand will use the service on a day, and thirdly the probability that between 1,000 and 2,000 passengers will use the service on a particular day. But I want you to express those as percentages rather than probabilities which you can do completely using Format cells, of course. And then also I’d like you to calculate the lower quartile, median, and upper quartile numbers of passengers and just express those in whole numbers. You won’t need decimal parts to those. My answer to that is example-07. © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced That’s it on this section. I’ll see you in the next one.

Chapter 6 – Text Functions Video: Text Function Case Study – 1 Toby: Hello again and welcome back to our course on Excel 2013 Advanced. In this section we’re going to look at text functions in Excel 2013. I’m going to assume that you have a basic knowledge of how to manipulate text in Excel, so you’ll be familiar with things like concatenating text, using the ampersand character, or indeed the concatenate function. The concatenate function is one of those listed under the text functions in Excel Help. And what I’m going to concentrate on in this section is a case study. I’ve received some data with some rather badly presented text and I’m going to look at ways of automating the cleaning up of that text using some of the more popular text functions. So let’s get started. So the situation here is that I’ve received some employee data and I’m going to try to work out how to clean that data up. There are various problems with it, and here’s the first 20 employees. Their details are each on a line. In fact each of them is in a single cell; the cells are overflowing. There are basically five pieces of information about each employee. There’s a name. The first guy is Jose Juarez. Then there is a value which I believe should be the start date with the company, but some of these have come through as numbers rather than dates. The numbers may well be the start dates in number format but I’ll need to look into that. Then I have another number which I believe is the annual salary. Then I have the department they work in. Now the department names are a bit sort of muddled up.

marketing, and then there’s R & D. So there’s a number of different departments involved there. And then finally, I have the employee’s role; their job title within that department and Jose is an intern. So what I’m going to do is to clean this data up and separate it out into a properly structured worksheet. Now when I do this kind of exercise in front of other people somebody always says Well, wouldn’t it be quicker just to retype it? I’m using these 20 people as an example and I need you to imagine that we’ve actually got thousands of employees presented like this and we’re trying to clean this up as automatically as we can for a very large number of employees.

Excel 2013 - Advanced Now the order in which I do these things is actually quite important. There isn’t only one way of doing it. But I want to do any of the general types of operation first. So before I split each of those lines up into its constituent parts, what I want to do is to do a couple of general things. The first general thing I’m going to do is to convert the text to proper case. At the moment, whoever’s presented this data or extracted it from a database has presented it all to me in upper case and I want to convert it to proper case, so with capital letters and so on. As with every other exercise we’re going to perform on this data, it’s very important to look at the results of what we do and try to identify any enforcing glitches, problems, generic kinds of issues. So when we’re trying to convert to proper case, we use the proper function and we’ll look carefully after we’ve applied the proper function to just check that everything seems to make sense. So the first job for me is to go down to right click on Sheet 1, move or copy. I’m going to create a copy of this at the end and that’s my saved copy. That’s basically a way of making sure that if all goes horribly wrong and I cannot recover back to where I was by doing undo and so on, I’ve got my saved copy back there. I could rename that to backup copy or something like that. Let’s go back to Sheet 1. Now what I’m going to do is to apply the proper function to A2. It’s pretty straightforward the proper function because you just say Equals proper and then you just enter the text. Now the text in this case will be A2. Tick that and that is now converted to proper case. So Juarez, Jose has now got capital J on both of the parts of his name. Admin becomes Admin in proper case, Intern in proper case and so on. That’s a pretty straightforward process to do to begin with. I’m going to fill down and then just have a little look through to see if there’s anything there which strikes me as having caused a problem. Note that R & D, if you look in that cell there, note that R & D is taken as two capitals because of the spaces then Excel assumes that each of those is the beginning of a word and, of course, de-capitalization or capitalization does not affect punctuation characters. So there we are; that all looks good. Now it’s time to make the first fundamental mistake which is to think, Ah! that’s much better. Let me get rid of column A. So I’m going to right click on A, click on Delete, and of course I have a problem because what was in column B references column A and I can’t just delete column A. So that’s hardly going to be a problem. Let’s just take a copy of column B’s contents and paste values into C. So I’m going to use the paste values there and then I can get rid of columns A and B. © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced Note that the proper function is one of a set of functions that converts case in Excel 2013. Lower converts to lower case, upper to upper case, and proper to proper case. Now we can look to see if there are any other kinds of general processing that we need to apply. I’m not going to do anything else in general now. The kinds of other changes I want to make, things like sorting out the names of the departments and so on I’m going to do once I’ve split each row of data up into its columns. So the next thing to do is to start doing that split up of each row of data.

Now rather

conveniently in this case the individual data items for each employee are separated by semicolon characters. So the key thing here is the position of the semicolon characters. Now generally speaking, once you’ve done what I’m going to do now a couple of time you’ll probably be able to do it in a much quicker way. But for the moment for anybody who really hasn’t done this kind of thing before, I’m going to break this down into two or three steps. The first question is this. If I wanted to split that up into its constituent parts how would I, for example, locate the first part, the name of the employee? Well, the name of the employee is everything up to the first semicolon. So the first question I’m going to ask is how far through that string of text in A2 is that first semicolon character? The function we use to identify that is the find function, and the find function takes three arguments and the third argument, a starting point is optional. We’re not going to need that this time. We basically say which text are we trying to find? Well, we’re looking for a semicolon character. What do we want to find that text in? We want to find it within A2. So tick that, and it tells us that it is the 14th character in the text in that cell. So that’s pretty good. Now there’s something else we’re going to need in just a moment and that is I also need to know how long all of the text is for reasons you’ll see in just a moment. The function that tells us how long a string of text is is the LEN function. So if I put Equals LEN, returns the number of characters in a character string. All I need to do is to put in the text string. Well, this will be A2, tick that, it tells us there are 38 characters altogether. You’ll see the use of that in just a moment. Now what I want to do is to identify that first part of the employee data, i.e., the name of the employee. In order to do that, I use another function, the left function which gives me a specified number of characters from the left of a string. So if I type in D2 here equals left, you’ll see it has two arguments; the text that I want to extract the characters from and then optionally © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced straight from A2 to D2, and of course it gives you the same answer. But my argument here would be that it does make what’s in D2 just that bit more complicated. Many people try to do these things as few steps as possible and as a consequence you finish up with pretty complicated formulae and use of functions. I generally find it’s easier to both do what you’re doing and understand later on what you’ve done if you break it into steps as I have here. You can easily hide the steps by hiding the columns or subsequently deleting the columns when you no longer need them. But I do think doing it step by step in the end produces results more quickly. So let’s now look at column E. Column E is now still details of each employee but with the employee names now stripped out. What I’m going to do now is to strip out the remaining fields or attributes for each of the employees. So I’m going to create four more columns, very similar way to the way that we’ve just done it. So again, I’m going to work on column E this time. I’m going to look for the first semicolon, create a new column, then the salary, new column, then the department, new column, then the job title, new column. Now there’s no point in me doing that with you watching me because you should know how to do that by now. So I’m going to do that and I’m going to take this particular workbook and get it ready for the next section where we’ll carry on working with text functions. So please join me for that.

Video: Text Function Case Study – 2 Toby: Welcome back to our course on Excel 2013 Advanced. At the end of the last section, I was stripping down the employee data that we’d received as a long upper case string with semicolon separators. I’ve now finished splitting that up using the methods I covered in the last section and now we’ve got a little bit more cleaning up to do of this data. So the first column we have there is the name column and that looks absolutely fine in general, although there is a problem that’s actually quite difficult to see but I’ll come back to that in a little while. The second column is the start date for each of the employees, and as you can see we seem to have quite a few problems with start date because you can see that some of them have come out as numbers which are presumably real dates, so the number of days since January 1, 1900, etc. And some of them have come out correctly as text dates. Now if I click on one of the ones that isn’t shown correctly, I get a little warning sign there and very often if you click that not only does it tell you what the problem appears to be but it will very often give you a good idea of what the solution to that problem might be. The number in this cell is formatted as text or preceded by an apostrophe. So what it’s saying is that in this particular case, it’s got a text number, 40697 in there and clearly a real date is not text. It should be a number. If I click on the little drop down, I’m given options and one of them is convert to number. Now in order for that to be treated as a real date, I need it to be a number so I’m going to click on convert to number. Now I’m going to change the format; I’m going to change the format to the format of a date. So if I go into Format cells, select date, my default here which is locale dependent is month, stroke, day, stroke, year. Click on OK. That looks better. So we’re really saying that this employee joined us on June 3, 2011. Now that’ll be same problem with the others probably. I’ll just quickly go through and sort those out as well. So you can see that the start dates look fine now. Let’s move on to the next column which is salary. Now in this case, I’ve also got markers that indicate problems here. Let’s click on the first one, check what the message is. It says Number stored as text. So we’ve got another case here where we’ve got a number stored as text. In fact, I’ll assume for the moment that it’s the same problem for all of them. Note that when I’m doing something like I’ve done here where I’ve imported data from another source when I take it in, in that semicolon separated or comma

Excel 2013 - Advanced separated value kind of format quite often Excel will treat numbers as text and I will quite often need to convert that text to being a number. It can even happen in a fairly apparently haphazard way as it did here where some of these start dates came through as dates and some didn’t. But with salaries, they all seem to have suffered from the same thing so I’m going to select the whole thing, click on the drop down, and convert to number. Generally speaking, you can tell whether what looks like a number is formatted as a number or not by its alignment. If it’s left aligned, it’s probably being stored as text. If it’s stored as a number it will normally be right aligned. So if I do convert to number, they will all become right aligned and then while they’re still selected if I go into Format cells, choose currency. I’m going to use the dollar symbol but I don’t want any decimal places. Click on OK, and I’ve got all of my salaries there now stored and displayed as numbers, currency. So that’s the salary column. Let’s move on to the department column. Now sometimes some of the things you need to do maybe look a little bit repetitive, even allowing for the use of functions in Excel 2013. If you look at department here, I’ve got a department of M-K-T-G, data I’ve imported has actually flagged the marketing department as MKTG. I really want to call that marketing. Similarly, R & D I might want to call research and development. Admin I might want to call administration. So you might consistently want to use the full names of my departments. Now I could, of course, go through and manually edit those values. I can also use the find and replace function that I’ll come to in just a moment. But there is a function; in fact there are two functions in Excel, text handling functions that are worth knowing about. Substitute is one and replace is another. Now let me just insert a column here and I’ll show you the use of the substitute function. Now the substitute function basically in a text string will substitute one string for another. So if I go for substitute it says first of all the first argument, the text. This is the text or reference to a cell that contains the text for which you want to substitute characters. So in this case that would be for instance D2. The next thing I do is to specify the old text, what I’m actually looking for. Well, let me suppose I’m going to say at this stage that I’m looking for the string MKTG and then I specify what I want to replace it with. Let’s suppose I want to replace it with Marketing. Now the fourth argument there, the instance number, actually specifies which occurrence of that old text you want to replace. So if it occurs multiple times, you can specify which occurrence © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced you want to replace. Now as far as this particular exercise is concerned, it’s only going to occur once so that’s an optional argument. I’ll leave that out and I’ll just tick that. Now, of course, it doesn’t find MKTG but if I fill down, Control-D, watch what happens. The one case where I do have MKTG it now says Marketing. So that’s the use of the substitute function. Now as I said just now one of the issues here is that this might well be quite a repetitive exercise. So having done that substitution, you may want to then run through and do another substitution of substituting Admin with Administration, R & D with Research and Development. So you may need to apply this function a few times, and this is where you get into the realms of writing program code to do something repetitively. But that’s really outside the scope of what we’re doing here. The other way you could do this is of course to go into Find and Replace. So if you went into the Replace dialog, you could actually say find MKTG, replace it with Marketing, find Admin, replace it with Administration, and so on. And then as you know you should be used to using find and replace. That will be another way of achieving the same effect. You need to be a little bit careful if you’re dealing with a very large set of data to make sure that the strings you’re replacing don’t occur in another unrelated situation. But if you’re careful that’s a perfectly feasible and valid way of doing this kind of text replacement. So there we are. We’ve done effectively an import of some data from an external system and we’ve used quite a few of the text functions mainly in order to clean that data up. There’s still a little bit more to do but as you can see it’s looking much better now. Notice one or two little side issues, a couple of little knock on effects you’d need to be careful of. If you look at number 15, Charles Cook, the job title of VP suffered from conversion to proper case; became capital V, small P. And that’s the sort of thing to look out for when you’re doing this kind of processing of bulk data. If I had had thousands of employees, I probably would have taken a sample of 20 and run through all of the processes that I was aiming to use first just to make sure that the general ideas are okay. But you’ve still then got the issue that even in thousands of employees where you’ve done some kind of test you may get the odd glitch like Charles Cook’s job title. So you need to be vigilant the whole time you’re doing this kind of processing.

Excel 2013 - Advanced And now there’s just one other function to mention. I’m sure you will have used it before. I did mention much earlier in this section that there is a problem that’s virtually impossible to see but which you should always be aware of, and that is that sometimes when you import data, you finish up with leading and trailing spaces. Now I’m just going to insert a column before column B and what I’m going to do is use the LEN function here to look at the length of Jose Juarez’s name. And the length according to Excel is 13 characters. Now if you count those characters including the space between the comma and the second J, you’ll actually see there are 12 there, not 13. The reason it says 13, if I select that cell, click in the entry bar, you’ll see there is actually a space between the final E and the flashing cursor. So there is a trailing space and in fact most or all of those names have got trailing spaces. They may well have got leading spaces. Part of import very often introduces leading or trailing spaces or sometimes they’re in the data anyway. So it’s very important to be able to trim leading and trailing spaces. Now the function you use for trimming leading and trailing spaces is Trim. And if you look at the screen tip there, Removes all spaces from a text string except for single spaces between words. So if you want to tidy up the spaces, the trim function is the one to use. We’ve looked at a selection of text functions in Excel 2013 and now we’re ready to move on. I’ll see you in the next section.

Chapter 7 – Lookup Functions Video: Lookup and Reference Functions Toby: Hello again and welcome back to our course on Excel 2013 Advanced. In this section we’re going to take a look at lookup and reference functions. There are just under 20 of these in Excel 2013, and in this section I’m going to concentrate on the two that are both probably the most popular lookup and reference functions, and also the functions that a lot of people struggle with getting quite right which are HLOOKUP and VLOOKUP. Basically these two functions do the same thing and that is they look something up as though they’re looking up in a reference document or maybe in a database getting some information from one table based on an index from another table. The difference between them is that in the case of HLOOKUP, the values that the looking up are arranged horizontally and in the case of VLOOKUP they’re arranged vertically. Both of them also have a major choice that you make which is whether you’re trying to exactly match what you’re looking up or you’re just trying to match it approximately or within some kind of range. So I’m back at the insurance policy sales figures for Orlando and what I’m going to do is to classify the monthly sales by the quartile in which they occurred. So the first thing I need to do is to calculate what the quartile values are. Now I’m going to put the first quartile value in here. So the function I use is quartile. I’m going to use QUARTILE.INC. I need to put in the array containing the data. So you should remember that we setup a name for that. That’s Policy Sales Orlando. And for the first quartile, okay, that’s 86.25. Let me now fill that right and I want the second quartile, third quartile, and fourth quartile. Now, of course, the other thing to bear in mind is that there is a beginning, a bottom end if you like, a lowest number here, a zero quartile if you like of zero. So if I wanted to divide all of these policy sale figures into the four quartiles, the numbers of policy sales per month that establish those quartiles are zero, 86.25 which is the first quartile, 111, 139.25, 189. Now the way this is going to work is that we’re going to take each of the policy figures. So for January 09, that’s 114 and we’re basically going to look it up in this list to work out which quartile it should be in.

The values we’re looking up then are horizontally arranged and

Excel 2013 - Advanced therefore we’re going to use HLOOKUP. If I’d put the numbers downwards, zero, 86.25, 111, etc., we would have been arranging them vertically and we would have used VLOOKUP. So the way that HLOOKUP works is this. I give it a value, in this case 114. It starts at the beginning of the list and compares each value in the list. So it says zero. Okay 114 is bigger than zero; 86.25. Okay 114 is bigger than that; 111. Okay it’s bigger than that. It eventually gets to a value which is greater than 114 or, of course, it might get to the end of the list. When it finds a value greater than the value I’ve given it, it basically returns a categorization or a reference value for the one before. So it’s looking for a value in this list less than or equal to 114. So the value that it finds will be 111. So 111 needs to return a value of three because I want it to say it’s in the third quartile. And that basically tells you how we setup our quartile values because if I move on to, say, 140, zero no, 86.25 no, still bigger, 139.25 still bigger. Eventually it gets to the last value, 189 and it will say okay 189 is too big. It’s that one I want, 139.25. It’s in the fourth quartile. Now the very final value, the maximum, also needs to return four because it’ll actually have a value that’s equal to the highest value. It’s going to be in the fourth quartile. So that is my lookup table for the use of this HLOOKUP function. The easiest way to set this function up, generally, is once I’ve established my table is to set it up as a name. So I’m going to define a name. Well, I’m just going to call it Lookup Table. Not a very inventive name. And I’ve now setup my lookup table. Okay, so let’s now try using the lookup function. I’m going to click in cell D4 and I’m going to type in there Equals HLOOKUP. Now, first of all, what’s the value that I want to look up? Well, it’s C4. And the table array, where is my lookup table? Well, I’ve given it a name. I’ve called it Lookup Table. And the next question is when you look it up which is the value that you want to return. You’re going to start looking in that first row but which row in the table gives the value that you want to return. Well, it’s the second row. So I’m going to put in there comma, two. And then the last option, range lookup, is either true or false. If you put true, which is the default, then it will do an approximate match. It will do a match on the basis that I just described where it’s looking for a value less than or equal to the lookup value. If I set that to false, then it would look for an exact match. So I’m going to set that at true. Tick and what we find is that 114 is in the third quartile, and that appears to be correct to me. So let’s just do a fill down of a few, just a little bit of a test. So, 102 is in the second quartile. That looks correct. So © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced on. I’ve got a whole load in the fourth quartile. It appears to working fine. Let’s fill it right down to the bottom and there we are. Every cell that’s occupied in column D now specifies the quartile for whatever the number of policies sold was in that month in the Orlando branch. Now I’m going to demonstrate the use of the VLOOKUP function and in this case we’re going to do an exact match. The worksheet you’re looking at, at the moment is a catalog. It’s a catalog of parts for a company that provides materials, hardware materials, door handles, that sort of thing. And I’ve got a section of the catalog here. I’ve just got three columns of information. Column A contains a part number, column B contains a description of the part and column C contains a price. In this case, the prices are given in U.K. pounds. Now what I want to do is to use this catalog to help me to produce invoices for my customers. So on Sheet 1 of this workbook I’ve actually got a draft of an invoice. It’s going to have a date. It’s going to have an invoice number. I’m going to put some sort of logo or some graphics at the top. But basically the way the invoice works is this. I’m going to specify the quantity of a part. I’m going to put the part number in here, the description of the part in here, the unit price here, and the total price here. Now what I don’t want to have to do is to put in a part number and then go to the catalog, carefully read down, and do some sort of copy and paste of the right description. I want both the description and the price to be automatically filled in on the invoice when I enter a part number. So the part number will be entered there and automatically I want to see the description appear here and the price appear here. So the key to all this is the part number. The part number is the thing that I’m going to look up in the catalog. And if I just go back to the catalog, the part numbers are arranged vertically. So I need a VLOOKUP because I’m going to look through a vertical list of the index items, in this case which are part numbers. Now I know I’m going to need to look up in the catalog, so the first thing I’m going to do is to define a name for the catalog. So let me select. I’ve only actually got a section of the catalog here. It’s not the whole thing. I’ll select that. Formulas, define name. I’m going to define a name of parts catalog. Okay. And then I’m going to go back to Sheet 1 where I’m creating the invoice and the basic principle on which I’m going to work is this. When I enter a part number in here, I want to get the description in here. So in the description field here, I’m going to need to put Equals, VLOOKUP. Now what am I going to look up? Well, the lookup value on that row is going to be C7. What table array am I going to © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced look up? Well, I’ve just setup the parts catalog so it’s going to be parts catalog. Now of course, I could put that in, in terms of cell references on another worksheet in this workbook but it is really very convenient to use names for this. And then next column index number. Which column am I looking at? The description column was the second column so I just need a two in there. And am I doing a range lookup? Am I looking at a less than or equal or am I looking for an exact match? Well, in this case I need an exact match so I’m going to put false in there. Now what I get here is N/A. I don’t want you to worry too much about N/A. Basically what it means is it can’t find that entry in the catalog because, of course, there is no part number which is blank. But if I put a real part number in here. Let’s put in 25189. Let’s see what happens. Now I get an actual description from the catalog. Now let me deal with the price of this part. So again it’s going to be a VLOOKUP. I’m still looking up C7 in this case. I’m still looking in the parts catalog but this time the column that I need is not column two, it’s column three because that’s the column with the price in it, and I still need false for an exact match. Tick on that and I find that the price of that item is 13 pounds and 33 pence. Now of course, I could enter quantity here and multiply quantity by unit price to give me total price. That’s maybe something you’d like to try out yourself. But in terms of the use of the VLOOKUP function, that’s basically how it works and what it does. Now as I mentioned just now we have a problem in that if I were to fill down this description so that I could use it for other rows in the order details, I’d get that N/A problem and I’m going to talk about that N/A problem in the next section where we’re going to look at logical functions. For the moment, of course if I don’t fill down then everything’s fine. But I would need to copy and paste or fill down one at a time the contents of the description field in the order detail section and the unit price field as well each time I put a new part number in, which is going to be a little bit of a pain if I have to keep copying and pasting or filling down. I’d really like to be able to fill this all up right at the start, but as I say I’ll come back to that in the next section. As I said at the beginning of this section HLOOKUP and VLOOKUP are very heavily used functions and quite a few people do have some trouble with them. I think once you’re used to them they’re pretty straightforward. And there are various other functions amongst the lookup and reference functions that you should find pretty useful.

Excel 2013 - Advanced I’m just going to look at one other now. This is the choose function which is a little bit under used. It’s like a very simple lookup function and let me just give you a quick demo of how you might use that. Let’s suppose you’ve got a particular situation, say, if I take a section of one of the score cards that we looked at earlier on in the course. John, Jane, Jim, etc. have each given their opinion of something. So they’ve scored something on the range of one to five and we’re going to interpret each of those values one to five in a different way. I’m sure you’ve done this sort of thing in an opinion poll, strongly agree, strongly disagree, etc. We could setup a lookup table and for each score, for each person doing the test we’d look up their score and turn their score into a term like strongly agree or disagree or undecided. But you can use the choose function when you have a small number of options and you can build the whole thing into the use of the function. So what I would do in this case, let’s suppose that I want to interpret John’s score of one and I’m going to put the interpretation into the cell B3. If I use the choose function, it’s Choose. Which score am I going to interpret? Well, it’s B2. That’s John’s score. If his score’s one, I’m going to say that means strongly disagree. If the score’s two, I’m going to say it means; if it’s three, and so on. Now I can do that with as many options as I like but clearly it would be quite impractical to use it for dozens of options. But having done that if I then do a fill right in this case and let me just spread them all out a bit more so you can read what they actually say. You can say it’s a pretty good way of interpreting a limited number of options. So that’s the choose function. That’s it for lookup and reference functions. In the next section, we’re going to look at logical functions. Please join me for that.

Chapter 8 – Logical Functions Video: Logical Functions Toby: Welcome back to our course on Excel 2013 Advanced. In this section we’re going to take a look at the relatively small group of logical functions. There may not be too many of them but they are extremely important because once you get beyond the very simplest workbooks in Excel, it’s very often the case that a calculation you have to do or a particular action you need to perform is dependent on something else. And very often it’s dependent on a combination of things. So logical functions like If and If N/A which are basically conditional functions and then other functions such And and Or that can combine conditions together become extremely important. In the previous section we looked at this workbook and on Sheet 1 we were creating an invoice specifying a part number here, 25189 in cell C7, and then using VLOOKUP functions here and here to get the description and the price from the catalog which is actually a named area on the catalog worksheet within the same workbook. And the problem we found is that if we fill down here, we get N/A wherever we’re dealing with a part where we haven’t entered a number. So that looks pretty horrible on the sheet there. So what we’re going to do now is we’re going to replace the original lookup. So let me just undo the fill down and let’s look back at this lookup. Now this lookup says Equals VLOOKUP and then it’s got C7; that’s the part number, Parts Catalog; that’s the named range that defines our parts catalog, two is the number of the column within that named range, and then false means we’re looking for an exact match of the part number. Now what I’m going to do is to replace that formula and I’m going to replace it by putting immediately after the equal sign, If N/A. If you look at the screen tip there for If N/A, Returns the value, you specify if the expression resolves to N/A. Otherwise it returns the result of the expression. So what this is saying is if in the field where I’m going to put this formula I’m looking at something, this is the thing defined by VLOOKUP, and it comes out as N/A, I’m not going to show N/A; I’m going to show something else. So it says let’s calculate VLOOKUP and if the result of that is N/A, what am I going to display? Well, I’m just going to display a space, a literally a space character. So what my use of that function means is do the VLOOKUP, if the answers N/A put a space. If it’s not N/A, actually put the result of that VLOOKUP if it’s not © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced N/A. So let’s try that in the cell where it actually occurs. Of course, we get the same answer that we got before. Now let’s do that fill down. Control-D. Now it appears that nothing happened because we’ve just got empty cells still but if I click in those cells, you’ll see that my fill down has happened, but you’ll also see that I’ve got empty cells so it looks much neater than it did before. Let’s do the same thing with the unit price cell. So again in there we’re going to put if it’s an N/A what we want to appear in the cell is a space. If it’s anything else, then the VLOOKUP is what will appear. Okay that still seems to work alright. Let’s fill that down, and again we’ve got a line of spaces. So let’s see if that works okay if I put another part number in. There we are. It’s absolutely fine. Now let’s take a look at the If function itself. This works in a similar kind of way. The difference is that with the If function you specify a condition and if the condition is true one thing happens. If the condition isn’t true another thing happens. Now the condition we’re going to use here relates to discounts on pricing within our orders. So what I’m going to do is I’m going to change this invoice a little bit and I’m going to put a discount percentage in between columns G and H, and then I’m also going to put a column for the discounted price. So the basis on which we’re going to calculate the discount is this. We’re going to say that generally speaking everybody gets a 10% discount at the moment. But we’re doing a special at the moment and our special involves giving you a 20% discount on door handles, but only door handles. Now we’re going to assume for the purposes of this exercise that you can recognize that something is a door handle because the description will start with Door handle. Now notice the ones there have got Door handles with an S. But anything with Door handle at the beginning will get a 20% discount. So what we have to do in this case in cell H7 is to insert an If statement and the If statement is going to check whether we’re dealing with door handles or not. So when you enter an If function, it is If and there are basically three arguments. The first argument is the test that you’re going to perform, the second argument is what you’re going to do if the test is true and the third argument is what to do is the test is false. Our test is going to be is the item a door handle? If it’s true, if the answer to that test is yes, then we’re going to put 0.2; that’s 20% into the cell. And if it isn’t, we’re going to put 10%. So in fact the second and third © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced arguments are easy. The second argument is 0.2 and the third argument is 0.1. So they’re the easy part. Now what’s this test? Well, the test is to say that if I look at cell D7 does it say that we’re dealing with door handles? Now I’m going to assume that if the part the customer has ordered is a door handle, the description will begin with door handle. You can’t actually in an If statement in Excel 2013 in a straightforward way do some sort of like comparison. You can’t say is the description like door handle something? But door handle is 11 characters and the test I’m going to make is this. Are the left most 11 characters of that description door handle? So what I’m going to say is If left, the function we saw before, D7, comma, 11. So if the left most 11 characters of D7 equal and then I’m going to put Door handle. That is my test. So if the left most 11 characters of D7 are door handle, 20% discount. If the left most 11 characters of D7 are not door handle, 10% discount. So let’s try that, and lo and behold for this first one, we get 20%. Now let’s put a different part number in here. Let’s try 23045. That part is that’s actually masonry screws. Let’s do a fill down now, and there we are. As you can see we’re now getting a discount of 0.1 for masonry screws, 0.2 for the two types of door handle. Now let’s suppose that I want to give an even better discount offer to my clients. I’m going to say we’re giving 20% discount on door handles and masonry screws at the moment; so all three of these items would actually get a 20% discount. So the test we need to do is if the description either begins with door handle or it begins with masonry screw give a 20% discount, anything else 10%. To put in multiple conditions, you can use the Or function or the And function. And in this case, either door handles or masonry screws will get a 20% discount. So in the If function there, within the If function what we’re going to do is to nest an Or function. So we put Or and we separate the different conditions that would make that Or statement true by commas. So we say left D7 11 equals door handle, next condition, left D7, comma, now masonry screws we’re talking about 13 characters and what we’re checking there is Equals masonry screw, close that. Let’s try that one, do a fill down. There we are. Now let’s put in a part which is not a door handle or a masonry screw. Fill down. That’s it. So we’re giving 20% discounts on door handles and masonry screws at the moment.

Excel 2013 - Advanced the ones around it but it does basically work. Let’s try putting a part number in here. And it does actually do the calculation correctly. So that’s absolutely fine. Now there are one or two ways that you could improve that, but that’s basically the approach that you need to take. So having shown you how to do that what I’d like you to do now is to work out a formula for discounted price that can go into cell I7 and be filled right down to the bottom and that’ll work in all cases. Having done that I want you to work out a formula for total price and having done that a grand total at the bottom. You don’t have to worry about varying the number of rows in order. The maximum number of rows is eight and, of course, anything from one to eight of those may actually have values in. Don’t forget to incorporate the quantity. Note that I’ve changed the formatting of the discount column here to percentage. But the number that’s stored is still a decimal number in the range zero to one. So it still holds 0.1. The percentage display there is only how it’s displayed. It’s nothing to do with the number that’s stored. That’s it for this section. I’ll see you in the next section.

Chapter 9 – Connecting to External Data Video: Connecting to Other Workbooks Toby: Hello again and welcome back to our course on Excel 2013 Advanced. In this section we’re going to start to look at connecting to external data from an Excel workbook. We’re going to start, first of all, by looking at connecting to data on other worksheets in the same workbook, and then at connecting to other workbooks; both open and closed workbooks. Now in order to demonstrate this I’m going to use a very simple example. I’ve got my sales data for the insurance company branches and I’m just going to do a comparison between the figures for Orlando and the figures for Miami just for one year. Now one thing you’ll already be aware of I’m sure is that if I were to say go to the Orlando figures and copy those, and then if I go to this tab that says Branch comparison and do a paste, what I get with a regular paste is that I just get the values that I’ve copied appear there. Now let me go back again. I don’t really need to do this copy again but just to demonstrate as though I’m doing it from the beginning, copy again. If I go back to Branch comparison and in effectively look as though I’m going to repeat that but instead of doing a paste I choose a different paste option, so let’s go into Paste Special and let’s go down to that one, Paste Link. When I click on Paste Link what I get in each of those cells is a reference to a cell on the other worksheet. So the reference has the structure of the name of the sheet is between single quotes, then there’s an exclamation mark. I normally say bang. And then C3 is a relative reference to the cell. So if I click at the other cells, of course I get the equivalent links in those cells. Now I’m sure you’re familiar with that and the advantages and disadvantages of those two basic approaches. If you paste links, then clearly if I make an update on the Orlando page, it will be reflected in the page where I’ve pasted as a link rather than a value. So let’s take that 544 value there. If I go to the Orlando page, click on the 544 and make it 5440, if I go back to that page of course it’s updated. And of course, if I’d pasted the values then that wouldn’t be the case. Now for the purposes of what we’re doing here, I’m ignoring things like pasting formatting, etc. I’m only concerned with the values themselves.

Excel 2013 - Advanced the values which is true but you also get below that area where I’ve just pasted a little menu, a little paste options menu is just sort of fairly indistinct but you should be able to see it there okay. You can either click on that or just by pressing the Control key on the keyboard, it opens the little menu up and then one of the options down there is the Paste link option. But, of course, you have all of the other options as well. So apart from paste link, you have linked picture and then you have the various values, formatting options, and so on.

So if you just do a

Excel 2013 - Advanced may only need a small subset of that information. So one approach within my invoicing system may be to just take the information that I need from my main database and use it within this invoicing system that’s actually based on an Excel workbook. So let’s do another connection to that same Access database, but this time I’m going to do it and look at few more of the options that are available. So back into Get external data from Access, choose the same Access database. Now this time in the top left hand corner of the Select Table dialog, there’s a checkbox, Enable selection of multiple tables. I’m going to select that and I can now connect to many tables at the same time. Each of the connections will be on a different worksheet, but let’s suppose that I wanted Actor, Movie, and MovieActor, so that’s the table that joins movie to actor. So I want three tables. Click on OK. Now the options for how we want to view this data. Now the default is a pivot table report, and pivot table report and indeed pivot chart we’re going to look at later on in the course so I’m not going to deal with those at the moment. If you’ve used pivot tables before, I’m sure you know what a pivot table report is and you could go straight into a pivot table report. That would give you a pretty neat way of doing some analysis on this table of data from Access straightaway within Excel. So let’s click on just Table, so that’s a regular table. When you say table, it’s going to be new worksheet. In fact, you’re going to get a new worksheet for each of the tables that we’re importing. The other thing here, although it’s grayed out at the bottom here, Add this data to the data model, in Excel 2013 there are some quite extensive facilities now to maintain structure between worksheets. You haven’t quite got the structural capabilities that you have in say Microsoft Access, but you can now establish in Excel 2013 the kinds of relationship that you in the past have only been able to create in a fully blown database system. When you import two or more tables, you automatically create a data model and these are included in that data model. If I click on the Properties box there, note that it says, Import relationships between tables. So if you’ve already got relationships, which of course we have, the fact that this box is checked means that those relationships will be imported as well as the data itself. Now before I do this import, let’s just look at the properties. Now the connection properties are pretty important. One of them here just near the top is Refresh every so many minutes. You can actually schedule to have a regular refresh from the © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

When you’ve setup a

Excel 2013 - Advanced connection to more than one table and possibly imported not only data but relationships as well, what Excel 2013 does for you is to create a workbook data model. If you want to refresh that, you’ve got refresh all but you’ve also got the option if you say decided having done as we’ve done here import movie and actor information from our source Access database and decided that you say wanted to now add the country and genre information, on the add here there’s an option Add to the data model. With add to the data model, we can go back into the Access database that we’ve accessed the data from and we can look at other things that we might choose to add to that data model. So there’s some of the connections we’ve setup already and here under Tables we’ve got a list of the available tables in our Access data source.

So you can actually

supplement those connections and build up the data model over time in Excel, probably to reflect the one that you’ve got setup in Access already. The key point here though is that you don’t need the whole of the Access data model. You can just create the bits that you want for this particular Excel application. So that’s pretty much it. I hope that’s enough to get you well started on creating connections to Access databases. There’s one little thing you may have noticed here. This line of hashes here, there’s clearly something wrong there. I believe you’ll find if you want to look into it that that’s because Humphrey Bogart’s date of birth was before the 1st of January 1900. He’s the only actor there that is and, of course, that is our counting point for date formats in Excel. That’s a problem that can be solved but maybe that’s a little challenge you’d like to try and solve that yourself but I’m not setting that as an example on the course. So that’s it on connections to Access data. In the next section we’re going to take a look at connections to web data. So please join me for that.

Video: Connecting to Web Data Toby: Hello again and welcome back to our course on Excel 2013 Advanced. In this section we’re going to take a look at connecting to web data which in Excel 2013 is actually surprisingly straightforward. However, you do need to know how to set it up properly and it’s very important to understand both the strengths and weaknesses of connecting to web data. I’m going to demonstrate this with just one or two examples. So first of all, let’s look at a little bit of financial data available via Microsoft. So first of all, I’ve got a new workbook open here with a blank sheet. I’m going to get external data and one of the options there is from web. Now when I click on Get data from web, what happens is it opens a new Web Query dialog. In fact it looks very much like an internet browser, and basically that’s what it is. But it’s an internet browser with a bit of a twist really, as you’ll see. It’s called New web query and it will open at my home page. Now what I’m going to do is to locate some web data and I can identify the sort of web data that I’m going to be able to import by these little black arrows on yellow backgrounds that you can just see in the corners, top left hand corner there.

Now generally speaking, when we’re

importing web data we’re importing tabular data and a very large proportion of the data that’s available on the web and certainly the sort of data we’re going to be able to import into Excel will be tabular data. Now sometimes the tabular data is tables of images and so on, and that may actually be what you want. But on this occasion, I’m primarily going to be interested in numeric data. So I want to get a stock price and so what I’m going to do is go to the MSN U.K. money page. Down at the bottom of the U.K. MSN, there’s a stock quotes link there, and then for the stock quotes I can choose the country which is for me is going to be U.K. And then I’ve got a choice of market or index. I’m going to stick with all. And then what I’m going to do is to find a particular stock that I’m interested in. I think I’m going to go for A.G. Barr PLC. That’s a pretty convenient one. So select that stock name and that takes me through to basically a page where I can see the stock price for this particular stock showing it’s variation over time, little graphs on the right and so on. Importantly to the left, I’ve got one of those what I’ll call those yellow arrows. Now in order to select the data that that yellow arrow refers to, if I hover over it, first I © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced been lost. But the raw numbers, the ones I actually want so the positions of the top 20 clubs there in the Premiership Table are correct. Manchester United at the top as usual. Number of games played is here. They’re coming towards the end of the season. This is only updated roughly once a week. They do sometimes play two games in a week but usually it’s once a week. So this is not something where it’s worth having an update set for every 30 minutes or 60 minutes because all I want it to do is to update when I open it from week to week. All of the main numbers are correct. The formatting is not generally carried over as you can see. But that’s the sort of thing I’d like you to do as your exercise in connecting to web data. I’m going to save this one as example-10 and if you want to refer to mine and in fact by the time you look at this that table will have changed. In fact, the English Premiership season is probably going to be over with by then so it will be interesting to see how that comes out when you open it on your computer in your country. Okay that’s it on connecting to web data. I’ll see you in the next section.

Chapter 10 – Tables Video: Creating a Table and Changing Table Name; Filter; Removing Duplicates; Total Row Toby: Hello again and welcome back to our course on Excel 2013 Advanced. In this section we’re going to start to look at tables. Now generally speaking, the data that you see on a worksheet in a workbook in Excel 2013 is tabular data. But when we talk about using tables in Excel 2013 we’re talking about a particular structure within a worksheet where we define a range of cells within the worksheet to be a table. Now when we do that, we get a lot of advantages. The range we’ve defined, the table that we’ve created can be formatted very simply. We have additional commands on a contextual tab that we can use to process the content of the table, and some functions we can perform, such as removing duplicates become very straightforward. Now I’m going to show you many of those advantages in this and the following sections. Now in order to demonstrate the use of tables, I’m going to use part of the invoice that we were working on earlier in the course. The order detail section which has a border around it here seems ideally suited to being a table. We have several rows; each row is an order item. And then several columns, each column represents a different piece of information about each of the order items. Now let’s suppose that I want to turn this tabular section, the section with the border around it into a table. Well, it’s straightforward enough as you’ll see in a moment but I want to warn you about something first and that is that when you are going to treat part of a worksheet, maybe all of a worksheet as a table you should avoid having merged cells included. Here we’ve got merged cells. So for instance, with columns D to F the rows here are merged on those columns. So the first thing I’m going to do is to remove the merging of the fields. Now the merging was done much earlier on in the life of this invoice and in fact it now serves no useful purpose. The order details heading which is outside the table, that’s a merged cell as well. It doesn’t matter about merged cells outside the table. It’s only the ones inside the table that you need to avoid.

Excel 2013 - Advanced Now the way that we specify which columns constitute duplicate evidence, so if the only evidence we need is the same part number, we only need to tick the part number column to identify a duplicate. Now in that case, that’s just one column. So if I unselect all of those cells and just tick part number, then it really will just consider that if the part number’s the same, it’s a duplicate. So what it should do here is to delete one of these two rows that’s got 25189 in it. Here’s a word of warning though. There is another duplicate under the same conditions. In fact, there are two more duplicates because the empty rows count as duplicates as well. Now from the point of view of our table, we don’t really need three empty rows because as you’re going to see in the next section, it’s very easy to add rows to a table. We don’t need lots of blank rows sitting here waiting. So when we actually push the button on removing duplicates, we’re going to lose two of those blank rows as well. So let’s click on OK and see what happens. And sure enough it says Three duplicates found and removed. Five unique values remain. So what it’s done, you can see it’s not only deleted one of the duplicate rows but it’s actually got rid of three blank rows because, of course, an extra blank row arose because of deleting that duplicate. So click on OK and our table is now reduced in size with in effect three less rows than it had before. So let’s move along the Table Tools Design tab again. There’s an option here, external table data with an Export button. The main option there is export table to SharePoint list. Now, SharePoint lists are outside the scope of this course but if you do use SharePoint, that is the facility you can use to get the contents of any one of these Excel 2013 tables into a SharePoint list. Now let’s move on to the style options. Most of the things to do with style I’m going to look at later on because I’m going to take a look at these table styles in a little bit more detail. But one of the things that I did before, you can see the poor lost total row there that was left behind and not included in the table. I can automatically have a total row included. If I check that checkbox, I get a total row. I get a heading that says Total, and then in each of the cells I can have a formula. Now most of them are empty except for the last one and the last one has in it a subtotal, 103, comma, total price formula. Let’s just see how that works. So if I click on the little drop down to the right of the cell that currently says five, I can see a list of functions and the selected function is the one that Excel 2013 has applied in this case. And it’s applied the count function. So it’s actually counting the number of rows. There are five © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced rows there, obviously not including the header. Four of them have prices in and one of them doesn’t, but there are five rows. If what I really wanted was the total, I’d need to choose the sum function. So if I click on Sum, I of course get a total of 95 pounds 83. So I can now get rid of that couple of cells we had down there. We no longer need those. We’ve now got a perfectly working total within the table. Now generally speaking, for the other cells in the total row, if I click in say that one I can see that it’s currently empty. If I click on the little drop down there, I can see that there’s none. So it’s not doing any kind of totaling or working out a max or a min or an average or anything else. But I can apply one of these functions and formulae to any one of those totals. So let’s take the first column for example. Supposing I wanted to actually know the total number of items in the order, if I click in there, click on the drop down, what I really want if I do a sum here is to see the total number of items. So we’ve got two of those, one of those, four of those, one of those. It should be eight items. So click on the sum and sure enough, I can see that the number of items in the order is eight and the total price of those eight items, 95 pounds 83. So there are a couple of other things on the Design tab here. I can have banded columns as well as or instead of banded rows if I want, for instance. So, click on banded columns if I would like banded columns; but more of styles later on. The only other thing to point out here is that the Filter button is actually an option. If you don’t want the Filter buttons in the header, you can switch them on and off using that checkbox. In the next section I’m going to look at inserting columns, inserting rows, changing the size of the table in general. So please join me for that.

Excel 2013 - Advanced So I think you’ll agree that’s a pretty good way of inserting a new row into a table and, believe it or not, there are a couple of even better ways or easier ways anyway of inserting a new row into the table. If I select the last cell in the last row of the order details and just press the Tab key, watch what happens. So press the Tab key and what I get is a new blank row. If you’ve used Microsoft Word before, you’ll know that that’s the behavior you get in Word when you’re working with tables. If you press the Tab key on the last cell on the last row of a table, it automatically inserts a new row at the end. Well, it’s exactly the same using tables in Excel and that’s one of the simplest ways of getting a new row in a table. There is yet another way of adding a new row to a table which is pretty easy, although from my experience it can only work provided you don’t have the totals row displayed. The reason I’m going to show you this way here though is because it gives you access to something else that’s quite useful to know. So I’ve got the same order details table here. I’m going to just go into the Design tab and switch off the total row for the moment. I can just switch it off. It’s not displayed. If I now click anywhere in the row underneath the table and start typing, so for instance, let me do four and then Tab. Notice that little lightning bolt symbol there under the second column. I’ll come back to that in a moment. Let me just put in a part number and you can already see that the table has been expanded to insert the new row. If I now go back into the Design tab, switch back on the total row, everything’s absolutely fine. So that way works but you do need to have the total row switched off for it to work reliably. So let me just go back to that lightning bolt again. Let’s just redo what I did just now. So select a cell in the row underneath the table, start typing, in this case four, press the Tab key. Now if I click on that lightning bolt, there’s AutoCorrect options. Click on the drop down and I have a choice. Undo Table AutoExpansion. That’s what’s just happened, Table AutoExpansion. You can stop automatically expanding tables if you don’t want that kind of behavior, then you can select that option. But also if you click on Control AutoCorrect options, this is the other useful thing to know about. If I click on that, that takes you into the AutoCorrect options for Excel 2013 with the Tab AutoFormat as you type selected and options there which are very useful. Apply as you work, include new rows and columns in table, and automatically as you work fill formulas in tables to create calculated columns. So it’s very important that you’ve got those

Excel 2013 - Advanced You’re saying if D7 is blank, then there is no total price. If D7 isn’t blank, then multiply the quantity by the discounted price. Similarly, if I wanted to replace D7 by its name, if I just click D7, if I just delete D7, and click in D7 within the table it is the description. So now my definition of total price is if the description is blank on this row, i.e., there is no part specified, then the total price is a space, it’s nothing. If there is a description, so there is a part here, I want to multiply the quantity by the discounted price to find the total price. Okay, we’ve got a couple more things to cover in the next section on tables but I’m now going to set you your next exercise to do and this really is a sort of two part exercise. I’m going to save this invoice workbook as example-11 and you’re going to work on that. The first part of the exercise is pretty straightforward. I just want you to go through all of the formulae in this workbook as it is here and replace cell references such D7, B7, etc. with names in the way that I just did in this exercise. That’s pretty straightforward. The second part is a little bit trickier and it’s not really got a lot to do with tables as such but it’s another step in the development of this invoice. That is the payment terms on our invoice will be 15 days if the value of the total of the invoice is less than 100 pounds and it’ll be 30 days if it’s more than 100 pounds. Now, if you’re working in a different currency to me, so if you’ve got your system setup to use dollars or some other currency, Euros or whatever, that’s fine. Just take the limit as 100, whatever you’re using. So the date of the invoice is here. This is currently May the 10th. The payment due date here will be 15 days beyond the invoice date if the total price is less than 100, and if it’s 100 or more, then the client has 30 days to pay. So you’re going to finish up with a formula in here. Note this formula is outside the table. We’re not talking about a table formula here but we are looking at a formula which is going to be conditional on the value in another field. So that’s it. My answer to that exercise is example-12. I’ll see you in the next section.

Excel 2013 - Advanced Details. Now I can refer to just about anything in this table from outside the table using a structured way of referencing. What I’m going to do here, first of all, is refer to that cell that is the total price, sum right at the bottom of the total price column I’m going to put that in this formula rather than H12. So rather than say H12 I want to be able to say the sum of the total price column. So first of all, let’s just delete H12 and let’s click within the cell and you will see straight away that Excel pretty does pretty much all the work for us. So it says Inside the If statement Order Details. Now that’s the name of the table and if you’re referencing something in a table from outside the table, you need to have the table name. If you’re inside the table, then you don’t need to put that table name if you’re referring to something in the same table. Obviously, if you’re looking at something in a different table, then you would need to put the table name. But in this case, we’re looking at a cell outside. We’re looking at the formula in the cell H3 and therefore because H3 isn’t in the table, we need to put the table name in, Order Details. That’s then followed by a pair of square brackets. Inside the square brackets we put what are called qualifiers. Now you can have one or more qualifiers and what Excel does is to combine those qualifiers to work out what you actually want. Now although it’s put here #totals in square brackets as one qualifier and total price in square brackets as another qualifier. Let’s deal with total price first. When you refer to total price in square brackets in a table from outside you’re really referring to the total price column. When you in addition put in the qualifier of #totals, you’re really saying, I want the thing that’s in the total price column but that’s in the #totals row. And that is H12 at the moment. Now obviously if I inserted more rows into this Order Details table or in another invoice using the same approach, then that total might be in a different position in column H. But the important thing here about using the table references it doesn’t really matter how many items there are in the Order Details table because it will cope with that because you’re referring to that total using a reference within the table. You’re not even giving a column reference there. You’re not really saying column H. You’re saying whichever column is the total price column. So if I later change this table to introduce a new column or take a column out, as long as I still had a total price column that formula would still work.

Video: Table Styles Toby: Hello again and welcome back to our course on Excel 2013 Advanced. In this section I’m going to take a quick look at table styles. Even if you’ve used an earlier version of Excel and one that included tables, you’ll find that the table formatting in Excel 2013 is now really pretty sophisticated. Some would say that it’s so complex now that it probably warrants a long section, of course, all on its own. But I’m just going to take you through the basics now and then I’ll leave you to experiment with these. So, first of all, on the Table Tools Design tab, if you click on the Quick Styles button, there’s a drop down there which gives you access to the gallery. The gallery of styles that are available is divided into light, medium, and dark. And provided you’ve got live preview enabled, then you can preview how any particular one of these styles is going to look. Generally speaking, the styles support things like banded rows and banded columns. But some of the styles don’t. So for instance, if you go back to this very basic style here, the None, which is basically removing all styles, then whether you’ve banded rows enabled or not doesn’t make any difference because this doesn’t support banded rows. But generally speaking, most of the styles support things like banded rows, first column, last column, banded columns, etc. So let’s go into one of the other styles. Let’s choose say that one and let’s try some of the other settings. First column, all darkened, last column, and then banded columns in general. And as you can see at the first level of customization using the checkboxes in table style options gives you a reasonable level of control over a table style. Now as I said I’ll leave you to experiment with these basic styles yourself and with the use of the options in table style options, but let’s look at customizing a style. So again back into Styles. Let’s choose one of these others here. Let’s try choosing that one. If I right click on that one of the options I have is duplicate it. So I’ve now made a duplicate of that style and I can customize that to my requirements. I’m going to give it a name. I’m going to call it Toby Order Style just to remind me what it is. And now I can go through and decide which features I want enabled. Now within this Modify Table Style dialog, I can select any one of the table elements and then I can format that element.

Excel 2013 - Advanced Let’s look at a couple of examples of the kind of formatting that I can do. I’ve got here, first of all, first column stripe. Now what I can do at the moment is to change the stripe size. At the moment, the stripe size is one which means in this case one column. I could make a stripe size two columns wide. So the stripes are two columns wide. If I looked at say the last column, let’s go into the last column and let me click on Format here. That lets me format the last column. So for instance, I could put a different fill color on the last column. So let’s choose something that’s very different from the others. It’ll look rather strange but let’s just try that. Click on OK. So I’ve changed the color for the last column. I’ve made a double column stripe. Click on OK and that is my table style. Now all I need to do is to apply it to this table; so click on that and it’s applied to this table. Rather strange looking but there you are. You can certainly see the double striping and you can also see the difference in the color in the last column. And you’re not actually constrained to modifying an existing style. You can create a style completely from scratch. If you click on that Quick Styles drop down again and then New table style near the bottom, there under the gallery gives you a New Table Style dialog. You can choose a name for your new style and then you are effectively starting from a blank canvas. The preview on the right just shows you a plain white table with a little bit of text in each of the cells and then you can choose each element, striping and so on, last column. You can go in and format to choose the fill color, border settings, font, and so on. And you can build up as customized a table style as you like and you’re not constrained to starting with one of the existing styles. So that’s a good thing to experiment with as well. Now while we’re looking at styles I just want to point out one other thing to you which sometimes people find useful. Not everybody likes using tables but some of the features of tables they do like, and particularly this ability to build up styles people like. Now what you can do if you wanted a layout like this one, I can’t imagine that you would but let’s suppose that you did, is to use a table and the use of table styles to create the particular layout that you want. And then when you’re in the table one of the options we haven’t looked at over here in the Tools Group is Convert to range. What convert to range will do is convert a table into a normal range of cells. So you don’t lose any data but the table or all of the cells in the table are no longer part of a table. They’re just within a regular range. So if I click on convert to range here, I get a confirmation dialog question, say yes. I don’t lose any formatting at all but this is no longer a © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced table. I’ve got all the style that I want. I’ve got this banding, first row, last, row, etc. that strange color in the right hand column, but I’ve no longer got a table. So if you want to build up a neat or in some cases not so neat style using one of the table styles but you don’t actually want to use a table, that’s a pretty good way of doing it. One thing to notice there, I’ve clicked in cell D7, is that having converted a table to a range none of the table references are actually usable so what Excel 2013 does for you is to convert those references back to more regular styles of reference, including references to worksheet and then cell, in some cases with absolute cell references and in some cases with relative cell references. So that’s it on table styles. In the next and final very short section on tables I want to have a quick look at selecting in a table so please join me for that.

Excel 2013 - Advanced see the black arrow? If I click once it selects that row and that’s all I have to do to select a row. Let’s try that with a column now. So hover over the top edge of the column, click once. What I’ve actually got selected now is the data within the column. So as you can see the header and the total are not selected. But if I click again, I include those in my selection. Click again and I’m back to just the data again; so it toggles between those two. And perhaps cleverest of all, if I go to the left hand corner, this is a little bit tricky this one. If I go to the left hand corner, I get a diagonal black arrow, click once. I’ve just got the data part of the table selected. Click again. I’ve got the whole table. Click again. I’m back to just the data part. So that’s a really neat way of doing selections just using the mouse. So that’s it for doing selections within a table and, in fact, that’s all the material we’re going to cover on tables in this course. I’ll see you in the next section.

Excel 2013 - Advanced this sheet. So taking that row, for example, we’ve got a date for the transaction. We’ve got a branch. It’s the Denver, Colorado branch. We’ve got a value, \$1. And the department in which the sale was made. It’s breads. Now if I go through all of this data and as I say there are many thousands of lines of data, you’ll see that I’m dealing with a few branches over a period of time, not a very long period of time. But there are hundreds of sales each day and they’re all categorized in exactly the same way. So first of all, what we’re dealing with is rows in a worksheet or some worksheets which each represent some kind of transaction. It’s not necessarily a sale but just something that happens; some event, something that was measured. Now one of the problems you always get with very large amounts of data is finding some meaningful ways of analyzing that data or presenting that data. So for example, given this straightforward convenient store sales data how might we want to analyze it? Supposing we wanted to show, for instance, how the sales in the stores that we’ve got varied over a period of time. Maybe we would plot the total sales for each store for each month in the time period where we’ve made the measurements or maybe for each week. Or would we split those sales by department and see if maybe one department in one store has a much better growth of sales over a period of time than another? There are various options for plotting what we’ve measured against other things that we’ve measured. So are we looking to see how the stores compare with each other? Are we looking to see how the departments compare with each other? Are we looking to see how things vary over time? Now, in order to do that, we want to be able to look at various ways of plotting different properties of this data against other properties of the data and we’ll want to be able to switch what we’re plotting round. We’ll want to be able to, if you think in terms of X and Y axes in a chart, we’ll want to be able to sometimes put something on the X axis, maybe sometimes put it on the Y axis, to plot something different against something else. Now the name pivot table arises because you have the ability to pivot properties of the data, individual attributes of the measured data, and to pivot them from one axis in a plot to another axis in a plot. And that’s originally how the name pivot table came into force for this kind of analysis and presentation of data.

Excel 2013 - Advanced across there. So you can see even from that the amazing power and flexibility of pivot tables in Excel 2013. So that’s the basic principle of pivot tables. In the next section we’re going to look at this in much more detail and we’re also going to look at some more of the new features. I’ll see you then.

Excel 2013 - Advanced table from one of those connections you’ve setup earlier on.

For example, it’s very

straightforward to create a pivot table using a connection to an Access data source. For the moment, let’s stick with this Store Sales table. Then I have options of either put it on a new worksheet or use an existing worksheet. Now I’m going to put this on a new worksheet. Sometimes there are advantages in putting pivot tables, charts, etc on the same worksheet but once you’ve got the pivot table itself, I find that having the raw data right in front of you can be a little bit distracting and you actually get more space to work with if you put it on a new worksheet anyway. So I’m going to stick with the new worksheet option, click on OK, and we have basically the bare bones of a pivot table. Now there’s a couple of things to notice here straightaway. One of them is you may want to rename the sheet here with the pivot table on it, which I’m going to do right now. Also note that what you can see here marked as pivot table 3 is actually a placeholder for a pivot table that hasn’t been built yet and it won’t be built until we select one or two of the fields here from the pivot table fields panel on the right. Incidentally although that panel seems to be firmly fixed on the right there, you can actually move the panel around. You can grab it by its heading and move it around and sometimes when you’re dealing with a pivot table and maybe it needs to be pretty big, you might find it convenient to just pull that away from its right hand side location there and move it to a more convenient location. To pull it back again, push it over to the right and then it snaps back into place.w Now as you’ll have realized from the previous section we actually build the pivot table by selecting fields from our data and moving them into these drop zones at the bottom of the pivot table fields panel on the right. Some things happen by default. If for example, you select a date type field it will automatically be added to the rows drop zone. So let me just select date there. It automatically goes into that zone. Similarly if I check a numeric field like value here that’s automatically added to the values drop zone and becomes a sum of values. The assumption being that in doing this pivot table analysis you’re looking at totals from a number of transactions. And as you can see with the selection I’ve made there, basically my pivot table has got a first column which shows dates, so individual dates starting 24th April 2012 and going forward from there. And then the second column is the sum of values, i.e., the sum of sales for each of those days summed up over all of the branches and all of the departments. And how I © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced would further build my analysis of the store sales is to introduce branches and departments. So let’s see how that works with a specific example. Let’s suppose I want to include branch in my pivot table. If I select branch, which is basically a text field, what will happen is branch automatically gets added to the rows drop zone. Note because of the sequence there, I’ve got date first and then branch. So on the 24th April 2012 broken down by the four branches for which I have sales on that date, they are the sums of values of the sales; similarly 25th of April, 26th of April, and so on. To change the order of the data fields in the rows column all I need to do is to grab one, branch in this case, and drag it up. Watch what happens to the analysis now. Now my analysis begins with branch and then I have all the days for the Boston branch and then right down to the end of the period in question then I start the Chicago branch. So the sequence of data fields in the rows drop zone determines the sequence in which those data fields appear in the pivot table on the left. Pivot tables very often involve reporting, including dates. And particularly as we’re often dealing with straightforward transactional data, dates are very often part of that data and it’s usually the case, particularly if you’ve got data over a long period of time that you wouldn’t want to present the data by individual days like this. You’d almost certainly want to group the dates by weeks or months or quarters or years or whatever. So let’s now look at grouping this date data. On the pivot tables Analyze tab there is a Group button and if I click on the drop down on the Group button, one of the options is Group Field. That shows me the start date for this date field, April 24th and the end date, August 19, 2012, and it allows me to specify grouping. Now at the moment, by default it’s suggesting grouping by months but in fact I can group by multiple criteria. I’m not restricted to grouping my month. So let’s suppose I wanted to group by months and quarters. I’ve got them both selected, click on OK, watch what happens. Now what I get is a sort of two level grouping. I’ve got the quarters grouping divided into months and then I can use these Expand and Collapse buttons here to hide the details if I want to. Now so far this pivot table is still pretty straightforward and hopefully you find it very easy to understand what’s going on. Let’s now make a couple of further changes to it. One of the things I’m going to do is to move branch over to columns. And what I’ve got now is a crosstab pivot © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced table where I’ve got dates for the rows, bear in mind I’ve got quarters and dates as two levels of field in the rows, and then the branches are the columns. And of course, within the date rows I’ve got the expand facility here where I can expand a particular quarter to show the figures for the individual months. Now at any point I can introduce additional data fields. So, for instance, let’s suppose I now want to put department in. As soon as I check department by default, department because it’s a text field will be added as a row in the table. So I’ve got departments here and departments are below date. So if I expand quarters I’ll actually find below the dates that I’ve got a breakdown for each month. Bear in mind, I’m grouped by quarters and months. I’ve got a breakdown by departments. And I’m sure you can work this out by now. If I wanted to promote department to the top of the rows drop zone, I’ve now got an arrangement whereby I’ve got individual departments and then within each department I’ve got the relevant quarters in which we’ve recorded sales in that department. And similarly, if I wanted to put departments over as a column and branches back as a row and so on. It’s extremely straightforward to change all of those things just by dragging and dropping within the drop zones. Now for the rest of this section I’d like to look at formatting what’s in a pivot table. Formatting is very important because we very often use pivot tables as presentational tools so the presentation is all important. In the body of this pivot table, of course the numbers we’ve got are basically currency amounts. If I click on one of them, note the screen tip that I get tells me, Sum of value. Value 1192.25, row Boston, May. So it’s telling me which row I’m in. Note that I’ve removed the quarters for simplicity, just to illustrate what we’re going to do next. And then the column I’m on is flatbread. Sum of value is the actual field value that I’m looking at here so that should be a currency amount. If you click on Analyze in the pivot table tools, in the active field group one of the options is field settings and these field settings apply to the field that’s currently selected. So that will be sum of values. Click on field settings and we get a Value Field Settings dialog. Now I could, at this point, customize the name if I wanted to. I could also change what I’m doing with that sum. At the moment it’s a sum. I could change it to a count or an average or a max or a min or © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced whatever. Now if I click on number format, I can actually do some number formatting, what I really want this to be is a currency field and I’m going to go for two decimal places. So I want these sum of values to be currency fields with two decimal places. Click on OK, click on OK, and now look at what an improvement that makes to my pivot table. Just a word of warning there; don’t be tempted to say select some cells here and do format cells in the way that you would conventionally on a worksheet. The way that we’ve just defined that currency format for sum of values using field settings for the active field, make sure that as the values related to this pivot table change, so if we introduce more departments, new branches, change the dates that we have data for, and so on, then that definition will be used throughout. No matter how we change the source data, then the sum of values there will always be formatted in that currency way. If you select a specific range of cells and do the formatting then if the size and shape of the pivot tables changes, then the formatting you’ve done will no longer correspond to the size and shape of the new pivot table. So make sure you do it as a definition for field settings for the active field in the way that I did just now. One other aspect of the presentational side of a pivot table, the form we’re looking at now is called a compact form. If you look at the Boston figures, for example, the total for Boston here, for each of the departments, let’s say coffee, is shown at the top. So 562.15 is the total. There are only sales there in May; similarly down for Chicago, etc. Now it’s not always best to have totals at the top. Some situations the totals at the top works, some it doesn’t. But there are a number of other ways of presenting a table and if you go to the Design tab and click on Report Layout, you can choose from a number of quite useful options. One of them, for example, is Show in tabular form. Now if you select show in tabular form, you get those totals at the bottom, so you get Boston and then a Boston total row; Chicago and then a Chicago total row. And then look again at the Boston figures here. We’ve got Boston, April, May, June, July, August, etc. If you wanted to have Boston in each of these cells here, which might be particularly useful if you’ve got many months, say, you’ve got many categories, many values, and things move out of view when you can’t see headings and so on, one of the other options on the Report Layout button here is to for Repeat all item labels. What happens in a situation like this is in this case the branch name get repeated. That can be very useful as I say if things would otherwise move out of view. © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced Now there are other settings there on the Report Layout button that I think it’s good for you to experiment with but that’s it for now on the presentational side of pivot tables. In the next section we’re going to look at filtering in pivot tables and in particular we’re going to look at slices, so please join me for that.

Video: Filtering in Pivot Tables; Slices Toby: Welcome back to our course on Excel 2013 Advanced. In the previous couple of sections we’ve been looking at pivot tables in Excel 2013 and in this section we’re going to continue looking at pivot tables. We’re going to concentrate in this section on filtering a pivot table and on the use of slices. Before we do that I’d like to do a couple of things that I mentioned earlier on and one of them is I’d like to change the range of data that’s actually covered by the pivot table we’ve been working on. So this is very straightforward to do, particularly as we set this pivot table up on the basis of using our store sales table. The store sales table we setup with the first 20,000 rows of the data that I had. The data I have extends to 34,109 rows. Let’s go back to the pivot table. This is the bottom right hand corner and the total value of the cells there, \$399,000; so we’ll call it \$400,000. Let’s go back into the store data here, click somewhere within the table, and then the Table Tools Design tab, go to resize table, and there’s the old definition of a range going up to 20,000. Let’s make it 34,109, click on OK. Now let’s go back to the pivot table. Notice that we still have a value here of 399,783.01. So the update is not automatic. Now this is pretty straightforward. If you went to the Analyze tab, you’ve got a couple of options there in the Data Group. One of the options is to change the data source altogether. So if in fact you were going to pick up a new set of store data, perhaps the latest figures from another worksheet or even another workbook, you could do that here. But if all you want to do is to refresh the data source you’ve already got setup and just bear in mind if I click on change data source don’t forget the data source here is the table Store Sales which I have just changed the definition of. So my pivot table is already defined in terms of a table, a named table. If I go back, then into analyze and just click on refresh it does the refresh. And then let me just go along to that total and you’ll see the total now is \$804,000. So I’ve almost doubled not only the number of transactions but the total value as well. Now I just want to adjust what we see a little bit more. So on the Analyze tab in the pivot table tools, Show on the right gives me a field list. If I click on that, I can hide the pivot table fields panel. I also use that drop down there to bring it back again. I’m also going to use the Design tab

Excel 2013 - Advanced next week or last year or next quarter, and so on. These are very often the sorts of date filter that can help when you’re doing analysis of transactional data. So having looked at a couple of ways of filtering, I want to look at one other way before we look at slices. Let’s look at the date filter again and what I’m going to do this time is to select one of the dates there and then on the Analyze tab go back into Group and I’m going to again say group field. Instead of just months, I’m going to specify that I want quarters as well. Now, of course, what happens here, as you would expect, is that I finish up with rows that say branch, quarters, and date. Now what I’m going to do is to drag quarters up into the filters drop zone. Watch what happens to the pivot table itself. The quarters column that I just introduced has gone again because now quarters is being used a filter on the whole table. If I look up here, A1 is quarters, B1 currently all. If I click on the drop down, I can see that I’ve got a filter I can use on quarters. So if all I want to do is to see say the quarter two sales figures, select quarter two, click on OK, and now my pivot table only includes the Q2 figures which are basically April, May, and June. So I can setup one or more of my data fields in the filters drop zone and use that as another way of filtering my pivot table. Maybe I should just mention one other thing very quickly and that is that sometimes if a particular one of your data fields has a lot of values, so in this case we may have many, many departments; for example, there is a search facility. So if I wanted to say filter on anything with the word Tea in it, for example, if I click on that filter, there’s a search box there and I could type in Tea and I would find that there are actually two departments which have Tea in them; hot tea’s and tea. Click on OK and I filtered on all of the departments with the word Tea in them. So finally in this section let’s take a look at slices which are a really good way of filtering a pivot table partly because they’re very flexible but partly because in terms of doing ad hoc analysis you can move them around on the screen and you can see several filters in force at once and build up really quite a sophisticated combination of filters and see very easily what you’re doing. One of the disadvantages of the methods we’ve used so far is that even when you’ve got a filter in force on one or more of these little drop downs here, you can’t generally see in the condition that this pivot table is in what those filters are set at. Now if you’re going to use slices, it’s a good idea to have a bit of space around your pivot table, on the left and above it as well.

Excel 2013 - Advanced So to use slices the first thing you need to do is to make sure you’ve got a cell selected inside the pivot table, then on the Analyze tab click insert slicer. Now you’re given the option here of five slices because we’ve got five data fields. Well, we’ve got four data fields and the additional quarters that I built up by grouping dates. Let’s suppose that we’re going to have slicer for branch and a slicer for department at the moment. So let’s just choose those two, click on OK, and you see a department slicer and a branch slicer appear. Now the first thing to note about these is that you can move them around on the screen. So let me grab the department slicer and just move it say over to there so we’re a little bit out of the way. And then I can grab the branch slicer and maybe put that over here on the left somewhere. Each of them is sizable. So if you want to make one a little bit narrower or a bit longer then you can do all the normal things that you would expect to be able to do on a floating panel on a window. Now when you’ve got a slicer selected, you actually have a Slicer Tools Options tab on the Ribbon as well and I’m not going to dwell on this too much but I want to show you one or two of the basic things you can do. You can certainly change the size either by dragging or by using the controls here. But you can also change the number of columns in a slicer. So if we consider the department data field here where we’ve got quite a few values, I could actually change this slicer to have more than one column. So let’s suppose I wanted to be able to see all of that a bit better. I could change it to being a five column slicer and I could change its shape so that I can see more of the pivot table. Now obviously exactly what you do there will depend not only on the resolution of your screen and on what particular data fields you’ve got, but what particular analysis you want to do at any time. Now the basic use of the slicers is very straightforward. If you just want to select values of one of your variables, one of your data fields, then all you do is click it. So if all I want to see is juices, I click juices and juices is all that’s shown in my pivot table. To select more than one value for this data field, hold the Control key down and select additional values. And then when I release the Control key, I can see all of the values that I’ve selected.

Excel 2013 - Advanced It’s straightforward to remove the filter that you’ve applied with the slicer at any time just by clicking on the Clear filter button; top right hand corner of the slicer. You’re back to a full selection again. Now it’s worth experimenting with some of the options under the slicer tools here, including the slicer styles where you’ve got a good gallery of alternative slicer styles and you can even create your own if you want to.

That can be particularly useful if you’re doing some kind of

presentation and you, say, want to match the pivot table and the slicers to some sort of corporate style, for example. Very briefly, just one other point on slicers; if you right click on the header of a slicer, you have a Slicer Settings dialog which you can use to determine whether you display a header, whether you’re applying a sort order, and so on. So slicers are even more adaptable than they appear to be at first really. One very final thing to briefly demonstrate here; with a selection inside the pivot table again, on the Analyze tab there is an insert timeline option. This inserts what you might think of as a special sort of slicer. It’s used only for fields of date type and if you select the only date field we’ve got here which is date, click on OK, you get a little date control here that you can use a bit like a slicer. But you can use it, for instance, to choose periods of time that you want to be covered within your report. So you can click on them, you can stretch this out, and so on. I’m going to leave you to experiment with filtering with a timeline. So that’s it on pivot tables, filters, etc. A little bit later on in the course we’re going to come back and draw a pivot chart but for now we’ve finished on pivot tables. I’ll see you in the next section.

Chapter 12 – Data Analysis Video: What if Toby: Hello again and welcome back to our course on Excel 2013 Advanced. In this section we’re going to start to look at data analysis and we’re going to start by looking at what if. So let’s start by looking at one of the little demonstration worksheets that we used earlier on in the course. In this one we use the PMT function to find the payments on a loan over a specified number of periods. So in this case, at a 5% annual interest rate over five years, 60 months, with a loan of \$5,000 we find that the payment per month is \$93.96. Now one of the things you might want to do is to say okay but supposing I want to compare that with say paying over a longer or shorter period of time. Now one thing you could do, if I delete column D there I could make a copy of this part of the model. So I can just copy that and I could paste a copy into here. Get exactly the same calculation but I could change it into instead of 60, I could maybe say well what about if I paid off over a period of ten years instead of five? So I make that 120 months. My monthly payment becomes \$52.81. Now what we mean by what if analysis is how we can do that. We can say what if I change the payment period from five years to ten years, 60 months to 120 months? So clearly, one option for me is to carry on with that. I can copy and paste, do different numbers of months, maybe try 48 months, 240 months, or whatever. But there is a more efficient and smarter way of doing this and that’s using what if analysis in Excel 2013. And in order to do that I’m going to use exactly the same formula but I’m going to setup this analysis in a different way. Just let’s remind ourselves about what this formula is. It’s the PMT. It’s, first of all, the interest rate which is in this case we’ve set it at 5%. That’s the annual interest divided by 12 because we’re dealing with monthly payments. Then we have D3 in this case which is the number of months. D4 is the present value which is basically the loan amount. D5, the future value, the amount we want left at the end which is zero. And then D6, the type of payment, whether we make the payment at the start of the period or the end of the period. Now I’m going to basically say that D5, the future value, is going to be zero and that D6 is going to continue to be one as it is now. And I’m going to setup what if analysis using a data table in Excel 2013. © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced So the first thing that I’m going to do in this case is to setup the framework for my what if analysis. I’m treating two values as fixed in all of this. So the amount of the loan is going to be \$5,000, the interest rate is 5% as before, and then what I’ve done here is to put down a sequence of numbers of months starting at 36 months, that’s three years of payments and going down to 120 months, that’s ten years of payments. Exactly how you arrange this in terms of whether you have, for instance, the number of months in successive rows or do those in columns, exactly where you put these doesn’t really matter. But it is very important that in this case the numbers of months we’ve got here are arranged next to each other in ascending order like this. Now let’s see how we setup the analysis itself. Now effectively what’s going to happen is that we’re going to put the payment for each of these numbers of months in the cell to the right of the number of months. Clearly, I could just enter the payment function in there and fill it down. But we’re going to use the what if and data table approach which is going to seem a little bit strange at first but as you’ll see in a little while it offers a lot of power once you get used to it. So what I do is I type in this cell, this is the cell above the ones where the answers are going to appear and I’m going to put the formula in there. So it’s Equals PMT. Now the interest rate that I want is in cell C3. I could put this in as an absolute reference but in fact when it comes to using data tables, you don’t actually need to. Of course, C3 is the annual interest rate so I need to divide it by 12. Next comes the number of payments. Now on this particular row where I’m entering the formula, there is no number of months so what I do is I put in a dummy field. Now I’m going to use A4 which may seem like quite a strange and bizarre thing to do but I hope that will make a little bit of sense in a couple of minutes from now. I must choose a field here which is effectively a dummy field outside the table, the data table that I’m going to build. Again, that’ll mean more in just a moment. Now I put in the principle of the loan which is C2. Again, it looks as though I should put in an absolute reference there but as you’ll see it doesn’t really matter. Comma, then I want my future value which I’m going to leave at zero so I can just put a comma in there, and then payments at the beginning of the period will be one. Now when I click on tick, you won’t be surprised to see that of course it gives me an error because there is no value in A4. So with no number of periods it just doesn’t like that formula at all. But as we’ll see that doesn’t matter. © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced You can tell by those squiggly brackets that we saw before. So it’s not a regular every day formula. It’s a rather special case. But let’s go back to the full selection of the data table again. Sometimes you’ll want to remove a data table. That’s pretty straightforward. If you just go to the Home tab, in the Editing Group, and click on this Clear button. Just use Clear all to get rid of the whole thing. So I’m still using that payment formula and I’m going to, this time, vary both the number of payments and the interest rate. And I’m going to vary the interest rate along a row here and the number of payments down this column. So I’m going to put the payment formula in here. Now the payment formula instead of using sort of random fields outside the table, I’m actually going to put the payment formula in referring to these three specific fields that I’ve got up here as my sort of standard or starting values if you like. So there’s my starting formula referring to those three values. Now what I’m going to do is put along here those interest rates. So I’m going to start with a low of 3% and then work along there, 4%, etc. So that’s one of my variables in place. Now I’m going to put the number of months down here. I’m going to do from one to ten years. So we’ll start with 12 months and work from there. So there we are. I’m back to having now the formula selected. Note that the formula is at the intersection of the row with one variable in it and the column with the other variable in it. So now I select everything from my data table, that includes the top row in the left hand column, and then on the Data tab again, what if analysis, data table. The difference this time of course is that I have both a row input cell and a column input cell. Now the row input cell will be the one that corresponds to the percentage, the interest rate percentage on the loan, which in the formula is C3. Note there in the formula C3, part of the first argument. So C3 goes in there. Now for the column input cell which cell is used in the formula for the column variable which is the number of payments? Well, the cell that’s used in the formula definition to correspond to the number of payments is C4. It’s used there as the second argument when we call the PMT function. So C4 is the one that goes in there. Now click on OK and I have all of the values for my repayments. Let me just select those, right click, and format cells. I want all of those to be

Excel 2013 - Advanced currency, red, okay. Click away and there we have all of my payments for every percentage in single unit percentages from three to ten over every period from one year to ten years. So now I’m going to set you another exercise to do. This one is a little bit tricky but I’m sure you’ll be able to cope with it okay. You’re going to need one of the financial functions we haven’t looked at so far. The C-U-M-I-P-M-T function, CUMIPMT. It’s the cumulative interest payment on a loan. Now if you look in Excel Help, it tells you what the arguments are. The first three are exactly the same as PMT but then you have a start period number and an end period number. The cumulative interest payment tells you the total amount of interest paid between the start period payment and the end period payment. So if I were actually looking at the total amount of interest paid from the start of a ten year loan to the end, my start period would be one and my end period would be 120 for monthly payments. Now in our latest what if scenario here we have the monthly payment for various interest rates, for various numbers of months. What I want you to show in the body here is not the monthly payment but the total amount repaid on the loan.

So you’re going to repay the \$5,000,

obviously, but to that you need to add the total interest paid, CUMIPMT. And although in many ways the formula for CUMIPMT using the relevant function is going to be the same as this, bear in mind that the end period is going to be different depending on which row you’re looking at here. So you’re going to need CUMIPMT in there instead of PMT. You need to think about the arguments in each case carefully. I’m not going to give you the worksheet you’re looking at now. You need to rebuild that yourself. But I want you to use the same figures, \$5,000, 5% as your sort of notional interest rate, 60 as your sort of notional number of payments, build it up in the same way that we did just now but instead of working out what the monthly payments are work out what the total amount repaid on the loan is at the end of whatever the period is. My answer to that is example-13 in the supplied files. That’s it for this section on data analysis. In the next section we’re going to look at the scenario manager.

Excel 2013 - Advanced click on define name. I’m going to call it, well it’s called there Loan_Amount. That will be fine. And then I’m also going to give payment type a name as well.

Click on define name,

Excel 2013 - Advanced these to have an effect. The first one, prevent changes, means that other people cannot change this particular scenario. The second one, hide, means that people can’t actually see that scenario because it’s hidden within the book. So let’s click on OK and we will have created our first scenario. So there we are. The payment type value is one. The loan amount is \$5,000. Click on that and we’re okay. Now, in scenario manager, we see listed our first scenario and with a scenario selected at any time, you can just click on Show and you will see within the sheet the values for that scenario. So notice that over 120 months, 10% interest, the monthly payment is \$65.53. So what I want to do now is to add a second scenario for comparison and I’m going to try a scenario where I still borrow \$5,000 but I’m going to pay at the end of the month instead of the start of the month. Now if I pay at the end of the month, I’m going to have a little bit more interest to pay each month so I would expect that payment that’s currently 65.53 to up a little bit because of that. So within scenario manager, click on add and I get a scenario name. I’m going to give it the same name to begin with. So we’ve still got a loan of \$5,000 but I’m going to say pay at month end. So that’s the name of my scenario. Obviously, the changing cells are the same. Everything else is the same. Click on OK. Now the values I’m going to enter, I’m going to change the payment type to zero, leave the loan amount at \$5,000, and click on OK and we’ll see what happens. I’ve now got my second scenario. And pay at month end, if I click on Show for that, you will see that the payment type becomes zero in cell C5 and my monthly payment in the one case that I asked you to look at just now has gone up from 65.53 to 66.08, so that’s 55 cents extra per month if I pay at the end of the month. Now I want to tell you a couple of other things about the scenario management capabilities of Excel 2013. I’m not going to go into these in detail. We could spend an awful lot of time on these. But let’s start with the fact that quite often when you’re dealing with much more complex situations than this one, you may be in a situation where different people or you at different times work on different aspects of some kind of, for example, financial model. So you may be, for instance, separately working out how much money you actually need to borrow perhaps to buy a

Excel 2013 - Advanced car or a house or something like that. Or at work you may be looking at some kind of merger question or the amount of capital you need to raise for a new initiative. You can get to situations where different people are working on different scenarios or maybe even sometimes on the same scenarios but they’re looking at different numbers, different aspects of those; so one person in this situation may be looking at varying that figure and another person looking at that figure and so on. Well, with the scenario manager you can actually do things like merge scenarios together. Each sheet in an Excel workbook can have scenarios and you can merge scenarios not only from different sheets in the same workbook but from other workbooks as well. So if you let somebody have a copy of this workbook with the scenario that you’ve started, the ball rolling with and they did some work, maybe produced two or three other scenarios, you could merge their scenarios back into the relevant sheet in your workbook. And in that way you could look at putting several people’s work together. Now as I say we’re not going to go through that here but I think it’s very important to understand that very often in scenario management, things are much more complicated then we’ve seen here and the ability to look at other people’s work, merge scenarios, and so on is very important. There’s one other thing to point out here. When I set this example up, I really wanted to illustrate how you can mix what if analysis into scenario management and come up with a combination. Very often in scenario management, you’re looking for just one or two numbers at the end, maybe trying to work out the best way to achieve a particular result, to get the lowest payments or the most interest or the most profit or something like that. You’re very often interested in just maximizing or minimizing something. Now when you’ve got a certain number of scenarios put together and you want to compare them, you may well want to produce a summary report. And what I’m going to do here from the scenario manager is click on summary and what it says is Do you want a scenario summary or would you like a scenario pivot table report? You can actually produce a pivot table as output and use that obviously for pivot table type analysis. Now at the moment, I just want a summary and I can choose one or more cells. There is a limit; but one or more cells and basically, say, I want to actually compare the values in L17, selected cell, I could choose more than one cell if I wanted to within my various scenarios. So L17 is that payment that I looked at before, the monthly payment for the ten year option where there’s a 10% interest rate. Click on OK and © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced what Excel 2013 does is to produce a little scenario summary report. It’s made as a separate worksheet within the workbook and as you can see it’s listed the changing cells. It’s listed the just two scenarios I’ve got. Obviously, I could have more than two and it’s basically saying when the loan amount is \$5,000 and the payment type is one the value in L17, the monthly payment is 65.53. With a payment type of zero, so that’s payment at the end of the month, same loan amount, the payment is 66.08. So I basically get a summary report. Here I’ve just looked at one output value, just the monthly payment in one particular combination of the loan amount and payment type variables. But obviously I could choose more than that. I could’ve chosen a different value and so on. But that’s the summary report generated by scenario manager. Before we move on there’s just one little thing to point out there. Note how useful it is to have used those defined names for payment type and loan amount rather than using cell references. Excel 2013 would have worked absolutely fine with cell references but when it comes to looking at things like this summary report, having those names in there really helps. Similarly, I could have defined a name here, say, Monthly payment or something like that, although this was a very specific monthly payment in this case. So you can see here why the use of defined names really makes the use of scenarios and scenario manager much easier. So that’s it on scenario manager. In the next section on data analysis we’re going to look at goal seek, so please join me for that.

Excel 2013 - Advanced be example-15. You cannot with goal seek set it up to work in an array way. So you can’t automatically get all of the answers from 12 months up to 120 months. When you’ve got the first answer, hopefully the same as the first answer that I’ve got you can stop at that point. But if you want a little bit more practice, you could go through and work out all of these other values and see what impact the term of the loan has in terms of the increase in the amount you can borrow over a period of time. My answer to that as I is example-15. If all you do is get the first one working, I’m sure you could get the other ones working as well. But make sure you get the first one right, same number as mine, currency unit use your local currency unit. That’s it for goal seek. In the next section we’re going to look at solver so please join me for that.

Video: Solver Toby: Hello again and welcome back to our course on Excel 2013 Advanced. In this section we’re going to look at one of the most powerful data analysis tools in Excel 2013 and that is solver. Whereas goal seek can solve a pretty straightforward problem by aversion of brute force, often involving many iterations to identify a value of a variable which will achieve a certain value in another variable.

With solver although essentially you’re trying to achieve one particular

objective, you can do that by varying many variables and subject to various constraints. Now in order to show you how to use solver, I’m going to use a very specific case study, but this case study will effectively demonstrate all of the key points that you need to bear in mind when you’re using solver and it should enable you to solve some pretty complex problems with solver. For those of you with a background in mathematics, engineering, statistics, and so on, effectively solver is a sort of linear programming tool but it’s a little bit more than that in that it can also do nonlinear programming as well. Now the case study we’re going to look at is a pretty straightforward one to understand. But as you’ll see, it’s not a particularly easy one to solve. My friend manages a bar and on each of the seven days of the week my friend knows pretty much how many bar staff he needs. On a Sunday, he needs ten people on duty to deal with the number of customers that he gets. On a Monday things are quieter, he only needs six. Tuesday he needs eight. Wednesday six. Thursday it starts to build up again and then at the weekend, Friday and Saturday, he needs twelve on each of those nights. Now his problem is this, pretty straightforward. How many staff does he need to employ to achieve that level of staffing in the bar? Now in practice, of course there are always constraints in a situation like this and let me introduce the two main constraints here.

The first constraint is that we’re not to include

overtime. So people are just working their standard working weeks. Secondly, each member of staff works five consecutive days.

So they can’t do say Sunday and Monday and then

Excel 2013 - Advanced Similarly here for Monday people. Rather than just put a one there, what I want there is that that Equals dollar-A, dollar-7. So however many Monday people I’ve got that’s what I get there and so on. So let me fill the rest of that, join me again in a moment. So the next step is to work out for each day of the week how many people that means we’ve got on staff. Well, if I look at cell C14, the total number on duty will be the sum from C6 to C12. So according to my current guess, I’ve got ten staff on duty. Now let me right fill that and get the equivalent sum for all of the other days of the week and I can now put my first constraint into a more mathematical, perhaps a more Excel like language. And I can say my first constraint is that these numbers, the numbers on duty are always greater than or equal to the number required. And my first guess of two types of each person works for five days of the week. It only comes unstuck on Friday and Saturday where I don’t have enough people. So that’s really where Excel is going to come in because I’m going to say to Excel, Work out a better way of doing this so that I get Friday and Saturday covered as well, but with the minimum number of staff. So let’s put this next constraint together. By the minimum of staff what do we mean? Well, at the moment the total number of staff will be the sum of that part of column A. So if I do an AutoSum in there, 14 staff at the moment, that’s the number that I need, and I would like that number to be as small as possible but at the same time each of these numbers must be at least as big as its equivalent number in that row. That’s what we’re going to ask solver to figure out for us. Now there’s one other constraint that I haven’t mentioned so far and some of you may already be thinking that you can see a flaw in all of this, but it’s a very common type of constraint and it’s one you need to be very careful to maintain when it’s needed., and that is that each of these numbers must be a whole number. You can’t have 2-1/2 Thursday staff and 2-1/2 Tuesday staff. You might argue, well you could maybe have part-timers but that’s not what we’re doing here. You could certainly do that with solver.

You would make the model a little bit more

complicated but it would still work. But we’re going to insist that we only have full members of staff. So each member of staff is a whole member of staff and does five consecutive days of work. Now we come to a slightly unusual step in the overall process. On the Data tab, it’s very likely that you won’t be able to see the solver command and what you need to do is to enable the solver © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced Now before I click the Solve button and come up with a solution to this I’d just like to talk about one other thing. There are three available solving methods. The default here is GRG Nonlinear. There are two others. Usually with reasonably straightforward problems, the GRG Nonlinear option will come up with a good solution. If for any reason that doesn’t work, you can try Evolutionary and then in turn, as a last resort maybe, you could try Simplex Linear Programming as a third option. We’re going to stick with that one. With these there are various options. I’m not going to look at those now. I’m really just trying to give you a good case study now of a good example. Let’s click Solve and see what we get. Now when we click Solve, solver tells us it’s found a solution. We’re given the option of keeping that solver solution. We can even save it as a scenario. Or we can restore the original values, go back in, maybe change one or two constraints, and we can return to the solver parameters dialog. Now on this occasion, I’m going to click OK because I think that’s going to turn out to be a good solution, and there we are. Solver has told us that we need one Sunday person, three Tuesday people, four Wednesday people, and so on and as you can see we’ve achieved our minimum staffing level on each day of the week. We’ve got extras. In fact, on a Wednesday we’ve got nine on staff when we only need six. But I would probably bet quite a bit of money that you can’t come up with a smaller number than 14 to satisfy those constraints. Solver’s pretty good at solving these things and although it came up with the same number as my guess, don’t forget my guess didn’t satisfy the constraints. So there we are. That’s the use of solver in data analysis in Excel 2013. In fact, that’s the end of our last section on data analysis in Excel 2013. I’ll see you in the next section.

Excel 2013 - Advanced team. And then obviously I get things like team totals and I get an overall total for the two teams as well. Now the first chart I’m going to demonstrate to you is an area chart. Now in many ways an area chart is really just a slick way of presenting a line chart. But in order to use area charts, particularly if you’re going to use them in presentations to improve your presentations, you’re going to need to know one or two very specific skills associated with using area charts, as I’m going to show you now. So first of all, let me just select the data for Andy Carothers. I’ve hidden the quarterly total columns and the year total column. I’m only interested in the progress over the 12 months of 2012. So for Andy I’ll select the data like that, go to the Insert tab, and I’m going to click on Line Chart, Insert line chart, and it’s just going to be a 2-D line at the moment and I’ve got my chart for Andy. Now I’m really not going to go into any detail about scales, axes, labels, legends, etc. I’m assuming that you’re familiar with doing all of that. So that’s what the line chart looks like for Andy for last year. Now what I’m going to do is to change the chart type and make it into an area chart. So I’m going to select area and what I get is pretty much the same information, but many people believe that having the color filled in underneath gives more of an impression of just how much Andy’s sales have varied over time. Once again, I’m sure you know how to change the fill color. Again, I’m not going to go into that kind of formatting of area charts. But where area charts from a presentational point of view very often come into their own is if you want to compare the performance of several people. Now what I’m going to do is to change the data selection and produce an area chart showing all of the members of Team A. So select data. It’s currently on Sheet 4, the one that I’ve got selected at the moment, and it’s A3 to P4. I’m going to make it A3 to P8. So I’m just going to go in there, delete the four, change it to an eight, click on OK, see what happens. What I now get is that rather baffling area chart there. Now that is not an unusual type of problem to have for a 2-D area chart. But one thing we can do which very often considerably helps is to turn it into a 3-D area chart. So let’s change the chart type. We’re going to stick with an area chart but of the available area charts, we’re going to choose the 3-D area option and go with that first one, click on OK, and we © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced five members of a team of sales people, and you want to compare them, sometimes you can actually get a pretty good message by comparing them, by showing them as parts of a whole. You can do that in an interesting way with an area chart as well. So if I go back into Change chart type, what I’m going to do this time is I’m going to go for that option which is the stacked area chart. Now watch what happens with a stacked area chart. Now what that shows me at any point in time, at any month in the year, is how much of the sales within that team is contributed by each member of the team. Although it’s a little bit of a scary, it looks like a bit of pop art really, the message sometimes when you present it in this way is quite interesting. So if you take a particular month, say, February, you can see that the dark red part here for Andy Carothers is quite small, whereas this sort of orangey kind of color here, the color for Michael DeVoe, is pretty strong. That also will work in 2-D as well. So let me go back to change area type. Let’s go back to stacked area in 2-D and that can also work in 2-D as well. So that’s it on area charts for now. I’ll see you in the next section.

Video: Surface Charts Toby: Hello again and welcome back to our course on Excel 2013 Advanced. In this section we’re going to look at surface charts and these are charts that are probably pretty much under used, although there are a couple of difficulties with surface charts that I’ll talk to you about during this section. But from the point of view of presenting certain types of data, I do think that surface charts can be very useful. Now when we’re plotting a surface chart, the reason we’re doing it is to show the relationship between three variables, and the three variables may be some sort of physical measurement. So for instance, I’ve seen surface charts which have plotted temperature against latitude and season or month. A surface chart with those three variables would give you a visual impression of how temperature varies the farther away you are from the equator and according to the time of year. But you can use a surface chart pretty much for any situation where primarily you’re plotting one dependent variable against two independent variables. Now what I’ve decided to do in this section is to do things in a very different way to usual. Rather than give you some actual physical measurements, I’ve decided that we’re going to use a surface chart to plot a mathematical function. Now I’ve got values of one independent variable, X, up here going from zero to ten. I’ve got values of another independent variable here, Y, going from zero to 100. Now the Y variable goes in steps of ten from zero to 100, and then the Z variable. So the values in the cells are determined by a mathematical function. Now I could have put any mathematical function in there I like but I’ve chosen one which is basically Z, equals, and then it’s ten minus X-cubed, plus Y minus 100-squared. Now note that the X here is C, dollar-2, so that’s C2. And then in the formula for this cell it will be D2, E2, F2, and so on. So I started off with putting the formula in there and then I did a fill right and a fill right down. If I, for instance, look at this cell, the X in that case is still C2 but the Y is B12 and so on. So those Z values are the values of a function and what I’m now going to do is to draw a surface chart. So I need to select the cells which obviously include all of my Z values, but I also need the Y values and the X values as well. So I’m going to make that selection, and then on the Insert tab, many different options for that of course. I can use the little launcher in the corner, go to All © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced Charts, select surface chart, and I’m going to start with this first form. Click on OK and I’ve got my surface chart. Now let me just make it a little bit bigger, a little bit easier to read. The axes aren’t labeled and I’ve actually got a legend here. The legend isn’t really particularly going to help us at the moment but let’s not worry too much about that. But the main thing about a surface chart is this. There will tend to be a direction on a surface chart. It’s a three dimensional chart on a two dimensional surface. You’re shown it on your computer screen and in this particular case the main problem is that the whole thing is pointing in the wrong direction. You can’t really make out any of the detail of the chart because we’re looking at it from behind. Now one way of getting round that is if I go and select this axis which is in effect the Y axis. So that’s the one that goes from zero to 100. If I right click, select format axis, one of the options is Axis options series in reverse order. If I check that and I’ll just close this panel again then I get a much better picture of this three dimensional relationship between X, Y, and Z. In order to achieve the affect that you want, you may need to do that on any of the three axes. So I’ve got the vertical axis here. I might want to go into format axis there so I can scroll down and put the values in reverse order there as well and then that is the affect that I achieve. Any particular situation you’ll want to choose the orientation of the axes that best suits your requirements. Another very important thing here which is a fairly general issue with the use of surface charts and another one of those things you need to be a little bit careful about is that when we’re dealing with numbers generated in this way Excel has used its own logic to divide these Z values into categories. And it chooses an optimum number of categories. In this case, there are six categories and not only has it scaled that Z axis accordingly but it’s assigned a color to each of the categories. Now if you want to change these colors, if you select a color using the legend here, then from that choice you can go in, you can change the color, you can change the fill, and so on. As usual, I’m not going to go through all that now. I assume you can go through and do those things.

Similarly on that Z axis, you could go in there and change the scaling,

categorization, and so on. So you do need to adapt whatever Excel does to suit the message that you’re trying to give. In this case, there really isn’t a reason to categorize these Z values because we’re really just plotting a function. But if I’d say been plotting temperature against latitude and © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced time of year I may want to categorize the temperatures into hot, medium, moderate, cold, freezing, and so on. And maybe use blue for the cold ones and red for the hot ones. So there are lots of possibilities of how to make your surface chart give as much visual impression as possible. Finally on this, let’s just look at the other types of surface chart because there are three other types.

If I go into surface chart again, one of the options is a 3-D Wireframe. The 3-D

Wireframe pretty much shows the same information, but it’s a wireframe. It’s effectively transparent and sometimes that gives a very good impression as well. We also have what are called Contour charts and what contour charts are, are sort of 2-D views of a surface chart. So let’s do this 2-D contour chart here and this is effectively like looking down on the surface chart.

It can be very useful for things like showing temperature

distributions, that kind of thing. And then along with the 2-D contour chart, the final type we’ve got in here is the Wireframe contour; so again, effectively the same thing but in this case with a wireframe. So it’s effectively transparent and you’ve just got the wire framing there. And obviously you can adapt this by selecting the legends at the bottom in terms of changing the color scheme and so on. So that’s it on surface charts for now. Please join me in the next section.

Video: Stock Charts – Part 1 Toby: Welcome back to our course on Excel 2013 Advanced. In this section and the next we’re going to look at stock charts in some detail. These are very powerful, very important types of chart that Excel has been able to do for some time. But in order to fully appreciate how stock charts work, you need to understand a little bit about stock market prices. So in this first section I’m going to explain the basics. If you already know the basics, then you may be able to skip this section, but I hope you don’t because during this section I’ll introduce you to the data we’re going to use in the next section. So first of all, I’m going to get some prices from Yahoo Finance. So I’ve already chosen a stock. If you’re going to work along with this why don’t you choose a different one to me, but you need to know the symbol or you can look for a name. I’m going to use one of the first ones that appear. It’s AllianceBerstein Holding L.P. If I click on Get quotes, I will get quotes for that particular stock from Yahoo Finance. One of the things I perhaps should point out here is that when Excel was much younger, the graphing and charting available in Excel was really very impressive. It still is impressive. But it was partly very impressive then because there was so little else available in the public domain that could do good graphing and charting. Since that time however, the graphing and charting that you see every day on the internet and particularly in relation to finance is so good now that it probably challenges Excel in terms of what you can do. And even on a publicly accessible site like Yahoo Finance you’ve got a chart here showing stock prices for AllianceBerstein Holding L.P. on the right. If you were to click on this and click on Customize chart down in the bottom right corner there, you could actually do some pretty good charting using what’s on this website. But that’s not what we’re doing here. What we’re going to do here is to chart the performance of AllianceBerstein Holding L.P. using Excel. So the first thing to do is to click on Historical prices on the left. Once we’ve clicked on historical prices, we get a table showing prices. Note the start date, April 15, 1988. Today’s date is May 17, 2013. There are 25 years worth of data available and surprisingly in some ways when you get stock data from virtually every source it’s pretty much always presented in the same tabular format. So it’s ideal for processing in Excel. You have a © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced happen. I don’t want to be too distracted into dealing with that now. Bear in mind that splits don’t happen that often. But that is one other thing to be aware of. Having said that, we’ve now got all the numbers we need to plot some stock charts. That’s what we’re going to do in the next section so please join me for that.

Excel 2013 - Advanced high, adjusted low, adjusted close, monthly figures. That would give me a good picture over a long period of time. Okay, so that’s the High-Low-Close chart. Let’s just finish this section by looking at one of the specialist stock charts but with volume included as well. Okay, I’ve set these columns up now for the works: Volume-open-high-low-close. I’m going to select, say, back to the beginning of May again to give me an understandable amount. And then it’s into the stock charts and the final type of stock chart, this one, Volume-Open-High-LowClose. Click on OK. Notice how with these ones that the weekends are left blank. I would expect that there are some stock markets somewhere in the world that work at weekends. I don’t ever refer to them but there we are. Here you can where the number of marker, volume, that’s the blue. And then you’ve got markers showing opening price, adjusted high, adjusted low, adjusted close. The symbols that are used in this chart are called candlesticks. The candlesticks have a body. A candlestick with a white body indicates a price that went up during a period. A black body indicates a price that went down. The range of prices for the whole of the period is from the top of the top wick to the bottom of the lower wick. So during the period in question, whether it’s a day or a week or a month or whatever, the lowest price is the bottom of the lower wick. The highest price is the top of the upper wick. The body represents the range between the open and close. For a white candlestick there, for the bottom of the body indicates the opening price and the top of the body represents the closing price. For a black candlestick like this one, it must have started at that figure and closed at a lower figure. In fact the wick below, this one, indicates that at one stage during the period it was actually lower than the closing price for the period that’s indicated by the bottom of the body of that black candlestick. So that’s how to interpret candlesticks. Now I don’t particularly like having these bars crashing into the candlesticks so I’m going to choose that right hand axis there, format the axis, and I’m going to make the maximum on the axis, let’s make it a million. In doing that, as you can see I pretty much moved the bars away from the candlesticks. There’s just a slight overlap there so I may want to adjust it a little bit more. And obviously I’ve got label, titles, etc to do. So that’s pretty much it on stock charts but I am going to leave you now with an exercise to do.

Excel 2013 - Advanced So here is the exercise for you to do. My answer to this is going to be example-16. I just want you to do a Volume-high-low-close chart. So that’s a VHLC. So there’s no open in it, just volume, high, low, close. I want you to do it for MSFT, that’s Microsoft, MSFT is the symbol and I just want you to do it for the last 12 working days. Obviously, you’ll be doing that on a different day to the day that I’m doing it but my answer will be example-16. That’s it for this section. I’ll see you in the next one.

Excel 2013 - Advanced That’s a very basic type of radar chart. There are still some others. Let’s change the type there. Let’s go for the second type of radar chart which is the radar with markers. Click on OK and now we get a point marker at each point where Jenny’s been measured. Now in this case, it’s pretty straightforward because it’s at each apex of each figure on the diagram and you can also see how the legend has been changed just to show the little markers as well. Let’s change that type again. Let’s go for the third type which is the field radar chart and again in some situations that’s quite good because it just shows the area where weaknesses show through, where the standard appears and Jenny’s performance doesn’t hide it if you like. So let’s go back to the first type now. It is possible to format this in many of the usual ways. So for instance, if I click one of the options within the legend, right click on that, I can say format legend entry, and then I can go through, change the color, and so on. So, all that is as normal. But let’s now delete this radar chart and let’s create one with a second person on it. So my selection now has Jenny and Steve DuPont and I am going to create a radar chart for them. Make it a bit bigger. Now we get quite a different picture because again we’ve still got the blue company standard. The scaling is still on the scale from zero in the middle out to five on the outside. But whereas Jenny’s red markers indicate a very strong performance by her, the gray-green color for Steve is well inside the blue markers. So we can see visually there without even looking at the scores that Steve is falling pretty far short of the company standard. In a couple of areas, loyalty and creativity, he does manage to match the company standard. But for many other areas, he’s well below it. So again a very straightforward snapshot view there that tells you a lot, not only about how each of those two people compares to the company standard but also to some extent of course to how they compare to each other. So there we are. That’s how to use radar charts. I’ll see you in the next section.

Video: Bubble Charts Toby: Welcome back to our course on Excel 2013 Advanced. In this section we’re going to take a look at bubble charts which are a very special form of scatter chart. Now before we start I should warn you a couple of things about bubble charts. They are one of the types of chart that do seem to cause people quite a few problems. Secondly, you only really use a bubble chart for a relatively small amount of data. Now what I mean by that will become apparent during this section. So here’s the data I’m going to use. I have six countries: India, China, U.S.A., Russia, Brazil, Japan. About each of those countries, I have three pieces of information. The information is a little bit out of date, admittedly, but for our purposes it’ll be absolutely fine. I’ve got the population of the country, the GDP in U.S. dollars per person, and the overall GDP of the country. What I’m going to plot first is a straightforward scatter chart. So all I’m going to do is to choose those two columns, the population and the GDP columns, and I’m going to do insert. I’m going to choose scatter chart. I can assume you’ve drawn scatter charts before. You have a scatter chart that plots the GDP per person along the Y axis and then the population in millions along the X axis. Of course, the two countries with the highest populations have actually got the lowest GDPs per person and the GDPs are low by quite a long way. So for instance, if you look at the GDP of a person in China, you can actually hover over these points to get the figure. The GDP per person there is just \$564 whereas the GDP per person up here for somebody in Japan is \$37,000. So that’s a heck of a big difference. Now what I want to do is to add this sort of third dimension to this scatter chart because with a bubble chart I can change the size of each of these points to reflect a third variable. Now the third variable I’m going to use in this case is the overall GDP of the country. So for a country with a very high GDP it will have a bigger bubble, that’s data point, then one with a low figure. Of course, again the variation is quite extreme. The overall GDP of India \$324 billion, for the U.S.A. it’s almost \$7,000 billion, almost \$7 trillion dollars.

Excel 2013 - Advanced it in the usual way. I could delete all three of those potential labels and type in instead Japan. I could move that around. I can format the text in it, color it in, and so on. In that way, I could label each of the bubbles in whatever way that I wanted to. Now I know that involves quite a bit of manual effort but if you’re doing this for a presentation, once you’re used to doing it it’s a pretty quick job to do. Before we leave bubble charts, there’s just one thing to mention. Don’t forget that you have a gallery of bubble chart styles that you can access here. So if you wanted to make this a little bit flashier like that, that would be fine.

You can then, of course, change color schemes,

backgrounds, etc. So you can achieve a pretty smart looking bubble chart without too much effort once you’re used to doing it. So that is the last of the sections on the more advanced and unusual types of chart in Excel 2013 that are in general use. But we’ve still got some more work on charts to do and over the next two or three sections we’re going to be looking at specific types of charts, including Sparklines and pivot charts. So please join me for those.

Chapter 14 – Graphs Video: Regression and Scatter Chart Toby: Hello again and welcome back to our course on Excel 2013 Advanced. Earlier on in the course we looked at regression and correlation and I showed you how to calculate the line of best fit through some values and also to look at the strength of the correlation between two variables. One tool that you can use to assist in this is to use the graphical facilities of Excel 2013 and that’s what we’re going to cover in this section. So earlier on in the course the last example we looked at was this one where we have a plot of time, in this case quarters against sales and we were looking at possible correlation to see if this was a straightforward linear growth in sales over time. Now what I’m going to do is to remove the chart that we drew at that time. I’m going to remove the unknown quarters, the ones that we were using the relationship to predict for, and I’m going to remove the data here which is the data table containing all of the relevant metrics related to that relationship. Now before I do that, I’d like you to just take a note of three numbers in this table. The first number in cell E5, 2.923, etc, that’s the gradient of the intercept line that we calculated from this XY data. The second number in F5, 89.3, that’s the intercept of the line. The other important value is the R-squared value which is a measure of correlation 0.894. So just remember those numbers because I hope you’re going to see something similar to those again in a couple of minutes from now. So let’s just get rid of that lot and we’re just back to our raw data, quarters and sales. So first of all, let’s draw a scatter chart rather than a line chart. So insert and then one of the options here is scatter chart. I’m just going to choose that one which just has the data points on it, and then what I’m going to do is to just make it a little bit bigger and just add a little bit of information to the labels and titles. So join me again in just moment. Okay, so I’ve done a little bit of work on the title and the labels on the axes, changed the chart style a little, and changed the scale on the vertical axis. Now what I’m going to do is to draw a © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced want to plot your variables on a scatter chart, then add the trend line, then the relevant coefficients, the coefficients for the linear equation, and the correlation coefficient are shown on the graph as well. So that’s a good way of supporting an analysis of regression and correlation on some data with some good graphical evidence. Of course, you can format this graph in any of the available ways. So it’s time for you to do another exercise now. I’d like you to take the data in this workbook which is example-17. The data shows the number of hours that some students each spent studying for an exam and the number of hours is shown in column B and in column C is the score that each student then got in that exam. I want you to draw a scatter chart and show the line of best fit and the R-squared, the measure of correlation between the variables hour of study and score. That’s it. My answer to this is example-18. I’ll see you in the next section.

Excel 2013 - Advanced we could check which filters are currently applied. But as an alternative to that, if I hover over the Department button here, notice how it tells us that there is a date filter in place so that the date is between September 1, 2012 and October 31, 2012 and that it’s sorted on branches in reverse alphabetical order. Now because I’ve got a date filter in place, I’m only getting three departments worth of data. There isn’t actually a department filter in place. Let me just click on the department filter here. I’ve actually got all departments but I’m only showing three because there’s only three with sales in that period. If I go to the date filter where I have this date filter, let me click on that and then click on the between which shows the between dates. Let me cancel that. Let me now just clear the date filter and we’ll see what happens. So go over date filter, say clear filter, and now what we get is we get all of the departments because all of have got sales within the period of time that covers all of our transactions. Of course because we’ve now got a much higher level of inclusion of data, this column chart is almost unusable. The lines are so thin and there are so many of them that it’s almost unusable. But, of course, we can narrow things down because apart from removing filters we applied in the pivot table itself, we can of course apply filters in the pivot chart. So let’s suppose that I just wanted to look at, say, two of the quarters; say quarter one and quarter two. In this type of filter to select multiple items I check here and that turns these into checkboxes. Let me switch them all off and we’ll just have quarter one and quarter two, click on OK, and now we’re restricted to quarter one and quarter two and automatically we only see departments that have sales in that period of time. If I further wanted to filter on branch, I could choose perhaps the Denver, Colorado branch and say the Chicago branch, click on OK, and I’ve just got two branches to compare. So I may have to remove filters that I basically pick up from the pivot table or I can apply them myself within the pivot chart. Now before we move on to look at some of the other capabilities of pivot charts, I’d just like to talk about the use of pivot charts in presentations and live discussions. I’ve very often been involved in using these in this kind of format where you have a pivot chart with perhaps a very large amount of data and where you want to present to other people or discuss with other people the underlying transactional data. A pivot chart is a really excellent way of being able to focus perhaps on one department, one period of time, perhaps one branch in this case, and you can © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced going to stick with our table, store sales, as my source data. I’m going to put this on a new worksheet again, click on OK, and what I get on a new worksheet is I get pivot chart fields on the right, all the same mechanisms that I had before, a choice of fields, and then my drop zones in the corner here. And then I have pivot table two here as a sort of placeholder for pivot table two and a place holder for a chart here. As I select, let’s suppose I chose date and department, as I select both of those, what I start to get straightaway is the table being built up on the worksheet and the chart being built up here within a chart area hovering over the pivot table. So let me now just drop value there and you see that my pivot table is started and my pivot chart is started. Both of them created pretty much instantaneously from the original store data. So that’s it on pivot charts. I’ll see you in the next section.

Chapter 16 – Sparklines Video: Using Sparklines Toby: Hello again and welcome back to our course on Excel 2013 Advanced. Earlier on in this course we looked at drawing stock charts and whilst we were doing that we looked at a couple of the main financial sites. Very often within financial websites, you’ll see tiny little graphs like this one that just demonstrate how the price or value of something has changed often over a short period of time, but it gives you a very quick visual snapshot of what is happening with a particular value, price, or whatever. Now in Excel 2013, Microsoft introduced a tiny little chart type and unlike other charts and graphs in Excel which are basically drawn over a worksheet this is a tiny little graph that will fit into an individual worksheet cell. Now these little graphs are called Sparklines. They have a very specific purpose. It’s pretty much like the purpose of this graph here. It’s a quick visual summary of something. You don’t have all the detail. You don’t have all the formatting capabilities of full charts and graphs but what you do have is something that will fit into a pretty small space and which is particularly suitable when you want to compare graphs, several graphs with each other. I’m going to show you an example of how that will be useful going back to the sales figures for one of our sales teams that we also looked at earlier in the course. So first of all, what I’m going to do is to insert some sparklines into this worksheet. So I’m going to put them in column B. So I’m going to do an insert so that I get a new column B and I’m basically going to put some sparklines for Team A first. So I’m going to select for Team A their sales in column B, then I go to the Insert tab, and there are three types of sparklines. There are Line, Column, and Win/Loss Sparklines. Now I’m going to do line. If I click on line, I get the Create Sparklines dialog. Now for the Create Sparklines dialog, I need to select the data range and then choose where I want the sparklines to be placed. Now on this occasion, I’ve chosen the range where I want them to go first and now I’m going to select the data range. I could do it the other way around. I could in fact just say Insert Sparklines without selecting either and then enter both but I’m going to stick with the fact that I’ve chosen where the sparklines are going, that’s B4 to B8.

Now let me select the data, so that’s pretty

Excel 2013 - Advanced selected but also the other sparklines that were created at the same time are selected. When you insert sparklines in the way that we did just now they’re basically inserted as a group. One of the reasons that they’re inserted as a group, although they all obviously refer to their own individual data, is that if you want to format them in some way rather than have to go through what may be not just five but could be 50 or 500 sparklines, rather than have to go through them and individually format them, the assumption that Excel makes is that at this stage because you’ve inserted them together you want to treat them as in many way similar. Therefore if you want to reformat them in any way, you probably want to reformat them all. Now let me show you the sort of thing that happens. Once you’ve got the selection there, you have the Sparkline Tools Design tab, a contextual tab. If I select that tab, watch what happens if I change that first sparkline from a line type to a column type. What happens is that the whole of its group is changed. Now there are various other formatting things that I could do. Let me put it back to being a line again. One of the options in the Show Group on the Design tab is high point and what this will do for each sparkline is mark the high point. If I check that you get the high point on each of the five sparklines for the members of Team A, it’s possible to ungroup those sparklines. So with those selected over on the right of this contextual Design tab, there is a Group group and if I click on Ungroup watch what happens. Those Sparklines, the set of five, are ungrouped and the only one that’s now selected is the one that I selected at first which is Andy’s. So now if I click on column, it’s only Andy’s that is converted to being a column type sparkline. Okay, so let’s take a look now at the Design tab of the Sparkline Tools. We have an edit data option on the left where we can edit group location and data. So we can go back to the original dialog so that rather than have to change the data for each individual sparkline where we’ve got a group even, if it’s not formally a group we can change the data range and corresponding location range. We’ve got the ability to switch between the types. I’ll talk about Win/Loss in a moment. We then have the show options. We can show the high point, the first point, the last point, the low point. We can show negative points where we have some kind of measurement where we do have negative points. Hopefully, we’ll never get negative sales from any of our team. But sometimes what you’re shown with Sparklines is measurements against some standard and a © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced negative may be a bad sign. You can then choose the style. In the case of Andy’s Sparkline that we’ve currently got selected, a column type, we’ve got a number of color schemes there. We can choose the appropriate style and then we can go in and change colors, marker colors, and then we’ve got the grouping options that I talked about just now. Now there are a couple of other very important things to realize about sparklines. What I’m going to do with my Team A Sparklines is I’m going to group them again and I want to look at these columns in a little bit more detail. In fact, I’m going to just zoom in even a little bit more. Within the Group group, there is another button over here, Axis, and if you click on the drop down at the bottom, you can see a couple of very important options. The way that Excel 2013 does this is that it treats each individual sparkline, even the sparklines in a group, as being different and separate from the point of view of how it scales the vertical axis. So for instance, if Andy’s sales were constantly twice as high as Michael’s that wouldn’t really show up in the sparklines because Andy’s and Michael’s are based on their own individual vertical scales. The options here that dictate that are this one in the middle, Automatic for each Sparkline, that’s the vertical axis minimum value option. And then you have vertical axis maximum values options, Automatic for each Sparkline. So each sparkline is having these calculated automatically. If you want the same vertical axis for all sparklines so that you can look at them and you can really compare them with each other and not just see how each person’s sales have varied over the year you need to click same for all sparklines in both cases. That then gives you a consistent scale on the two. Just to give you a good example of this, look at the February figures now for Andy, very low, \$63, Michael’s \$466. You can see how much higher Michael’s bar is here for that month. Now obviously if you looked at the whole team, you’d see much more variations here and you get a much better idea of who’s selling a lot and who isn’t. Now I’m not going to go into sparklines in too much more detail now. I’ve got a couple of other things to point out to you, but hopefully you’ve got the general idea there and plenty for you to experiment with. Let me just go back down to Team B for a moment. I’m going to just click in Team B and click on high point. You saw that before, the little markers that show the high point in sales for each member of the team. If I go back to Team A notice that high point is marked there. I’ll switch off low point but high point is marked there. In the case of the column style of sparkline, the high point is not marked by a mark on a line as it is with the line type of sparkline; © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced My answer to that will be example-19. That’s it on Sparklines. I’ll see you in the next section.

Excel 2013 - Advanced whole number as a percentage. Now let’s suppose I say that I can afford a monthly payment of 200 of whatever my currency units are. My currency units here are pounds so I’m just going to call that currency. Again, I can reduce the number of decimal places if I want to, but I won’t. Let’s say I’m going to borrow that over a period of five years. So that’s 60 months. And here I’m going to say that’s a number, number format. Again, I get two decimal places. I want to reduce that down to no decimal places and now all I need in the loan amount is the correct formula. Now although I’m using the Excel web app if I start to type the formula, you may recall way back near the beginning of the course it’s the Pv function that we need. So it’s Equals Pv and you do actually get pretty much the same help that you get in the desktop version. So if I open the round brackets, I’m prompted for the arguments that I need. The rate will be C2 divided by 12, that’s the monthly interest. The number of periods is C4. The payment is C3. I’m going to say the final value will be nil and then the type of payment I’m going to choose payment type of one. And then I can just hit the Enter key, and there you are. I could borrow 10,396 pounds and 84 pence. So that’s my Loan Affordability Calculator. Not very long ago it would have been unthinkable that you could actually put that as a working workbook just on a website as it is. You’ve been able to do things like make workbooks work within SharePoint and under certain other circumstances for a while. But in terms of generally making a workbook available on a website that really is a bit of a new innovation. So in the next section I’m going to show you how I put that Affordability Calculator into my website and make it available to anybody I want to, to use. Just one or two final points before we close this section. It’s very important to recognize that what has been done here is to use Excel from within a web browser and it’s not necessary to have an installed copy of Excel. You don’t need the desktop app to be able to do this. In fact, I’ve done all of this several times for this type of functionality on an iPad and there’s certainly no installation of any Microsoft Office software on my iPad. So it gives you a massive amount of flexibility and freedom. It is true that it is still a little restricted, not only in terms of the fact that the functionality if you look at it holistically is only a relatively small part of the overall Excel functionality. But as you can see from what we’ve done there, an awful lot of it is included and I think over the coming months and years it’s going to grow and grow and grow. © Copyright 2008-2013 Simon Sez IT, LLC. All Rights Reserved.

Excel 2013 - Advanced Okay. That’s it for this section. In the next, we’ll look about making this workbook, this Loan Affordability workbook available to the general world. That’s it for this section. I’ll see you in the next one.