1.4 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; the drop-down list displays a preview of each font to help you pick the best font face for your project. 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, or 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 the 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, applying fill effects, and gaining 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 than to change 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 predefined 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.