module two: comprehensive formatting€¦ · web viewwelcome to the second lesson in the prc’s...

PRC Student Guide Mod 2: Comprehensive Formatting

Module Two: Comprehensive FormattingWelcome to the second lesson in the PRC’s Excel Course 2. This lesson covers the heart of Excel formatting, the Format Cells dialog. You will explore two sample workbooks and make modifications to each. The workbooks can be found in the Excel 2 Files folder on your computer or as advised by your instructor. After completing this lesson, you will be able to completely control the look and feel of Excel worksheet.

Topics

Global Formatting...........................................................................................................2.2Local Formatting.............................................................................................................2.5Format Numbers and Dates............................................................................................2.6Align Information in Cells................................................................................................2.7Borders......................................................................................................................... 2.10Colors and Shading......................................................................................................2.13Protection...................................................................................................................... 2.14

Exercises

Exercise A – Format the Stock Portfolio Worksheet.....................................................2.16Exercise B – Format the Pro Forma Income Worksheet...............................................2.18Summary....................................................................................................................... 2.19

Objectives

Change the appearance of a worksheet using various formatting techniques Set Excel window options using the Office Button and the Excel Options dialog Understand the relationship between the various Formatting groups and the Format Cells

dialog box Protect cell formatting from inadvertent change

Excel II 2007 2.1 ©1998-2011 Peoples Resource Center All rights reserved.


Global FormattingFormatting controls the appearance, the look and feel, of a worksheet. Formatting can be global, affecting the complete worksheet or workbook, or it can be local, affecting only one or several cells in the worksheet.

A template is a pattern for making something. It is usually designed with the intent to be used many times, each time perhaps with some minor modifications. A dress pattern is a template; so is a molding jig. Workbooks also have templates. Global formatting can occur by default, determined by the template used when the workbook is first created. You will learn how to set the formatting for this template in a later lesson.

Global formatting can be controlled by two other methods. The first way is by selecting the Office Button and clicking on Excel Options. This brings up the Excel Options dialog in Figure 2.1. Click on the Advanced option and scroll down until you see the Display options as shown below.

Office Button

Figure 2.1 Excel Options Dialog

The check boxes in each section are toggle switches that turn on or off some aspect of Excel (the Display section) or the current workbook selected in the dropdown (Display options for this workbook), or the worksheet selected in Excel II 2007 2.2 ©1998-2011 Peoples Resource Center

All rights reserved.


that section’s drop down (Display Options for this worksheet) . These options do not affect the way the worksheet is printed – that’s controlled through the Page Layout tab and the Page Setup group. They affect only the designated workbook or worksheet you’ve selected.

Most of these switches are reasonably self-explanatory or have no other consequence except turning some feature on or off. The exceptions are:

Page breaks: If you click on the Office Button, the Print option then Print Preview, you are given a new window to see how the printed version of the worksheet will look. When you close that window the page break indicators (vertical and horizontal dashed lines) remain on the worksheet. Checking this selection will display the page break indications without all the steps needed in Print Preview.

Formulas: Usually this option is unchecked, formulas are displayed in the Formula bar and the results of calculating the formulas, the values, are displayed in the cells where the formulas are located. When a worksheet is audited - that is, checked for errors in logic and structure - sometimes it is easier if you can see all the formulas in all the cells at the same time. Checking this option displays formulas in cells.

Figure 2.2 Sample worksheet with formulas displayed

This view is not pretty, but it is functional. Excel does some unusual things to create this view. Column widths are changed to accommodate long formulas. This distorts the look of the worksheet, but the formatting of the normal view will be restored when you return to the normal view. The formulas are all text although individual cells retain the formatting they had in the normal view. Excel does this by putting quote marks before and after each formula although it does not show you the quote marks. It is not recommended, but this means you can edit formulas in this view – you have to take care not to undo what Excel has done to create this view. To restore the view back to normal, click in the box again to remove the check mark.



Gridlines, row and column headers: These features are used when you want the screen display to look like a word processing document rather than a worksheet.

