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 on 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 tap 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 at 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 act is limiting it to the current row suggests

that we can actually get the total sales for

the entire table just by leaving that at symbol out.

Let's test this theory,

so I'm going to come to total sales row 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 bid,

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

open or 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 and 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

note through to fall in this column but made them white so he hasn't had to hype 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 sum ifs 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 to come 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 up in 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 forming 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 saying what

happens when we do add a new word 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 copy 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.