Data Management Chapter 9: Hands-on

Open e09h1Indianapolis and save it as e09h1Indianapolis_LastFirst.

Click the Qtr2 sheet tab, click each Qtr sheet tab, and then click the Yearly Totals sheet tab to see the differences.

The Qtr1 worksheet contains a title and row labels, whereas the Qtr2, Qtr3, and Qtr4 worksheets are missing the title and the row labels. The Yearly Totals worksheet contains only a few headings.

Click the Qtr1 sheet tab, press and hold Shift, and then click the Yearly Totals sheet tab.

You grouped five worksheets together. Anything you do to the active worksheet affects all grouped worksheets. The title bar displays Group after the file name. The colors of the grouped sheet tabs are faded, indicating that they are grouped.

Click cell A1 in the Qtr1 worksheet to select it, click Fill in the Editing group on the Home tab, and then select Across Worksheets.

The Fill Across Worksheets dialog box opens so that you can select what to fill from the active worksheet to the other grouped worksheets. The default option is All, which will fill in both the content and the formatting.

Click OK. Keep the worksheets grouped.

Excel fills in the formatted title from the Qtr1 worksheet to the other worksheets.

Select the range A2:A10 on the Qtr1 worksheet. Click Fill in the Editing group on the Home tab, select Across Worksheets, and then click OK.

Troubleshooting

Do not select the range A1:D10 to fill across worksheets. If you do, you will overwrite the other worksheet data with the January, February, and March labels and data. If this happens, click Undo to restore data in the other worksheets and complete Step f again.

Right-click the Yearly Totals sheet tab and select Ungroup Sheets. Click each worksheet to review the results. Save the workbook once your review is complete.

You ungrouped the worksheets. All worksheets that were grouped contain the formatted title and row labels that were copied across worksheets.

Mac Troubleshooting

Press control and click the sheet tab. Select Ungroup Sheets.

Figure 9.10 Formatted Title and Row Headings

Figure 9.10 Full Alternative Text

Step 2 Enter and Format Data on Grouped Worksheets

You will regroup the worksheets so that you can increase the width of column A in all worksheets at the same time. In addition, you want to insert monthly and department totals for the quarterly worksheets. Refer to Figure 9.11 as you complete Step 2.

Click the Qtr1 sheet tab, press and hold Shift, and then click the Yearly Totals sheet tab.

Click cell A2 and click Format in the Cells group on the Home tab. Select Column Width, type 18 in the Column width box, and then click OK.

You changed the column width of column A in all grouped worksheets to 18.

Right-click the Qtr1 sheet tab and select Ungroup Sheets.

Press and hold Shift and click the Qtr4 sheet tab.

You ungrouped all the sheets and regrouped only the four quarterly worksheets because you want to add functions and formatting to the quarterly worksheets only.

Do the following to the grouped quarterly worksheets:

Select the range B3:E11 and click AutoSum in the Editing group on the Home tab to insert department totals in column E and monthly totals in row 11.

Apply Accounting Number Format to the ranges B3:E3 and B11:E11.

Type Monthly Totals in cell A11. Keep cell A11 as the active cell, click Bold in the Font group, and then click Increase Indent in the Alignment group on the Home tab.

Type Dept. Totals in cell E2.

Select the range B11:E11, click Cell Styles in the Styles group, and then select Total.

You applied the Total cell style to the monthly totals to conform to standard accounting formatting practices that displays a single line immediately above the totals and a double line immediately below the totals.

Click cell A1, right-click the Qtr4 sheet tab, and then select Ungroup Sheets. Click each quarterly sheet tab to ensure the formats were applied to each worksheet. Save the workbook.

Figure 9.11 Data and Formatting Filled in Qtr4 Worksheet

Figure 9.11 Full Alternative Text

Step 3 Insert Hyperlinks

You want to insert hyperlinks on the Yearly Totals worksheet so that you can jump back to the respective quarterly worksheet quickly. Refer to Figure 9.12 as you complete Step 3.

Click the Yearly Totals sheet tab. Click cell B2, click the Insert tab, click Link in the Links group, and then select Insert Link.

Mac Troubleshooting

Click Link in the Links group on the Insert Tab to open the Insert Hyperlink dialog box.

The Insert Hyperlink dialog box opens so that you can specify the destination when the user clicks the hyperlink.

