Preview
Video Player
00:00
00:00
Formatting the Spreadsheet
7:28Use best practices to format our spreadsheet project.
Let's get started with
formatting a spreadsheet.
0:00
The first thing I'm gonna do is, I'm gonna
delete these top four rows because I think
0:03
they're distracting and redundant.
0:08
Then, the next thing I'm gonna do is,
0:12
I'm going to expand these two columns so
I can see the names.
0:15
And actually I don't need column A, so
I'm just gonna delete that as well.
0:20
Well I had all those rows,
those columns selected.
0:25
So okay, delete column A.
0:28
All right, then I'm going to freeze
the top pane, so I can see a year.
0:30
And I'm also gonna freeze column A.
0:35
So if I scroll left to right and
up to down,
0:39
I can see what year and
what category we're looking at.
0:42
I'm only interested in
the major categories.
0:45
There's a lot of detailed line items in
here that are certainly informative and
0:47
could be useful for other purposes.
0:53
Then the example I'm using today,
for example,
0:56
how much Americans spend on sports and
recreational vehicles in different years.
0:59
You can see how much that's increased.
1:05
Wow, there's musical instruments,
jewelry and watches, educational books.
1:10
There's lots of detailed information here,
but I don't need this level of detail for
1:16
the purposes of what we
are trying to accomplish today.
1:21
So I wanna go through and
1:25
I'm gonna delete the different
categories that I'm not interested in.
1:26
So I can instead of selecting each one and
1:32
deleting them individually,
I can use the Shift key.
1:36
And click the top one and
1:41
then go down to the bottom just like
you can do with cells for selecting.
1:42
I'm going to right click and delete.
1:46
There's a lot of rows in here,
so I'm gonna go ahead and
1:51
delete all the extra stuff and we're gonna
skip ahead to after I've done all of that.
1:54
Okay, so I went ahead and deleted all
the extra rows that we don't need for
1:59
the purposes of what
we're working on today.
2:03
Next, I'm going to change all
these numbers to be blue,
2:06
because they are mainly inputted or hard
coded numbers and I want to signal that.
2:10
Even though I've imported
them from another source,
2:20
they're still manually inputted.
2:22
They're not derived from other formulas.
2:24
So again, I'm using the Shift button and
the Cmd button.
2:26
I'm pressing down to select
the entire column, and
2:32
then I'm going to go to the left.
2:35
Now I don't want to select
things in column A.
2:36
So I'm gonna press right once,
while still holding down Shift and Cmd.
2:39
Actually, I have to let go of Cmd.
2:45
So, Shift, Cmd left, let go of Cmd.
2:47
Press right once, and
that gets me to column B.
2:52
And I want these to be numbers.
2:56
And I want to round them so
there are no decimal places showing.
3:02
Okay, next I'm going to
format these rows so
3:08
that the text in column A are all
lined up with each other.
3:12
Here I have a very large amount of text,
so
3:18
I'm gonna actually press Return and
shorten it down a little bit.
3:21
So instead of spelling out final
consumption expenditures of nonprofit
3:24
institutions serving households,
which I guess there's an acronym for
3:28
that, NPISH Non Profit
Institution Serving Households.
3:34
I'm going to hold Shift and
press Alt to skip from word to word.
3:40
So I don't have to go and
use my arrows if I wasn't pressing Alt,
3:45
I'd have to use arrows to go all
the way through each letter.
3:47
But if I hold down Alt while Shift,
I can get over from gap to gap.
3:51
So I'm gonna delete that.
3:56
Press Alt to navigate again.
4:00
Delete that.
4:02
I'm also gonna shorten final
consumption expenditures,
4:04
I'm gonna shorten consumption
expenditures to just be CE.
4:06
And also it's not lined up with the other
categories, so I wanna press Return again,
4:12
Alt, left, left, left, left, space,
space, space, space to line it up.
4:18
If you're in a rush you don't have to
do that but I want it to look good so
4:24
I'm gonna make sure they line up.
4:27
And the main category of
personal consumption expenditures
4:30
is indented all the way.
4:33
I don't want it to be lined
up with the other rows.
4:35
So I'm going to delete the entire
space there, and get it to that spot.
4:37
Okay, so we're almost there, but there's
a few other changes I wanna make to this.
4:42
So first I wanna collapse this pane, so
4:47
that it's not as wide as it is right now
cuz it doesn't need to be that wide.
4:50
I'm actually gonna change this to be f and
b cuz that's pretty well known.
4:56
And I'll shorten it
again a little bit more.
5:04
Now there are a lot more
columns than there are rows.
5:07
And for the purposes of the analysis we're
doing today, I think it makes a lot more
5:15
sense to have the years as rows and
the columns as these different categories.
5:19
Because we'll probably only see
much more on the screen at one time
5:27
than it is set up right now.
5:31
So the way we do this is we
select everything in a sheet,
5:32
we copy it, and
then we're gonna transpose.
5:37
So we're gonna go to Paste special and
we're gonna Paste transpose.
5:41
And you see how there's these underlines
here, those are keyboard shortcuts.
5:46
So if I press E,
it just transposed the data.
5:51
Transposing basically flips
the axes that you're looking at.
5:56
So everything that was along the Y axis or
the X axis gets switched, so
6:01
now it's along the X axis or Y axis.
6:04
Or in other words, everything that was
a column header becomes a row, and
6:07
everything that was a row
becomes a column header.
6:12
So we've transposed the data,
we've got column headers.
6:16
And I'm actually gonna make these bold.
6:22
And I can't see what
the names are right now,
6:25
and I'm gonna do a few more things here.
6:27
So I'm going to do what's
known as text wrapping.
6:29
And that means that, instead of
the text flowing into the other cells,
6:35
it'll come back down and
you can see it all.
6:40
And, I'm gonna try to shorten up some
of these names so I can get them.
6:43
All looking pretty.
6:49
So I'm gonna go through
here a little bit and
6:51
clean up these title headers,
and I'll be right back.
6:57
All right, now I've finished
cleaning up the data header rows so
7:01
that they're less chaotic.
7:05
And you can see that it's
kinda more intuitive for us to
7:08
scroll top to bottom across the years here
than it is all the way to left to right.
7:13
It's a bit easier, there's less columns
and generally easier to look at the data.
7:17
This spreadsheet's now in a place where
we can begin adding some functions and
7:22
formulas.
7:27