0:03

This week, Alex is going to be using

Â some slightly more advanced formula techniques in

Â helping create a workbook for an online retail company.

Â His first problem is to help create some summary data from

Â the quarterly sales figures because the data is going to be added to over time,

Â he wants to make sure that these formulas will automatically pick up the latest values.

Â And the easiest way to do that is to use a table.

Â So Alex has put all the figures in a table.

Â He's also named the range year and you'll see why very shortly.

Â Now, one of the real benefits of working with the table

Â is it actually sees that range as a standalone range

Â and you can actually use that to get

Â useful information like how many rows are actually in the table.

Â So to do that we're going to look at Excel's ROWS function.

Â I'm going to click into M4 and I'm going to type =RO and select ROWS.

Â Now, I need to specify my data but I'm not going to select it,

Â I'm actually going to refer to the table by name.

Â So I'm going to type S and the table is

Â called Sales and you see it comes top of the order complete,

Â press Tab and click Enter and there 40 rows

Â my table and that doesn't include the header or the total row.

Â So, we're really starting to see some advantages.

Â Now, let's jump into the table and get a total sales for each quarter.

Â So we're going to try =SUM and Tab and select our values.

Â And immediately, you notice something is quite different,

Â when you work in a table you get something called, "structured references".

Â Structured references look a little like named ranges they're very easy to

Â create they're very easy to understand but let's just have a quick look at the syntax.

Â So first, we have the table name which is Sales and then a square bracket,

Â inside the square bracket we can list parts of the table we want

Â to work with and in this case we want the column Australia to the column China,

Â the @ symbol indicates current row.

Â So this is going to only add up the values for the current row and we will click Enter.

Â It has added up the values and even copied the formula down.

Â So, the only thing left to do is to pop in Total. So efficient.

Â Now, the suggestion that the @ is limiting it to the current row suggests

Â that we can actually get the Total Sales for

Â the entire table just by leaving that @ symbol out.

Â Let's test this theory,

Â so I'm going to come to Total Sales here and type

Â =SUM and I'm going to type S to select my table and then a square bracket.

Â And when I open the square bracket you get

Â all the components of the table that you can refer to,

Â if you're just working with a single component you can just

Â double click it but if you want to refer to more than one,

Â you're going to need another set of square brackets.

Â So, I'm going to type my square brackets and double click

Â Australia then close my square bracket, colon,

Â open my second square bracket and choose China,

Â close my square bracket,

Â close my first set of square brackets.

Â You can see it's now selecting the entire table and there's my Total Sales.

Â Now, let's see how we can use the header information from the Sales

Â referencing to pull all of these headers through to this range over here.

Â So, I'm going to start with my equals again and my table name,

Â I'm then going to open my square brackets but this time I want to use the headers.

Â So, another square bracket and double click headers,

Â close my square bracket and now the not so obvious bit,

Â to specify that we want the header from the Australia column we type a comma,

Â open our square bracket,

Â and double click Australia.

Â Now, close both sets of square brackets.

Â You can see it's selected the Australia heading and Enter.

Â So, a little bit like named ranges but watch what happens when we copy this across.

Â It has actually gone more like

Â a relative reference and that is

Â well-structured references are both very useful and a little bit different.

Â If you drag them to the right or to the left, horizontally,

Â they will behave like relative references when you drag them down however,

Â they will still remain absolute so they're basically mixed cell references.

Â Let's have a look at another example,

Â so I'd like to get my last five years and to help with this Alex has put the numbers

Â 0 through to 4 in this column but made them white so he hasn't had to hide the column.

Â We're going to click into L8 and we're going to use

Â our MAX function and I'm going to look in the column Year.

Â So once again, just type S for Sales,

Â open square brackets tabs, select my Year.

Â So actually very quick to enter then I'm going to close

Â my round bracket and I'm going to remember to subtract the hidden value over here.

Â So I should get 2017 minus 0 which gives me 2017 and when I copy that down,

Â you'll see it behaves more like an absolute cell reference.

Â So, we don't see that traveling in any way.

Â Great. Now, let's look at how we can solve

Â a slightly more interesting problem using a combination of different references.

Â So, I want to get my total sales for each of my regions for each of

Â my years for this I need my SUMIFS function,

Â so I'm going to start =SUMIFS and

Â my sum range is going to begin with Australia but then UK, US,

Â and Singapore so to make my life easy,

Â I'm going to use a structured reference so type S for Sales,

Â open square bracket A for Australia,

Â close your square brackets, and comma.

Â Now, my criteria range is my year but unlike my country,

Â I do not want this to move when I drag the formula.

Â I want an absolute cell reference.

Â So for this one I'm going to use my named range.

Â So, a little bit of a cheat and then a comma.

Â And finally, I need to click on my year but

Â I'm going to need to make this a mixed cell reference,

Â so I'm going to remember to press my F4 three times,

Â so I'm just locking the column,

Â close your brackets and Enter.

Â And when we copy that down it works beautifully and the same going across.

Â So, there is a combination of

Â different referencing styles to help us solve a slightly more interesting problem.

Â Now, the last thing we're going to do is have a look at how we

Â could create an absolute structured reference, if we needed to.

Â In this example, we have another table and this time

Â we want to find out how top three quarters for each of our regions.

Â Now, we can't to use Max here because Max only gets the highest,

Â the second highest and the third highest it can't help us with.

Â So, we're going to use Excel's LARGE function,

Â and there is an equivalent SMALL function,

Â which works more like the MIN. All right.

Â Let's see how this works.

Â Clicking in M-16, I'm going to type =L for LARGE,

Â and my array here is going to be Australia again.

Â So, I'm just going to type Sales open my square brackets,

Â Australia, and close my square brackets. Nice and easy.

Â Now, the way the large function works is it allows you to specify this little K here,

Â if you want the biggest,

Â one, second biggest, two, and third biggest, three.

Â So, I'm going to type a comma and I'm going to click on my one.

Â But now I have a problem because I'm in a table,

Â I'm being forced to use the structured reference

Â and that is going to move as I drag this formula across.

Â So, I need to make this absolute and

Â unfortunately this is not totally straightforward but it's quite doable.

Â So we're going to click just before

Â the bracket and we're going to specify the table name,

Â which for this table is Top Quarter.

Â Then, we're going to come in after the first set of

Â square brackets but before the second and we're going to type a colon,

Â open on square brackets,

Â and select Best Quarters again and then close

Â our square bracket and that is how you

Â can create your absolute sale reference in a table.

Â And now, when we click enter and copy that across, it's worked fine.

Â So, that is the syntax for an absolute structured reference. All right.

Â Let's just finish off quickly by seeing what

Â happens when we do add a new row to the bottom of this table.

Â So I'm scroll down to the bottom and I'm going to be

Â very lazy and just copy this last bit of data,

Â just so you can see what happens when I copy then down the formula of course,

Â automatically is copied down for us and the table has extended to include the new data.

Â When I come back into my data,

Â we now have 41 quarters and 2018 is now

Â my most recent year and all calculations have updated to reflect the changes.

Â So, there you see the power of working with tables and structured references.

Â