1 00:00:00,620 --> 00:00:02,690 Let's get started with formatting a spreadsheet. 2 00:00:03,740 --> 00:00:08,011 The first thing I'm gonna do is, I'm gonna delete these top four rows because I think 3 00:00:08,011 --> 00:00:09,987 they're distracting and redundant. 4 00:00:12,962 --> 00:00:15,610 Then, the next thing I'm gonna do is, 5 00:00:15,610 --> 00:00:19,719 I'm going to expand these two columns so I can see the names. 6 00:00:20,860 --> 00:00:24,610 And actually I don't need column A, so I'm just gonna delete that as well. 7 00:00:25,770 --> 00:00:28,010 Well I had all those rows, those columns selected. 8 00:00:28,010 --> 00:00:29,290 So okay, delete column A. 9 00:00:30,780 --> 00:00:35,726 All right, then I'm going to freeze the top pane, so I can see a year. 10 00:00:35,726 --> 00:00:39,130 And I'm also gonna freeze column A. 11 00:00:39,130 --> 00:00:42,310 So if I scroll left to right and up to down, 12 00:00:42,310 --> 00:00:45,440 I can see what year and what category we're looking at. 13 00:00:45,440 --> 00:00:47,930 I'm only interested in the major categories. 14 00:00:47,930 --> 00:00:53,570 There's a lot of detailed line items in here that are certainly informative and 15 00:00:53,570 --> 00:00:56,760 could be useful for other purposes. 16 00:00:56,760 --> 00:00:59,660 Then the example I'm using today, for example, 17 00:00:59,660 --> 00:01:05,720 how much Americans spend on sports and recreational vehicles in different years. 18 00:01:05,720 --> 00:01:07,940 You can see how much that's increased. 19 00:01:10,080 --> 00:01:16,470 Wow, there's musical instruments, jewelry and watches, educational books. 20 00:01:16,470 --> 00:01:21,540 There's lots of detailed information here, but I don't need this level of detail for 21 00:01:21,540 --> 00:01:23,960 the purposes of what we are trying to accomplish today. 22 00:01:25,180 --> 00:01:26,640 So I wanna go through and 23 00:01:26,640 --> 00:01:31,170 I'm gonna delete the different categories that I'm not interested in. 24 00:01:32,590 --> 00:01:36,096 So I can instead of selecting each one and 25 00:01:36,096 --> 00:01:41,510 deleting them individually, I can use the Shift key. 26 00:01:41,510 --> 00:01:42,970 And click the top one and 27 00:01:42,970 --> 00:01:45,810 then go down to the bottom just like you can do with cells for selecting. 28 00:01:46,900 --> 00:01:50,090 I'm going to right click and delete. 29 00:01:51,580 --> 00:01:54,320 There's a lot of rows in here, so I'm gonna go ahead and 30 00:01:54,320 --> 00:01:58,680 delete all the extra stuff and we're gonna skip ahead to after I've done all of that. 31 00:01:59,760 --> 00:02:03,340 Okay, so I went ahead and deleted all the extra rows that we don't need for 32 00:02:03,340 --> 00:02:06,080 the purposes of what we're working on today. 33 00:02:06,080 --> 00:02:10,800 Next, I'm going to change all these numbers to be blue, 34 00:02:10,800 --> 00:02:18,010 because they are mainly inputted or hard coded numbers and I want to signal that. 35 00:02:20,740 --> 00:02:22,990 Even though I've imported them from another source, 36 00:02:22,990 --> 00:02:24,400 they're still manually inputted. 37 00:02:24,400 --> 00:02:26,240 They're not derived from other formulas. 38 00:02:26,240 --> 00:02:32,620 So again, I'm using the Shift button and the Cmd button. 39 00:02:32,620 --> 00:02:35,230 I'm pressing down to select the entire column, and 40 00:02:35,230 --> 00:02:36,890 then I'm going to go to the left. 41 00:02:36,890 --> 00:02:39,228 Now I don't want to select things in column A. 42 00:02:39,228 --> 00:02:42,690 So I'm gonna press right once, while still holding down Shift and Cmd. 43 00:02:45,510 --> 00:02:47,290 Actually, I have to let go of Cmd. 44 00:02:47,290 --> 00:02:52,070 So, Shift, Cmd left, let go of Cmd. 45 00:02:52,070 --> 00:02:55,060 Press right once, and that gets me to column B. 46 00:02:56,510 --> 00:02:58,605 And I want these to be numbers. 47 00:03:02,788 --> 00:03:05,830 And I want to round them so there are no decimal places showing. 48 00:03:08,824 --> 00:03:12,303 Okay, next I'm going to format these rows so 49 00:03:12,303 --> 00:03:16,710 that the text in column A are all lined up with each other. 50 00:03:18,380 --> 00:03:21,550 Here I have a very large amount of text, so 51 00:03:21,550 --> 00:03:24,730 I'm gonna actually press Return and shorten it down a little bit. 52 00:03:24,730 --> 00:03:28,880 So instead of spelling out final consumption expenditures of nonprofit 53 00:03:28,880 --> 00:03:34,240 institutions serving households, which I guess there's an acronym for 54 00:03:34,240 --> 00:03:39,530 that, NPISH Non Profit Institution Serving Households. 55 00:03:40,630 --> 00:03:44,000 I'm going to hold Shift and press Alt to skip from word to word. 56 00:03:45,270 --> 00:03:47,970 So I don't have to go and use my arrows if I wasn't pressing Alt, 57 00:03:47,970 --> 00:03:51,340 I'd have to use arrows to go all the way through each letter. 58 00:03:51,340 --> 00:03:55,840 But if I hold down Alt while Shift, I can get over from gap to gap. 59 00:03:56,900 --> 00:03:58,010 So I'm gonna delete that. 60 00:04:00,020 --> 00:04:02,040 Press Alt to navigate again. 61 00:04:02,040 --> 00:04:02,570 Delete that. 62 00:04:04,060 --> 00:04:06,867 I'm also gonna shorten final consumption expenditures, 63 00:04:06,867 --> 00:04:09,739 I'm gonna shorten consumption expenditures to just be CE. 64 00:04:12,197 --> 00:04:18,659 And also it's not lined up with the other categories, so I wanna press Return again, 65 00:04:18,659 --> 00:04:24,700 Alt, left, left, left, left, space, space, space, space to line it up. 66 00:04:24,700 --> 00:04:27,780 If you're in a rush you don't have to do that but I want it to look good so 67 00:04:27,780 --> 00:04:29,190 I'm gonna make sure they line up. 68 00:04:30,430 --> 00:04:33,290 And the main category of personal consumption expenditures 69 00:04:33,290 --> 00:04:34,320 is indented all the way. 70 00:04:35,450 --> 00:04:37,830 I don't want it to be lined up with the other rows. 71 00:04:37,830 --> 00:04:42,720 So I'm going to delete the entire space there, and get it to that spot. 72 00:04:42,720 --> 00:04:47,060 Okay, so we're almost there, but there's a few other changes I wanna make to this. 73 00:04:47,060 --> 00:04:50,893 So first I wanna collapse this pane, so 74 00:04:50,893 --> 00:04:55,600 that it's not as wide as it is right now cuz it doesn't need to be that wide. 75 00:04:56,630 --> 00:05:00,910 I'm actually gonna change this to be f and b cuz that's pretty well known. 76 00:05:04,310 --> 00:05:06,110 And I'll shorten it again a little bit more. 77 00:05:07,690 --> 00:05:14,165 Now there are a lot more columns than there are rows. 78 00:05:15,375 --> 00:05:19,775 And for the purposes of the analysis we're doing today, I think it makes a lot more 79 00:05:19,775 --> 00:05:27,330 sense to have the years as rows and the columns as these different categories. 80 00:05:27,330 --> 00:05:31,280 Because we'll probably only see much more on the screen at one time 81 00:05:31,280 --> 00:05:32,830 than it is set up right now. 82 00:05:32,830 --> 00:05:37,507 So the way we do this is we select everything in a sheet, 83 00:05:37,507 --> 00:05:41,490 we copy it, and then we're gonna transpose. 84 00:05:41,490 --> 00:05:46,080 So we're gonna go to Paste special and we're gonna Paste transpose. 85 00:05:46,080 --> 00:05:50,610 And you see how there's these underlines here, those are keyboard shortcuts. 86 00:05:51,690 --> 00:05:56,400 So if I press E, it just transposed the data. 87 00:05:56,400 --> 00:06:01,100 Transposing basically flips the axes that you're looking at. 88 00:06:01,100 --> 00:06:04,880 So everything that was along the Y axis or the X axis gets switched, so 89 00:06:04,880 --> 00:06:07,490 now it's along the X axis or Y axis. 90 00:06:07,490 --> 00:06:12,390 Or in other words, everything that was a column header becomes a row, and 91 00:06:12,390 --> 00:06:14,460 everything that was a row becomes a column header. 92 00:06:16,010 --> 00:06:22,360 So we've transposed the data, we've got column headers. 93 00:06:22,360 --> 00:06:24,130 And I'm actually gonna make these bold. 94 00:06:25,190 --> 00:06:27,470 And I can't see what the names are right now, 95 00:06:27,470 --> 00:06:29,640 and I'm gonna do a few more things here. 96 00:06:29,640 --> 00:06:34,470 So I'm going to do what's known as text wrapping. 97 00:06:35,740 --> 00:06:40,070 And that means that, instead of the text flowing into the other cells, 98 00:06:40,070 --> 00:06:42,540 it'll come back down and you can see it all. 99 00:06:43,950 --> 00:06:49,920 And, I'm gonna try to shorten up some of these names so I can get them. 100 00:06:49,920 --> 00:06:51,800 All looking pretty. 101 00:06:51,800 --> 00:06:57,180 So I'm gonna go through here a little bit and 102 00:06:57,180 --> 00:07:00,740 clean up these title headers, and I'll be right back. 103 00:07:01,830 --> 00:07:05,380 All right, now I've finished cleaning up the data header rows so 104 00:07:05,380 --> 00:07:08,220 that they're less chaotic. 105 00:07:08,220 --> 00:07:13,370 And you can see that it's kinda more intuitive for us to 106 00:07:13,370 --> 00:07:17,940 scroll top to bottom across the years here than it is all the way to left to right. 107 00:07:17,940 --> 00:07:22,770 It's a bit easier, there's less columns and generally easier to look at the data. 108 00:07:22,770 --> 00:07:27,210 This spreadsheet's now in a place where we can begin adding some functions and 109 00:07:27,210 --> 00:07:27,800 formulas.