Figure 2.3 Sample worksheet with and without gridlines

Figure 2.4 Sample worksheet without gridlines or headers

There’s no doubt gridlines and headers are a great help when creating or editing a worksheet. However, their functional value falls when the worksheet is complete. If the worksheet were presented as a projected screen image then a much cleaner look can be had by turning these features off. A convenient alternative way to make these choices is found in the Sheet Options group on the Page Layout tab of the Ribbon. See figure 2.4.1. These switches allow you to toggle on or off the gridlines and/or Headers on the screen image as well as the printed version.

Figure 2.4.1 Page Layout Tab Sheet Options group

Note: Each tab group has an arrow in the bottom right corner of the group if more options are available.

Outline symbols: This feature is coupled with consolidated tables, a topic not covered in this course.

Zero values: In an effort to unclutter the look of a worksheet, some designers choose to display a blank cell when the cell contains the value zero. This is effective when there are a lot of zero values splashed around in a table. It can lead to subtle errors, however. Functions built into Excel, like AVERAGE( ), will still sense the zero value producing results that look odd even if they are correct. It’s probably best to leave this switch on (checked) until you become very comfortable with the Excel functions you use.


Click here for more options


The second method of global formatting is to use a local formatting command, like font size or fill color, and apply it globally to the worksheet. To do this, first select all the cells in the worksheet by clicking on the Select All hot spot in the upper left corner of the worksheet and then apply the format.

Figure 2.5 The Select All hot spot or how to highlight all cells in a worksheet

This method does not always produce coordinated results. For example, if you change from a small font size to a much larger one, row height is not automatically adjusted. A portion of the cell entries will be cut off until you manually adjust the row height to accommodate the change. Excel is smart enough to do this for you when you change font size in a single cell, but not smart enough when a local format is applied globally.

Local FormattingExcel clusters local formatting commands into six categories: number, alignment, font, border, patterns, and protection. When all the categories are considered together they make up a style. On the Home tab, in the Styles group, click New Cell Style to bring up the Style dialog shown in Figure 2.6. Click on the Format button to bring up the Format Cells dialog which allows you to change the format of each of the style categories shown. Each category in the Style dialog corresponds to a tab in the Format Cells dialog.

Figure 2.6 Style dialog and options

You can combine different elements of a style and then save them by naming the style. The style name shown here is “Style 1” but you can change that name to anything you want. A named style can then be applied to a single cell or a range of cells.



In all honesty, I don’t think this technique is used much. Rather, one or two elements of the underlying style is altered for a specific cell or group of cells, such as changing from regular font to bold font or adding a border around a cell or changing the number format. First highlight or select the cells for which you want to apply the format change(s).

On the Home tab, select the Cells group and in the dropdown menu select Format Cells. This brings up the Format Cells dialog. The tabs on the Format Cells dialog provide all the same categories that make up a style. Although there are many formatting options present here, the Format Cells dialog box contains most of the options for changing to the look of a worksheet in one place. We will start the exploration of this dialog box with formatting numbers.

Figure 2.6.1 The Format Cells dialog

However, the quickest way to change the font (including cell border), cell alignment or number formats, etc, is to use the corresponding named groups on the Home tab as shown in figure 2.6.2….

Figure 2.6.2 Formatting options available on the Home tab of the Ribbon

Note: For the purposes of the remainder of this module we will use the Ribbon options whenever they are available and provide alternatives if needed.


On the Home tab in the Cells group click on Format

Select Format Cells



Format Numbers and DatesThere are several different formats you can apply to a number.

General is the default and this format removes trailing zeroes. So if you type 934.40 into a cell formatted as General, you will see 934.4, which might not be what you expected.

The Number format shows the numbers rounded to two decimal places and no comma separator. Currency gives you the expected appearance of a list of currency amounts with a dollar sign and comma

separator. Accounting gives you s format similar to currency except that the dollar sign is aligned in the column cells. Percentage changes the values to percentages and appends the percentage sign.

