Cell Formatting

Quick reference

• 00:04 Let's look at adding some visual appeal to your report so
• 00:08 that people will actually bite into them, and it adds credibility.
• 00:12 Now the first thing that I'd like to do is take these report headers and
• 00:16 actually get them centered across this area.
• 00:18 Your initial temptation might be to go straight to merge and center.
• 00:22 The challenge is if you do this it's gonna go and wipe out a bunch of information and
• 00:26 merge cells to be honest with you cause all kinds of problems in Excel,
• 00:30 there's a better way.
• 00:31 What we're gonna do instead is right-click and go to format cells.
• 00:36 And under the alignment tab under horizontal,
• 00:39 you'll notice there is a center across selection.
• 00:41 And this is a command that, to be honest with you, I wished was actually up here.
• 00:45 I would prefer that you never ever use use a merge cell in Excel,
• 00:48 they're just horrible things that cause all kinds of issues.
• 00:51 We'll say OK,
• 00:52 and notice that it centers everything across the selection beautifully.
• 00:56 But the key thing you have to recognize about
• 00:58 these cells is that they look the same as merge cells but they act better.
• 01:04 Now when I say that though, there are some tricks to understanding what's going on.
• 01:07 If I select payments collected,
• 01:09 you'll notice in the formula bar there's nothing there, so what's going on?
• 01:14 Well when you look at it,
• 01:15 notice how there's no error lines in between these cells.
• 01:18 That means that this data is actually all the way in the leftmost cell,
• 01:23 it actually sits over here in B5.
• 01:26 Now if this were a merged cell, it would select the entire block, but right now,
• 01:29 it doesn't, it's just centered across this area.
• 01:32 So that's something to keep in mind,
• 01:34 I would definitely encourage you to use these over merge cells any time you can.
• 01:38 Now let's look at these headers,
• 01:40 let's see if we can format these to look a little bit different.
• 01:43 What I'm gonna do is start with a purple background and
• 01:48 then I wanna get a little bit more funky with this.
• 01:51 We gonna right-click and
• 01:52 we're gonna go into format cells because I can control everything from in here.
• 01:56 So I could do something like, rotate the text by 45 degrees,
• 02:01 if the cell is not big enough I could even wrap it.
• 02:05 On the font tab, I could say,
• 02:08 is a black font really gonna look good on a purple background?
• 02:11 No, I'd rather see a white font,
• 02:13 now this is where you're gonna see something happen here.
• 02:15 Notice that the Calibri sample disappears, I wish it'd pulled the color in here, but
• 02:19 unfortunately it doesn't.
• 02:20 But I'm pretty sure that white will look better on purple,
• 02:23 I'm even gonna set it to Bold.
• 02:25 I think I'm gonna add a border, black on the outside and
• 02:29 the inside of all the cells in that range.
• 02:32 And if I wanted to change the fill, I can do that here, when I say OK,
• 02:36 ooh that looks a little bit weird, something strange happened.
• 02:41 Let's go and turn off the text wrapping feature and
• 02:45 that looks a little nicer, so there you go, some things work, some things don't.
• 02:50 The color issue now, the columns are too wide, so let's narrow those up.
• 02:54 I'm gonna grab these guys, and I'm gonna double-click right between b and
• 02:58 c here on the line.
• 02:59 And it shortens everything up so that the column is the exact width it needs to show
• 03:03 the largest piece of data in the cell.
• 03:06 Now I'd also like to play around with this header, I'd like to
• 03:10 turn this 90 degrees and I'd like to center it across this vertical selection.
• 03:14 But one of the things that you will notice about this when I go to format cells and
• 03:18 I go to the alignment tab, unfortunately under vertical,
• 03:23 there is no center or cross-selection.
• 03:26 This is the one area where you actually have to merge cells,
• 03:29 this is the only reason to merge cells in Excel at all.
• 03:33 Once done, I can rotate the text 90 degrees, I could go and say,
• 03:38 hey, you know what?
• 03:39 Let's put this in a white font that's bold, let's make our border black,
• 03:44 let's change our fill to the same color purple.
• 03:47 And at this point, I can say, okay, and it's gonna rotate everything nicely.
• 03:51 Except that it's not centered, it's also not centered this way,
• 03:55 we can see some extra white space here.
• 03:57 So let's go back and fix that, right-click,
• 03:59 format cells, back to alignment, and
• 04:03 for our horizontal we'll center it, so that's gonna center it this way.
• 04:09 And for a vertical, we'll also center it, center it this way, and
• 04:13 now we'll say OK and it moves it up.
• 04:17 But the column's too wide, so let's just double-click on that to shrink it down,
• 04:20 whoa, that didn't shrink it down, what happened there?
• 04:23 Well the issue is, up above,
• 04:25 there's a large piece of text here around our header.
• 04:28 So this one, unfortunately, we're gonna have to do and shrink down manually
• 04:32 by left-clicking and dragging the border in order to get it into the right place.
• 04:36 And at that point, you've got data that's formatted differently, whether or
• 04:39 not it's better is really up to you.
• 04:41 But the key thing to remember here is you've got lots of granular control to
• 04:45 format things and make them look the way that you want.

Lesson notes are only available for subscribers.