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.xlsx10.3 KB Cell Formatting - Completed.xlsx
10.8 KB Cell Formatting - Extra Practice.xlsx
15.7 KB
Quick reference
Topic
Cell formatting.
Description
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:03 Now once you've worked really hard on getting your spreadsheet set up the last thing that you
- 00:07 always want to do before you release it to someone is to beautify it, prettify it
- 00:11 make it look a little bit more attractive and inviting. So I'm going to show you a
- 00:15 few different techniques that we can use to do that. Starting off with our headers
- 00:19 up the top here what I'm going to do is I'm going to centre these across my selection here. This is different than merging cells
- 00:27 it's actually much better than merging cells. Right click and go in to Format Cells and
- 00:32 go to Alignment and under my Horizontal Alignment there's a Center Across Selection.
- 00:38 The reason why I say this is better than merging cells is because merging cells causes all kinds of problems inside Excel. There are certain
- 00:45 instances were you do need them but as a general rule Center Across Selection is going to be much better
- 00:52 it's not going to have problems when you copy and paste and unmerging and things like that.
- 00:56 So Center Across Selection will work for us nicely and it will center it right across columns B through F for all of these.
- 01:04 While we're in the format cells dialog box I'm also going to go in to Font, I'm going to choose to set this to a 14 point font in Bold
- 01:12 and that should do for this particular selection.
- 01:14 And say ok so I get this nice header here.
- 01:18 Now we can also put another header on this particular table and with this we'll get a little bit more fancy.
- 01:23 We'll right click say Format Cells again
- 01:26 and this time we're going to fool around a little bit with the alignment of our text. We'll set this to 45°
- 01:35 and we'll also go back to our Font tab and we'll set this to Bold and sure, why not let's put it in Italics, so Bold Italics that will work.
- 01:44 We'll go choose a Border, let's go with an Outline and Inside so we'll get all the borders involved in this thing here.
- 01:52 And we'll go and throw a nice light purple fill on it, why not, just to be something different and we'll say OK. And
- 01:59 this time we get some real funky looking little headers that might work a little bit better if we're trying to actually narrow our data.
- 02:07 You can see when we actually go and select all these columns here if I double click between the rows or between the columns rather
- 02:15 it will actually shrink things up. Now
- 02:17 column B
- 02:19 is actually expanding because Cell Formatting is the longest piece of text in here but because
- 02:23 that can spillover we really don't need all this white space.
- 02:27 We can go and drag this back and narrow it up and by virtue of the fact that our text is actually going across to the right here we can actually
- 02:36 have stuff that expands over so this actually works quite nicely for this.
- 02:41 Now here is the trick where we get into some other pieces so if we wanted to actually have this centered vertically and
- 02:48 horizontally in this particular area maybe we'd want to try and wrap the text first.
- 02:53 Right click and Format Cells
- 02:55 go to Alignment and Wrap text.
- 02:59 What we can see is this breaks on to two lines which could be good except that it gives us a lot of white space in here and we don't really want
- 03:07 that so if I wanted to actually center this vertically this is an instance where I have to
- 03:12 use merge cells. So I'm going to right click on this now and go to Format Cells again.
- 03:17 This time we're going to Merge cells and while I'm here I think I'm going to tilt this up 90°.
- 03:22 We'll also center it horizontally
- 03:25 and vertically in the box. Make sure that it shows right up in the middle and while we're here
- 03:31 we'll throw a border on it and fill with a nice purple inside as well so it looks the same.
- 03:37 We should probably throw a Bold font on there as well.
- 03:40 When we say OK it now actually looks pretty good that way too.
- 03:45 So this is one way you can sort of narrow up your data.
- 03:49 It does kind of look a little funny up here though I think I'm going to expand this just a tiny bit more.
- 03:53 Click Format Cells
- 03:55 expand the Center Across Selection a little bit further
- 03:59 at this point when I actually go and say OK it should now center it across selection all the way over to here so looks like it's a bit more
- 04:05 centered on top of my angular columns here.
- 04:10 Just like that so it look a little bit better. It's a little bit more flair and pizzazz to a report that we're
- 04:14 trying to build so that hopefully users will be a little more engaged in it
- 04:19 and can really want to examine it a little bit more.
Lesson notes are only available for subscribers.