[SOUND] Welcome to week two, and welcome Nicky. >> Hi Prashan. >> This week, we’re going to be looking at advanced formula techniques in Excel. Sounds intriguing, Nicky. What are advanced formula techniques in Excel? >> What we’re going to be looking at is a bit of a mixed bag this week. So, rather than looking at advanced functions- >> Mm-hm. >> We're going to be looking at how we can actually create our formulas in slightly different ways to solve those really tricky problems. And we're going to get our users to that power user level by showing them some of Excel's more advanced ways of creating these formulas. And the two main things we're looking at will be array formulas and structured references. >> Now in previous courses in this specialization, we've talked about relative, absolute, and mixed cell references. So where exactly does structured references fit in? >> So, structured references came in with tables. Whenever you work in a table, you're going to be working with a structured reference. So our users have probably seen them- >> Mm-hm. >> But don't necessarily completely understand the syntax. So we're going to break them down, so that they can start using those structured references outside of the table as well. And structured references have quite a few benefits. Like named ranges, you can refer to them by name, which means you don't have to go and select the data. And they're easier to understand, but most importantly, they're dynamic, which means that as we add data to our table, the structured references automatically pick up those new values. >> Next up in the week, we're going to be looking at how to use functions to sort our data. Can you let us know about that please? >> Of course. So, it does seem a little strange. Why would we want to use functions to sort data? But imagine you've got data that's being updated really regularly, and it would be too time consuming or even impractical to go and manually sort it the whole time. You can actually use Excel functions to sort it. So, it's partly that we're going to look at solving what might be a really useful problem. But more than that, we're going to be actually looking at how we can start creatively using a combination of functions to solve a trickier problem. >> And then the rest of the week, we're going to be focusing on array formulas in Excel. So what exactly are array formulas? >> Very good question. Now, array formulas are sometimes called CSE formulas. >> Mm-hm. >> Because when you enter them, you actually type in CTRL Shift Enter, rather than just Enter. So you get the idea, they are quite different, and they are structured quite differently as well. But they allow us to perform calculations on multiple values in an array. >> So, perhaps we need to know what exactly an array is, please. >> Yes, an array is really just a set of data, so a one dimensional array would be a column or a row, and a two dimensional array would be a collection or a set of rows and columns. >> So, why might we be using array formulas in a workplace setting? >> Well, the real reason for using array formulas is that they can solve those really tricky problems that regular formulas either can't solve, or it would just be really difficult to use them. And that would be the main reason we use array formulas. >> Anything to look out for when you're using array formulas? >> Yeah, there are two things we need to be careful of. First of all, a lot of users don't know about array formulas, so you need to make sure if you're using them in workbooks that other people will be using, that they are properly documented, and that those users are taught about array formulas. And the other thing is that if you have really large data sets, you may start finding you get a performance problem with the array formulas, in which case, they're better avoided. >> So, an action-packed week of advanced formula techniques in Excel. Thank you so much, Nicky. Now, we have some practice videos coming up for you. Make sure you download the Excel workbooks so that you can work alongside us step by step. And then check out this week's quizzes, as well as the practice challenge to take your skills even further. There's a toolbox to help you as well, and make sure you check out this week's great ninja tip. Now, it's over to you.