In this section, we're going to take a look at various formatting tools to make your data look great.
Formatting
There are many topics we'll go over in this section so I'll move through them briefly. you can always look at the video again and 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.
Color, Font, Size
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, also the style of the font, as well the size. so let's take a look at three different ways you can do this on the PC. Let’s start with 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 gonna go for snap by ITC.
Also, I want to change the size. I can increase the size by clicking on the letter A with the triangle pointing up or decrease the size by clicking on the letter A and the triangle will be pointing down. I could also do this using another method called the mini toolbar. When I select this cell all I have to do is right-click. this option is not going to be available on the Mac. For that, it'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. actually everything I need to perform the same formatting I did 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. 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.
Dialogue Box
Now, formatting cells using the dialog box you can use several different methods to get there. you can press control one on the PC and then you will open up the format cells dialog box. Now I'm going to go over to the 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 a 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 it 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.
Alignment
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 that 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 gonna 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 the column width. the column width is 12.1 3. so now I can go here and type 9.0 and as soon as I click OK I've made that column a little smaller. I could have also gone to the edge of that column letter and looked for the double arrows pointing east and west and click and drag to resize any individual column. As I click and drag I can see the measurement and I can resize it to an exact amount.
Autofit
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 gonna happen. here I'll double click all apparently it looks like this text is not the largest text string and if I look carefully it's actually the text up here, NYC career centers, that the column is auto fitting to. be aware of that down.
Here I see football players overlapped 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 reinjured in the next 6 months according to a recent study but I can't see the text here because the text that says overlap text is actually overlapping the text. 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. 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. what I'm going to do is select subtopic 1 and subtopic 2 and then I'm gonna 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.
Number Formatting
Our next topic going up and to the right is formats these are formats for numbers so these are actually number formatting I'm gonna 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 cuz 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 what the value is actually gonna look like when I choose number format. it's gonna add a decimal in two zeros. I want to change this to currency formatting so I'll click the drop-down and choose currency. 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 zero dollar value when I click the dropdown I see zero dollar 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. 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. The reason I've heard for that being the case is you may not want to mistake the dollar sign for five and think you're looking at 51,000. you might mistake the currency amount instead of 1325. when you're working with accounting formatting and you're using zero dollar values they don't like zeros so they turn zeros to dashes.
Date Formatting
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 forty-three thousand nine hundred, a nice even number so what is forty-three thousand nine hundred. what you may not know is Excel needed to come up with a beginning computer date for Excel. The beginning computer date is January 1st, 1908 is forty-three thousand nine hundred actually forty-three thousand eight hundred and ninety-nine days away from January 1st, 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 one and that is the first day.
Other Formats
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 zero seven one zero one when I press ENTER Excel says I'm gonna help you out you actually meant to type seven thousand one hundred and one. you don't need that leading zero but what I'm trying to tell Excel is no but 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 gonna go to more number formats and then you're gonna go over to special because it's a special format and you'll use 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.
Shortcuts
Very simple keyboard shortcuts to format text
- Bold Text: CTRL + B
- Italicize: CTRL + I
- Underline: CTRL + U
Borders
A couple more topics to go. I want to put a border around this text so I'm gonna select around the text. I'm gonna head over to the font group and when I click the drop-down. I can choose the type of border that adds a border on the outside. that's gonna 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.
Merging
Finally, we're gonna 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 and 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 and 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 and that makes it much simpler to merge multiple rows. then over here I would like to merge all 12 cells so it's one big box so you'll click the drop-down and choose to merge the cells now everything is one big box and if you want to you can play around with 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.
in this section we covered some of the formatting options that are available to you in excel we didn't go over all of them but this just gives you a sense of what's possible from some very common options