Excel Formatting Techniques: Font, Alignment, Borders

1. Microsoft Excel 2002 Tutorial 3 – Developing a Professional Looking Worksheet New Perspectives on Microsoft Excel 2002 Tutorial 3

2. Open the Format Cells dialog box • Formatting is the process of changing the appearance of your workbook. • A properly formatted workbook can be easier to read, appear more professional, and help draw attention to important points. • The formatting toolbar is the fastest way to format your worksheet. • With buttons on this toolbar, you can apply a comma format, adjust the number of decimal places in a number, apply Currency and Percent formats and even quickly copy formats. • If you select a cell or range, click Format on the menu bar and then click Cells, the Format Cells dialog box opens. New Perspectives on Microsoft Excel 2002 Tutorial 3

3. The Format Cells dialog box The Format Cells dialog box has six tabs, each dedicated to a different set of format properties. The Font tab will be used to format the font, size and style of text in your worksheets. New Perspectives on Microsoft Excel 2002 Tutorial 3

4. Format data using different fonts, sizes and font styles • A font is the design applied to letters, characters and punctuation marks. Each font is identified by a font name or type face. • Fonts can be displayed in various sizes and you can even change the color of the font or the background color in the cell. • These options are available in the Format Cells dialog box and there are also buttons available for the formatting toolbar to make formatting faster. New Perspectives on Microsoft Excel 2002 Tutorial 3

5. Examples of various formats Excel, by default, will format all your entries using a style called the General format. This figure shows examples of some items formatted with the General format. You can also see the Percent Style and Currency Style applied to various cells. New Perspectives on Microsoft Excel 2002 Tutorial 3

6. Copy existing formats to other cells • One way to copy a format is to use the Format Painter button. To use the Format Painter: • Select a cell, then click the Format Painter button, which has a picture of a paintbrush on it • Select the cell or range you wish to format and the operation is complete • Another way to copy a format is to drag the fill handle and click the Auto Fill Options button, then click the Fill Formatting Only option. • Another way to format cells is through the Format Cells dialog box. New Perspectives on Microsoft Excel 2002 Tutorial 3

7. Align cell contents • When you enter numbers and formulas into a cell, Excel automatically aligns them with the cell's right edge and bottom border, while text entries are aligned with the left edge and bottom border. • You can control the alignment of data within a cell horizontally and vertically. • Left, Right and Center alignments can be selected using their respective alignment buttons on the Formatting toolbar. • To align the cell's contents vertically, open the Format Cells dialog box and choose the vertical alignment options on the Alignment tab. New Perspectives on Microsoft Excel 2002 Tutorial 3

8. Align using Merge and Center • Another option available for alignment in the Format Cells dialog box and on the Format toolbar is the Merge and Center option, which centers text in one cell across a range of cells. • If you want to fit a lot of text within a cell but without having to expand the column width to be very large, you can use the text wrapping option on the Alignment tab, or even choose to indent text. • You can also have Excel shrink the text to fit within the given column width you have chosen or even rotate text from -90 to +90 degrees. New Perspectives on Microsoft Excel 2002 Tutorial 3

9. The Alignment tab of the Format Cells dialog box The Alignment tab provides many options for aligning data. Click the check boxes to select these options. Rotate text by moving the arrow with the mouse, or specify the number of degrees in the text box. New Perspectives on Microsoft Excel 2002 Tutorial 3

10. Examples of text formatting This column header shows an example of text wrapping. The text in column A is indented. New Perspectives on Microsoft Excel 2002 Tutorial 3

11. Add cell borders and backgrounds • Excel provides a range of tools to format not only the contents of a cell, but also the cells themselves. • The gridlines you see in Excel in a new worksheet are not displayed on printed pages. • You can add a border to a cell using either the Borders button on the Formatting toolbar or the options on the Border tab in the Format Cells dialog box. New Perspectives on Microsoft Excel 2002 Tutorial 3

12. The Borders button versus the Border tab • When you click the list arrow for the Borders button, a Borders palette appears showing common choices as well as a Draw Borders button at the bottom of the Border palette gallery. • The Borders button allows you to create borders very quickly, whereas the Format Cells dialog box allows you to refine your choices further. • The Border Tab in the Format Cells dialog box is especially useful for controlling how a block of cells or a range appears with borders. • You have the option to change the outermost top, bottom and sides of the range independently, as well as determine different borders for the lines separating the cells inside the range's grid. New Perspectives on Microsoft Excel 2002 Tutorial 3

13. Add patterns or colors to cells • Patterns and colors can be used to enhance the appearance of spreadsheet cells. • The fastest way to apply background color to cells in the worksheet is by clicking the list arrow of the Fill color button and choosing a color from the palette. • To apply a fill pattern to cells, use the Patterns tab on the Format Cells dialog box. New Perspectives on Microsoft Excel 2002 Tutorial 3

