Welcome to Office 2013 video project number 27.
If you want to download this workbook--
and this is video 23 to 27 workbook-- click on the link
below the video or go to our class website.
Hey, this is Excel.
We're still studying in Excel.
This is Excel basics number nine.
And we want to talk about keeping data
in Excel, either with a proper data set or the amazing Excel
table feature.
Now we've already been talking about proper data set.
In order to do pivot tables and sorting,
you have to have a proper data set.
So we've talked about this before,
but here will formalize it with these four characteristics
of a proper data set.
Field names in the first row.
So for this stock table example, each field name
says what kind of data gets to go in this column.
Now not only is it useful for us,
because when we're filling out each record,
we know to put the price in the column that says price.
Earnings per share in the column that says earnings per share.
But if we do pivot tables, we've seen already in the class
that these show up in our field list, which
allows us to drag and drop.
So field names in the first row.
No empty field names, right?
If you have an empty field name, it's no good for the person
using the table because then they
don't know what data goes there.
And then for pivot tables it's not going to work.
Records are in subsequent rows.
That just means you have a collection of related data.
So in this example it's for each stock we have.
Earlier in our example-- I'll scroll down here.
Here's our example we've been using throughout the class.
Each one is a different transaction, a different sale.
And down here we'll have a medical example
where we have collected different blood pressure
readings.
So each blood pressure reading is a new record
because it's a new recording for a new day.
Each record is a collection of related data.
Number four, try not to have empty cells in the record.
If you have, for example, an empty cell,
occasionally when you do a pivot table then
the calculation gets a little messed up.
It's easy enough to fix, but in a perfect world
you'd have no empty cells in any of your records.
Finally you must have empty cells all around your data set.
So even though that word names right there
looks like it's right next to the data set,
it's not, because all that data is sitting in cell B8.
It's just hanging out.
So this entire data set has empty cells all the way around.
You can't do sorting, or filtering, or pivot tables
if you don't have empty cells all the way around.
Notice I have this label up here,
but I'm not touching the data set.
Now the data sets can be touching row headers.
That means Excel row headers.
Or they could be touching the column headers up here.
But all the way around empty cells.
The thing that people do that messes up pivot tables
and sorting sometimes is maybe they have a note here.
Forget it.
You've got to have a notes column.
If you're going to have a note here,
then put it one, in this case, column to the right.
All right?
So those five things.
As long as we have those things, you can do amazing things.
Data ribbon, filtering, sorting, some other features here
we'll see later.
And we saw a couple videos ago we added our pivot table
button here in our data ribbon.
So those are the five rules for proper data set.
Now if we have a proper data set,
let's just remind ourselves.
Let's do our pivot table.
I'm going to click in a single cell.
Alt N V, as we learned a couple videos ago.
I'm going to put this in an existing-- actually
let's do this on a new worksheet.
We haven't done a new worksheet yet.
Our goal here is simply to do sales rep,
and then the total sales.
And we want to look at the field list
now that we know what a proper data set is.
So new worksheet, and then click OK.
Now notice there's going to be a new worksheet down here when
I click OK.
And immediately I'm going to double click this and call it
PT1 or something.
That's our pivot table one.
Here's the field list.
And if we didn't have a proper data set, how in the world
would we know which fields to drag where.
So proper data sets are amazing.
Sales rep, down to the rows.
Sales down to values.
All right, so that's just a quick example
of proper data sets.
We've got to have proper data sets
so we can do amazing things like pivot tables.
Let's go back over to Excel tables.
Of course, sorting.
So here's a great example.
I want to sort and get all of the biggest values to the top.
So right click Sort.
You could do it that way, notice A to Z. Or with a single cell
you could go up to data and use the A
to Z. Now that'll bring the smallest.
I think I said the biggest.
So I want Z to A. Just like that.
Proper data sets for doing data analysis.
Now we're going to see the Excel table feature.
This was added all the way back in 2003,
but it wasn't called a table until 2007.
So 710 and we're studying 13 here.
Here's what an Excel table will do.
Well first, you've got to have a proper data set.
You've got to field names at the top, and records, and rows.
But this is what it'll do.
It'll actually create dynamic ranges.
That means as we add new records, our formulas that
are pointing at our Excel table or we're going to make a chart.
The chart, if it's pointing at an Excel table, as we
add new records or in this case, blood pressure readings,
the chart will just update.
The formulas will just update.
As we add new records, notice the range would expand.
If we added one single record, it would expand there.
Add two extra records it would expand there.
So dynamic ranges.
That means we no longer have to keep creating new formulas
or charts every time we add new records.
It'll just happen automatically.
Table feature will add formatting.
It will also add Sorting and Filtering buttons to the top.
Here's how you do it.
Click in a single cell and go to Insert.
And we saw pivot table.
There's Table.
Now if you do tables a lot, the keyboard is right there.
Control T. So that's what I'm going to use,
Control T. It's polite.
It says hey, my table has headers.
If you don't have headers in your field names,
you better add them.
I'm going to click OK.
Now look at that.
There's the formatting just like that.
Now if you don't like the formatting,
you just come up to design and pick a different format.
But before we do anything, let's name this table.
We've been talking about naming in this class.
Naming our folders.
Naming our files.
Naming our sheet tabs.
So let's name this table.
Oh look.
As soon as we create a table, there's
a table tools context sensitive ribbon.
I'm going to click on Design, and all the way over
in Properties group table name.
Click.
And I'm going to call this blood pressure T,
and then I'm going to hit Enter.
That way if we make any formulas,
the formula will actually show the table name and the field
name.
It's quite amazing.
Now let's make a chart to start this off and see
this amazing dynamic range.
So I'm going to highlight only the first two
columns and the data.
And now I'm going to go up to Insert.
And over in the charts group, I'm going to say column.
Now just quickly I'm going to do some formatting.
Let's see if we can click on the line.
Sometimes it's hard.
Make sure it's not the square, but it's the lines.
You can use your arrow keys to cycle
through all the chart elements.
When you see the lines, Delete.
Let's click on the axis, Delete.
Let's come over to the pluses and say data labels.
And that's all we're going to do for here.
Now let's point to the corner, click, and drag.
All we're doing, the goal in this video
is just to see the table feature in action.
And what is it going to do?
It it's going to add new columns as I add new records.
Now there's a few ways you can add records.
You could can just click below.
I'm going to use the keyboard for today's date,
Control semicolon, tab.
Oh, look at that.
It actually carries the format forward.
I can already see that my chart is updated.
That is simply amazing.
Oh, this was a stressful day.
I'm in big trouble.
I'm back up at 149, and this was 93.
Now another way to add new records
is if you're in the last cell in the last record in your table,
Tab will add a new record.
And now this is going to be 10/29 Tab.
Finally back down 131, and this was 87.
That is simply amazing.
Now let's do a formula here.
Max.
I'm just going to put a label here, maybe go
up and do a little formatting.
Fill.
Font.
Border.
You know I could have come down here and gone right
click the mini toolbar, and then some
of the most common formatting features like Border,
All Borders.
All right, so now I want to do equals Max.
Oh, the Max function.
The Max function will tell us the biggest number in a range.
So when we see our screen tips for numbers,
I'm simply going to highlight.
Now when we highlight this, it will tell us
the table name and the field name.
And look at that, it's given us the field names
in square brackets.
That's called table formula nomenclature.
Just think of it as polite.
It's telling you hey, that's the table and that's the field.
Now we'll have to get used to these square brackets
for some formulas we make in Excel
and when we learn access, the database program for Microsoft.
When we build queries, we'll see that the field names are
going to be in square brackets.
Let's come over here and do Min.
And watch this.
I'm going to highlight and a right click
and point to my mini toolbar and get the paintbrush, or format
painter.
And that will allow me to click in some cell--
you can see the paintbrush there--
and it will copy just the formatting and place it there.
All right, now I'm going to do equals Min.
I want to do some strange here just
to show you how cool this Table feature is.
Notice this is J35 to J40.
Typing cell references is kind of risky.
I'm going to try it.
J35.
Immediately I can see it up there,
so that's kind of helpful.
And then colon J-- let's see, is it J39?
Let's say J39.
Oh wait a second.
It's not 39.
Typing is always tough for me.
40.
Oh, OK.
So it's looking at the same range.
I'm going to Control Enter, and then hit F2.
You notice it brings it up to capitals.
I just wanted to do that to show you
that this range will expand.
That 40 will turn to 41 when I start adding new records.
So on 10/30, it was 125, and this was 84.
Now I'm not going to hit Tab because I
don't want to add a new record.
I'm going to hit Enter.
That won't add a new record.
It's only tab.
And now let's look at the Max.
Oh, OK.
So that's not going to change as I add new records.
It's just polite.
It's saying hey, I got the blood pressure table and the field
right there.
F2 on this one.
But look at that.
Explicitly that formula is updating 35 now to 41.
So the Excel table feature can be quite amazing.
One other thing, you can use it to sort.
I think that's off the screen there.
I have to scroll down a little bit.
You can use it to sort, and later in the class
we'll see how to use Filter.
So formatting.
Drop downs for sorting, and filtering, and dynamic ranges.
That's the Excel table.
As long as you have a proper data set
with field names, records in rows,
you can convert it to an Excel table.
And we, of course, have our five rules for proper data set.
All right, we'll see you next video.