Aligning numbers and text in columns and rows

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:

• Changing the horizontal (side-to-side) alignment: On the Home tab, click the Align Left, Center, or Align Right button. You can also click the Alignment group button, and on the Alignment tab of the Format Cells dialog box, choose an option on the Horizontal drop-down list. Figure 4-2 shows the Format Cells dialog box.
• Changing the vertical (top-to-bottom) alignment: On the Home tab, click the Top Align, Middle Align, or Bottom Align button. You can also click the Alignment group button to open the Format Cells dialog box (refer to Figure 4-2) and choose an option on the Vertical drop-down list. The Justify option makes all the letters or numbers fit in a cell, even if it means wrapping text to two or more lines.
• Reorienting the cells: On the Home tab, click the Orientation button and choose an option on the drop-down list. (For Figure 4-2, I chose the Angle Counterclockwise option.) You can also click the Alignment group button, and on the Alignment tab of the Format Cells dialog box (refer to Figure 4-2), drag the diamond in the Orientation box or enter a number in the Degrees text box.

Changing the orientation of text in cells is an elegant solution to the problem of keeping a worksheet from getting too wide. Numbers are usually a few characters wide, but heading labels can be much wider than that. By changing the orientation of a heading label, you make columns narrower and keep worksheets from growing too wide to fit on the screen or page.

Inserting and deleting rows and columns

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:

• Deleting rows or columns: Drag across the row numbers or column letters of the rows or columns you want to delete; then right-click and choose Delete, or, on the Home tab, open the drop-down list on the Delete button and select Delete Sheet Rows or Delete Sheet Columns.
• Inserting rows: Select the row below the row you want to insert; then, on the Home tab, open the drop-down list on the Insert button and choose Insert Sheet Rows, or right-click the row you selected and choose Insert on the shortcut menu. For example, to insert a new row above row 11, select the current row 11 before choosing Insert Sheet Rows. You can insert more than one row at a time by selecting more than one row before giving the Insert Sheet Rows command.
• Inserting columns: Select the column to the right of where you want the new column to be; then, on the Home tab, open the drop-down list on the Insert button and choose Insert Sheet Columns, or right-click the column you selected and choose Insert on the shortcut menu. You can insert more than one column this way by selecting more than one column before giving the Insert command.

A fast way to insert several rows or columns is to insert one and keep pressing F4 (the Repeat command) until you insert all the rows or columns you need.

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.

To insert more than one row or column at a time, select more than one row number or column letter before giving the Insert command.

Changing the size of columns and rows

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.

Cell styles for quickly formatting a worksheet

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.

Applying a built-in cell style

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:

1. Select the cells that need a new look.

2. On the Home tab, click the Cell Styles button.

   As shown in Figure 4-4, the Cell Styles gallery opens.

3. Select a cell style.

   The Cell Styles gallery is divided into categories. Find a style that suits your purposes.

To remove a style from cells, select the cells, open the Cell Styles gallery, and choose Normal. (You find Normal in the “Good, Bad and Neutral” category.)

Creating your own cell style

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:

1. 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.

2. 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.

3. 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.

4. Enter a descriptive name for your style in the Style Name text box.

5. 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.

Formatting cells with table styles

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:

1. Select the cells you want to format as a table.

2. 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.

3. If the cells you want to format include headers, the labels at the top of column rows that describe the data in the columns below, select the My Table Has Headers check box.

4. 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.

Slapping borders on worksheet cells

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:

• Borders button: On the Home tab, open the drop-down list on the Borders button (it’s in the Font group) and choose a border, as shown in Figure 4-6.
• Drawing: On the Home tab, open the drop-down list on the Borders button and choose Draw Border or Draw Border Grid. Then drag on the screen to draw the borders. Press Esc when you finish drawing.
• Format Cells dialog box: On the Home tab, click the Format button and choose Format Cells, or choose More Borders on the Borders button drop-down list. The Format Cells dialog box opens, as shown in Figure 4-7. On the Border tab, select a border style and either click in the Border box to tell Excel where to draw the border or click a Presets button. The Border tab offers different lines for borders and colors for borderlines as well.

To remove the border from cells, select the cells, open the drop-down list on the Borders button, and choose No Border.

Decorating worksheets with colors

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:

• On the Home tab, click the Format button and choose Format Cells on the drop-down list. You see the Format Cells dialog box. On the Fill tab, select a color and click OK. Figure 4-7 shows what the Fill tab looks like.
• On the Home tab, open the drop-down list on the Fill Color button and select a color.

