Module Two: 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 ics TOC \t "Section Hdr,1" Global Formatting2. PAGEREF _Toc492461464 \h 2Local Formatting2. PAGEREF _Toc492461465 \h 5Format Numbers and Dates2. PAGEREF _Toc492461466 \h 6Align Information in Cells2. PAGEREF _Toc492461467 \h 7Borders2. PAGEREF _Toc492461468 \h 10Colors and Shading2. PAGEREF _Toc492461469 \h 13Protection2. PAGEREF _Toc492461470 \h 14Exercises TOC \t "Exercise Hdr,1" Exercise A – Format the Stock Portfolio Worksheet2. PAGEREF _Toc494533864 \h 16Exercise B – Format the Pro Forma Income Worksheet2. PAGEREF _Toc494533865 \h 18Summary2. PAGEREF _Toc494533866 \h 19ObjectivesChange the appearance of a worksheet using various formatting techniquesSet 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 boxProtect cell formatting from inadvertent change 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. 94805532766056896017684753165475250190Office ButtonFigure 2.1 Excel Options DialogThe 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 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 displayedThis 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 gridlinesFigure 2.4 Sample worksheet without gridlines or headersThere’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.Click here for more options Figure 2.4.1 Page Layout Tab Sheet Options groupNote: 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.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 worksheetThis 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 Formatting-9525875030Excel 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 optionsYou 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.164020527876532169102768606223001110615On the Home tab in the Cells group click on FormatSelect Format Cells Figure 2.6.1 The Format Cells dialogHowever, 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…. Click here for more optionsFigure 2.6.2 Formatting options available on the Home tab of the RibbonNote: For the purposes of the remainder of this module we will use the Ribbon options whenever they are available and provide alternatives if needed.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.Use the optional buttons to change aspects of the chosen number format Figure 2.7 Using the Home tab, Numbers group to show various number formatsThere 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 formatsDates 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.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 Rotate text a cell or group of cells.Horizontal allignmentVertical allignment1448435147320Indent text Click here for more optionsFigure 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 optionsThree 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.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 optionsMerge 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 formattingTo 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. Border IconClick here for more optionsFigure 2.14 The Home tab, the Font group and the Border iconFor 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 StyleLine 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. 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 borderIf 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 cellsFigure 2.17 shows a selected cell that has a Top, Left, and Bottom border. The borders displayedToggle Right BorderToggle Left BorderToggle Top BorderToggle Bottom BorderFigure 2.17 The Border selection optionsin 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.Colors and ShadingClick on the Fill TabPatterns 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. 335915216535 Figure 2.18 The Format Cells dialog box showing the Color and Patterns optionsCell 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 outThe 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.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.Click on the Protection Tab5168905334030270452540Figure 2.20 The Format Cells dialog with the Protection tab selectedOr 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.2543810-132715Figure 2.21 The Tools menu with Protection selectedOnce 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.Close all open Excel documents.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.To preserve the original worksheet, save the worksheet using a new name.Click the Office Button then click Save As and save the document as 2.2stock_rev.Click in the column header for Column A to select the entire column.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. 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. 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.Using the same sequence from #7 above set the width of columns B through H to 9.Click outside of the selected area to remove the highlighting.Select the cell range B4:H4.Using the Home tab, Alignment group, click on the Wrap Text icon. 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. Select the cell range C4:H4.Click the Align Right button in the Alignment group on the Home tab.Select the cell range D2:E2.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.Click in cell G2 and then click on the Align Right button as you did in #15 above.Click in cell C2.49510956985On 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. 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. 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. Select cell B1 and bring up the Format Cells dialog box.Select the Font tab. Change the Font Style to Bold, the Size to 16, and the Color to Blue then click OK.Merge and Center the title “Stock Portfolio” across columns A through HSelect the cell range D5:H11.Bring up the Format Cells dialog box as you did in #20 above. Select the Number tab.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.Click in cell H2. Bring up the Format Cells dialog and format the number as Currency with no decimal places. 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.The cell range B5:B11 should still be highlighted. Change the Font Color to Pink.Select the cell range C5:C11. Change the Font Color to Pink.Select the cell range G2:H2. Change the Font Color to Red and the Font Style to Bold.Select the cell range B4:H4. Change the Font Color to Red. Select the cell range B2:C2. Change the Font Style to Bold Italic.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.Click outside of the selected area to unselect the range.Hold the Ctrl key and press Home to return to cell A1. Your worksheet should look similar to the one in Figure 2.22.Save your work.Figure 2.22 Exercise A completed worksheetCongratulations! 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. 1Open the 2.2income file.2Save the worksheet as 2.2income_rev.3Change the formatting of the worksheet so that it looks like this: Figure 2.23 Exercise B completed worksheetSummaryNow you can...Remove gridlines when viewing a worksheetDisplay formulas on the worksheetSelectively display elements of the Excel windowUse all six tabs in the Format Cells dialog boxNOTES ................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- self checking excel spreadsheets
- gsa government wide it accessibility program
- formatting cells in excel jerry
- creating ada compliant excel spreadsheets
- copy the data into excel for formatting and then into word
- intermediate microsoft word
- module two comprehensive formatting
- meditech exporting data to whonet using baclink
- formatting in microsoft excel norwich high school
Related searches
- comprehensive list of autoimmune disea
- comprehensive list of autoimmune diseases
- comprehensive list of autoimmune dise
- cms comprehensive history and physical
- comprehensive list of lupus symptoms
- comprehensive h p requirements
- conditional formatting excel two conditions
- excel conditional formatting dates in two columns
- excel conditional formatting two sheets
- conditional formatting two if statements
- conditional formatting with two conditions
- conditional formatting with two criteria