1.3 Formatting Cells and Sheets

Now that you have experience working with cells and worksheets, it is time to turn our attention to defining the format, or appearance, of the cells and worksheets in our workbooks. Specifically, we will cover how to (1) apply basic formatting (i.e. bolding, italicizing, centering) to the contents of cells, (2) define the data type of cells, (3) specify the background color, patterns, and outlines of cells, (4) use the format painter to copy the format of a cell and apply it to other cells in your worksheet, and finally (5) define the page margins and print areas for a worksheet.

Basic Formatting of Cells

The menu items used to change the appearance of the contents of cells can be accessed from the "Font" and "Alignment" groups on the "Home" menu tab. To change the appearance of a particular cell, you first select the cell and then use the appropriate menu item. To alter the appearance of multiple cells, just select a range of cells before making your changes. Our discussion in this tutorial will be limited to the commonly-used menu items that appear by default when the Excel window is maximized. It is important to note, however, that you can access more formatting features by clicking the icon (see below) in the bottom right corner of either the "Font" or "Alignment" groups.

The "Font" group contains menu items that change the appearance of the text in the cells (see Figure 1.16). The "Font Face" drop-down menu allows you to choose the font from the list of installed system fonts on your computer; to aid in picking the best font face for your project, the drop-down list displays a preview of each font. The "Font Size" menu list allows you to specifically define the size of the text in the cells. The "Increase/Decrease" items will quickly make text larger and smaller. You will notice that there are also other menu items for making text bold, italicized, underlined, and for changing the color of text.

Excel also allows you to change the positioning of text within a cell or range of cells (Figure 1.17). These features comprise the "Alignment" group of menu items. You can specify the top-to-bottom position of cell contents using the "Vertical Alignment" menu items; notice that text can be placed in the top, middle, or bottom of a cell. In addition, the "Horizontal Alignment" menu items define the left-to-right position of text. Cell contents can be centered or positioned to the far left or right of a cell. The "Orientation" feature allows you to angle cell contents. "Wrap Text" allows the entire contents of a cell to appear by displaying it on multiple lines within the same cell. The "Indent" items increase or decrease the space between the left edge of a cell and where the contents appear in the cell. Finally, the "Merge Cells" feature allows you to combine cells to create larger cells that span columns or rows in the spreadsheet.

Colors, Patterns, and Outlines

Excel also offers options for changing the appearance of the cells. You can change the background color of a cell (or range of cells) by selecting that cell and choosing a background color from the "Colors" menu list in the "Font" group of the "Home" menu tab (see Figure 1.18). To access the "Colors" menu list, you click on the upside-down triangle next to the "Font Colors" icon. The "Colors" menu list is populated with frequently used "Standard Colors" and those colors that fit the scheme of the colors already used in the spreadsheet (called "Theme Colors"). To save time, you can apply the last color you used to another cell by clicking on the "Font Color" icon instead of the upside-down triangle.

If you need access to a wider assortment of colors, or if you would like to apply a pattern to the background of your cells, you can click on "More Colors…" in the "Colors" menu list. This will open the "Format Cells" dialog window to the "Fill" tab. Here you will find options for specifying a pattern style and color, fill effects, and gain access to more color options.

In addition to changing the background colors and patterns within a cell, you can define borders for cells. To define a border for a cell (or range of cells), click on the upside-down triangle next to the "Borders" icon found in the "Font" group on the "Home" menu tab (Figure 1.19). Excel populates the "Borders" menu list with popular choices for cell borders. You also have the option to "Draw Borders" as well as change the color and style of the lines used in the bordering. Additionally, you can access more options by clicking on "More Borders…." This will open the "Format Cells" dialog window to the "Border" tab, where you can precisely define the border you wish to apply. As with background colors, you can apply the last border you used to another cell by clicking on the "Borders" menu icon.

Defining Data Types

You can store a number of different types of data in Excel worksheets. Different types of data include text (letters, words, and phrases), numbers, dates and times, and other specialized or customized data types. Most worksheets contain several different types of data.

For example, a budgeting worksheet will likely contain text to describe the line items in the budget, dates to describe when a particular income or expenditure item will occur, and numbers to record the monetary value of each line item. While you can use many different types of data in a single worksheet, each cell in a worksheet can only be defined as a single data type. To define the data type associated with a cell, you click on the cell and select the desired data type from the "Numbers" group of the "Home" menu tab (Figure 1.20).

