Excel Skill Review Ch 4.1

Question 1

1) Naming Worksheets (Skill 4.2)

Answer 1

To rename a worksheet:

1) Double-click the sheet tab or right-click the worksheet tab and select Rename.
2) Excel highlights the sheet name, allowing you to replace it as you type.
3) Type the new sheet name, and press Enter.

Question 2

2) Changing the Color of Sheet Tabs (Skill 4.3)

Answer 2

To change a worksheet tab color:

1) Right-click the sheet tab and point to Tab Color to display the color palette.
2) Hover the mouse pointer over each color to preview how the color will look when the worksheet is active.
3) Click the color you want.

Question 3

3) Moving and Copying Worksheets (Skill 4.4)

Answer 3

To move a worksheet within a workbook:

1) Click the worksheet tab and hold down the mouse button.
2)Notice that the mouse pointer changes to the shape.
3) Drag the mouse cursor to the position where you want to move the sheet, and release the mouse button. Excel places a small black triangle to let you know where the sheet will be placed.

Question 4

4) Grouping Worksheets (Skill 4.6)

Answer 4

To group worksheets:

1) Click the first worksheet tab.
2) Hold down Shift and click the tab for the last worksheet you want included in the group. If you want to select noncontiguous worksheets (sheets that are not next to each other), press Ctrl instead, and then click each sheet tab.
3) Notice that the title bar now includes Group after the file name.
4) Make the change you want to the sheet. The change will be made to all sheets in the group.
5) To ungroup, click any sheet tab that is not part of the group.

Question 5

5) Modifying Column Widths and Row Heights (Skill 4.9)

Answer 5

To make the column automatically fit the contents:

1) Move your mouse over the right column boundary.
2) The cursor will change to a (two black arrows crossed) shape.
3) Double-click the right column border.

Question 6

6) Changing the Worksheet View (Skill 4.15)

Answer 6

Excel offers three ways to view a worksheet.

Just as the name implies, Normal view is the typical working view. In Normal view, Excel shows the aspects of the worksheet that are visible only on-screen. Elements that are visible only when printed (like headers and footers) are hidden.

Page Layout view shows all the worksheet elements as they will print, including page margins and headers and footers as shown in Figure EX 4.21. You will work with Page Layout view when you learn about headers and footers.

Page Break Preview view allows you to manipulate where page breaks occur when the worksheet is printed. You will work with Page Break Preview view when you learn about inserting page breaks.

To switch between worksheet views, click the appropriate button in the status bar at the bottom of the Excel window, or on the View tab, in the Workbook Views group, click the button for the view you want.

Question 7

7) Adding Headers and Footers (Skill 4.15)

Answer 7

A header is text that appears at the top of every page, just below the top margin; a footer is text that appears at the bottom of every page, just above the bottom margin. Typically, headers and footers display information such as dates, page numbers, sheet names, file names, and authors’ names.

To add a header or footer to a worksheet from Page Layout view:

Switch to Page Layout view by clicking the Page Layout button on the status bar.
The header area has three sections with the text Add header in the center section. If you do not see the Add header text, move the mouse pointer to the area just above row 1 to make the header area visible. Click the header section where you want to add information (left, center, or right).
The Header & Footer tab appears.
In the Header & Footer group, click the Header button and select one of the predefined headers, or click a button in the Header & Footer Elements group to add a specific header element such as the sheet name or the current date. Excel inserts the code for the header element as show in Figure EX 4.22. Once you click away from the header, you will see the actual header text as shown in Figure EX 4.23.

Question 8

8) Applying Themes (Skill 4.8)

Answer 8

A theme is a unified color, font, and effects scheme. When you apply a theme to the workbook, you ensure that all visual elements work well together, giving the workbook a polished, professional look. When you create a new blank workbook in Excel, the Office theme is applied by default.
To apply a theme to a workbook:

On the Page Layout tab, in the Themes group, click the Themes button to expand the gallery.
Roll your mouse over each theme in the gallery to preview the formatting changes.
Click one of the themes to apply it to your workbook.
From the Themes group, you can apply specific aspects of a theme by making a selection from the Theme Colors, Theme Fonts, or Theme Effects gallery. Applying one aspect of a theme (for example, colors) will not change the other aspects (fonts and effects).

Theme Colors—Limits the colors available from the color palette for fonts, borders, and cell shading. Notice that when you change themes, the colors in the color palette change.

Theme Fonts—Affects the fonts used for cell styles (including titles and headings). Changing the theme fonts does not limit the fonts available to you from the Font group on the Ribbon.

Theme Effects—Controls the way graphic elements in your worksheet appear. Chart styles change according to the theme color and effects.

Question 9

9) Splitting Workbooks (Skill 4.14)

Answer 9