Figure 2.7 Using the Home tab, Numbers group to show various number formats

There are several formats for Date as well. Excel will always keep the complete date, i.e., day of the week, month, day and year as well as the time of day (12:00 am by default). You may want to display the date in different ways.

Figure 2.7 Using the Home tab, Numbers group to show various date formats

Dates are a little tricky in worksheets. If you enter =12/3/10 into a cell, Excel will interpret this as a formula and display the result of repeated division, 0.4. If you enter 12/3/10 (without the leading equal sign) into a cell, Excel will interpret this as a date and display it as entered. But Excel will store this value as a number that represents the number of days that have elapsed since January 1, 1900. It does this so that dates can be added to and subtracted from one another to find elapsed time. The question Excel has to wrestle with is whether the two digit year, 10, means 1910 or 2010. The default is that two digit dates 00 – 29 are interpreted as 2000 – 2029. You can change this default.

There are many more options for displaying numbers in the worksheet. These are available on the Number tab of the Format Cells dialog we looked at earlier. Easy access to this dialog is available by clicking on the arrow at the bottom right of the Number group (see Figure 2.7 above).

Tip: If entering many numbers select the desired range of cells and then apply the desired format.


Use the optional buttons to change aspects of the chosen number format


Align Information in CellsThe Alignment group on the Home tab may be used to adjust the location and orientation of the text or numbers inside a cell or group of cells.

Figure 2.9 The Alignment group on the Home tab

In general, text is left justified and numbers are right justified, but these defaults can be overridden using various options

Three buttons allow you to horizontally align the cell contents – left, center or right. Three buttons allow you to vertically align the cell contents – top, center or bottom. Two buttons let you increase or decrease the indentation of the cell contents in the cells. One button allows you to rotate the position of the contents of the cell. One button allows you to easily Wrap Text within the cell. One button allows you to select two or more cells and merge the cells and center the contents of the merged

cell.



Rotate text

Vertical allignment

Horizontal allignment

Indent text


Four of the settings are worth investigating in more depth. They are:

Wrap Text, often used in column titles; Shrink to Fit, a handy way to save space in your worksheet; Merge Cells, used to create titles that span several cells; and Orientation, an option that allows you to run text at an angle to the horizontal.

Figure 2.11 Examples of common alignment options

Merge Cells is a favorite with those who have struggled trying to center a title over any number of columns. First, type the title in the leftmost cell in the row you have reserved for the title and press enter. Second, click and drag (highlight) the cells in the row across all the columns where the title will be centered. Start with the cell that contains the title. Lastly, select the Merge and Center button in the Alignment group.



BordersThere are many different types of worksheets. One use of a worksheet is to create a form. Perhaps like an appointment form showing the time of day and a box or border surrounding the time slot. In this case there are no formulas, just words typed into various cells and formatted using borders and shading.

Figure 2.13 Sample form showing advanced formatting

To add simple borders to a cell or group of cells go to the Font group on the Home tab and click on the border icon.

Figure 2.14 The Home tab, the Font group and the Border icon

For more options click the arrow in the lower right corner of the Font group. This will bring up the Format Cells dialog. Next click on the Border tab.

There are 3 aspects of the border that we can control:

Line Style Line Color Border location – top, bottom, left, right, inside horizontal, inside vertical and across the cell.

Each cell in a worksheet has a Top, Bottom, Left and Right side. If you selected just one cell, Outline is the same as top, bottom, left and right. If you selected multiple cells the inside cell borders are also affected by our choices. In the examples below, each color block is 4 cells.

Outline is a preset used when you select multiple cells and would like to surround the entire selection with perhaps a heavier border, like a picture frame. If you do not want a picture frame type border, use Top, Bottom, Left and Right to surround all cells with their own border.



Border Icon


There is one confusing aspect of borders. The Top border of one cell and the Bottom border of an adjacent cell may be two borders. (See Figure 2.15)

Figure 2.15 Cells B2 and B3 share a common border

