Welcome to Excel
basics video number 21. Hey in this video we're
following up on the last video where we're used VLOOKUP
and pivot tables. And in this video
we want to see how to use relationships
rather than VLOOKUP out for pivot table reports. Yes this is a big no symbol
because in some situations we do not want to
use VLOOKUP when we're adding an extra
column to a large data set. Now last video we had
73,000 records in our table, and we added two extra
columns using VLOOKUP. And the problem was, and we'll
see this in just a moment, is that 140,000 formulas
in your Excel spreadsheet that all have to calculate. Not only does that take
a long time to calculate, but it increases the
size of the file. So in this video we want to see
how to use on the data ribbon tab the relationships button. We'll simply make a
relationship between the sales table and our
country lookup table connecting the
country code in both, and we'll be able to
create the same pivot table report without VLOOKUP. Now I want to go look at
what we did last video, and there's three different
Excel files to download. There's the start file, there's
the finished file, which we'll come and look at
in just a moment, and there's Excel Basic's
21 with the VLOOKUP example. And here's what
we did last video. We're going to have the same
data set in this video, date, product, country code,
units, revenue, and cost of goods sold. That's what we'll
have in this video. We'll also in this video
have the country lookup table and the product lookup table. But unlike last video
we're not going to do this. Now notice if I hit
F2, we used VLOOKUP to look up a country
code, find a match in the first column
of the lookup table, and return the country
name back to this cell. Now I want to show
you something. I'm going to delete all
these formulas here. There's our VLOOKUP, this
is how we did it last time, control enter, and I want
you to watch what happens when I copy this formula down. Because just for a second
you'll see double click and send it down, it
has a slow reaction time because it had to
calculate 73,000 formulas. Now I'm going to double
click this one, same thing. Now for one data set with
two columns like this, it's not so bad. But if you have many tables,
and many extra columns, and many pivot
tables in a workbook, you can imagine that things
would start to slow down. Our ability to
create relationships instead of using VLOOKUP
will help that dramatically. Now before we can
use a relationship I want to understand
what's really happening between the country
code column in our sales table and the country code
column in the lookup table. Well, of course, the first
column of our lookup table has to have only
one of each value. Now look, VLOOKUP
is looking up MEX. Down here VLOOKUP
is looking up USA. If there were any
duplicates over here, VLOOKUP wouldn't
know what to do. So that's why when we
create a lookup table we have one of each item in
the first column of the lookup table. Now what that means is
this, USA, USA, Mexico, Mexico, there's lots of
duplicates over here. That means in the country
code column there can be many of each country code. But over here there
can only be one. Similarly in the product
column, fun fly, fun fly, there's lots of fun
flies because we can sell as many fun flies as we want. So in the product column
in the sales table we have many products repeated. But over here if we're
going to look something up there has to be a unique list. And of course, there's
only one fun fly. Now that means there's a
relationship between country code and the sales table and
country code in the lookup table, and product in the sales
table, product in the lookup table. And here it is. It's called a one to
many relationship. We can have exactly one of
each item in the first column of the lookup table
but of course you can have many over here. That means that for
our product we're totally allowed to look
up fun flies as many times as we want over
here, but there's going to be just one on the
one side or the lookup table. Same with our
looking up country. When it's looking
up country code there can be many
over here because we can have many sales in Mexico. But of course, we're
looking up there's only one on the one side or
the lookup table. Now this is important
because this is a one to many relationship. Now this is the first time
we've learned about a one to many relationship. We will see it
today in this video. We'll also see it when
we get over to access. And every time you do VLOOKUP,
you're actually using a one to many relationship. Now, VLOOKUP up is a one
to many whether or not you're doing exact match
or approximate match. But when we're
building relationships it's going to be for
exact match only. Now I want to go look
at the end result before we learn how to
create these relationships. I'm going to go over
to the finished file. I'm over here in Excel
Basics 21 finished file. And this is our finished
pivot table fields task pane. Yes, instead of one table with a
bunch of extra VLOOKUP columns, we're going to be
allowed to put all three tables into the pivot
table field list and then simply drag country
from the country lookup table down to the rows, product
category from the product lookup table down to rows, and
then yes indeed our revenue column down to values. Now let's go back over
to our start file. Now back over here
in the start file, I want to look at this picture
of the relationships we're going to create. Now we're simply going
to select both tables and tell the column to connect
in a one to many relationship. But this is a picture that
will help us understand. Not only that, but this
picture is exactly what we'll see over in Access
when we study Access, which is a database program. Notice it has a one too many. One, that means
the product column is the first column in our
lookup table with exactly one item each in the first column. And then there's this line to
represent the relationship, and there's a relationship
between the product column. And over here in Excel they
represent the many side of the relationship
with an asterisk. Over in Access they'll
have an infinity symbol. But what it means is that
we'll create this relationship for both lookup
tables, and then we'll be able to have one, two, three
tables in our pivot table field list and drag and drop and
create our pivot table. All right, let's go
see how to do this. I'm going to go over to
the sales data sheet. I'm going to
scroll, down there's some instructions at the top. Now in order to go up
to data, data tools, and click the
relationships button, we actually have to convert
each one of our tables to an official Excel table. Notice relationship button
isn't even available because it doesn't work unless
we have dynamic Excel tables. That means this
brand new feature is going to insist that you
convert all of the tables to Excel tables. So if we add any records or
delete any records everything will update perfectly. All right now we
remember from video 15 how to convert tables
to Excel tables. We clicked in a single
cell of our proper data set, go up to insert,
and click on the table, although we're never
going to do that. We're going to use
that keyboard there. Control T, there is the
create table dialog box, I'm going to click OK. Immediately I go up to
table tools designed over to properties that we're
going to name this table. I'm going to call it something
smart like sales table and enter. Now I'm going to go to
the country lookup table, control T, enter. Immediately go up to
properties and I'm going to call this something
smart like country lookup table and enter. Now, I go to the product
lookup table, control T, enter. Immediately I go up to
properties clicking the table name and give it a smart name
like product lookup table and enter. Once we have our
three tables, now over in the data
ribbon tab, data tools, it's totally polite. There are relationships
but I'm simply going to click this
relationship button. There aren't any
relationships so I click new. Now, I only click
the drop down and I want you to notice
there's four what are called worksheet tables. Worksheet table is a
synonym for Excel table, meaning we converted the proper
down sets using the Excel table feature. Now why are there four tables? I'm going to click
escape, escape, escape because over on extra table,
I added an extra table. We're not going to use this. I just wanted to show you
that even though we're only using three tables, all of
our tables in the entire Excel workbook, however many we have,
will show up in lots of places that we use when we're
creating the relationship. Now I'm going to click the
relationship button, click new. And now we want to select
from table and related table. Our table is always going
to be our sales table. The related table is always
going to be the lookup table. Right so you just
got to remember, related table, lookup table. Another way to remember
this, and we'll have to learn these terms
primary and foreign when we get over to Access
and start studying databasing, primary means
there is a unique list of items in the first column of a table. Foreign means that's
the many side. All right so I'm
going to select table, that's always going
to be our sales table. Now notice it says worksheet
table, sales table. Now I'm going to come
down to related table. That's our lookup table. We're going to do the
country lookup table first. Notice it says worksheet table. All right now this is the
easy part, both of the columns have to be country code. There is the many side, primary,
that means though one side. Now this is create
relationships. We're about to create that line
between these two tables, one to many relationship. But when I click OK something
interesting is going to happen. And what's going to happen
is going to happen all behind the scenes in Excel. When I click OK, the sales table
and the country lookup table will actually be imported
into something called the data model. The data model is a
behind the scenes location where it can store
tables and relationships. Now we're not going
to be able to see that but it is behind the scenes. And the amazing thing
about the data model for storing tables
and relationships is it's going to be dramatically
smaller in size, that means file size, than using VLOOKUP. Now I'm going to click OK. And you can see it's taking
a second there because it actually had to import those
into the data model, both the tables and the relationship. Now I will give you a behind
the scenes look at it later just to prove that it's there. But for most of our
versions of Excel, we're not going to have
access to seeing it. But there is our first
relationship, tables and relationship
in the data model. Now I'm going to click
new, select the sales table but wait a second. Look at that. There is our sales table and now
it doesn't say worksheet table, it says data model table. Yes indeed, that sales table is
now stored in the data model. Now I'm going to select that. And now when I come down to
select the product lookup table, notice right now
it's this worksheet table, but as soon as we create
the relationship, click OK, it will be converted
into a data model table. Now I'm going to go over to
the column and we want product. The product columns are
the two columns that will connect these two tables. Notice it says primary,
that's the one side, foreign, that's the many side. When I click OK this
product lookup table will be dumped
into the data model and there will be a relationship
between the two tables. Click OK there we go. Now I'm going to click
close and before we create our pivot
table I am going to show you the data model. Now the problem is that manage
data model button in the data ribbon tab, it may be grayed out
in your version of Excel 2016. But if it is grayed out you
still have the data model and you're still allowed to
create relationships and build a pivot table we're going to do. If your button is
grayed out it just means you can't go
look at the data model. Now, in order to have
this button not grayed out you have to have the
professional version of Excel 2016, either Office 365 Pro Plus
or Professional Office package. You can also have the
stand alone Excel. Now, actually manage data
model relationships are only in Excel 2013 and
16 and you have to have the professional
version of either Excel 2013 or 16 in order to
have these two buttons and to go and look
at the data model. Now, in our computer
labs at school where you don't have
the right version. But again, we
don't actually need to go look because our ability
to create relationships and use that relationship
in a pivot table will work in any version,
even if we can't go look at the data model. All right I'm going to go
click manage data model. And because I have the
professional version I can see sure enough
there is a preview of each one of the tables. Not only that, but
up in view, I'm going to click diagram view. And here is a
visual presentation of the relationships. There's the sales
table, many many, the line follows
to the one side. And you can see if I hover
over the relationship country code is connected. If I hover over the product,
the product is connected. Now, this view will be
exactly the same view we'll see when we get
over to study Access. One to many relationships
are very common and they simulate VLOOKUP. Now this is the PowerPivot
for Excel data model window. I'm going to close
this, and again you don't have to have
that ability to go look because those tables
and the relationships will be behind the scenes
in that data model. All right so we're ready. I'm going to go over to
the pivot table sheet and select cell A1. Now we go up to
insert pivot table or simply use our
keyboard, alt N V. Now I want you to
notice something that's totally awesome. It says use this
workbook's data model. That is the default
button that's selected if you actually have
some tables in your data model. Now even if for some reason
it came selected up here, you just come down
and select this. Now, I'm going to keep existing
worksheet cell A1, click OK. And that is so amazing. We can have multiple tables in
our pivot table field list task pane. Now remember I showed
you that extra table because all of
your Excel tables, whether they're
in the data model or they're not in
the data model, will show up in this
pivot table field list. Now how can you tell? Well it's easy. That little black line at
the top like the field names are highlighted in dark,
that means those tables are in the data model. Now I'm simply going to
expand, expand, expand. Pull down the
middle, pull this up, and now I can drag and drop
fields from any one of our look up tables or our sales table. I'm going to drag
country down to rows instantly and get a
unique list of countries. Drag product category down
below country in the rows area. Instantly I get a unique
list of product categories. Now I drag revenue over to
values, that is simply amazing. Now I don't like row labels
so I come up to design. Report layouts,
show, and tab them. I'm going to come over to More
button and choose a style. Right click in the values area,
went to number formatting. I'm going to select currency, 0
decimals, get rid of the sign, click OK. Click up in some of
revenue F2 and type the unit and control enter. I'm going to change
the column width. And that is amazing, we created
the same pivot table report without VLOOKUP, and we used
relationships and the data model. Now, I want to control
S to save this. And now I want to go
look at the file size and compare the VLOOKUP
file to this start file where we use relationships
and the data model. Here is the VLOOKUP,
exact same workbook, but with VLOOKUP
and our pivot table this is the start
file we just created. Same exact tables but
we used relationships and created our pivot tables. And look at the file size
difference, about 2 megabytes. Now this is the file size
difference for 73,000 rows. Now that's still a small data
set in the business world. In the business world we might
have 100,000, 500,000 rows. In fact in the class
after this, Business 2016, we'll have millions
of rows of data. And what happens is the file
size difference is amplified. The fact that we're using
relationships and the data model just has a
huge advantage when you have very large tables. Now, in this video, we saw
how to convert our proper data sets to Excel
tables, then we saw how to use relationships
to create relationships, and then when we invoked
the pivot table we saw that the pivot table knew
that there was a data model and we were able to
drive multiple fields from multiple tables and
create our pivot table. If you liked that video, be
sure to click that thumbs or leave a comment and
sub, because there's always lots more videos to come
from Excel Is Fun, including a few more Excel basics. All right we'll
see you next video.
