How to use excel named ranges in Tableau | Tableau tutorial

0:00In a previous video about how to learn

0:01Tableau, one of the skills I mentioned that

0:03was really

0:03vital was having a better than average

0:05understanding of Microsoft Excel.

0:08Unfortunately in this video,

0:09I'm going to be showing you one of those

0:10skills that I think is going to save your

0:12bacon next

0:12time you get a difficult data set inside of

0:14Excel. Let's get stuck in. Okay, so here I

0:17've got an excel

0:19table, I've just generated the data from

0:21Mockaroo, I'll include a link to this file

0:23in the description of this video if you

0:25want to download it and have a go at it

0:26yourself.

0:27Now the issue with this data set is less

0:30about the data structure, actually each of

0:32these four

0:33tables have exactly the same information. I

0:35've deliberately sort of cooked it that way

0:37just to

0:38keep this example simple, but nonetheless

0:40you can see there's a potential issue. If I

0:42try to connect

0:43to this in Tableau, this is what it ends up

0:45looking like. I can't really do much with

0:47this,

0:47you can see that the column names are sort

0:49of everywhere and also it's kind of lost

0:51its structure

0:51that you could kind of see visually in

0:53Excel but you can't really see it when you

0:55connect to it in

0:56Tableau. So how can we solve this? Well

0:58there's one feature in Excel that can

1:00actually save your

1:01bacon here and I don't really encourage

1:03people jump to this as a first solution.

1:06Typically I

1:06try and understand how did the data get to

1:08this step because there's obviously someone

1:10preparing

1:10the data in this manner because they

1:12believe it's useful and so if you can

1:14actually work with that

1:15person to understand where they get their

1:17raw data from then it's going to be a much

1:19better approach

1:19to go and get the data from that place

1:21rather than using this Excel file as a

1:23mechanism. But if you

1:24only have an Excel file you only have

1:26access to it and this is the only way you

1:27're going to be able

1:28to do this and this isn't a bad solution.

1:30The solution here is a feature called named

1:32ranges

1:33in Excel. Essentially what named ranges

1:35allow you to do is to define tables inside

1:37of a single sheet.

1:38So the dilemma we have here is that inside

1:41of Tableau we essentially can't pick out

1:43each of

1:43these individual tables when we connect to

1:45the data. But if you look at this sheet you

1:47can see

1:48that I've actually got three fairly visible

1:50tables. You can see this one here is a

1:52table,

1:53this one in orange, this one in gray and I

1:56've also got what looks like an ad hoc

1:58addition to the data

1:59set over here on the right hand side, on

2:01the left hand side even. And so we can

2:04actually use the

2:05name ranges feature to allow us to

2:07structure this data a little bit and then

2:09in Tableau we can bring

2:10in those name ranges and use it like they

2:12're separate connections inside of Tableau.

2:14If we

2:14go up here to the formulas tab and let me

2:16just open that fully so you can see it,

2:19click on define

2:19name you'll see that we actually get a

2:21table that tells us how many named ranges

2:24we already have. We

2:25have three named ranges, Table 1, Table 2

2:27and Table 3. If I actually just bring this

2:29over here

2:30and we click on these dots you can see that

2:32when I click on that particular item it

2:34tells me how

2:35that particular cell looks like. So you can

2:38see here mock data 2 it's at k16 and k16 is

2:41simply

2:41the column and the row. Now if I go and

2:43highlight this for example you'll see that

2:45it actually

2:46defines a table and you can see this is

2:47actually called Table 1 and it's basically

2:50everything in

2:50Table 1. If I go back to that menu you can

2:52see that as I click through these they've

2:54all got

2:55a definition and those definitions are

2:57actually the starting and finishing points

2:59of a particular

3:00table. Tableau can actually understand

3:02these when it opens an excel file it

3:03actually reads this. I'll

3:05show you this and this is true in Tableau

3:07prep and Tableau desktop as well. So if I

3:09go to this bottom

3:10table you'll see this is not formatted as a

3:12table so there's a couple ways to do this.

3:14The first way

3:15is just to highlight this little range and

3:17then go to define name. It will actually

3:19give you the

3:20ability to sort of give it a definition and