If you remove the Top border from cell B3 you may not see any visible change because cell B2 still might have a Bottom border. Removing the Bottom border for cell B2 will finally open up the rectangle.

Figure 2.16 The new border visually joins two cells



Figure 2.17 shows a selected cell that has a Top, Left, and Bottom border. The borders displayed

Figure 2.17 The Border selection options

in this window are toggle switches. Clicking on a border that is present, like Top here, will turn the border off. Clicking on a border that is absent, like Right here, will turn the border on. From time to time you may notice that a border selection may be “grayed out” in this window. This typically means there is a mix of borders in the selected range. No action taken with this window will “stick” unless the OK button at the bottom of the Format Cells dialog box is clicked.


Toggle Top Border

Toggle Bottom Border

Toggle Left Border Toggle Right Border


Colors and ShadingPatterns and color are another way to dress up a worksheet. These options are located on the Fill tab in the Format Cells dialog box. Go to the Home tab and in the Cells group click on Format then Format Cells.

Figure 2.18 The Format Cells dialog box showing the Color and Patterns options

Cell entries and cell borders may be colored, but Color selected from this tab affects the inside (background) of a cell. It can be used, for example, to draw attention to cells which need certain kinds of information entered.

Patterns are a holdover from a time when color printers were rare. Use patterns sparingly. Text entered on top of a pattern can be very difficult to read. Alternatively, a pattern used to surround a single cell can make that cell truly stand out.

Figure 2.19 Cross hatching and heavy borders make a single cell stand out

The useful patterns and colors are very dependent on the type of printer you will use to print the worksheet. Experiment with the different patterns and colors. Colors will print as shades of gray on a black and white laser or inkjet printer.


Click on the Fill Tab


ProtectionThe Protection tab is the last unexplored tab in the Format Cells dialog box and it is used to lock or hide specific cells, rows, or columns on a worksheet.

Figure 2.20 The Format Cells dialog with the Protection tab selected

Or rather, it is used to unprotect specific cells, rows, or columns. When a new worksheet is opened, all the individual cells are locked and the sheet as a whole is unlocked (unprotected). Select the cells you want unlocked then deselect Locked on the Protection tab. You can unlock all the cells on the sheet by clicking on the Select All hot spot before navigating to the Protection tab. Once selected cells have been unlocked, you then protect the rest of the sheet. On the Home tab, in the Cells group, click Format and in the dropdown menu click Protect Sheet.


Click on the Protection Tab


Figure 2.21 The Tools menu with Protection selected

Once the sheet has been protected, you can change the contents of only the unlocked cells. You cannot change formatting anywhere on a protected worksheet, so all formatting should be completed before protecting the worksheet.

There are two levels of protection you can provide once the worksheet is protected:

The first is to protect the worksheet without using a password. If someone attempts to change a protected cell, Excel issues a warning. A knowledgeable user could then use the sequence shown above to unprotect the worksheet and make a change. This method is used to prevent casual, inadvertent changes.

The second is to protect the worksheet using a password. The danger with this method is that, although it is very secure, if the password is lost, it is irrecoverable. Passwords are encrypted – not even Microsoft can help you with a lost password.

____________________________________

The following exercises will further solidify the topics taught in this lesson. The sample worksheets can be found on your computer hard drive. Explore the samples and have fun making changes.



Exercise A – Format the Stock Portfolio WorksheetIn this exercise you will continue working on the stock portfolio worksheet. You will make several changes to improve the appearance of the worksheet. The worksheet can be found on your computer as 2.2stock.xlsx. Make changes to the worksheet and save it as 2.2stock_rev.xlsx.

1. Close all open Excel documents.2. Click the Office Button then click Open and open the file named 2.2stock in the My Documents\Excel 2 Files

folder on the computer.

3. To preserve the original worksheet, save the worksheet using a new name.

4. Click the Office Button then click Save As and save the document as 2.2stock_rev.5. Click in the column header for Column A to select the entire column.6. On the Home tab in the Cells Group, click on the small arrow next to Insert then select Insert Sheet Columns

