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.
Exercise files
Download this lesson’s related exercise files.
Cell Formatting - Begin.xlsx25.3 KB Cell Formatting - Complete.xlsx
25.5 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 ability 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 Now, the reality is in the real world that no matter how perfect your data
- 00:09 analysis is, it's not going to hold the same credibility if it looks ugly.
- 00:14 So we want to be able to format our data so it's more attractive and
- 00:17 that instantly gives it more credibility.
- 00:20 So I'm going to take this particular data range that we have here and
- 00:24 format it to be a little bit more readable.
- 00:26 And we're going to start by trying to expand these columns here so
- 00:30 that we can see all the data.
- 00:32 The fastest way to do this when you have multiple columns is to actually
- 00:35 select the columns.
- 00:37 Mouse up between the columns in the header to where you get this double pointed
- 00:41 arrow.
- 00:42 You're going to double click on one of those line separators.
- 00:45 And at this point, it opens up the column to the exact width it needs to display
- 00:49 the widest piece of text.
- 00:51 Of course, that's a little overkill for column B.
- 00:53 Its size based on for the month ended here.
- 00:55 So we're going to go and we're going to just narrow this one back down.
- 00:58 But the rest are all good now, which is what I want to see.
- 01:02 Now, the next step is that I actually want to center all of this data across these
- 01:06 multiple cells.
- 01:08 And what most people will do is they'll grab one of the lines here and
- 01:11 they'll reach for this Merge and Center button, okay?
- 01:14 There's a few different options across this thing for Merge and Center,
- 01:17 Merge Across, Merge Cells.
- 01:19 The only one you should ever really use is Unmerge cCells.
- 01:22 We want to avoid this at all costs because Merge Cells generally actually cause
- 01:26 problems for Excel.
- 01:27 This is a horrible feature.
- 01:28 Instead, what I'm going to do is I'm going to grab all these, okay?
- 01:33 We're going to go right click and choose to Format Cells.
- 01:37 This will bring up your favorite dialog, the Format Cells dialog.
- 01:40 We go to the Alignment tab and under Horizontal, we have an option for
- 01:44 Center Across Selection, which really should be the first option on this button.
- 01:48 When you click OK, it centers it across the selected cells, and that's great.
- 01:53 The tricky bit is though, when you select cell D5,
- 01:56 if this was merged, it would actually select the entire block.
- 02:00 But it doesn't, because you can still select D5.
- 02:03 But notice there's no data in it.
- 02:05 If I go to C5, there's no data.
- 02:07 If I go to B5, that's where the data lives.
- 02:10 It just happens to be centered across the selected cells.
- 02:12 So just need to know how this actually works and
- 02:15 why it might look a little different.
- 02:17 I'm going to go and select these now, change this to bold,
- 02:20 increase the font size just a little bit.
- 02:22 There we go, that looks better.
- 02:24 And now, I'm going to go and deal with this little header over here for
- 02:27 testing services.
- 02:28 What I want to do with this is I want to turn this on its side and
- 02:32 have it vertically centered across here.
- 02:34 The problem is there's no center across a vertical selection.
- 02:38 This is the one area where merging cells is important.
- 02:42 So what I'm going to do is I'm going to press Ctrl+1,
- 02:45 that's the keyboard shortcut to bring up the Format Cells dialog.
- 02:48 And in this one, I'm going to merge cells.
- 02:52 I am going to use a Center Horizontal Alignment and
- 02:55 a Center Vertical Alignment as well.
- 02:59 I'm also going to rotate this text by 90 degrees.
- 03:03 And when I say OK, what we get is testing services right in the middle, so
- 03:07 that's perfect.
- 03:08 I'm going to go and just move this one around.
- 03:10 Maybe I'll make it bold so it's a little bit easier to read.
- 03:14 I'm now going to go and take this even a little bit further.
- 03:17 What I'm going to do is I'm going to go and I'm going to add some color to this.
- 03:21 So once again, I'm going to press Ctrl+1 to go back into my Format Cells dialogue,
- 03:25 because I can do all of this stuff in the same place.
- 03:27 So I'm going to come over to font.
- 03:30 I'm going to change my font color to white.
- 03:34 I'm going to go to my filt tab and change the fill color to this purple.
- 03:40 And then I think I'm also going to go and put a border on.
- 03:43 I'm going to change the colour of the border to white and use an outline border.
- 03:48 And now, I'm going to say OK, and when we take a look at it,
- 03:51 we've got a nice purple header there with a white border.
- 03:55 Let's do the same thing for these guys but
- 03:57 we're going to actually up this one up a little further.
- 04:00 So once again, Ctrl+1 to get me into my Format Cells.
- 04:04 I'm going to go with white borders, outline to get all the way around them.
- 04:08 I wish you could see that.
- 04:09 Unfortunately, you can't.
- 04:11 We're going to go with purple on this one here, so that's cool.
- 04:15 We'll go back to the font.
- 04:17 Let's change our font color to white, make these bold.
- 04:22 There we go, that looks good.
- 04:23 And now on my Alignment tab, I'm going to rotate this to 45 degrees and say, OK.
- 04:30 And what you see is that we get something that looks like this.
- 04:33 I don't feel that I've got my borders quite setup right so
- 04:36 I'm going to go back into Format Cells here for a second.
- 04:39 I want a white line between these, so I'm going to go back to the border.
- 04:42 And what I realized is that I clicked outline.
- 04:44 I actually need the inside borders to get the lines in the middle as well.
- 04:48 So we'll click inside and OK.
- 04:51 And at that point, I've got a report that doesn't look too shabby.
- 04:54 So we've got a nice setup of data there that's actually displayed in a more
- 04:58 attractive way with some different formatting options.
- 05:01 The biggest thing is really to think about what you want your report to look like.
- 05:04 And I can guarantee you that once you do that,
- 05:07 you can find the formatting commands in here somewhere to make it happen.
Lesson notes are only available for subscribers.