Welcome to week one. Welcome, Nicky. Hi, Prashan. So, what are we going to get started with for this week? Well, we are going to start of really nice and gently by looking at working with multiple worksheets. You'll find in your place of work that a lot of your workbooks will have more than one sheet. So it's important that we can create those and manage them effectively. Also we're going to look at labeling those sheets which makes them easier to identify, but more importantly when we start doing our calculations across sheets, it's important that you actually have named them correctly. You mentioned calculations across sheets, which we covered in the essentials course, which is a great tool to have. Another way to perform calculations across sheets is using 3D formulas. Could you prime our learners about this please? Absolutely. So 3D formula is a really intriguing name and actually a great tool. Probably the best way to explain is to compare it to a 2-D formula. So when working in a single sheet, if we want to add three cells that are next to each other, we don't go this plus this plus this. We say equal sum and we add them all up. Now imagine that we wanted to add the same cell from three different worksheets. Again, we don't want to say equals this plus this plus this, what we'd like to do is say equal sum and simply drill down and that's what we're going to do. So imagine you've got your three sheets stacked on top of each other. What a 3D form allows us to do is actually drill straight through them. Exactly. Not just multiple sheets but in a workplace, we'll be working with multiple workbooks. Could you let our learners know how we do that please? That's a really good question and it's actually one of the most common questions come out of businesses, how do I get data and add it up for multiple workbooks? And there are several different options. We're going to start by looking at linking workbooks. What this is is when you create a formula that basically refers to a cell in a different workbook. You do it exactly the same way as you would link worksheets and it works really well if your workbooks are all located in the same place and don't move around too much. If any of the linked workbooks update, your calculation immediately picks up that change. You mentioned the issue about moving things around. And this has happened to me a few times where I've got all my workbooks sitting in one folder at work and I take one of the files home and the links break or I send one of those files to a client and they get an error message. How can you help with this please? Okay, I'm so pleased you asked. In the first instance where you've taken the book home, you will get a message popping up saying do you want to update links? Don't worry about it. Just ignore it. Okay. You can change the workbook, take it back to work the following day, put it back where you found it and those links will automatically reestablish. So that's not really such a problem. But the second issue when you are sending it to a client, that is a problem. He's got no way of updating those links and you don't really want to send a link to workbook out of your organization. True. Yes, so what we're going to do is look at a different tool. It's Excel's consolidate tool and it's fantastic when you have multiple workbooks or even worksheets that are pretty similar and you want to aggregate that data and it could be added up but you could also average it or count it. And the consolidate tool will do that for us. And like you mentioned that would assume that the workbooks have a similar structure. Can Excel help us when the workbooks have different structures? Yes. Now, there are actually two variations of the consolidate tool. The one is consolidate by position, which needs them to be identical. But there's a variation called consolidate by category, also known as consolidate by reference and that allows them to be similar but slightly different. And what it does is instead of using the position to add the data, it uses the labels, which are the row and or column headings and where they match, it sums the data. So really powerful alternative and you can do things with that that what you couldn't do with linking workbooks. Sounds great, Nicky. Now we have some great practice videos for you. We will start with multiple worksheets, then 3D formulas and then multiple workbooks and then we'll look at consolidating by position as well as consolidating by category, also known as consolidating by reference. Work alongside the practice videos by downloading the Excel file and working with Nicky step by step yourself. Then take a break, recharge and come back and practice them again. Because practice makes permanent. We've also got a great practice challenge for you where we will show you slightly different ways for you to work through these Excel functionalities and master them. Check out this week's toolbox as well as this week's great ninja tip. And now it's over to you.