14. The Border tab of the Format Cells dialog box The Border tab of the Format Cells dialog box gives you complete control over the border you want to create for a cell, range of cells, or the entire worksheet. Click a button to turn on or turn off the border for the indicated area. New Perspectives on Microsoft Excel 2002 Tutorial 3

15. The Patterns tab of the Format Cells dialog box Using the Patterns tab of the Format Cells dialog box, not only can you change the background color of the worksheet, but also you can select from a palette of patterns, as shown in the figure to the right. The color palette on the Patterns tab is the same one that is also available from the list arrow of the Fill Color button. New Perspectives on Microsoft Excel 2002 Tutorial 3

16. A worksheet with formatting applied This figure shows how you might use borders, background colors and patterns to improve the appearance of a worksheet. Certain background patterns can overwhelm the text in some cells, so you can improve the appearance by changing the color of the pattern itself to a lighter color if you are using standard black text. New Perspectives on Microsoft Excel 2002 Tutorial 3

17. Merge a range of cells • To merge a range of cells into a single cell: • Use the Merge option on the Alignment tab in the Format Cells dialog box • Click the Merge and Center button on the Formatting toolbar • To split a merged cell back into individual cells: • Select the merged cell • Click the Merge and Center button again • Or uncheck the Merge Cells check box on the Alignment tab in the Format Cells dialog box New Perspectives on Microsoft Excel 2002 Tutorial 3

18. Hide rows and/or columns • You can hide rows or columns, which does not affect the data stored there, nor does it affect any cell that might have a formula reference to a cell within the hidden row or column. • To hide a row or column: • Select the row or column and then choose Hide from either the Row or Column option of the Format menu, or, from the shortcut menu that pops up when you right click the row or column heading • To unhide a row or column: • Select the headings of the rows or columns that border the hidden area, then choose Unhide from either the Row or Column option of the Format menu, or, from the shortcut menu that pops up when you right click the row or column heading New Perspectives on Microsoft Excel 2002 Tutorial 3

19. Merge headings across multiple cells This figure shows a sample worksheet with a boldfaced title in the first row merged and centered across the columns used for data. The Merge and Center button is the fastest way to merge several cells into one or to split one merged cell back into several cells New Perspectives on Microsoft Excel 2002 Tutorial 3

20. Worksheet with hidden cells This figure shows the same worksheet that was shown in the previous slide, but it has now had several cells hidden. Hiding extraneous cells can frequently improve the overall appearance of the worksheet. New Perspectives on Microsoft Excel 2002 Tutorial 3

21. Format the worksheet background and sheet tabs • You can use an image file as a background for a worksheet. • Images can be used to give the background a textured appearance, like that of granite, wood, or fibered paper. • The background image does not affect the format or content of any cell in the worksheet, and if you have already defined a background color for a cell, Excel displays the color on top, hiding that portion of the image. • You cannot apply a background image to all the sheets of the workbook at the same time. New Perspectives on Microsoft Excel 2002 Tutorial 3

22. Insert a background image and change a worksheet tab color • To add a background image to a worksheet: • Click Format on the menu bar, point to Sheet and click Background • Locate and select an image from your hard drive, floppy drive, network, etc., and click the Insert button • You can also format the background color of the worksheet tabs, but this color is only visible when the worksheet is not the active sheet in the workbook. • Right click the tab you want to change and choose Tab color from the shortcut menu • Select a color from the color palette, click the OK button, and then click on a different tab in order to see the color displayed on the changed tab New Perspectives on Microsoft Excel 2002 Tutorial 3

23. A worksheet with a background image This figure shows a worksheet with a background image added, creating a textured background. New Perspectives on Microsoft Excel 2002 Tutorial 3

24. Find and replace formats within a worksheet • The Undo button on the Standard toolbar is very useful for removing formatting choices you have decided you do not want to use. • You can also clear the formatting of selected cells, returning them to their initial, unformatted appearance. • To clear formatting, select a cell or range, click Edit on the menu bar, point to Clear and then click Formats New Perspectives on Microsoft Excel 2002 Tutorial 3

25. Use Find and Replace to change formats • Click Edit on the menu bar and then click Replace. • When the Find and Replace dialog box opens, click the Options >> button to expand the box and display additional find and replace options. • Click on the Replace tab and then click the topmost Format button to open a Find Format dialog box, select the format combinations you want to search for, then click the OK button. • Click the lower Format button and when the dialog box opens, select the options you want to use for replacing the formatting. • Click the OK button and then the Replace All button to quickly change all the cells that meet your Find Format criteria. New Perspectives on Microsoft Excel 2002 Tutorial 3

26. Dialog boxes used for Find and Replace operations The Find Format dialog box. The Find and Replace dialog box. New Perspectives on Microsoft Excel 2002 Tutorial 3

