Microsoft Excel 77-727 – 2.2. Format Cells and Ranges Part 2

5. 2.2.5 Apply Number Formats

Excel is designed primarily to manipulate numbers. The formatting of numbers is usually the most common type of formatting applied to a spreadsheet. There are a lot of different number formats that you can use to meet a wide variety of needs. The easiest way to see the difference between the various formats is to look at one number and then we change the format to see the difference. So here we have our number one, two, three, 4. 56. If I select the cell, right click and then go to the Format Cell dialog box. The Number tab provides access to all of the possible numeric formats. This preview window shows a sample of what the number will look like for each of the formats which you can select. You can also access these options on the Home tab, the Number group and clicking on this small arrow to reveal the same dialog box. The general category is the default format for all cells.

When you enter numbers into an unformatted cell, Excel displays them exactly as you enter them, except that it does not display trading zeros after the decimal point. So if I type in 1234 point, you can see that the zeros don’t display if I type in 12341, it does display the . 1. This can make the worksheet somewhat harder to read because the numbers in a column will not be lined up by the decimal point, as you can see as I type in these numbers.

A further problem is that if you enter a number that’s much larger than the width of the cell, excel automatically changes the format to scientific notation and the number will look very different like this. To get more control over the formatting of the numeric data, you can use the Number option, which is a standard format for numbers and separates values of 1000 and higher. Using the period or full stop, you can choose to use the comma as a separator if you wish to by checking this box. The number of digits after the decimal point must also be specified, but the default is two. You also have the option to show negative numbers in one of these formats. If any number has more than two decimal digits, the number formats will round it off to the nearest second decimal digit. As with all formatting, this is for display purposes only. And if you look at the formula bar, you can see that the complete number is actually stored in the cell.

The currency format is similar to the number format except that the currency symbol, for example, the dollar symbol is shown and the comma separator is automatically displayed. So you can select the currency symbol you want from this drop down menu and can then choose the formatting you want from the available selection. The accounting format is similar to the currency format, except that negative numbers appear in brackets and the currency symbol appears at the far left side of the cell. Positive values include a space to the right of the value to ensure that decimal points always line up. The date format category displays date values in various ways.

One. Complication is the region of the world to which your computer is set. For example, if you live in the United States, then your default date format would be MDY. However, if your computer is set to the United Kingdom, your date format would be Ddmy. The D indicates that Excel will split single digit day values as one digit without filling in the left with a zero. DD indicates a two digit day value with a leading zero. For single digit values, the same format applies to the month and year values. If you enter your date value with the month as a name, for example, September 8, 2016, eight Sep 2016 or September 8, 2016, then Excel will display it using the custom date format of D-M-M-Y-Y where MMM indicates the first three characters of the month’s name.

However, if you enter your date value with the month as a number, for example 9816 or nine 2016 or 20 116 five eight, then Excel will display it using the date format that matches your region as described above. The Asterisk indicates that this format changes with the computer region setting as described at the bottom of the dialog box. The time format will once again be determined by the region to which a computer is set. Excel always shows percentage values as 100 times the number value. So if I type in 20 and format the number as a percentage, you can see that it becomes 2000%. This format shows a percentage sign at the right side of the cell. You can also specify the number of decimal places, with the default being two. However, if you click the Percent Style button in the Number group of the Home tab on the ribbon, excel will use percentage format with a decimal places value of zero. I find that it’s easy to just type in a number with the percentage symbol directly.

After this, Excel understands the number as, for example, 20%. The fraction format converts decimal digits to fractional values based on the fraction type that you select. The scientific notation will usually use this format in Scientific Applications. From very large and very small numbers, excel shows only one digit to the left of the decimal point, and you control the number of significant digits by specifying the number of decimal places. The special format category is for miscellaneous items such as phone numbers and zip codes. So if I specify the location as the United States, you can see that there are a number of special formats, such as the Social Security number, which is specific to that region of the world. If you can’t find the exact format you want in the other categories, you can create a format of your own with a custom category.

You also have the choice of using the Ribbon for the more frequently used formatting features. These options are found on the number group on the home tab. So we have a drop down for the number format, multiple accounting formats, percentage and separators and we also cannot also specified the number of decimal places by increasing and decreasing.

6. 2.2.6 Apply Cell Formats

