Chapter 4
IN THIS CHAPTER
Aligning numbers and text
Applying cell styles to data in cells
Splashing color on a worksheet
Preparing a worksheet before you print it
This short and pithy chapter explains how to dress a worksheet in its Sunday best in case you want to print and present it to others. It explains how to align numbers and text, insert rows and columns, and change the size of rows and columns. You find out how to decorate a worksheet with colors and borders as well as create and apply styles to make formatting tasks go more quickly. Finally, this chapter describes everything you need to know before you print a worksheet, including how to make it fit on one page and repeat row labels and column names on all pages.
Especially if you intend to print your worksheet, you may as well make it look sharp. And you can do a number of things to make worksheets easier to read and understand. You can change character fonts. You can draw borders around or shade important cells. You can also format the numbers so that readers know, for example, whether they’re staring at dollar figures or percentages. This part of Chapter 4 is dedicated to the proposition that a worksheet doesn't have to look drab and solemn.
To start with, numbers in worksheets are right aligned in cells, and text is left aligned. Numbers and text sit squarely on the bottom of cells. You can, however, change the way that data is aligned. For example, you can make data float at the top of cells rather than rest at the bottom, and you can center or justify data in cells. Figure 4-1 illustrates different ways to align text and numbers. How text is aligned helps people make sense of your worksheets. In Figure 4-1, for example, Income and Expenses are left-aligned so that they stand out and clarify what the right-aligned column labels below are all about.
Select the cells whose alignment needs changing and follow these instructions to realign data in the cells:
At some point, everybody has to insert new columns and rows and delete ones that are no longer needed. Make sure before you delete a row or column that you don’t delete data that you really need. Do the following to insert and delete rows and columns:
After you insert rows or columns, the Insert Options button appears. Click it and choose an option from the drop-down list if you want your new row or column to have the same or different formats as the row or column you selected to start the Insert operation.
By default, columns are 8.43 characters wide. To make columns wider, you have to widen them yourself. Rows are 15 points high, but Excel makes them higher when you enter letters or numbers that are taller than 15 points (72 points equal one inch). Excel offers a bunch of different ways to change the size of columns and rows. You can start on the Home tab and choose options on the Format button drop-down list, as shown in Figure 4-3, or you can rely on your wits and change sizes manually by dragging or double-clicking the boundaries between row numbers or column letters.
Before you change the size of columns or rows, select them (Chapter 2 of this minibook explains how). Click or drag across row numbers to select rows; click or drag across column letters to select columns.
Here are ways to change the height of rows:
Here are ways to make columns wider or narrower:
The job of gridlines is simply to help you line up numbers and letters in cells. By default, gridlines aren’t printed, and because gridlines aren’t printed, drawing borders on worksheets is absolutely necessary if you intend to print your worksheet. Use borders to steer the reader's eye to the most important parts of your worksheet — the totals, column labels, and heading labels. You can also decorate worksheets with colors. This part of the chapter explains how to put borders and colors on worksheets.
A style is a collection of formats — boldface text, a background color, or a border around cells — that can be applied all at one time to cells without having to visit a bunch of different dialog boxes or give a bunch of different commands. Styles save time. If you find yourself choosing the same formatting commands time and time again, consider creating a style. That way, you can apply all the formats simultaneously and go to lunch earlier. Excel comes with many built-in styles, and you can create styles of your own, as the following pages explain.
By way of the Cell Styles gallery on the Home tab, you can choose from any number of attractive styles for cells in a worksheet. Excel offers styles for titles and headings, styles for calling attention to what kind of data is in cells, and styles to accent cells. Follow these steps to reformat cells by choosing a cell style:
On the Home tab, click the Cell Styles button.
As shown in Figure 4-4, the Cell Styles gallery opens.
Select a cell style.
The Cell Styles gallery is divided into categories. Find a style that suits your purposes.
The names of cell styles you create on your own are placed at the top of the Cell Styles gallery under the Custom heading. Create a cell style if you’re the creative type or if no built-in style meets your high standards. Follow these steps to create a cell style:
Apply the formatting commands you want for your style to a single cell.
For example, left-align cell data. Or apply a fill color to the cells (see “Decorating worksheets with colors,” later in this chapter). Or change fonts and font sizes. Knock yourself out. Choose all the formatting commands you want for your new style.
On the Home tab, click the Cell Styles button to open the Cell Styles gallery.
Depending on the size of your screen, you may have to click the Styles button and then click the More button first.
Choose New Cell Style at the bottom of the gallery.
You see the Style dialog box shown in Figure 4-5. It lists formatting specifications that you chose for the cell you selected in Step 1. If these specifications aren’t what you’re after, or if you want to change a specification, you can click the Format button and construct your new style in the Format Cells dialog box.
Click OK.
Next time you open the Cell Styles gallery, you see the name of your style at the top under Custom.
To remove a style you created from the Cell Styles gallery, right-click its name in the gallery and choose Delete on the shortcut menu.
Especially if your worksheet data is arranged neatly into columns and rows so that it looks like a conventional table, one of the easiest ways to decorate cells is to take advantage of table styles. Excel offers many preformatted table styles that you can apply to columns and rows on a worksheet.
Follow these steps to experiment with table styles:
On the Home tab, click the Format As Table button and select a table style in the gallery.
The Format As Table dialog box appears.
Click OK in the Format As Table dialog box.
You can go to the (Table Tools) Design tab to refine your table. Choose a different table style in the gallery if you don’t care for the style you chose.
To remove a table style from cells, select the cells, go to the (Table Tools) Design tab, and choose Clear in the Table Styles gallery.
As Chapter 5 of this minibook explains, choosing the Format As Table command can be a way to filter and sort table data. This is why the Format As Table command places filter buttons in the first row of tables. To remove these filter buttons, deselect the Filter Button check box on the (Table Tools) Design tab.
Put borders on worksheet cells to box in cells, draw lines beneath cells, or draw lines along the side of cells. Borders can direct people who review your worksheet to its important parts. Typically, for example, a line appears above the Totals row of a worksheet to separate the Totals row from the rows above and help readers locate cumulative totals.
To draw borders on a worksheet, start by selecting the cells around which or through which you want to place borders. Then do one of the following to draw the borders:
To remove the border from cells, select the cells, open the drop-down list on the Borders button, and choose No Border.
Apply background colors to cells to make them stand out or help the people who review your worksheets understand how they are laid out. Select the cells that need a background color and use these techniques to splash color on your worksheet:
Printing a worksheet isn’t simply a matter of giving the Print command. A worksheet is a vast piece of computerized sprawl. Most worksheets don’t fit neatly on a single page. If you simply click the Print button to print your worksheet, you wind up with page breaks in unexpected places, both on the right side of the page and the bottom. Read on to discover how to set up a worksheet so that the people you hand it to can read and understand it.
Unless you tell it otherwise, Excel prints everything from cell A1 to the last cell with data in it in the southeast corner of the worksheet. Usually, it isn’t necessary to print all those cells because some of them are blank. And printing an entire worksheet often means breaking the page up in all kinds of awkward places. To keep that from happening, following are some techniques for making a worksheet fit tidily on one or two pages.
To print part of a worksheet, select the cells you want to print, go to the Page Layout tab, click the Print Area button, and choose Set Print Area on the drop-down list. This command tells Excel to print only the cells you selected. On the worksheet, a box appears around cells in the print area. To remove the box from your worksheet, click the Print Area button and choose Clear Print Area on the drop-down list.
If your worksheet is too wide to fit on one page, try turning the page on its side and printing in landscape mode. In landscape mode, pages are wider than they are tall. Landscape mode is often the easiest way to fit a worksheet on a page.
To make yours a landscape worksheet instead of a portrait worksheet, go to the Page Layout tab, click the Orientation button, and choose Landscape on the drop-down list.
Reading a worksheet is extremely difficult when it’s broken awkwardly across pages. Where one page ends and the next begins is a page break. Use these techniques to see where page breaks occur, adjust the position of page breaks, and insert and remove page breaks:
Switch to Page Layout or Normal view after you’re done fooling with page breaks. You can clearly see page breaks in Page Layout view. In Normal view, page breaks are marked by a dotted line.
To scale the numbers and letters in a worksheet and make them a bit smaller so that they fit on a page, you can experiment with the Scale to Fit options. These options are located on the Page Layout tab. Starting in Page Layout view, go to the Page Layout tab and test-drive these options to make your worksheet fit on a single page or a certain number of pages:
Another way to stuff all the data onto one page is to narrow the margins a bit. Go to the Page Layout tab and use either of these techniques to adjust the size of the margins:
Before you print a worksheet, visit the Page Setup dialog box and see what you can do to make your worksheet easier for others to read and understand. To open the Page Setup dialog box, go to the Page Layout tab and click the Page Setup group button. Here are your options:
If your worksheet is a big one that stretches beyond one page, you owe it to the people who view your worksheet to repeat row and column headings from page to page. Without these headings, no one can tell what the data in the worksheet means. Follow these steps to repeat row and column headings from page to page:
On the Page Layout tab, click the Print Titles button.
You see the Sheet tab of the Page Setup dialog box.
Select the Row and Column Headings check box.
You can find this check box under Print.
To repeat rows, click the Range Selector button next to the Rows to Repeat at Top text box; to repeat columns, click the Range Selector button next to the Columns to Repeat at Left text box.
These buttons are located on the right side of the dialog box. The dialog box shrinks so that you can get a better look at your worksheet.
Select the row or column with the labels or names you need.
As long as they’re next to each other, you can select more than one row or column by dragging over the row numbers or column letters.
Click the Range Selector button to enlarge the dialog box and see it again.
The text box now lists a cell range address.
Click OK to close the Page Setup dialog box.
If I were you, I would click the Print Preview button in the Page Setup dialog box first to make sure that row and column headings are indeed repeating from page to page.
To remove row and column headings, return to the Sheet tab of the Page Setup dialog box and delete the cell references in the Rows to Repeat at Top text box and the Columns to Repeat at Left text box. You can also press Ctrl+F3 and delete Print_Titles in the Name Manager dialog box.