Here I'm going to show you an alternative to how most people input stuff into Excel and how most people do calculations, and these are known as array formulas. As you gain more experience with Excel, you'll find that using array formulas and even array functions will really help you be more efficient in Excel. You all probably know that you could create something known as an array by just typing in numbers. So there we have, this is known as an array. I'm going to show you an alternative to punching this in into six different cells. If you have Excel 2019 or Office 365, you can just start typing in, and we start this with a squiggly bracket. So left and negative 1,2,3. The commas denote new columns, the semicolon denotes a new row, 4,5,6 right squiggly bracket. If you have Excel 2019 or Office 365, when you press Enter, it puts it into this array, so this is known as an array. It's all it's own thing. If you don't have Excel 2019 or Office 365, let me go ahead and delete this. You can enter an array by highlighting the size of the array that you're going to want to enter, and then you can do the same thing. So left squiggly bracket {1,2,3;4,5,6}. Then you don't press Enter, you do Ctrl-Shift-Enter. The key combination on a MAC is a little bit different, but now you have what is known as an array. Now, that's not entirely useful and exciting. So I'm going to show you an example where this is useful and exciting. This is any file called Savings.xlsx. We've got a formula here, we have a principle, we have an interests. So the savings after a certain number of years n, is given by this formula. First, we want to name these variables. So I'm going to go ahead and name those by going formulas, create from selection. Then we put this in a single cell, which is going to be peers or P times parentheses 1 plus I raised to the, and we're going to do the number of years. This is known as a case study, we're performing a case study on the number of years. Then I can go ahead and click that down. So that's how 99 percent of the people will do this type of calculation. Let me show you an alternative using an array. So first, I'm going to just select this entire region cells C8 to C18. I'm going to type in a formula here, P times, it's the same thing we did before, P plus I, but now instead of raising it to or clicking in the cell adjacent to it, I'm going to click this entire region because this is known as an array. More specifically, it's a vector, it's a one-dimensional array. Then if you have anything other than Office 365 or Excel 2019, you can do Ctrl-Shift-Enter or if you've got Excel 2019 or Office 365, you can just press Enter, and it does the same calculation that we did before. So that's kind of a shortcut. Let me go ahead and delete that. Another way you can name this, so this is going to be its own array n. Then I can highlight this, I can type in equals P times 1 plus I raised to the n, Ctrl-Shift-Enter or Enter if you have a newer version, and that's how we can perform something known as an array calculation or an array formula. Finally, I want to show you just one last example. We want to create a multiplication table. We just want to multiply these values here with each of the corresponding ones over here. For example, in this cell, what we want to show up would be two times eight. So obviously you wouldn't do this manually, you would put in a formula and each of the cells, we want to automate it. So the question here is, how do we do this? What single formula could I put in cell C3 that I could drag over the row and then drag down? Well, let's start this. I can take B3 times C2 to give me the product. So it looks like it's working fine. But then what happens as I drag this over, you'll see that the second item is, if I reference it, if I double-click in here, it looks like we're multiplying the cell adjacent to it to the left by the one above. We want the cell above in cell D2, but we don't want C3, we want B3. So back here in the original formula, I always want this to be column B, and this goes back to the differences between relative and absolute references. There is actually something known as a mixed reference, and in this case, we want to pin column B with that dollar sign. So when I copy to the right, I'm always going to be using column B. When I drag down, I'll be using B4, and then B5, and so on, which is what I want. So I can take that and I can drag this over, and let's just make sure it's working. Yes, 7 times 1 is 7, and I'm pretty confident that's working. So now I can see what happens when we go down. This is a special case. It actually works because of how we have a six there, and that's also a six. But if I drag it down one more cell, we see that there is something wrong with this formula. In fact, if we take a look at the C4, because I wrote those original references as relative references, so I need to change my original second argument here, the reference, to something else. Let's go back to our original cell here. Now when I drag this down, I always want to use row 2. So I can double-click in here, and I'm going to press F4 twice until I get C$2, that means when I drag down is going to be C, but it's always going to be row 2. So it's going to be the top one, which is what I want. Now I can drag this back over, if you make a change to a formula that's already been dragged over, you have to read drag it over, and then I can drag this down and it's working. So now each of these represents the product of the row up here and the column over here. Now, there is a much easier way to do this in Excel. I can delete this. Again, this is an array formula. If you have a newer version of Excel or Office 365, you can just type in to the upper left, and I'm just going to very easily drag B3 to B7, and then do times and C2 to G2, press Enter and we get our multiplication table. Alternatively, if you don't have one of the newer versions, you can just highlight that region, you can type in equals this, so B3 to B7 times C2 to G2 Ctrl-Shift-Enter, and there is your multiplication table. So hopefully, this screen cast gave you a better idea of how we might use array formulas in Excel.
