Cell Formatting

Quick reference

• 00:04 In order to get people to appreciate our work more sometimes we want to apply
• 00:07 a level of formatting to our data to actually make it look a little bit better,
• 00:11 a little bit more presentable.
• 00:13 So we're going to go through with a set of data to try and do exactly that.
• 00:18 The first thing I'd like to do is I'd like to actually center
• 00:21 these particular items across this selection of area here.
• 00:25 Now you might be tempted to go to the Merge & Center button.
• 00:28 But this is actually bad news for Excel for a lot of reasons.
• 00:33 Merging cells can be problematic especially when copying and
• 00:36 pasting things.
• 00:37 Try to unmerge and it becomes a bit of a problem.
• 00:39 We actually have a better way, by right-clicking, and going to Format Cells.
• 00:45 If you go to the Alignment tab, what you'll notice is that there's a horizontal
• 00:49 alignment drop down, and one of those options is Center Across Selection.
• 00:54 And when we say okay, it centers the cells across or
• 00:57 centers the data across the selected cells.
• 01:00 The key piece to be aware of here is that even though the data appears to be in some
• 01:05 of these cells, you'll notice the formula bar is empty.
• 01:07 That data started in cell B, and that's why it's centered across.
• 01:12 So for this, you want to recognize that those lines that are showing
• 01:15 the individual columns, they're missing.
• 01:17 And that tells you how to get all the way back to the left-hand side.
• 01:21 We could of course take these and increase the font size,
• 01:24 make it a little bit bigger and bolder so that looks kinda nice.
• 01:27 And now we can work on the next level of headers.
• 01:31 Wouldn't it be neat if we could take something like this, and
• 01:34 add some color and rotate the text a little bit?
• 01:37 So let's try that.
• 01:38 Let's right-click.
• 01:39 We'll say Format Cells and we'll go to the Fill tab.
• 01:44 Let's use a purple fill and we'll put a black border around and inside.
• 01:50 So we can apply lots of different formatting here.
• 01:53 We'll set it to a white font,
• 01:55 which unfortunately doesn't show in the preview here.
• 01:57 I wish it pulled over the fill color in here as well but
• 02:00 the white font on purple background should be readable.
• 02:03 Then we'll go to Alignment and
• 02:05 what we're gonna do is we're gonna get a little tricky with this.
• 02:07 We're gonna grab this little handle, we're gonna rotate it up to 45 degrees.
• 02:12 When we say OK, you'll notice that things all flip sideways for us.
• 02:18 Now, that looks kind of neat, but I'd also like to center the names across here.
• 02:24 So we're going to right-click, go to Format Cells, and
• 02:27 we're going to use a center from the Horizontal Alignment.
• 02:32 And that moves our street addresses and stuff into the right place.
• 02:36 Now, at this point, we notice we've got a bit of extra space in this area.
• 02:39 So what we'll do is we'll highlight these columns, and
• 02:42 we'll just double-click on a line between a column.
• 02:44 And you'll notice that it shortens it up to the minimum width required.
• 02:48 So, that's kinda nice.
• 02:50 We could, obviously, change individual ones if we wanted.
• 02:53 But the double-click action will always shrink a column to the minimum
• 02:58 required space that's required to show the longest item.
• 03:01 Now the last thing I want to do is I'd like to center this,
• 03:05 I'd like to turn it sideways and center it across these cells.
• 03:09 This one becomes a bit more of a challenge.
• 03:11 We can't use Center Across selection on a vertical basis.
• 03:15 This is one instance where we have to use Merge Cells.
• 03:18 So let's try and do that, we'll right-click.
• 03:21 We'll say, format cells.
• 03:23 Bring this back online here.
• 03:25 We're gonna go and set things up the same as we did for the other header.
• 03:28 So, purple fill, we'll put a border around it.
• 03:31 We'll go with a white font.
• 03:34 And now, what we're gonna do is we're gonna say, let's flip the text 90 degrees.
• 03:41 We'll merge our cells, and
• 03:43 then what we'll do is we'll choose to center vertically in this area.
• 03:49 And you know what, while we're at it, why don't we also center horizontally.
• 03:53 With the merging done here, this allows us to actually flip it.
• 03:56 While we can flip the text 90 degrees without merging, but
• 03:59 it would just show in one cell and it would make it really, really tall.
• 04:03 But by merging these cells here, we can now have center-across selection
• 04:07 on a vertical basis, because it's not available that way otherwise.
• 04:10 So we'll say OK, and you'll notice that it looks good now.
• 04:14 Let's try and narrow this column down, I'll double-click.
• 04:17 And wait a minute, it's expanded because self-formatting is the longest piece.
• 04:21 We don't want that, so we're gonna drag this a little bit shorter,
• 04:24 to make our table look a little bit nicer.
• 04:26 So there we go, we've applied some nice formatting.
• 04:29 And we've used the Format Cells dialogue to
• 04:32 do it because that's where all of our more advanced formatting tools actually live.

Lesson notes are only available for subscribers.