Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Well done!
You have completed Spreadsheet Basics!
You have completed Spreadsheet Basics!
Preview
Use best practices to format our spreadsheet project.
This video doesn't have any notes.
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
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
You need to sign up for Treehouse in order to download course files.
Sign upYou need to sign up for Treehouse in order to set up Workspace
Sign up