3.6 WORKING WITH WORKSHEETS

In this section we will learn to name, add, delete, group or ungroup worksheets. We will also learn to format a worksheet for printing.

3.6.1 Name a Worksheet

The default names of Worksheets are Sheet1, Sheet3 and Sheet3. Since these names are not useful and descriptive, we will learn to rename the worksheet.

You can rename a worksheet using any of the following ways:

Right mouse click on the Sheet1 tab. Menu appears as shown in figure 14(a).
Select option Rename. The name Sheet1 is highlighted by a black box and becomes editable.
Type the new name at the tab.
Press Enter. The worksheet is renamed. OR
Click the Format command of the Cells group in Home Tab. A menu appears as shown is Figure 14(b).
Select option Rename Sheet under Organize Sheet. The sheet name is highlighted by a black box and becomes editable.
Type the new name at the tab.
Press Enter. The worksheet is renamed.

3.6.2 Insert a New Worksheet

You can add worksheets to the workbook anytime you want. The new sheets added will be named as Sheet4 and so on. There are many ways that you can add a new worksheet:

Click on the Insert Worksheet icon near the worksheet tabs OR press Shift+F11.
A new worksheet after the last tab will be added. OR
Right mouse click on the worksheet tab.
Choose Insert… from the menu (shown in Figure 14(a)). Insert dialog box opens.
Select Worksheet. Click Ok
A new worksheet before the selected tab will be added.
Click the down arrow of Insert command in the Cells group of Home Tab. A menu appears.
Choose Insert Sheet from the menu
A new worksheet before the selected worksheet will be added.

3.6.3 Delete a Worksheet

Any number of worksheets can be deleted irrespective of the fact that they contain any data or not. But, there should be at least one worksheet in the workbook. To delete a worksheet:

Right mouse click on the worksheet tab.
Choose Delete from the menu (shown in Figure 14(a)).
The selected worksheet is deleted. OR
Click the down arrow of Delete command in the Cells group of Home Tab. A menu appears.
Choose Delete Sheet from the menu
The selected worksheet is deleted.

3.6.4 Grouping Worksheets

If the multiple worksheets of a workbook contain identical formula and formatting, then you can group them together. When the worksheets are grouped together, then any change made to one worksheet will be applied to all other worksheets in the group. You can group both contiguous and noncontiguous worksheets. To group contiguous worksheets:

Click on the first worksheet tab.
Press the Shift key
While holding the Shift key, click the last worksheet tab you want in the Spreadsheet group.
Release the Shift key
All the sheets from the first sheet to the last sheet are now grouped. The tab colour will now change to white indicating that they are grouped together

To group noncontiguous worksheets:

Click on the first worksheet tab.
Press the Ctrl key
While holding the Ctrl key, select all the other worksheets you want in the group.
Release the Ctrl key.
All the sheets that you selected while keeping the Ctrl key pressed would be grouped together and sheet tabs will appear white.

3.6.5 Ungrouping Worksheets

To ungroup worksheets:

Right mouse click one of the worksheets in the group.
Select Ungroup Sheets from the menu.

3.6.6 Reposition Worksheets in a Workbook

To change the position of worksheets in a workbook:

Click and hold the worksheet tab that is to moved until an arrow appears on the left corner of the sheet.
Drag the worksheet to the desired location .

3.6.7 Hide Worksheets

To hide a worksheet:

Right-click on the tab of the sheet you wish to hide.
Select Hide OR
Click Format button.
Select Hide & Unhide under Visibility in the menu.
Choose Hide Sheet option.

To unhide a worksheet:

Right-click on tab of any sheet.
Select Unhide…. A dialog box with the list of hidden worksheets is displayed.
Choose the sheet to unhide OR
Click Format button.
Select Hide & Unhide under Visibility in the menu.
Choose Unhide Sheet… option. A dialog box with the list of hidden worksheets is displayed.
Choose the sheet to unhide.

3.6.8 Formatting and Printing the Workbook

In this section, we will learn how to set page headers, footers, margin, etc and prepare our workbook for printing.

To Change Page Orientation

Select Page Layout Tab on the Ribbon.
Click Orientation command in the Page Setup group
Choose the orientation you want – Landscape (horizontal) or Portrait (vertical).

To Change Paper Size

Select Page Layout Tab on the Ribbon.
Click Size command in the Page Setup group.
A drop down menu appears with all the available paper sizes. Current size is highlighted.
Choose the size option. Page size of workbook changes.

To Set Page Margins

Select Page Layout Tab on the Ribbon.
Click Margins command in the Page Setup group.
Choose the predefined margins from the list. or
Customize your margins by selecting Custom Margins from the menu and entering the desired margins in the appropriate fields.

To Set Headers and Footers

The header is the text that appears in the top margin of every page of the printed worksheet. Similarly, the footer is the text that appears in the bottom margin of every page of the printed worksheet. To add header and footer:

Select Insert Tab on the Ribbon
Click the Header & Footer button in the Text group. A Design context tab Spreadsheet appears under Header & Footer Tools. And worksheet changes to Page Layout view from the Normal view. Page Layout view structures the worksheet so that it is easy to change the format of the worksheet.
Both Header and Footer are divided into three sections: left, center, right. You can type in your custom header/ footer or you can use predefined headers and footers. Click on Header/ Footer button in Header & Footer group of Design context tab to see the list of pre-defined headers and footers.

To Use Scale to Fit

Scale to Fit is a useful feature that can help you format spreadsheets to fit on a page.

Select the Page Layout tab.
Locate the Scale to Fit group.
Enter a specific height and width, or use the percentage field to decrease the spreadsheet by a specific percent.

Be careful with how small you scale the information – you should be able to read it.

To Define a Print Area

At times you may want to print just a part of the whole worksheet. In that case you need to select your print area that you need to be printed. To define your print area:

Click and drag your mouse to select the cells you wish to print.
Click the Print Area command in Page Setup group of Page Layout Tab.
Choose Set Print Area. Now, only the selected cells will print. You can confirm this by viewing the spreadsheet in Print Preview.

To return to printing entire worksheet, which is the default setting, click the Print Area command and select Clear Print Area.

To Print Titles on Each Page

Print Title command allows you to select specific rows and/or columns to appear on each printed sheet. This helps when the worksheet prints into many pages, since we can have row and column heading printed on each page for easy association and readability.

Select the Page Layout tab.
Click the Print Titles command in Page Setup group. The Sheet tab of Page Setup dialog box opens.
Click the icon at the end of the field Rows to repeat at top.
Select the row headings in the spreadsheet that you want to appear on each printed page.
Repeat for the column, if required.
Click OK. The select row/ column will now appear on each printed page.

Preview before Printing

Click Office Button
Select Print  Print Preview. The worksheet opens in the Print Preview mode. In Print Preview, you can access many of the same features that you can from the Ribbon, through the Page Setup dialog box. However, in Print Preview you can see how the spreadsheet will appear in printed format.
Click Print to print the document or Close Print Preview to come back to the document in original mode.

You can modify page margins, orientation, page size, etc in Print Preview mode.

To Quick Print the Document

Click Office Button.
Select Print  QuickPrint
The document prints to the default printer. It bypasses the Print dialog box.

To Print the Document

Click Office Button.
Select Print  Print. The Print dialog box appears.
Select the printer from the drop down list.
Click Properties to change any necessary settings.
Select the pages you would like to print – specific pages, all of the worksheet, a selected area, the active sheet, or the entire workbook.
Select the number of copies.
Click OK to print.

Excel 2007: Working with Worksheets