(upbeat music)
- Hello, I am professor Rick Jerz
and I welcome you to this session titled,
Multiple Worksheets, Prepping and Cleaning Data,
and Named Ranges in Tableau.
In this session, we are not going to learn
how to do any new visualizations.
Instead, I plan to give you some tips
of how to work with Excel data in Tableau.
I think that you are going to enjoy some of these tips.
Let's get started.
Think back to our earlier exercise
when we were trying to create frequency diagrams,
histograms, and some of the other common statistics charts.
We had a subtle problem.
Maybe you noticed it.
When we were trying to create our qualitative charts,
we dragged our M&M color worksheet data
into Tableau's workspace.
Then, when we wanted to create our quantitative charts,
we started a new Tableau visualization
and we dragged the M&M weight worksheet
into our Tableau work area.
I had provided to you an Excel file
that had multiple worksheets, and we created
each of these Tableau visualizations each individually.
Some of you who did decide to share your visualizations
with other students uploaded two Tableau files,
one for quantitative data, and one for qualitative data.
Some of you might have wondered,
is there a method to use multiple Excel worksheets
within one Tableau file.
Let me first show you the problem.
If I connect to this Excel file
and drag the M&M colors worksheet into Tableau work area,
and then drag the M&M weights worksheet data
into the same area, Tableau thinks that we somehow
want to combine the data from these two worksheets,
but no, we don't want to do this.
We want to be able to make visualizations
from each of these two worksheets,
so this process of dragging more than one worksheet
into Tableau's work area does not work.
How do we work with multiple Excel worksheets
in one Tableau file?
Let me show you how.
First, I connect to this Excel file.
I drag the M&M colors worksheet into Tableau's work area.
Next, click on this little drop down arrow
next to the little data icon in Tableau
and pick new data source.
Then simply reconnect to the same Excel file.
Now I will drag the M&M weights worksheet
into this Tableau work area, and that's about it.
We are now working with two worksheets
in the same Excel file.
Let me show you what we have accomplished.
I'm going to click on sheet one,
and look at this upper left area near the data tab.
I have two data sources in this one Tableau file.
Right now, I am working with the M&M weight data.
But if I wanted to work with the M&M colors worksheet,
all I would have to do is click on M&M colors.
Notice that both the dimensions and measures
contain different data
depending upon which data source you select.
Of course we are not limited to just two Excel worksheets.
We could repeat this process
for as many worksheets that we would want to work with.
We could also use this process
if we wanted to work with more than one Excel file
within one Tableau file.
Just add a new data source
and connect to your other Excel files.
This technique of working with multiple Excel files
or multiple worksheets within any Excel files
is a subtle technique that is not very obvious,
so I thought I would show it to you.
Next, I'd like to share with you a few comments
about prepping and cleaning data.
Sometimes we might encounter a problem
bringing Excel data into Tableau
when our Excel data has a heading.
Let me show you the problem.
I connect to my Excel M&M data for Tableau file,
and in this case, I'm going to drag
the M&M weights raw worksheet into my Tableau work area.
When I do so, I notice that the column headings
for this Excel data in Tableau are not quite correct.
Why?
Let's return to our Excel worksheet
and see what this data looks like.
Ah, look at this.
I have a heading in my Excel file,
and this is causing Tableau to have problems
interpreting our data.
How do fix this?
Tableau does have a utility, seen right here
on the data source tab called use data interpreter.
If I click on this, somewhat magically,
Tableau cleans my data.
It gets rid of these heading lines,
and Tableau now sees proper column headings
for this Excel data.
This use data interpreter utility
might help you clean up other kinds of data problems
when you are bringing Excel data into Tableau.
Remember to give it a try and see if it helps.
I do want to mention that Tableau
has a special utility called Tableau prep
that is described on Tableau's website.
I haven't used this utility,
and I'm not sure whether or not we could get
a student license for it,
so you might have to explore this utility on your own.
Hey, let's see what happens if we try to bring in
our M&M weights all data from our Excel model's file.
Remember, this data is in our M&M weights
worksheet right here.
Might Tableau's use data interpreter help us?
Let's give it a try.
I bring the M&M weights in,
and I click on use data interpreter.
And it looks like Tableau has done
oh maybe a fair job cleaning up data.
I would probably have to delete or hide
these columns not of interest,
and there's a lot of columns over to the right
that I have to get rid of.
This seems like a lot of work,
so I think when you face this kind of problem,
there is a better solution.
What I suggest doing is to just copy and paste your data
of interest into a new worksheet.
Then bring this worksheet into Tableau.
This seems to me to be a little bit faster
than trying to use Tableau's use data interpreter
to clean up my data.
In fact, I think that in general,
it is better to format your Excel data
before bringing it into Tableau
because most of us are faster with Excel than with Tableau.
Okay, I will end this session by mentioning
one more important thing that some of you might have noticed
when I connected to my Excel model's file.
Tableau sees Excel's named ranges.
Let me show you.
I connect to my Excel model's file,
and look at this.
In my Excel file, I had purposely named
the M&M weight data as a defined name range.
So if I want to use this data,
all I need to do is to drag it into Tableau's work area.
That's it.
Likewise, if I want to use my data,
which is in our descriptive statistics model,
all that I have to do is drag my data
into Tableau's work area.
That Tableau sees Excel's defined name ranges,
I believe, is a very important feature of Tableau.
Give it a try.
(upbeat music)
Thanks for taking time to spend some moments with me.
I hope that you have enjoyed what I have shared with you.
If you ever have any questions or need my help,
please let me know.
(upbeat music)