Excel Formatting Techniques for Polished Data

Formatting. In this section, we're going to take a look at various formatting tools you can use to make your data look great. There are several mini topics we'll go over in this section, so I'll move through them briefly.

You can always look at the video again or slow it down if you need to focus on a particular area, if you feel like I'm moving a little too quickly through it. Since there's a lot of information to go over, let's get started. So number one, when it comes to formats, we have the ability to change the format of a cell as it relates to its cell color, its font color, the style of the font, and the size as well.

So let's take a look at three different ways you can do this on the PC. So the ribbon. If I select the cell and I want to change the formatting of this particular cell, I can go to the Home tab, and over in the Font group on the ribbon, I can change the background color.

I can go and change the font color, maybe to red. Then I'll make the text bold and italic. I'll click the drop-down, and then I'll choose a different font style.

I'm going to go for Snap ITC. Also, I want to change the size. I can increase the size by clicking the letter A with the upward-pointing triangle, or decrease the size by clicking on the letter A with the downward-pointing triangle.

I could also do this using another method called the mini toolbar. When I select the cell, all I have to do is right-click. This option is not going to be available on the Mac.

For that, you'll use the third option, which we'll take a look at in a moment. But if I right-click on the PC, I don't have to go all the way up to the ribbon. Everything I need to perform the same formatting as in the first cell is right here.

Yellow background, red text, bold, and italic. I can click the drop-down, and I'll look for Snap ITC. I can increase the font size.

I can decrease the font size. So this might be useful if you're towards the bottom of the spreadsheet and you want to be able to format the cells without going all the way up to the ribbon. Now, formatting cells using the dialog box: you can use several different methods to get there.

You can press Control+1 on the PC, and then you will open up the Format Cells dialog box. I'm going to go over to Font, and in here, I can change the font color to red. I can make the text bold and italic at the same time.

I can go over to the font area and look for Snap ITC. I'll just have to alphabetically look for it, and there it is right here. I can change the size.

I also want to change the background color, so I'll go to Fill, and I'll choose yellow. If I click OK, then I've performed that formatting right there. On the Mac, you'll use the Format Cells menu, or you can press Command+1.

Something else you can change when it comes to your cell content is the alignment. This is going to be located over in the Alignment group. So in the Alignment group, I can align the text to the left.

I can align it to the right. I can align the text to the top and also the bottom. If I want, I can move it to the center and middle in terms of vertical positioning.

Something you may not be aware you can do: you can change the angle of the text. I can angle it counterclockwise. I can angle it clockwise.

I can make the text vertical. I can rotate the text up, and I can also rotate the text down. This is perfect for situations where we might want to squeeze in the header if the text is a little too wide.

I'm going to go back and unselect the last option, and now we're back here. Resizing columns and rows: This says to make this column 9.0. So the assumption is it's not 9.0. Let's actually see what it is.

I'll right-click here on letter F and choose Column Width. The column width is 12.13. Now I can go here and type 9.0. As soon as I click OK, I've made that column a little smaller. I could have also gone to the edge of the column letter and looked for the double arrows pointing left and right, then clicked and dragged to resize any individual column.

As I click and drag, I can see the measurement, and I can resize it to an exact amount. Auto fit: You might want this column to fit the size of the text that's in it.

So you can go over between column H and I and just double-click, but you have to be careful because the column will resize to the size of the largest text string in that column. Well, let's take a chance and see what's going to happen here. I'll double-click.

Well, apparently it looks like this text is not the largest text string. If I look carefully, it's actually the text up here, NYC Career Centers, that the column is auto fitting to. So be aware of this.

Down here, I see “football players overlap text.” That sounds weird. If I look in the formula bar, it actually says, “Football players who have an injury are more likely to be re-injured in the next six months, according to a recent study.”

But I can't see the text here because the text labeled "overlap text" is actually overlapping the other text. So what I'll need to do is go over to the Alignment group and choose the option for Wrap Text. This will wrap long text into multiple lines so I can see it in that cell.

Now there's all the text. Now, as a result of what I just did, the row height just increased. If I don't like that, I might choose to click Wrap Text again, so it goes back to its default size. And maybe a better option is just to move this text out of the way so it's not overlapping.

Indenting. There are situations where I might want to indent some text so I can show relationships between topics and subtopics. So what I'm going to do is select Subtopic One and Subtopic Two. And then I'm going to head over to the Alignment group and choose the option to Increase Indent.