n Excel, you can split the worksheet view into two or four panes. Each pane scrolls independently of the other(s), so you can see two (or four) different areas of the worksheet at the same time. This can be especially helpful if you want to compare data in multiple parts of the worksheet at the same time.

To split the worksheet view:

Click the cell in the worksheet where you would like to split the view. You can select an entire row or column as the split point.
If you want to split the worksheet into two horizontal panes, click a cell in column A.
If you want to split the worksheet into two vertical panes, click a cell in row 1.
If you want to split the worksheet into four panes, click any cell in the worksheet. The cell you selected will be the top left cell in the pane in the lower-right quadrant.
On the View tab, in the Window group, click the Split button.

Question 10

10) Inserting and Deleting Rows and Columns (Skill 4.7)

Answer 10

You may find you need to add rows or columns of new information into the middle of your workbook. Adding a new row will shift down other rows; adding a new column will shift other columns to the right.

To insert a column:

Place your cursor in a cell in the column to the right of where you want the new column.
On the Home tab, in the Cells group, click the Insert button arrow and select Insert Sheet Columns.
The new column will appear to the left of the selected cell.

Question 11

11) Hiding and Unhiding Worksheets (Skill 4.12)

Answer 11

Just as you can hide rows and columns in a worksheet, you can also hide entire worksheets. When you hide a worksheet, the sheet is hidden from view, but it is not deleted from the workbook. Hiding worksheets can be helpful when you have several worksheets in a large workbook, but only need to be working in a few sheets at once.

To hide a worksheet:

On the Home tab, in the Cells group, click the Format button.
Point to Hide & Unhide and click Hide Sheet.
To unhide a worksheet:

On the Home tab, in the Cells group, click the Format button.
Point to Hide & Unhide and click Unhide Sheet.
In the Unhide dialog, if necessary, select the worksheet to unhide, and click OK.

Question 12

12) Freezing and Unfreezing Rows and Columns (Skill 4.10)

Answer 12

If you have a large spreadsheet, you may want to freeze part of the worksheet so it is always visible. By doing this, you can keep column headings and row labels visible as you scroll through your data.

To freeze part of the worksheet:

Arrange the worksheet so the row you want to be visible is the top row or the column you want is the first column visible at the left.
On the View tab, in the Window group, click the Freeze Panes button.
If you want the first row to always be visible, click Freeze Top Row.
If you want the first column to always be visible, click Freeze First Column.
If your worksheet has both a header row and a column of labels in the first column, use the Freeze Panes option to freeze the worksheet at the selected cell, so the rows above the cell and the columns to the left of the cell are always visible.

Select the cell immediately below the header row and immediately to the right of the label column (usually cell B2).
On the View tab, in the Window group, click the Freeze Panes button, and select Freeze Panes.
To return your worksheet to normal, click the Freeze Panes button and select Unfreeze Panes.

Question 13

13) Hiding and Unhiding Rows and Columns (Skill 4.11)

Answer 13

When you hide a row or column, the data still remain in your workbook, but they are no longer displayed onscreen and are not part of the printed workbook. Hiding rows can be helpful when you want to print a copy of your workbook for others but do not want to share all the information contained in your workbook.

To hide a row or column:

Select any cell in the row or column you want to hide.
On the Home tab, in the Cells group, click the Format button.
Point to Hide & Unhide, and click Hide Rows or Hide Columns.
To unhide a row or column:

Select the rows or columns on either side of the row or column you want to unhide.
On the Home tab, in the Cells group, click the Format button.
Point to Hide & Unhide, and click Unhide Rows or Unhide Columns.

Question 14

14) Changing Worksheet Orientation (Skill 4.18)

Answer 14

Orientation refers to the direction the worksheet prints. It doesn’t affect the way the worksheet looks on your computer screen. The default print setting is for portrait orientation—when the height of the page is greater than the width (like a portrait hanging on a wall). If your workbook is wide, you may want to use landscape orientation instead, where the width of the page is greater than the height.

You can set the worksheet orientation from the Page Layout tab on the Ribbon:

On the Page Layout tab, in the Page Setup group, click the Orientation button.
Click the Portrait or Landscape option.
You can also change the worksheet orientation when you print:

Click the File tab to open Backstage view.
Click Print.
In the Settings section, click the button displaying the current orientation setting, and then click the orientation setting you want.

Question 15

15) Setting Up Margins for Printing (Skill 4.21)

Answer 15

Margins are the blank spaces at the top, bottom, left, and right of a printed page. You may need to adjust the margins individually for each worksheet in your workbook to ensure they print exactly as you intend. Excel provides three margin settings:

Normal—Uses Excel’s default margins: 0.75 inch for the top and bottom and 0.7 inch for the left and right.
Wide—Adds more space at the top, bottom, left, and right sides.
Narrow—Reduces the amount of space at the top, bottom, left, and right sides, so more of your worksheet fits on each printed page.
If none of the automatic margins options is exactly what you want, the Custom Margins… option opens the Page Setup dialog where you can specify exact margins.