A font is a style of text. Changing fonts will alter how the text and numbers appear in the worksheet. You can access the Fonts command from the Home tab and the Fonts group. Excel applies any changes to the entire cell or range of cells that you’ve selected. You can also select individual characters and numbers inside the cell and change the font or size. The Font tab is located in the The Format Cells dialog box, which is accessed by clicking on the Dialog box launcher. You can also access it from the right mouse buttons format cells and then click on the Font tab. This tab enables you to select the many font related formatting options available in Excel. Let’s have a quick look at each and what it does. Notice that the preview window shows you the effect of your selection before you apply them to the worksheet. Font this refers to the typeface of the text. Characters excel displays a set of characters in the same typeface as a font. You can see here that there are many available in Excel. Font Style Most fonts can be formatted bold, italicizes, italicized, or both.

 Size refers to the height of a character with a proportionate width. Most of the fonts are scalable that is, they have a variety of sizes. So you just select the font size that you want to apply and you can see the difference immediately. Underline this pull down shows you the options available for underlying the content of a cell. Be aware that an underline is not the same as a cell border. If selected, the underlying appears inside the cell, whereas borderlines appear along the selected edges of the cell. Color this pull down allows you to select from a vast range of colors, including the More Colors option, which allows you to create your own. Whatever you select will change the color of the characters in the cell. Effects you can use special character effects such as Strike through SuperScript and Subscript.

The options on the ribbon here contain the most frequently used font options and can be changed directly from here. We’ve seen that you can underline the contents of a cell or cells. Cell borders are different. Borders separate groups of data from each other to improve the readability of a worksheet, especially when it includes a large volume of numbers. You can get to the border features by invoking the Format Cells dialog box, as we’ve seen a minute ago.

You can also get all of the border choices in this pull down menu on the Font group. The border feature enabled you to draw lines around any or all of the four edges of a cell or range of cells. The dialog box displays several presets line thicknesses, color, and style options and allows you to specify where the lines will appear. Let’s look at the dialog box first. The Line group allows you to choose a line, style and or color for the border. If you want different lines or colors for specific borders, you have to select a style or color and then click on the border area for the appropriate border that you want. So if I want the thick red line, then I select them like so. At the moment, there’s nothing to see here because we’ve yet to indicate which border we want the formatting to be applied to. If I click on the outline preset, you can see that all four borders are changed.

The buttons around the outside of this window act as toggle switches. In other words, they turn the selection on and off. So if I click on this one, the top border, it now turns that selection off. If I click on it again, the border returns. The nonoption preset removes all borders. I can also click directly inside the box and the border appears and disappears. So there are a variety of ways in which to set the borders, but whichever way you choose, the selection will be applied to the cells which you’ve selected previously to invoking the dialog box. Using the Borders button on the Ribbon is a faster method of applying borders to the selected cells. You can click on the button to apply the border using the current border settings for this button, or you can click the arrow for it to display a dropdown menu with commonly combined borders for a cell. Notice that by default, when you start Excel, the Borders button on the Ribbon is set to bottom border.

As you select different border options from the drop down menu, the icon for the Borders button changes to show the new setting. This convenient feature allows you to minimize mouse clicks while applying borders to multiple cells throughout a worksheet. You can also fill cells and ranges of cells with background colors to increase the visual appeal of the overall worksheet. Patterns and color can help draw viewers attention to particular parts of your worksheet or serve to divide it off visually from the rest of the information. This can prove very useful when trying to highlight the sum, total, draw or differentiate heading information from data. The Fill Color button in the Ribbon begins with a default color of no fill color. If I select the fill color of blue, you can see the effect in the sample preview area.

The More Colors option provides a wider range from which you can select either standard or customized colors. In this small box, you can see the difference between the color as it currently is and the new selection that you are reviewing. The pattern style allows you to select a pattern for your selected cells, which can be applied just to make those cells stand out from all the others in the worksheet. You can also apply a color to the pattern by using the Pattern Color button on the Ribbon. You can access many of the same choices by clicking on the Fill button in the Fonts group. This pulldown provides the same options as are provided using the Format Sales dialog box, but also provides access to theme colors for selection. This pull down allows you to quickly change the color of the text within the cells that you have selected.

7. 2.2.7 Apply Cell Styles