Click Place in This Document in the Link to section on the left side of the dialog box. (On a Mac, click This Document in the top-middle section of the dialog box.)

Click in the Type the cell reference box, delete A1, type E2:E11, click ‘Qtr1’ in the Or select a place in this document list.

This action creates a hyperlink to the range E2:E11 in the Qtr1 worksheet.

Click ScreenTip, type Qtr 1 Totals in the ScreenTip text box, and click OK. Click OK in the Insert Hyperlink dialog box.

You entered text for a ScreenTip.

Create the following hyperlinks by adapting Steps b through d:

Click cell C2. Create a hyperlink to the range E2:E11 in the Qtr2 worksheet with ScreenTip text Qtr 2 Totals.

Click cell D2. Create a hyperlink to the range E2:E11 in the Qtr3 worksheet with ScreenTip text Qtr 3 Totals.

Click cell E2. Create a hyperlink to the range E2:E11 in the Qtr4 worksheet with the ScreenTip Qtr 4 Totals.

Point to cell E2 to display the ScreenTip.

The ScreenTip displays Qtr4 Totals.

Click cell E2.

The hyperlink jumps to the destination—the range E2:E11 in the Qtr4 worksheet.

Click the Yearly Totals sheet tab and click the other hyperlinks to ensure they work. When you are finished, click the Yearly Totals sheet tab and save the workbook.

Open e09h1Indianapolis_LastFirst if you closed it at the end of Hands-On Exercise 1, and save it as e09h2Indianapolis_LastFirst, changing h1 to h2.

Click cell B3 in the Yearly Totals sheet tab. Type = and click the Qtr1 sheet tab, click cell E3 in that worksheet, and then press Ctrl+Enter.

The formula is =’Qtr1′!E3. Qtr1 refers to the worksheet, and E3 refers to the cell within that worksheet.

Double-click the cell B3 fill handle to copy the formula to the range B4:B11.

The formula in cell B4 is =’Qtr1′!E4. The sheet name stays the same, but the cell reference is a relative reference, which changes as you copy the formula.

Click cell C3 in the Yearly Totals worksheet. Type = and click the Qtr2 sheet tab. Click cell E3 in that worksheet and press Ctrl+Enter. Double-click the cell C3 fill handle to copy the formula to the range C4:C11.

The formula is =’Qtr2′!E3. Qtr2 refers to the worksheet, and E3 refers to the cell in Qtr2.

Adapt Step d to enter references to the appropriate totals in the Qtr3 and Qtr4 worksheets. Save the workbook.

The width of column E may be too narrow to display the total in cell E11. (On a Mac, the column width increases automatically.) On a PC, you will adjust the width in the next step.

Figure 9.20 Worksheet References

Figure 9.20 Full Alternative Text

Step 2 Insert a Function with a 3-D Reference

You want to calculate the total annual sales by department. Although you could simply sum the values in the Yearly Totals worksheet, you want to build a function with 3-D references to provide a cross-check that the totals are correct. Refer to Figure 9.21 as you complete Step 2.

Click cell F3 in the Yearly Totals worksheet.

