Hi Nicky. How are you? Hi Prashan I'm doing great and you? All the better for starting another exciting week. We've looked at text functions, date functions, what's next? Actually, we're going to have a bit of a break from functions this week, and we're going to focus on cell referencing, and more specifically, Named Rnamanges. That is really important and up to now, all our formulas have worked fine with relative cell referencing. But obviously that won't always be the case. So Nicky, tell us a little bit about cell referencing. Well, cell referencing is how you refer to as cell in a calculation. So if I wanted to multiply the value in B3 by the value in C3, my calculation refer to the cells as B3 and C3. If I then copy that formula down, the next calculation will multiply B4 by C4. This is called relative referencing, and this is why we can copy calculations in Excel. The problem is that sometimes we don't actually want to use relative positioning or relative cell referencing. So if, for example, I had a rate in D1 and I wanted to multiply a whole column by that rate, as I drag that calculation down, I don't want D1 to become D2, D3. I want a way to lock it in place. Now, what I do is I use an absolute cell reference, and we do this by putting a dollar in front of the D, and a dollar in front of the one. There's a great keyboard shortcut to put those dollars in, you just need to press your "F4" function key. Some people find it confusing that we need two dollar signs. Can you please explain why this is? Yeah, sure it's a really good question. The dollar in front of the column letter or the D, locks the column, whereas the dollar in front of the number, or in our example, the one, locks just the row. So if, for example, I just had a dollar in front of the D, if I dragged that calculation across, it would stay D1, but if I drag it down, it will become D1, D2, D3. This is what we call a mixed cell reference, and obviously you can put the dollar in front of the column or in front of just the row. They're really handy when you need to drag a calculation down and across. But as well as relative, absolute, and mixed references, we have another way of referencing cells called a Named Range. Can you please explain what this is? Yes. As you say, a Named range is another way of referencing a cell, and basically it's where you give a cell a sensible name. So if I was there, I had a discount rate in cell E2, rather than referring to it as E2, I could actually refer to it as discount rate. That makes sense, but why would we want to do this? Well, for one thing, it makes our formulas much easy to understand, and we'll see in the videos it also makes them much easier to create. Another advantage is that Named Ranges give us an absolute cell reference so we don't need to worry about the dollars, which is nice. But actually the most important reason is they support automation, and actually that's why they are one of the most important tools in our Excel toolkit. So not just a nice to have, Named Ranges are a need to know. So how do we create a Named Range? Well, there are few options. We're going to start with a really easy one called the Name Box. We're then going to use a tool called Define Name, which gives us a few more options, and finally, we're going to look at my favorite, which is Create from Selection, and this allows us to create multiple Named Ranges simultaneously. What about if we want to see what ranges you've created or changed them or even delete them? So we're going to look at a great tool called the name manager, which allows us to do all these things, and then we'll get stuck into looking at how we actually use our Named Ranges to simplify calculations. So are we going to do some calculations? Yeah, we are. We're going to look at two really exciting functions called COUNTIFS and SUMIFS. They basically allow us to summarize or aggregate data. In this context, we are going to be using them to demonstrate Named Ranges, but they are excellent analysis tools. So I'm happy we're sneaking them in here. Once we master COUNTIFS and SUMIFS, we'll be able to use AVERAGEIFS, MAXIFS and MINIFS as well. One last question, you mentioned that name ranges support automation, now that's really interesting. Can you explain this further, please? Yeah. Well, when you refer to a ranges B3-B9, it can only ever be those cells. So if I now add row of data in row 10, my calculations are not going to pick up those new values. A Named Range can be redefined. So if we can find a way to mitigate automatically pick up the new values in row 10, our calculations will keep working. We're going to look at quite a tricky solution this week using dynamic Named Ranges, but next week we'll see an even easier solution using tables. Thanks Nicky. Our five topics this week are, cell referencing and naming, creating Named Ranges, managing Named Ranges, calculations with Named Ranges, and then automating processes with Named Ranges. Don't forget to download this week's exercise files and work alongside. Take some time to check out the toolbox and cement those skills with this week's really interesting practice challenge. Now, it's over to you.