I'll do the same thing for Subtopic One, Two, and Three. Increase Indent. And now people who are looking at that particular column can see the relationships between topics and subtopics.

Our next topic, going up and to the right, is formatting. These are formats for numbers. So these are actually number formatting.

I'm going to go over to Number and I want to change this to a number format. It's not a number format currently, and I can tell because if I go over to the Home tab and look at the Number group, it says General. I'll click the drop-down and choose Number.

The cool thing about this is I get a preview of how the value will appear when I choose Number format. It's going to add a decimal and two zeros. I want to change this to currency formatting.

So I'll click the drop-down and choose Currency. Again, I see the preview there. And just so you can really notice that you are actually getting the preview of the content that's in the cell, if I go to Currency when there's a $0 value, when I click the drop-down, I see $0 values in the currency drop-down.

Now I want to change this value to Accounting formatting. That is not Currency. Something you may not be aware of is this icon doesn't stand for Currency.

It's actually Accounting formatting. So I don't even have to click the drop-down. I can just click here and that adds Accounting formatting.

In Accounting formatting, the dollar sign moves all the way over to the left. Reasons I've heard for that being the case is you may not want to mistake the dollar sign for a five and think you're looking at 51,000 instead of 1,325. When you're working with Accounting formatting and you're using $0 values, they don't like zeros.

So they turn zeros to dashes. This is today's date. This is also today's date.

I want to see today's date in a raw format. So I'll click the drop-down and choose General. Today’s date is 43,900.

A nice even number. So what is 43,900? What you may not know is Excel needed to come up with a beginning computer date for Excel. The beginning computer date is January 1, 1900.

Today is 43,900 — actually, 43,899 — days away from January 1, 1900. That's why when you subtract dates in Excel, you actually get the exact number of days, because you're not subtracting months, days, and years.

You're actually subtracting numbers. Just a quick way to show you this: if I type 1/1/1900, if that is the first day, if I change it to General formatting, I should see 1, and that is the first day.

Percentage. This is thirteen and a quarter percent in its raw numerical format. I'll click the drop-down here, choose Percentage, and now it actually looks like thirteen and a quarter percent.

Zip codes. We often run into a situation where we type a zip code that begins with zero. If I start with 07101, when I press ENTER, Excel says, I'm going to help you out. You actually meant to type 7,101.

You don't need that leading zero. But what I'm trying to tell Excel is that this is a zip code. It's not seven thousand.

So there is a special format for that. If you click the drop-down, it's not going to be available in the drop-down list. You're going to go to More Number Formats and then you're going to go over to Special because it's a special format, and select Zip Code.

You can see in the preview the leading zero is kept. I'll click OK and there it is. There's also a phone number format in the same location.

I'll go back to More Number Formats and go to Special and there I'll find the Phone Number format. I'll click OK. Very simple keyboard shortcuts to format text.

If I want to make text bold, I'll press Control+B. If I want to change the text to italic format, press Control+I. And if I want to add an underline, press Control+U.

A couple more topics to go. I want to put a border around this text, so I'll select the text. I'm going to head over to the Font group and I'm going to click the drop-down.

I can choose the type of border that adds a border on the outside. So that's going to be Outside Borders. One click, and then I'll just need to step away from the cell so I can actually see the border applied.

Finally, we're going to take a look at merging. I may want to create a centered title above a large table and I want all the cells to be merged. So I'll select all the cells going across.

Then the very first simple option to choose from in the Alignment group is Merge & Center. With one click, I've merged all four cells and the text is centered at the top. Now I want to merge these four cells into one row, and also these four cells into one row, and also these four cells into one row.

But I don't want to do this individually. So there is an option that will allow you to merge across. If I go to Merge & Center and click the drop-down, I'll see Merge Across. With one click, I am not merging up and down — I'm only merging across. That makes it much simpler to merge multiple rows.

Then over here, I would like to merge all 12 cells so that it’s one big box. So you'll click the drop-down and choose Merge Cells. Now everything is one big box, and if you want to, you can adjust the alignment so that you can move the text maybe to the center and also to the middle.

The only other option we didn't look at is Unmerge — and you know what that does. It takes cells that are merged and unmerges them so you can see the individual cells. So in this section, we reviewed some of the formatting options that are available to you in Excel.

We didn't go over all of them, but this gives you a general sense of what's possible from some very common options.