In the previous video, we learnt how we could create array formulas in Excel. Now a lot of Excel's array functions also support this array capability. For example, the mode multifunction, allows us to upload multiple modes from a data set or the OFFSET function which we're going to learn about in week five. But one of the most useful of Excel's array functions is the TRANSPOSE function and that's the one we're going to look at now. Alex has to complete the Conversion Table that we started earlier this week. What he wants to be able to do is to quickly get a translation from one currency rate to another currency rate without having to first convert to dollars. The first thing he needs to do is take all of these currency rates and put them across the top. If you're familiar with Excel's Copy Paste Transpose, that is similar to what we want to be doing. I'll quickly demonstrate in case you haven't seen it before. So for example, if I want this to appear as a row instead of a column, I could copy. And then when I come to paste, right click and choose the Transpose, and you can see basically what it does is swing the data through 90 degrees. So a very useful little Copy Paste option, but not what we want here. We actually want to use a formula to pull through exactly the value that is sitting on the corresponding cell. Now if I was going to do this without a raise, I would have to click on the first one and click Enter. And then click on the next one and click Enter, etc. Time consuming and not very flexible. So instead, I would like to use a function to perform that process for me. And the function I'm going to need is the TRANSPOSE function. It works much like the Copy Paste Transpose except obviously using a calculation. So we're going to start by selecting all the cells where we want the values to go, and we're going to type = and tr and tab to select TRANSPOSE We're now going to select all the values that we actually want to swing through 90 degrees. And then we're going to press, Ctrl Shift Enter. Nice and easy, and if any of these values should change, the ones in the row would automatically change. So that was a nice simple example. We now want to do something tiny bit more interesting. We actually need to get the inverse of each of these rates. So what we've got at the moment is how many dollars each of these currencies would buy? Across the top, we would now like to put how much of each of those currencies one dollar would buy and this is called the inverse. The calculation works by taking the number one and dividing it by the value. But instead of doing them one at a time, we're going to use a single calculation to do them all at once. We're going to select all the cells where we want our inverse values to go. And we're going to type =tr and Tab. We're now going to type in 1/, and select our array. And when we press Ctrl Shift Enter, there are all our inverse values. Much quicker than having to do them one at a time. And now just to put a little cherry on the top, to complete the table, what we really want to do is multiply each of those values by each of these values. Now we could do these with conventional formulas using mixed cell references but actually it's quite complex. Whereas using arrays is much easier. So we're going to simply select our table where we want our answers to go. We're then going to type =, select our first array, type our times, and select our second array. And when we press Ctrl Shift Enter, Conversion Table complete. So we've now seen a couple of situations where array functions actually make our lives much, much easier. In the next video, we'll look at some slightly more tricky problems that we can use array formulas to solve.