on the dropdown menu. 7. Column A is still selected. Set the width of the column to 3. On the Home tab, in the Cells Group, click the

small arrow next to Format. On the dropdown menu click Column Width. In the Column Width dialog change the number to 3 and click OK.

8. Click and hold the left mouse button on the column header of column B and drag from Column B through Column H selecting all of those columns.

9. Using the same sequence from #7 above set the width of columns B through H to 9.10. Click outside of the selected area to remove the highlighting.11. Select the cell range B4:H4.

12. Using the Home tab, Alignment group, click on the Wrap Text icon. 13. The range B4:H4 should still be highlighted. On the Home tab, in the Cells Group, click the small arrow next to

Format. On the dropdown menu click Row Height. In the Row height dialog change the number to 37 and click OK.

14. Select the cell range C4:H4.

15. Click the Align Right button in the Alignment group on the Home tab.16. Select the cell range D2:E2.17. Move the mouse pointer over the border of the selected area and when the mouse pointer turns into an arrow,

press and hold the left mouse button and drag the range to cells G2:H2.18. Click in cell G2 and then click on the Align Right button as you did in #15 above.19. Click in cell C2.20. On the Home tab in the Numbers group, click the small icon on the bottom

right of the group to show the Number tab of the Format Cells dialog box.

21. In the Category list select Date then in the Type window scroll down to select the option that will give you the mm/dd/yyyy format. It will likely be shown as 03/14/2001.

22. While still in the Format Cells dialog box, select the Alignment tab and use the Horizontal drop down menu to select the Left alignment option. Click the OK button to complete the formatting.

23. Select cell B1 and bring up the Format Cells dialog box.24. Select the Font tab. Change the Font Style to Bold, the Size to 16, and the Color to Blue then click OK.25. Merge and Center the title “Stock Portfolio” across columns A through H26. Select the cell range D5:H11.27. Bring up the Format Cells dialog box as you did in #20 above. Select the Number tab.28. In the Category window, select Number. Make Decimal places = 2, select Use 1000 separator, and select

the option which will show negative numbers in red. Click OK.29. Click in cell H2. Bring up the Format Cells dialog and format the number as Currency with no decimal

places. 30. Select the cell range B5:B11. Bring up the Format Cells dialog box and select the Fill tab. Choose a Light

Gray Color and click OK.31. The cell range B5:B11 should still be highlighted. Change the Font Color to Pink.



32. Select the cell range C5:C11. Change the Font Color to Pink.33. Select the cell range G2:H2. Change the Font Color to Red and the Font Style to Bold.34. Select the cell range B4:H4. Change the Font Color to Red. 35. Select the cell range B2:C2. Change the Font Style to Bold Italic.36. Select the cell range H5:H11. Bring up the Format Cells dialog box and select the Border tab. Select the

Color Red then click once on the Outline button to place a red outline around the range of cells. Change the style of the line to a single, medium heavy, solid line and click once on the Outline button again. Click OK.

37. Click outside of the selected area to unselect the range.38. Hold the Ctrl key and press Home to return to cell A1. 39. Your worksheet should look similar to the one in Figure 2.22.40. Save your work.

Figure 2.22 Exercise A completed worksheet

Congratulations! You have successfully completed Exercise A.



Exercise B – Format the Pro Forma Income WorksheetIn this exercise you will continue working on the pro forma income worksheet. You will make several changes to improve the appearance of the worksheet. The 2.2income worksheet can be found in the Excel 2 Files folder on your computer. Make changes to the worksheet and save it as 2.2income_rev.

1 Open the 2.2income file.2 Save the worksheet as 2.2income_rev.3 Change the formatting of the worksheet so that it looks like this:

Figure 2.23 Exercise B completed worksheet



SummaryNow you can...

Remove gridlines when viewing a worksheet Display formulas on the worksheet Selectively display elements of the Excel window Use all six tabs in the Format Cells dialog box

NOTES
