How to Change Workbook Appearance in Microsoft Excel 2010

  • 8/25/2010
In this chapter from Microsoft Office Professional 2010 Step by Step, you'll learn how to change the appearance of data, apply existing formats to data, make numbers easier to read, change data's appearance based on its value, and add images to worksheets in Microsoft Excel 2010.

Chapter at a Glance

Entering data into a workbook efficiently saves you time, but you must also ensure that your data is easy to read. Microsoft Excel 2010 gives you a wide variety of ways to make your data easier to understand; for example, you can change the font, character size, or color used to present a cell’s contents. Changing how data appears on a worksheet helps set the contents of a cell apart from the contents of surrounding cells. The simplest example of that concept is a data label. If a column on your worksheet contains a list of days, you can easily set apart a label (for example, Day) by presenting it in bold type that’s noticeably larger than the type used to present the data to which it refers. To save time, you can define a number of custom formats and then apply them quickly to the desired cells.

You might also want to specially format a cell’s contents to reflect the value in that cell. For example, Lori Penor, the chief operating officer of Consolidated Messenger, might want to create a worksheet that displays the percentage of improperly delivered packages from each regional distribution center. If that percentage exceeds a threshold, she could have Excel display a red traffic light icon, indicating that the center’s performance is out of tolerance and requires attention.

In this chapter, you’ll learn how to change the appearance of data, apply existing formats to data, make numbers easier to read, change data’s appearance based on its value, and add images to worksheets.

Formatting Cells

Excel spreadsheets can hold and process lots of data, but when you manage numerous spreadsheets it can be hard to remember from a worksheet’s title exactly what data is kept in that worksheet. Data labels give you and your colleagues information about data in a worksheet, but it’s important to format the labels so that they stand out visually. To make your data labels or any other data stand out, you can change the format of the cells that hold your data.

httpatomoreillycomsourcemspimages1409815.png

Include data labels to identify the data in a worksheet.

Most of the tools you need to change a cell’s format can be found on the Home tab. You can apply the formatting represented on a button by selecting the cells you want to apply the style to and then clicking that button. If you want to set your data labels apart by making them appear bold, click the Bold button. If you have already made a cell’s contents bold, selecting the cell and clicking the Bold button will remove the formatting.

Buttons in the Home tab’s Font group that give you choices, such as the Font Color button, have an arrow at the right edge of the button. Clicking the arrow displays a list of options accessible for that button, such as the fonts available on your system or the colors you can assign to a cell.

The Font Color gallery.

Another way you can make a cell stand apart from its neighbors is to add a border around the cell. To place a border around one or more cells, select the cells, and then choose the border type you want by selecting from the Border list in the Font group. Excel does provide more options: To display the full range of border types and styles, in the Border list, click More Borders. The Format Cells dialog box opens, displaying the Border page.

httpatomoreillycomsourcemspimages1409819.jpg

The Border page of the Format Cells dialog box contains the full range of tools you can use to define your cells’ borders.

You can also make a group of cells stand apart from its neighbors by changing its shading, which is the color that fills the cells. On a worksheet that tracks total package volume for the past month, Lori Penor could change the fill color of the cells holding her data labels to make the labels stand out even more than by changing the labels’ text formatting.

If you want to change the attributes of every cell in a row or column, you can click the header of the row or column you want to modify and then select your desired format.

One task you can’t perform by using the tools on the Home tab is to change the standard font for a workbook, which is used in the Name box and on the formula bar. The standard font when you install Excel is Calibri, a simple font that is easy to read on a computer screen and on the printed page. If you want to choose another font, click the File tab, and then click Options. On the General page of the Excel Options dialog box, set the values in the Use This Font and Font Size list boxes to pick your new display font.

In this exercise, you’ll emphasize a worksheet’s title by changing the format of cell data, adding a border to a cell range, and then changing a cell range’s fill color. After those tasks are complete, you’ll change the default font for the workbook.

  1. Click cell D2.

  2. httpatomoreillycomsourcemspimages1409821.png On the Home tab, in the Font group, click the Bold button.

    Excel displays the cell’s contents in bold type.

  3. httpatomoreillycomsourcemspimages1409823.png In the Font group, click the Font Size arrow, and then in the list, click 18.

    Excel increases the size of the text in cell D2.

    httpatomoreillycomsourcemspimages1409825.jpg

    Larger text simulates a page header.

  4. Click cell B5, hold down the Ctrl key, and click cell C4 to select the non-contiguous cells.

  5. On the Home tab, in the Font group, click the Bold button.

    Excel displays the cells’ contents in bold type.

  6. Select the cell ranges B6:B15 and C5:H5.

  7. httpatomoreillycomsourcemspimages1409827.png In the Font group, click the Italic button.

    Excel displays the cells’ contents in italic type.

    Local formatting such as bold and italic emphasizes cell content.

  8. Select the cell range C6:H15.

  9. httpatomoreillycomsourcemspimages1409831.png In the Font group, click the Border arrow, and then in the list, click Outside Borders.

    Excel places a border around the outside edge of the selected cells.

  10. Select the cell range B4:H15.

  11. In the Border list, click Thick Box Border.

    Excel places a thick border around the outside edge of the selected cells.

  12. Select the cell ranges B4:B15 and C4:H5.

  13. httpatomoreillycomsourcemspimages1409833.png In the Font group, click the Fill Color arrow, and then in the Standard Colors area of the color palette, click the yellow button.

    Excel changes the selected cells’ background color to yellow.

    You can distinguish header cells from other cells by applying a background color.

  14. Click the File tab, and then click Options.

    The Excel Options dialog box opens.

  15. If necessary, click General to display the General page.

  16. In the When creating new workbooks area, in the Use this font list, click Verdana.

    Verdana appears in the Use This Font field.

  17. Click Cancel.

    The Excel Options dialog box closes without saving your change.