Formatting the Spreadsheet7:28 with Michael Watson
Use 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
You need to sign up for Treehouse in order to download course files.Sign up