As you can imagine, Excel will treat different types of data in different ways; for example, you could not do arithmetic on text. Additionally, you will likely want to display some types of data differently than others. In one case, you may wish to display monetary values using currency symbols or fractions as percentages. You may also want to change the number of decimal places that are visible to the user. All of these options are available in the "Number" group of the "Home" menu tab.

Should you find that you would like more options for defining the data types and display options for your worksheets, click on "More Number Formats…" on the "Data Types" drop-down menu list (Figure 1.20). This will open the "Format Cells" dialog window to the "Number" tab (Figure 1.21). Here you can select the data type and display options for a cell. You will notice that you can see a preview of the options you select in the "Format Cells" window.

Reusing and Clearing Cell Formats

It is not uncommon to apply a number of different format options to a cell to make it look how you want it to look. Often you will want to reuse this same set of formatting options to display other elements of your worksheets. Fortunately, you can reuse the same set of formatting options from one cell in other cells quickly and easily using the "Format Painter" feature (see Figure 1.22). To apply the format from one cell to another, you first select the cell with the formatting you want to copy, and then in the "Home" menu tab followed by the "Clipboard" group you click on the "Format Painter" button. Next, select the destination cell where you want to apply the formatting.

Also, when you are using a number of different formatting options in your cells, you might not like the result. In this case, you might find that it is easier to clear away all of the formatting you have done and start over rather than changing the different formatting options you have already applied. To easily clear the formatting options used in a cell, select the cell you would like to clear and click on "Clear Formats" in the "Editing" group of the "Home" menu tab (Figure 1.23). Notice that you can also clear the cell contents and other aspects of the cell.

Formatting Worksheets

Excel also allows you to format entire worksheets. Excel has a number of worksheet styles, called Themes, which you can apply to quickly add colors and other style effects to your worksheets.

Worksheets are different than other documents because they are not designed around paper boundaries. When you create a word processing document, you do so with the intention of printing the document. The layout of each page is designed by thinking about how the document will appear on paper.

However, when you create a worksheet, you do so to complete a set of calculations. Often you format the various elements of the worksheet to be easy to read, and you will rarely worry about how the worksheet will appear on a printed page.

On occasion you may find it necessary to print a report from a worksheet. You can format worksheets to make them more printer-friendly by managing the "Page Layout" menu tab options of the worksheet. Figure 1.24 demonstrates the process for setting the margins and page orientation for a worksheet. Margins refer to the amount of blank space that appears around the outside of a printed document. You define the margins for a worksheet using the "Margins" menu list in the "Page Setup" group of the "Page Layout" menu tab. Notice that you have a number of pre-defined options for page margins, but that you can also customize the page margins by clicking on "Custom Margins…" on the "Margins" menu list.

Page orientation refers to the layout of the paper when the worksheet is printed. "Portrait" orientation means that the worksheet will be printed on the page with the widest part of the page running from the top to the bottom. "Landscape" orientation means that the worksheet will be printed with the widest part of the page running from side to side. You can change the page orientation of a worksheet by clicking on the "Orientation" menu list in the "Page Setup" group of the "Page Layout" menu tab.

Often the reports you generate using spreadsheets can span multiple pages. You may wish to repeat some elements of the report, like a title or page numbers, across all pages. This is done by defining page headers and footers (Figure 1.25). Top define report headers and footers, click on "Print Titles" in the "Page Setup" group of the "Page Layout" menu tab. This will open the "Sheet" tab of the "Page Setup" dialog window. Switch to the "Header/Footer" tab. Using the drop-down menus below "Header:" and "Footer:" you can add commonly used elements (such as page numbers) to your reports. Notice that you can also define custom headers and footers.

You may also find that you need to print a large data table that will span multiple pages. When this is the case, it is helpful for the reader if you repeat the column or row labels on each page of the report. You can define rows or columns to repeat on a printout in the "Page Setup" dialog window (see Figure 1.25).

You may also find that there are parts of your worksheets that you do not wish to include in a report printout. You can define the parts of the worksheet that you want to print by setting the "Print Area" of the worksheet. As the name implies, the print area is the set of consecutive cells in a worksheet that will be printed. Anything outside of the print area will not be sent to the printer when the print command is given. To define the print area of a worksheet, select the range of cells you would like to be included in the print area and click "Set Print Area" found after clicking the "Print Area" button in the "Page Setup" group of the "Page Layout" menu tab (Figure 1.26). A worksheet can only have one print area defined at a time. If you need to print multiple areas in the same worksheet, you will need to add those areas to the print area using the "Add to Print Area" option. Notice that the "Add to Print Area" option is only available if a print area is already defined.