Type =SUM(

You start the 3-D formula with =, the function name, and the opening parenthesis.

Click the Qtr1 sheet tab, press and hold Shift, and then click the Qtr4 sheet tab.

You grouped the worksheets together so that you can use a common cell reference for the range of cells to sum.

Click cell E3, the cell containing the quarterly sales, and press Ctrl+Enter.

Look at the Formula Bar. The function is =SUM(‘Qtr1:Qtr4’!E3). If you select the range B3:E3 in the Yearly Totals worksheet, the status bar shows that the sum is $1,306,949.09, the same unformatted value that displays when you inserted the 3-D formula.

Double-click the cell F3 fill handle to copy the formula to the range F4:F11.

Apply Accounting Number Format to the ranges B3:F3 and B11:F11. Apply Comma Style to the range B4:F10.

You applied Accounting Number Format to the first and last rows and Comma Style to the middle rows.

Apply the Total cell style to the range B11:F11. With the range B11:F11 still selected, change the column width to 14. Save the workbook.

You applied the Total cell style to designate the last row as the totals.

Figure 9.21 3-D Formulas

Figure 9.21 Full Alternative Text

Step 3 Consolidate Data

Although you used 3-D references in a function to pull in the quarterly sales for each department, you decide to create a comprehensive worksheet displaying monthly sales for the entire year. You will consolidate data into one worksheet. Refer to Figure 9.22 as you complete Step 3.

Click the Consolidated sheet tab.

Click the Data tab and click Consolidate in the Data Tools group.

The Consolidate dialog box opens. The default function is Sum.

Click Collapse Dialog to the right of the Reference box. Click the Qtr1 sheet tab, select the range A2:E11, and then click Expand Dialog.

The Reference box displays ‘Qtr1′!’Qtr1’!$A$2:$E$11.

Click Add. (On a Mac, click +.) The range you select is added to the All references list.

Repeat and adapt Steps c and d for the Qtr2, Qtr3, and Qtr4 worksheets.

Click the Top row check box to select it, click the Left column check box to select it, and then click OK.

The data from each column in each worksheet is consolidated into the worksheet. The data is still selected, but the column widths are too narrow.

Click the Home tab, click Format in the Cells group, and then select AutoFit Column Widths.

Select the range B1:N1, bold, and center horizontally the data. Select the range B10:N10 and apply the Total cell style. Save the workbook.

Open e09h3FortWayne, click OK when prompted to fix a circular error, and then save it as e09h3FortWayne_LastFirst.

Ensure that the Qtr1 worksheet is active. Click cell B3, click the Formulas tab, and then click Trace Dependents in the Formula Auditing group.

Excel displays tracer arrows from cell B3 to cells E3 and B11, indicating that value in cell B3 is used in formulas in cells E3 and B11.

Click cell E11 and click Trace Precedents in the Formula Auditing group.

Excel displays a tracer arrow, showing that the values in the range B11:D11 are used within the current cell’s formula.

Click Remove Arrows in the Formula Auditing group. Save the workbook.

The tracer arrows are removed from the worksheet.

Figure 9.35 Dependent and Precedent Arrows

Figure 9.35 Full Alternative Text

Step 2 Check for and Repair Errors

The Qtr2 worksheet contains errors. You will use the Error Checking dialog box and trace precedents to identify the errors. Refer to Figure 9.36 as you complete Step 2.

Click the Qtr2 sheet tab.

Cell E3 contains the green error checking indicator.

Click the Error Checking arrow in the Formula Auditing group and select Error Checking.

The Error Checking dialog box opens, indicating an error exists in cell E3. Excel detects that the formula in cell E3 does include a cell in the range.

Click Update Formula to Include Cells.

Excel modifies the formula from =SUM(C3:D3) to =SUM(B3:D3) to include the April sales.

Click OK in the message box that informs you that error checking is complete.

Although you used Error Checking to detect an error in the SUM function, the circular reference was not detected as it is detected by the Circular References option in the Error Checking menu. The status bar still indicates that a circular reference exists.

Click the Error Checking arrow in the Formula Auditing group, point to Circular References, and then select $E$11.

The formula in cell E11 includes a reference to E11 in the function argument. The status bar displays Circular Reference: E11. (On a Mac, the status bar displays Circular References.)

Change the formula to =SUM(B11:D11). Save the workbook.

The status bar no longer indicates that a circular reference exists.

Figure 9.36 Error Checking

Figure 9.36 Full Alternative Text

Step 3 Use the Iferror Function to Detect Errors

For Qtr3, you want to insert the AVERAGEIF function to calculate the average monthly revenue for departments that contain the word ball in column A. The function should calculate averages for only the Football, Baseball, and Basketball departments. However, when you create the function, #DIV/0! is returned for rows that do not contain ball in the department name. You will nest the AVERAGEIF function within an IFERROR function to avoid displaying #DIV/0! Refer to Figure 9.37 as you complete Step 3.

Click the Qtr3 sheet tab and click cell F3.

Type =AVERAGEIF( and click Insert Function.

The Function Arguments dialog box opens so that you can enter the arguments for the AVERAGEIF function.

Type A3 in the Range box, type *ball in the Criteria box, and then type B3:D3 in the Average_range box. Click OK.

Cell A3 is the range (cell) to be evaluated. The criteria *ball uses the asterisk wildcard to include anything before the text ball. If the criterion is true, Excel will calculate the average of the values in the range B3:D3. The result is #DIV/0! as shown on the left side of Figure 9.37.

Double-click the cell F3 fill handle to copy the function to the range F4:F10.

Only cells F7, F8, and F10 contain averages. The other departments do not contain ball, which leads to the error.

Click cell F3 and click between = and A in the Formula Bar. Type IFERROR( and click within IFERROR in the Formula Bar. Click Insert Function.

The Function Arguments dialog box opens. The AVERAGEIF function becomes the Value argument for the IFERROR function.

Type

“” in the Value_if_error box and click OK.

Because the function detects an error, it does not try to calculate the average. It returns an empty text string.

Double-click the cell F3 fill handle to copy the function to the range F4:F10. Save the workbook.

The averages display only for the three departments that meet the condition. The #DIV/0! error does not display for the other departments now (refer to the right side of Figure 9.37).

Open e09h3FortWayne_LastFirst if you closed it at the end of Hands-On Exercise 3, and save it as e09h4FortWayne_LastFirst, changing h3 to h4.

Click the Qtr1 sheet tab, press and hold Shift, and click the Qtr4 sheet tab.

You want to unlock the same cells in the quarterly worksheets.

Select the range B3:D10.

You selected the range containing values for the monthly department sales values.

Click the Home tab, click Format in the Cells group, and then select Lock Cell. Click Format to display the menu again.

The Lock Cell option does not change to Unlock Cell. However, when you unlock a cell, the Lock Cell command does not have a border around the padlock icon on the menu. (On a Mac, the check mark by the Lock Cell command is removed.) The selected cells are unlocked and will remain unlocked when you protect the worksheet later.

Click Format to close the menu, right-click the Qtr1 sheet tab, and then select Ungroup Sheets. Save the workbook.

Figure 9.48 Unlock Input Cells

Figure 9.48 Full Alternative Text

Step 2 Protect a Worksheet

Now that you have unlocked the input cells, you are ready to protect the quarterly worksheets. The other cells in the worksheets still have the Lock Cell property enabled. After you protect the worksheets, users will not be able to modify those cells. Refer to Figure 9.49 as you complete Step 2.

Click Format in Cells group on the Home tab and select Protect Sheet. Ensure the Select locked cells and Select unlocked cells check boxes are selected.

The Protect Sheet dialog box opens. The Protect worksheet and contents of locked cells check box is selected by default. In addition, the users can Select locked cells and Select unlocked cells. Although they can select locked cells, they will not be able to change those cells. Notice that users cannot format data, insert columns or rows, or delete columns or rows.

Type Expl0r!ng (using a zero, not the letter O) in the Password to unprotect sheet box and click OK. (On a Mac, type the password in the Verify box.)

Remember that passwords are case sensitive and that you must remember the password. If you forget the password, you will not able to unprotect the sheet.

Read the caution, type Expl0r!ng in the Reenter password to proceed box, and then click OK.

You password-protected the Qtr1 worksheet.

Click cell E3 and try to type 100. Click OK to close the warning box.

Troubleshooting

If you can enter the new value without the warning box, the cell is not locked. Click Undo to restore the formula, review Steps 1 and 2a-d, and then click the cell.

Adapt and repeat Steps a–d to set the same password on the Qtr2, Qtr3, and Qtr4 worksheets. Save the workbook.

The Protect Sheet option is unavailable for grouped sheets. You must protect each sheet individually.

Figure 9.49 Protect the Worksheets

Figure 9.49 Full Alternative Text

Step 3 Encrypt a Workbook File with a Password

You want to protect the workbook so that unauthorized people cannot open the file. Therefore, you will encrypt the workbook with a password. Refer to Figure 9.50 as you complete Step 4.

Mac Troubleshooting

Complete Step 3 with these steps: Click the File menu, click Save As, and click Options. Type the password to open. Type the password to modify. Click OK. Click Save.

Click the File tab and click Info.

Protect Workbook has a light yellow background with a note One or more worksheets in this workbook have been locked to prevent unwanted changes to the data. The four quarterly worksheets are listed with links to unprotect them.

Click Protect Workbook and select Encrypt with Password.

The Encrypt Document dialog box opens.

Type Expl0r!ng in the Password box and click OK.

The Confirm Password dialog box opens.

Type Expl0r!ng in the Reenter password box and click OK.

Click Protect Workbook and select Encrypt with Password, delete the password in the Password box.

You deleted the password for this Hands-On Exercise file so that you can access the workbook without having to enter a password.

Click OK and click the Back arrow. Save the workbook.