Making a worksheet fit on a page

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.

As you experiment with the techniques described here, switch occasionally to Page Layout view. In this view, you get a better idea of what your worksheet will look like when you print it. To switch to Page Layout view, click the Page Layout button on the status bar or View tab.

Seeing and adjusting the page breaks

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:

• Viewing where page breaks occur: Click the Page Break Preview button on the status bar or View tab. As shown in Figure 4-8, you switch to Page Break Preview view. In this view, page numbers appear clearly on the worksheet and dashed lines show you where Excel wants to break the pages.
• Adjusting page break positions: In Page Break Preview view, drag a dashed line to adjust the position of a page break. After you drag a dashed line, it ceases being a default page break and becomes a manual page break. Manual page breaks are marked by solid lines, not dashed lines (see Figure 4-8). You can drag them, too. Excel shrinks the numbers and letters on your worksheet if you try to squeeze too much data on a worksheet by dragging a page break.
• Inserting a page break: Select the cell directly below where you want the horizontal break to occur and directly to the right of where you want the vertical break to be, go to the Page Layout tab, click the Breaks button, and choose Insert Page Break. Drag a page break to adjust its position.
• Removing a page break: Select a cell directly below or directly to the right of the page break, go to the Page Layout tab, click the Breaks button, and choose Remove Page Break.
• Removing all manual page breaks: To remove all manual page breaks you inserted, go to the Page Layout tab, click the Breaks button, and choose Reset All 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.

“Scaling to fit” a worksheet

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:

• Scaling by width: Open the Width drop-down list and choose an option to make your worksheet fit across one or more pages. Choose the 1 Page option, for example, to squeeze a worksheet horizontally so that it fits on one page.
• Scaling by height: Open the Height drop-down list and choose an option to make your worksheet fit across a select number of pages. For example, choose the 2 Pages option to shrink a worksheet vertically so that it fits on two pages.
• Scaling by percentage: Enter a percentage measurement in the Scale box to shrink a worksheet vertically and horizontally. To scale this way, you must choose Automatic in the Width and Height drop-down lists.

You can also fit a worksheet on a select number of pages by going to the Page Setup dialog, box shown in Figure 4-9. With this technique, you get a chance to “print-preview” your worksheet and get a better look at it after you change the scale. On the Page Layout tab, click the Page Setup group button to open the Page Setup dialog box. On the Page tab of the dialog box, select the Fit To option button and enter the ideal number of pages you want for your worksheet in the Page(s) Wide By and Tall text boxes. Excel shrinks the data as much as is necessary to make it fit on the number of pages you asked for. Click the Print Preview button to preview your worksheet in the Print window and find out whether shrinking your worksheet this way helps.

Making a worksheet more presentable

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:

• Including page numbers on worksheets: On the Page tab of the Page Setup dialog box (refer to Figure 4-9), enter 1 in the First Page Number text box. Then, on the Header/Footer tab, open the Header or Footer drop-down list and choose an option that includes a page number. Choosing the Page 1 of ? option, for example, enters the page number and the total number of pages in the worksheet in your header or footer.
• Putting headers and footers on pages: On the Header/Footer tab of the Page Setup dialog box, choose options from the Header and Footer drop-down lists. You can find options for listing the file name, page numbers, the date, and your name. By clicking the Custom Header or Custom Footer button, you can open the Header or Footer dialog box and construct a header or footer there. Figure 4-10 shows the Footer dialog box.
• Centering worksheet data on the page: On the Margins tab of the page Setup dialog box, select Horizontally or Vertically to center the worksheet relative to the top or bottom or sides of the page. You can select both check boxes. The preview screen shows what your choices mean in real terms.
• Printing gridlines, column letters, and row numbers: By default, the gridlines, column letters, and row numbers that you know and love in a worksheet aren’t printed, but you can print them by going to the Sheet tab of the Page Setup dialog box and selecting the Gridlines check box as well as the Row and Column Headings check box.

Repeating row and column headings on each page

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:

1. On the Page Layout tab, click the Print Titles button.

   You see the Sheet tab of the Page Setup dialog box.

2. Select the Row and Column Headings check box.

   You can find this check box under Print.

3. 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.

4. 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.

5. Click the Range Selector button to enlarge the dialog box and see it again.

   The text box now lists a cell range address.

6. Repeat Steps 3 through 5 to select column or row headings.

7. 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.