As you can tell, we just scratched the surface of the options that are available within Excel for formatting cells and worksheets. Take some time to experiment with the other formatting options available within Excel.

Practice Working with Formatting

Task 1: Change the cells in the "Birthday" column (column D) to the "Short Date" format. To do this, select the range D6:D71. Next, click on the "Number Format" drop-down box in the "Number" group of the "Home" menu tab. Finally, select "Short Date" as the data type.

Task 2: Change the format of all of the actual student scores to "Number" and display two decimal places for each score by selecting the range E6:J71 (the student scores). Then select "Number" as the format from the "Number Format" drop-down box in the "Number" group of menu items in the "Home" tab. Notice that the default display option is with two decimals by default.

Task 3: Merge cells B4:D4 and center the text "Student Information" over columns B through D. First, select cells B4:D4, and then click on "Merge & Center" in the "Alignment" group of menu items in the "Home" menu tab.

Task 4: Merge cells E4:I4 and center the text "Exams" over columns E through I using the process described in task 3.

Task 5: Merge cells J4:K4 and center the text "Final Grades" over columns J and K using the process described in task 3.

Task 6: Make all of the column labels (range B5:K5) bold and centered. Start by selecting the range B5:K5. Then, click on the "Bold" icon in the "Font" group and the "Center" icon in the "Alignment" group of the "Home" menu tab.

Center

Bold

Task 7: Make the merged cells B4:D4 bold and italicized. To do this, select the merged cell B4:D4 (notice you just click where cell B4 would be) and then click on the "Bold" icon and the "Italic" icon in the "Font" group of the "Home" menu tab.

Bold and Italic

Task 8: Make the merged cells E4:I4 bold and italicized using the same process described in task 7.

Task 9: Make the merged cells J4 and K4 bold and italicized using the same process described in task 7.

Task 10: Place a thick bottom border under the column labels (range B5:K5). To do this, select the range B5:K5. Now click on "Thick Bottom Border" in the "Borders" menu list of the "Font" group of the "Home" menu tab.

Task 11: Place a dotted line border under each of the remaining rows in the table. First, select the range B6:K71. Next, click on "More Borders…" at the bottom of the "Borders" menu list. This will open the "Border" tab of the "Formal Cells" dialog window. Then, in the "Style:" area, choose the dotted line. Finally, click on the middle and bottom border icons and click "OK".

Bottom border

Middle border

Dotted line

Task 12: Change the "Fill Color" for range B7:K7 to any color of your choosing (note: don't leave the "Fill Color" as the default clear).

Task 13: Use the "Format Painter" feature of Excel to copy the format of cells B7:K7 to format cells B9:K9. Start by selecting cells B7:K7. Next, click on "Format Painter" in the "Clipboard" group of the "Home" menu tab and then select cells B9:K9.

Task 14: Use "Format Painter" on range B8:K9 (one blue row and one white row) to format the fill color for the rest of the table (range B10:K71) to alternate between light blue and none. You can apply the format painter to the entire range B10:K71 in one step by using the process described in task 13.

Task 15: Change the left and right page margins to .25 inches by clicking on "Custom Margins…" at the bottom of the "Margins" menu list of the "Page Setup" group on the "Page Layout" menu tab. Change the left and right margins to .25 and click "OK."

Task 16: Set the print area to comprise the area of table (range B4:K71). Do this by selecting the range B4:K71 and then clicking on "Set Print Area" in the "Print Area" menu list. The "Print Area" menu list is located in the "Page Setup" group of the "Page Layout" menu tab.

Task 17: Set rows 4 and 5 as "Print Titles" when the worksheet is printed. This is done using the "Print Titles" menu option, which can be found in the "Page Layout" menu tab and "Page Setup" group. This will open the "Sheet" tab of the "Page Setup" dialog window. Enter "$4:$5" in the box "Rows to repeat at top:" which will print rows 4 and 5 as titles on every printed page. Alternatively, you can click on the

icon to select rows 4 and 5 and then click on that icon again to return to the "Sheet" tab of the "Page Setup" dialog window. icon to select rows 4 and 5 and then click on that icon again to return to the "Sheet" tab of the "Page Setup" dialog window.

Navigate to the "Assignment" menu and click on the submit button to see how you did.