Style is a grouping of specific format setting for a cell, such as font size and color. When you apply a style to a cell Excel format, it the same way if the style is changed, excel applies the changes to all cells with that style in order to maintain a consistent look throughout the worksheet with minimal effort. Most of the style options can be accessed here in the Styles group of the Home tab. For example, if I select this group of cells, I can now apply a preset style to those cells. By just clicking on one of these buttons. You can see that as I hover over the style, the selection changes to look exactly like that style. So it’s just a preset format that I can apply to a selection. The more button here allows me to get access to an even bigger range of styles. With a set of theme styles in different colors, you can see that each one has a specific name. To see which styles have been applied to a selected cell, just highlight it and go to the cell size group in the ribbon.

Specified style will be highlighted with a border. You can also modify any of the size to suit your particular taste. Just highlight the style and rightclick to select the Modify option. This brings up the Format Sales dialog, which can then be used to modify and save the new style. You can now see that the pattern I’ve applied to who accent Three is now part of that style. Notice that the salesforce that already applied this style have also changed when I modified the style that had been applied to them. If you apply a style in the workbook and modify that style in any way, then those changes will be automatically applied to all of the cells to which that style has been applied. For cells with numeric data, there are a group of Quick size available to sect from here.

If you want to create a brand new style, then the New Style button will invoke this dialog here, you can give the style a name and then specify what you want that style to look like.

8. Exercises Domain 2.2

And the first thing we’re going to do is select the e three to P 14. Now we’re going to format this selection then as being currency. So on the ribbon you have the option here on this pull down to just select currency. We want to center the line so we have the alignment options here. Now an e one we’re going to put in the heading Help US Airlines Monthly Revenue 2017. So we have a heading in now and we want to merge and center that. So to merge and center you select the cells that you want to merge across. So it’s e one to P one. So we select all those cells and then click on the Merge and center button and there it is. We apply a style to that.

Now actually I haven’t applied the style but if you click on the pull down menu, click on Title and that style will be applied. But firstly we’re going to change the size there’ll be formatted, change the font to bold and the size of the font which is 22, press OK, let’s modify the style. And now I can go in and I can click and apply the style, which I’ve done.

Now we’re going to do the same thing to A one. We’re going to apply the type of style and as you can see, you can’t see all the information because it’s bigger. So we need to modify the width of the columns to make it fully visible. Now we’re going to go to C two and we’re going to change the heading. So I click on C two and change the heading to Percentage Contribution. And because it’s wider than the cell, it’s going to go across into the D area. What we want to do is wrap this text. So select the cell and move the option up here to wrap the text.

But we need to increase the row width so that we can see all the information. So we’ll just drag down here to increase the size of the width of the row. Now we’ll auto fill Q three. We go to Q three and auto fill this formula down and we go to E 15 and we’ll autofill across. Now I’m going to enter a formula, we’re going to Q 15 and enter the formula the sum equal to sum Q three to Q 14. So it’s basically the total of the airports. Of each of the airports. So it’s the total for all of the airports. Now in E 16 I enter G 15 divided by that total. That’s divided by the total that’s going to give me a percentage.

So when I press Enter I get this number. But I need to format as a percentage because that’s what it should be. So I click on it and I go to percentage and now I’ll auto fill that. So we have all the percentages. Now I want to put that into the column cycle c Three. So I selected. Now I click onto C three. Right click and paste special. Or go to Paste Special from the ribbon if you wish. And now on Paste Special, what I want to say is that I want to only devalue and I want to transpose them. And you can see that those are very nicely, but the formatting is still incorrect. So I make that a percentage.

So select all these cells and make them a percentage, which is correct. Now I select E 16 to P 16 and I want to change the style of that. So I can go up into the style options. Here you see in the style, click on the pull down and I can select the one specified which is 60%, accent Six. Here we go. Click on it and change.

Now, in the information in the same range, I want to left the line. So I click on left line and then indent twice by clicking on the Indent button twice. Next I’m going to autofill B three. So we get January, February and March and so on. And next I’m going to angle Counterclockwise by going up into this little option. Open the home button so you can see it’s indicated by a B with a narrow pointing upwards to the right. You see different options on this when you click on the pull down. So watch. Now I click on this pull down and I have different options. Counterclockwise is one of them. You can see the effect. Next I’m going to create a new style. So I click on the pull down and select New Style and we give the sign, the name, my numbers. And now we can change the formatting to be what I want it to be. So I have various formats that I fill because I can make this anywhere I want, really. I create my own style. There. We have blue accent, one lighter, 40%. Now that I have that done, I can apply that style to the of sales that I have. Now I just go up and pick on the pull down menu and apply that custom site my numbers. That’s it.