Excel chapter 4 Large data sets

To unlock the rows and columns from remaining onscreen as you scroll...

.click Freeze Panes in the Window group and select Unfreeze Panes, which only appears on the menu when you have frozen rows and/or columns. After you unfreeze the panes, the Freeze Panes option appears instead of Unfreeze Panes on the menu again. When you freeze panes and press Ctrl+Home, the first unfrozen cell is the active cell instead of cell A1. For example, with column F and rows 1 through 5 frozen in Figure 4.3, pressing Ctrl+Home makes cell G6 the active cell. If you want to edit a cell in the frozen area, click the particular cell to make it active and edit the data.

To reset all page breaks back to the automatic page breaks, complete the following steps:

1. Click Breaks in the Page Setup group. 2. Select Reset All Page Breaks.

To remove a manual page break, complete the following steps:

1. Click a cell below a horizontal page break or a cell to the right of a vertical page break. 2. Click Breaks in the Page Setup group and select Remove Page Break.

To set a manual break at a specific location, complete the following steps:

1. Click the cell that you want to be the first row and column on a new printed page. For example, if you click cell D50, you create a page for columns A through C, and then column D starts a new page. 2. Click the Page Layout tab. 3. Click Breaks in the Page Setup group and select Insert Page Break. Excel displays a solid blue line in Page Break Preview or a dashed line in Normal view to indicate the manual page breaks you set.

Print Break

Based on the paper size, orientation, margins, and other settings, Excel identifies how much data can print on a page. indicating where data will start on another printed page.

Print order is the sequence in which the pages are printed...

By default, the pages print in this order: top-left section, bottom-left section, top-right section, and bottom-right sec- tion. However, you might want to print the entire top portion of the worksheet before printing the bottom portion. To change the print order, open the Page Setup dialog box, click the Sheet tab, and then select the desired Page order option

Another way to print part of a worksheet is to select the range you want to print....

Click the File tab and click Print. Click the first arrow in the Settings section and select Print Selection. This provides additional flexibility compared to using a defined print area in situations in which you may be required to print materials outside a consistent range of cells.

To set a print area, complete the following steps:

Select the range you want to print. Click the Page Layout tab and click Print Area in the Page Setup group. Select Set Print Area.

Using Freeze

To freeze one or more rows and columns, use the Freeze Panes option. Before select- ing this option, make the active cell one row below and one column to the right of the rows and columns you want to freeze. For example, to freeze the first five rows and the first column, make cell B6 the active cell before clicking the Freeze Panes option.

making descriptive column and row labels....

When you click Print Titles in the Page Setup group on the Page Layout tab, Excel opens the Page Setup dialog box with the Sheet tab active so that you can select which row(s) and/or column(s) to repeat on each page of a printout

To avoid large sets of data printing on multiple papers you should

always use print preview

To identify where these automatic page breaks will occur...

click Page Break Preview on the status bar or in the Workbook Views group on the View tab. In Page Break Preview,

Freezing is the process....

of keeping rows and/or columns visible onscreen at all times even when you scroll through a large dataset. Table 4.1 describes the three freeze options. TabLe 4.1 Freeze Options option Description Freeze panes Keeps both rows and columns above and to the left of the active cell visible as you scroll through a worksheet. Freeze top row Keeps only the top row visible as you scroll through a worksheet. Freeze First Column Keeps only the first column visible as you scroll through a worksheet.

To add print areas where each print area will print on a separate page...

select the range you want to print, click Print Area, and then select Add to Print Area. To clear the print area, click Print Area in the Page Setup group and select Clear Print Area.

To repeat rows or columns at the top or left of each page when printed...

select the row(s) that contain the labels or titles (such as row 5) in the Rows to repeat at top box to display $5:$5. To print the row labels at the left side of each page, select the column(s) that contain the labels or titles (such as column A) in the Columns to repeat at left box to display $A$A.

The default Print settings...

send an entire dataset on the active worksheet to the printer. However, you might want to print only part of the worksheet data. If you display the worksheet in Page Break view, you can identify which page(s) you want to print. Then click the File tab and select Print. Under Settings, type the number(s) of the page(s) you want to print. For example, to print page 2 only, type 2 in the Pages text box and in the to text box. You can further restrict what is printed by setting the print area, which is the range of cells that will print. For example, you might want to print only an input area or just the transactions that occurred on a particular date.