27. Create and apply styles • If you have several cells that use the same format, you can create a style for those cells. • A style is a saved collection of formatting options: number formats, text alignment, font sizes and colors, borders, and background fills. • If you modify the specifications for a style, the appearance of any cell associated with that style would be automatically changed to reflect the new style. • To create a style, click on a cell that has formatting applied to it and this formatting becomes the basis of the new style you want to create. New Perspectives on Microsoft Excel 2002 Tutorial 3

28. Create a style using the Style dialog box • Click Format on the menu bar, and then click Style. The Style dialog box opens and all the formatting options associated with the active cell are listed. • Give the style a name, and then modify the formatting options by removing or adding to the existing ones listed in the dialog box. Click the OK button to create a style with a specific name. • To apply a style within a worksheet, first select the cells you want associated with the style, then open the Style dialog box, choose the style name from the list arrow and then click the OK button. • When you create a style, you can also click the Merge button in the Style dialog box to merge a style with those from other open workbooks. New Perspectives on Microsoft Excel 2002 Tutorial 3

29. The Style dialog box The Style dialog box allows you to create, name and customize styles. You can also copy styles from one workbook to another. Copying styles allows you to create a collection of workbooks that share a common look and feel. New Perspectives on Microsoft Excel 2002 Tutorial 3

30. Apply an AutoFormat to a table • You can apply a professionally designed format to your worksheet by choosing one of 17 predefined formats from the AutoFormat gallery. • To apply an AutoFormat to a table: • Select a range that has a table of information in it • Click Format on the menu bar, click AutoFormat and the AutoFormat dialog box opens. Scroll through the gallery to view different table formats, click on one you want to try, and then click the OK button. • Click on a cell outside of your selected range to remove the highlighting from your table so you can see what it looks like with the AutoFormat design applied. New Perspectives on Microsoft Excel 2002 Tutorial 3

31. The AutoFormat style gallery This figure shows some of the AutoFormats available for use. The designs in the AutoFormat gallery are very useful. You can either employ the professional design that Excel provides you, or simply use it as a starting point to apply a design that is close you what you want, which you can then modify to fit your own needs. New Perspectives on Microsoft Excel 2002 Tutorial 3

32. Format a printout using Print Preview • Open a Print Preview window by clicking the Print Preview button on the Standard toolbar. • Excel will display the preview as a full page, which may be difficult to read. • Click the Zoom button on the Print Preview toolbar, or pass your mouse over the page, and the pointer changes to the shape of a magnifying glass. When you click any portion of the page Excel will zoom in. Zoom out using the same methods. • By clicking the Setup button on the Print Preview toolbar, you can change margins, orientation, center the page or set several other formatting and printing features. • You can also open the Page Setup dialog box by selecting that option from the File menu. New Perspectives on Microsoft Excel 2002 Tutorial 3

33. The Margins tab of the Page Setup dialog box The Page Setup dialog box controls how a worksheet is placed on a page for printing. You can adjust the size of the margins, which are the spaces between the page content and the edge of the page. Most printers require a minimum margin. New Perspectives on Microsoft Excel 2002 Tutorial 3

34. Create a header and footer for a printed worksheet • A header is text printed within the top margin of every worksheet page and a footer is printed within the bottom margin of every page. • Headers and footers can add important information to your printouts. • Excel tries to anticipate headers and footers and provides several preformatted options in list boxes on the Header/Footer tab of the Page Setup dialog box. • Click the list arrow for these header and footer options and select one of Excel's suggestions or create your own by choosing the Custom Header or Custom Footer buttons on the Header/Footer tab. New Perspectives on Microsoft Excel 2002 Tutorial 3

35. The Header dialog box This figure shows the Header dialog box. This dialog box presents the same options as the Footer dialog box. You can type in any text you like and use the Font button to format the text just as you would in a worksheet cell. The other Header/Footer formatting buttons provide some common actions using built-in Excel formatting codes. New Perspectives on Microsoft Excel 2002 Tutorial 3

36. Define a print area and add a page break to a printed worksheet • By default, Excel prints all parts of the active worksheet that contain text, formulas, or values. • You can define a print area that contains only the content that you want to print. • To define a print area, select the range you want to print, click File on the menu bar, point to Print Area, and then click Set Print Area. • You can also specify different sections of your worksheet to print on separate pages. • Insert a page break by clicking on a cell, clicking Insert on the menu bar, and then clicking Page Break New Perspectives on Microsoft Excel 2002 Tutorial 3

37. The Sheet tab of the Page Setup dialog box The Page Setup dialog box can specify cells that will repeat on each page, print gridlines, and whether to print or not to print headings on each page. New Perspectives on Microsoft Excel 2002 Tutorial 3