3:22again you just want to make sure that it's

3:24highlighting

3:25the right thing. So you can see here it

3:27says a28 so if I go to 8 you can see that

3:28it starts at a28

3:30and then it goes all the way to f33. So it

3:33's essentially defining the top left corner

3:35and

3:36the bottom right corner of the table. That

3:38's how this is defined and then when we

3:40click ok you'll

3:40see that a named range appears. If I go

3:42back to define name you'll see that it's

3:44there and it's

3:44called id. Because I didn't give it a name

3:47it used the very first column essentially

3:49the a28 field

3:51to give it a file name. So now that that's

3:53done you'll see we have four tables in here

3:54and now

3:55we're pretty much ready to go and hop over

3:57into Tableau so we can make this one nice

3:59clean data

4:00set. Let's hit ok and switch over to Table

4:03au. Okay now I'm here in Tableau and what I

4:05'm going to do

4:06is I'm going to close this window because

4:07what I'd done is I'd previously connected

4:09to the file

4:10and when I connected to the file in that

4:12instance you can see that actually the

4:14three tables that

4:14are highlighted before that were already

4:16tables are actually here already and you

4:18can see that

4:19these tables have a slightly different icon

4:22on the left hand side. This is the signal

4:24to you,

4:24this is a named range. You might even see

4:26this with the sample superstore file

4:28sometimes when

4:28you connect you'll see orders twice and you

4:30'll see returns twice. This is because they

4:33've also

4:33set that up as a named range so you can use

4:35the named range or you can just use the

4:37whole sheet

4:38in excel. In essence a named range acts

4:40like an individual connection inside of

4:43that particular

4:44sheet so you can actually have multiple

4:46named ranges inside of one sheet in excel

4:48and bring

4:49these in as separate connections and even

4:51do things like create relationships, union

4:53them or

4:53join them to make the data set work. So now

4:56you know this let's see this happening in

4:58practice.

4:58Now you'll notice I added a forts table and

5:00that was a very deliberate thing because

5:02what I did

5:03there is I worked on a file while the excel

5:06was open so let's go back and make sure

5:08this is saved.

5:09I'll save this and what I'd like to do

5:10especially when I'm working with excel and

5:12Tableau is if I

5:13no longer need the excel file I'm just

5:15going to close it because I don't need it

5:18sort of

5:18interfering. Essentially there is this

5:20issue sometimes that if you leave Tableau

5:22open in

5:22excel open at the same time sometimes Table

5:24au doesn't get the freshest data from the

5:26excel

5:26file so you just want to close it to make

5:27sure it's doing the right thing and then

5:29what I'm

5:29going to do is go back in here click edit

5:32connection and reconnect to that file just

5:34to make sure I've got the latest version of

5:36that data and when I do that just give it a

5:38second.

5:39Hopefully we should get the connections

5:40coming through properly and there you go

5:42you can actually

5:42see it's appeared. Now in some cases this

5:45won't actually work the reason this won't

5:47work is

5:48because essentially Tableau can sometimes

5:50cache the excel file in a hidden folder on

5:53your computer

5:54and in that instance what you actually have

5:55to do is close Tableau and open it again

5:57because when

5:57you close it it kind of clears out the

5:59cache and then everything works fine. The

6:01other thing you

6:02can do is save the excel file as a

6:03different file name and that also tends to

6:05trigger the cache to

6:07update itself because it perceives it as a

6:08new excel file so you don't have to worry

6:10too much

6:10about that there. But nonetheless you can

6:13see here I now have the four different data

6:15sets ready to

6:16go and I can start working with them. So

6:18whereas before I can actually show you what

6:20the data

6:20looked like this is what the original data

6:22set looked like when I connected it

6:23straight to the

6:24sheet not using any named ranges we've

6:27added one additional named range called id

6:29which was the

6:30additional set of data that we had and now

6:32what I can do is I can actually work with

6:34these

6:34individually and notice that as I bring

6:36them in they look like tables they actually

6:38work normally

6:39and they behave as normal and what I can

6:41actually do in this particular case I'm

6:42going to convert

6:43this to a union and what Tableau will ask

6:46me to do is hey can you add the other

6:47things you expect

