MOS Excel Crash Course Part 2: Manage Data in Cells and Ranges - YouTube
Primary Language: English
My Recent Work
You did not do any work on this video
Subtitles in: English
Start End Subtitles
00:00:00 00:00:02 >> Mike: Hey guys this is Mike and I'm gonna
00:00:02 00:00:07 be showing you the Excel MOS Exam 77-727 Crash Course
00:00:07 00:00:10 I created and this is gonna be part two.
00:00:10 00:00:13 In this video I'm gonna show you how to manage data cells
00:00:13 00:00:16 and ranges which is going to be about 15 to 20%
00:00:16 00:00:19 of your exam and I'm specifically gonna show you how
00:00:19 00:00:20 to insert data and cells
00:00:20 00:00:23 and ranges, format cells and ranges.
00:00:23 00:00:26 And lastly how to summarize and organize data.
00:00:26 00:00:28 So when it comes to inserting data in cells
00:00:28 00:00:29 and ranges one of the most common skills
00:00:29 00:00:34 that you'll be asked in your exam is to AutoFill a formula
00:00:35 00:00:37 and AutoFill the formula
00:00:37 00:00:40 down without changing the formatting.
00:00:40 00:00:41 So if we look in this example
00:00:41 00:00:46 and you see that the totals row has a top border
00:00:46 00:00:48 and a double bottom border.
00:00:48 00:00:51 And I'm asked to copy this formula down.
00:00:53 00:00:56 So if I copy this down all the way to the totals area
00:00:57 00:00:58 or the totals row.
00:01:00 00:01:03 You see that that gets sort deleted.
00:01:03 00:01:06 The formula got copied down so that's one part
00:01:06 00:01:07 of the answer.
00:01:07 00:01:10 But the next part is fill without formatting
00:01:10 00:01:13 or you might be asked to just fill
00:01:13 00:01:15 out the formula without changing any
00:01:15 00:01:16 of the formatting options.
00:01:16 00:01:17 So the way you do that is click
00:01:17 00:01:20 this little AutoFill Options drop arrow
00:01:20 00:01:23 and then remember to click Fill Without Formatting
00:01:23 00:01:25 and that way we copy the formula down.
00:01:25 00:01:27 But we didn't change the formatting
00:01:27 00:01:28 in any way.
00:01:28 00:01:29 When it comes to the skill
00:01:29 00:01:31 of formatting cells and ranges.
00:01:31 00:01:33 There's two skills that you'll most likely be asked
00:01:33 00:01:37 to perform and that is one the Merge and Center
00:01:37 00:01:38 and then how to Wrap Text.
00:01:38 00:01:42 So if I wanna Merge and Center this title across
00:01:43 00:01:45 from columns A to I,
00:01:45 00:01:47 I would highlight the row
00:01:47 00:01:49 in row one column eight I
00:01:49 00:01:51 and then I could simply press Merge and Center
00:01:51 00:01:53 if I was asked to.
00:01:53 00:01:55 But I feel like that's too easy for this type of exam.
00:01:55 00:01:58 So you might be asked to do something a little bit different
00:01:58 00:02:02 where you're asked to merge the cells but keep the typing
00:02:02 00:02:04 or the text on the left and the way
00:02:04 00:02:06 you do that as press Merge Across.
00:02:06 00:02:09 So you either might be asked to do Merge and Center
00:02:09 00:02:11 which centers the text or Merge Across
00:02:11 00:02:14 which would keep the text on the left side
00:02:14 00:02:18 but still merge the cells and are the columns
00:02:18 00:02:19 in this row.
00:02:20 00:02:22 A second skill that you'll be asked
00:02:22 00:02:25 to do is when it comes to formatting cells
00:02:25 00:02:27 and ranges as to Wrap Text.
00:02:27 00:02:29 And the reason you would do that if you had
00:02:29 00:02:31 an example like the text on the right
00:02:31 00:02:35 here which didn't fit perfectly into the L column
00:02:35 00:02:40 and you just simply highlight that that cell L3
00:02:40 00:02:43 and then wrap text and now kind of just fits
00:02:43 00:02:45 on multiple lines so if you're asked to put something
00:02:45 00:02:49 on multiple lines that's the keyword for Wrap Text.
00:02:49 00:02:51 So when it comes to summarizing
00:02:51 00:02:53 and organizing data the two main skills
00:02:53 00:02:56 that you have to know for this section is how
00:02:56 00:03:00 to insert Sparklines and also subtotals.
00:03:00 00:03:02 So I'm gonna show you first how to do Sparklines
00:03:02 00:03:03 and then we'll move on to subtotal.
00:03:03 00:03:07 So in the Insert tab let's say I wanna get a Sparkline
00:03:07 00:03:09 or a visual graph that fits
00:03:09 00:03:13 within the cell here representing the students marks.
00:03:13 00:03:15 So I would click in this cell.
00:03:15 00:03:16 We'll get the first cell first
00:03:16 00:03:17 and then we'll copy the formula down
00:03:17 00:03:19 or the Sparklines down.
00:03:19 00:03:22 And then in the Insert tab there's this Sparkline group.
00:03:22 00:03:25 So be careful and read the instructions carefully
00:03:25 00:03:27 on your exam if it's just a normal Sparkline.
00:03:27 00:03:28 That's the line Sparkline.
00:03:28 00:03:32 But there are other types like Column or Win or Loss.
00:03:32 00:03:35 So if you're asked to put in this Column Sparkline
00:03:35 00:03:39 which I think we should do just for practice then do it.
00:03:39 00:03:42 But if it's just a normal Sparkline
00:03:42 00:03:42 and doesn't say anything
00:03:42 00:03:45 else then it's just this Line Sparkline which tends
00:03:45 00:03:47 to be the default Sparkline.
00:03:47 00:03:51 So we're gonna do a Column Sparkline on this first one.
00:03:52 00:03:54 We're gonna choose our data range
00:03:54 00:03:58 from all of the assignments and quizzes in this course
00:03:58 00:03:59 and press okay.
00:03:59 00:04:01 And that's what our Sparkline looks like.
00:04:01 00:04:03 You might also be asked to copy this down
00:04:03 00:04:07 to represent all students and I'll use the fill handle
00:04:07 00:04:08 to do that.
00:04:09 00:04:12 And now we have like a little visual graph
00:04:12 00:04:13 within these cells, all right.
00:04:13 00:04:16 So the last scope we're gonna do is subtotals
00:04:16 00:04:18 and let's say at each change in cities
00:04:18 00:04:21 so there's three cities here Vancouver, Toronto, Montreal.
00:04:21 00:04:25 I wanna know the subtotal of the amount of each city
00:04:25 00:04:27 and then a grand total at the end.
00:04:27 00:04:31 So on your exam you probably won't have to sort these cities
00:04:31 00:04:34 but just in case whenever you're doing subtotals
00:04:34 00:04:37 and you want to show a change
00:04:37 00:04:39 in each something
00:04:39 00:04:42 whether it's a city like in this example you have
00:04:42 00:04:44 to sort these into groups first.
00:04:44 00:04:46 So I'm just gonna highlight this range
00:04:46 00:04:48 and then in the Data tab click Sort.
00:04:48 00:04:51 Doesn't matter which order I sought these in.
00:04:51 00:04:53 I'm just gonna sort them in alphabetical order
00:04:53 00:04:55 and other group Montreal, Toronto, Vancouver
00:04:55 00:04:57 and now I can do subtotals.
00:04:57 00:04:59 And also notice this is on a table you can't
00:04:59 00:05:02 do a subtotal on a table so you have
00:05:02 00:05:03 to make sure this is a structured range.
00:05:03 00:05:07 Like this example now I'm gonna highlight all my values
00:05:07 00:05:11 and then I'm gonna go to the Outline group
00:05:11 00:05:13 in the Data tab and click subtotal
00:05:13 00:05:18 and I have to make sure that I have the right functions.
00:05:18 00:05:21 So I want the sum or the subtotal amount
00:05:21 00:05:24 of the amount column,
00:05:24 00:05:25 so you could choose a function
00:05:25 00:05:28 you get the average but sum is more common.
00:05:28 00:05:30 At each change in like we said before city.
00:05:30 00:05:34 So wherever the groups are and that's at each change
00:05:35 00:05:39 and then I wanna show the money amount below obviously
00:05:39 00:05:41 and then money amount columns
00:05:41 00:05:44 so that's where you want your subtotal to show
00:05:44 00:05:47 and sometimes you'll be asked to replace current subtotals
00:05:47 00:05:50 if there was an existing subtotal you might have the option
00:05:50 00:05:54 of summary to show a summary below the data
00:05:54 00:05:56 which is pretty common but you also might be asked
00:05:56 00:05:59 to show a Page Break between groups
00:05:59 00:06:01 which would mean the Toronto subtotals would
00:06:01 00:06:04 be on one page Montreal would be on another and so on.
00:06:04 00:06:07 So if we choose that option this is what it would look like.
00:06:08 00:06:12 Okay, so there's a Page Break here, Page Break here.
00:06:12 00:06:15 Grand total here and it might tell you
00:06:15 00:06:16 where the grand total should be.
00:06:16 00:06:19 So just make sure it's in the right column
00:06:19 00:06:21 and that's how you do subtotals.
00:06:21 00:06:24 So remember to check a video one from this Crash Course
00:06:24 00:06:26 and if you wanna know more about formulas
00:06:26 00:06:29 and functions and Excel you can also check out this video.
00:06:29 00:06:30 Thanks for watching.
Copy and paste the embed code above
Copy and paste the embed code above