To change the margins, on the Page Layout tab, in the Page Setup group, click the Margins button, and click one of the preset margins options: Normal, Wide, or Narrow, or click Custom Margins… to specify your own values.
Because you will often want to adjust margins once you are ready to print, Excel allows you to adjust the margins directly from Backstage view.

Click the File tab to open Backstage view.
Click Print.
In the Settings section, click the button displaying the current margins setting, and then select the option you want.

Question 16

16) Scaling Worksheets for Printing (Skill 4.20)

Answer 16

When printing your worksheet, you can set the scale and specify that the worksheet prints at a percentage of the original size or at a maximum number of pages wide and/or tall. Each worksheet in the workbook has its own scale settings.

On the Page Layout tab, in the Scale to Fit group, select the option(s) you want.

Click the Width arrow and select the maximum number of pages you want the worksheet to print across.
Click the Height arrow and select the maximum number of pages you want the worksheet to print vertically.
Click the Scale box and enter a percentage to grow or shrink the worksheet when printed.

Because you often need to adjust scaling once you are ready to print, Excel has included scaling options on the Print page in Backstage view:

Click the File tab to open Backstage view.
Click Print.
In the Settings section, click the button displaying the current scaling setting and select the setting you want to use.

Question 17

17) Showing and Hiding Worksheet Elements (Skill 4.17)

Answer 17

Gridlines are the lines that appear on the worksheet defining the rows and columns. Gridlines make it easy to see the individual cells in your worksheet. By default, gridlines are visible on-screen when you are working in Excel, but they do not print.

Headings are the numbers at the left of rows and the letters at the top of columns. By default, Excel displays the row and column headings on-screen to make it easy to identify cell references but they do not print.

To hide gridlines or headings on-screen for the active worksheet:

On the Page Layout tab, in the Sheet Options group, click the View check box under Gridlines or Headings to remove the checkmark.

To print the gridlines and headings when you print the worksheet:

On the Page Layout tab, in the Sheet Options group, click the Print check box under Gridlines or Headings.

Question 18

18) Printing Selections, Worksheets, and Workbooks (Skill 4.22)

Answer 18

By default, Excel will print the current, active worksheet. You can change the printing options, however, to print only part of a worksheet or the entire workbook at once.

1) Click the File tab to open Backstage view.
2)Click Print.
3) In the Settings section, the first button displays which part of the workbook will print. By default, Print Active Sheets is selected. To change the print selection, click the button, and then click one of the other options:
Print Entire Workbook—Prints all the sheets in the workbook.
Print Selection—Prints only the selected cells in the active worksheet, overriding any print area definitions in the active worksheet.
Print Selected Table—Prints the table only (available only if the current selection is within a defined table).
4) If you want to ignore the defined print area, click Ignore Print Area at the bottom of the list.
5) Click the Print button to print.

Question 19

19) Printing Titles (Skill 4.21)

Answer 19

If your worksheet includes a large table of data that prints on more than one page, you should ensure that the column or row labels print on every page.

To repeat rows and columns on every printed page:

1) On the Page Layout tab, in the Page Setup group, click the Print Titles button.
2) In the Page Setup dialog, on the Sheet tab, click in the Rows to repeat at top box, and then click and drag to select the rows to repeat. You can also type the row reference(s) using the format $1:$1. This example would repeat the first row only.
3) Click in the Columns to repeat at left box, and then click and drag to select the columns to repeat. You can also type the column reference(s) using the format $A:$A. This example would repeat the first column only. $A:$B would repeat columns A and B on every printed page.
4) Click OK.

Question 20

20) Inserting Page Breaks (Skill 4.16)

Answer 20

Excel automatically inserts page breaks so columns and rows are not split across pages when you print. However, you may want to control where page breaks happen so your worksheet prints in a more logical order. When the worksheet is in Normal view, page breaks appear as faint dotted lines. To view the page breaks more clearly, switch to Page Break Preview view.

To manually insert a new page break:

1) 2From Normal view or Page Break Preview view, begin by selecting the cell below and/or to the right of where you want the new page break.
2) On the Page Layout tab, in the Page Setup group, click the Breaks button.
3) Click Insert Page Break.
4) A new page break is inserted to the left of the selected column or above the selected row.
When the worksheet is in Page Break Preview view, automatic page breaks appear as blue dotted lines and manually inserted page breaks appear as solid blue lines. You can manually move a page break by clicking the page break line, and then dragging to the right or left or up or down. Release the mouse button when the line appears where you want the break. Notice that if you move an automatic page break, the line changes from dotted to solid.

To remove a manual page break, select any cell adjacent to (to the right of or below) the break, then on the Page Layout tab, in the Page Setup group, click the Breaks button, and click Remove Page Break.