6:49to do that in this union so once I've

6:51converted that to union we get this little

6:53icon here

6:54so then now I can drag everything else that

6:56's supposed to be part of that data set

6:58right into

6:59this hit apply hit ok and there we go we've

7:02got our beautiful data set and I didn't

7:04have to go

7:05and sit there and copy and paste things

7:07into a sort of different format we can go

7:09ahead to sheet

7:10one and I have a nice beautiful clean data

7:13set and for the record if we open this up

7:15this all looks

7:16perfectly great and we also have some

7:18context the named range maintains its name

7:21so you can see here

7:22that we actually know which named range it

7:25came from here in the source data Tableau

7:27adds a field

7:28called table name because essentially we've

7:30done a union so that tells us which table

7:32that union

7:33set of data has come from so we can

7:35actually use that in our data set if we

7:37want to distinguish

7:38where the data came from or add it as

7:40context but nonetheless we have one

7:42beautiful sort of set of

7:43data for the record these are not real data

7:45it's mock data so I'm not sharing anyone's

7:48ip address

7:48or name or email or anything like that now

7:51the final thing to do is to just be sure

7:53that named

7:54ranges are a little bit tricky you need to

7:56make sure that essentially you keep the

7:58data within

7:59that range and so what I tend to do is

8:01again I avoid using this because it's not

8:03resilient under

8:04really sort of typical work styles in most

8:07teams essentially named ranges should be

8:10used where the

8:11data is being dynamically updated in excel

8:14and there's no other way of getting out the

8:16data so

8:17essentially the use case here is you've got

8:19the file in the SharePoint someone goes

8:21into that

8:21excel file opens some weird macro that does

8:24something loads up a particular set of data

8:26into that excel file once that data is

8:28loaded there is a sort of another macro in

8:30excel that's

8:31reading that data and generating a lookup

8:34table of some sort on another sheet and

8:36then that's what

8:37you want to connect to because it contains

8:38some context for something you're going to

8:40use in table

8:41it's really painful you should ideally just

8:43go get tableau prep or altrix or something

8:45like that to

8:46do this but nonetheless that's essentially

8:48how it looks like in tableau desktop now

8:50let me show you

8:51how it looks like in tableau prep very

8:53quickly before we end this video okay so I

8:55'm here in

8:55tableau prep I'm just going to go ahead and

8:57connect to the exact same excel file let's

8:59go over here

9:00and click on the connection and just bring

9:02in the same excel file that we've got here

9:03my name ranges

9:04folder very one at the top and now that we

9:07're here when we connect to it you'll see

9:09that again we

9:10have those ranges over here on the left

9:12hand side so they've got the same icon it

9:14looks slightly

9:15different it's just it's less of a sort of

9:17a colored icon and it's more of a sort of a

9:21linear

9:21icon but nonetheless it's all the same

9:24information it's all the same data so now

9:26we can do exactly

9:27the same thing and again you can just sort

9:29of drag these in as you would it's exactly

9:32the same

9:32as before the flexibility you have with

9:34tableau prep is of course that you can kind

9:37of get away

9:38with adding things to this union just by

9:40sort of taking advantage of the way the

9:43interface works so

9:44you're literally bringing in four

9:46connections and it keeps them as separate

9:48connections so

9:48whereas in tableau we sort of relied on

9:50bringing them together at the connection

9:52stage in tableau

9:53prep here you could actually do things to

9:55each individual one before they come

9:56together because

9:57you've got a little bit more control and of

9:59course we've got exactly the same final

10:01output here with

10:02one large table now the table names come

10:04across slightly different because that

10:06includes the excel

10:08file name but nonetheless you have the same

10:10sort of flexibility you could sort of pass

10:12this out

10:12everything after the forward slash just get

10:14rid of everything before that keep

10:16everything after

10:17that and you're pretty much good to go so

10:19that's named ranges in excel helping out in

10:22tableau both

10:22prep and tableau desktop as well and

10:24hopefully you found this video useful as

10:26ever if you've

10:27liked this hit the subscribe button hit the

10:29like button share this far and wide let

10:31people know

10:32how much time they're going to save with

10:33this particular feature i'll catch you in

10:35the next one