Locked lesson.
About this lesson
Learn to work with the Format Cells dialog to apply text rotation and borders, and to center data across multiple cells.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Cell Formatting.xlsx24.2 KB Cell Formatting - Completed.xlsx
24.4 KB
Quick reference
Cell Formatting
Working with the Format Cells dialog to apply text rotation and borders, as well as centering data across multiple cells.
When to use
Having a report is one thing, but if it looks ugly, no one is going to want to engage with it. The format cells dialog contains the abilities to apply more advanced formatting than the Ribbon’s shortcuts allow, in an effort to make your reports truly grab your audience.
Instructions
Setting up the report header
- Select the data in B3:H5, right click, choose Format Cells, and select the Alignment tab
- From the Horizontal dropdown, choose “Center Across Selection”
- From the Font tab, set to Bold, 14 point and click OK
Setting up the table header
- Select the data in B7:F7, right click, choose Format Cells, and select the Alignment tab
- From the Horizontal dropdown, drag the orientation indicator to 45 degrees
- From the Font tab, set to Bold
- From the Border tab, click Outline and Inside
- From the Fill tab, choose a light purple fill and click OK
Shrink the table columns
- Select columns B:G and double click one of the lines between the column header
- Drag Column B to a width of approximately 8
Setting up the row header
- Select A8:A13, right click, choose Format Cells, and select the Alignment tab
- Set the Horizontal and Vertical alignment to Center, click Merge cells, and rotate the text to 90 degrees
- From the Border tab, click Outline
- From the Fill tab, choose a light purple fill and click OK
- 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.