Test 7 CS 385
Insert a Function with a 3-D Reference 1. In cell F3, insert a 3-D formula to sum the value of cell E3 across Qtr1, Qtr2, Qtr3 and Qtr4 sheets. 2. Copy the function in cell F3 to the range F4:F11. 3. Simultaneously apply the Accounting Number Format to cells F3 and F11. 4. Apply Comma Style to the range F4:F10. 5. Select cell F11 and apply the Total cell style.
1. Select cell F3 and type =SUM( . Click the first sheet tab, hold shift, and then click QTR4 sheet tab. Click cell E3 and press CRTL+ENTER
Test the Data Validation Rule 1. Test the data validation by typing 600000 in cell D4. Click Yes in the Potentially Invalid Data message box. 2.Use the Data Validation to Circle Invalid Data.
1. follow rule 2. go to formula, click circle invalid data
Open and Arrange Worksheets Open three new windows within the workbook so that all four windows display the Qtr1 worksheet. Arrange the windows of the active workbook so that they are tiled on the screen.
1. Click the view tab, click new window. repeat steps. 2. Click the view tab, click arrange all. Select windows of active workbook
Insert a Formula with a 3-D Reference 1. Enter the label Quarterly Total in cell A11 with Bold and Purple font. 2. In cell B3, insert a relative reference to cell E3 on the Qtr1 worksheet. Use the fill handle to copy the formula through cell B11 [the remaining formulas will autocomplete].
1. Follow Directions 2. In cell B3, type = and then click QTR1 worksheet. Click cell E3 and click CRTL+ENTER. Drag down column
Trace Precedents and Dependents Trace Dependents on cell B3 of the Qtr1 worksheet. Trace Precedents of cell E11. Simultaneously remove all the trace arrows.
1. Go to formulas and click trace dependents 2. ^ click trace precendents 3. click remove arrows
Check for and Repair Errors The Qtr2 worksheet contains errors. Use the Error Checking dialog box to identify and repair the error. Locate and repair the circular reference.
1. On the formulas tab in the formula auditing group, click the error checking arrow, and then click arrow checking. click the update formula to include cells button 2. click the error checking arrow, point to circular references, and click e11. In the formula bar, select E11, then type D11. CRTL+Enter
Insert Hyperlinks 1. Insert a hyperlink in cell B2 to the range E2:E11 in the Qtr1 worksheet. 2. Insert a hyperlink in cell C2 to the range E2:E11 in the Qtr2 worksheet. 3. Insert a hyperlink in cell D2 to the range E2:E11 in the Qtr3 worksheet. 4. Insert a hyperlink in cell E2 to the range E2:E11 in the Qtr4 worksheet. 5. Click the hyperlink in cell E2.
1. On the insert page, click link, say in this page, type cell reference
Group and Fill Across Worksheets 1. Group the Qtr1, Qtr2, Qtr3, Qtr4 and Yearly Totals sheets. 2. With the Qtr1 worksheet displayed, fill the contents and formatting in cell A1 across all worksheets. 3. Fill the heading information in range A2:A10 on the Qtr1 worksheet across all worksheets. 4. Ungroup the worksheets.
1. Press shift and select all worksheets 2. Select cell A1, on the home tab, click in editing group and fill, and then accross worksheet. Click ok. 3. Select A2:A10 on the worksheet, click fill on the home tab, and across all wrkeets 4. Click one worksheet
Enter and Format Data on Grouped Worksheets 1. Group the Qtr1, Qtr2, Qtr3, Qtr4 and Yearly Totals sheets. 2. With the worksheets grouped, change the width of column A to 18.00. 3. Ungroup the worksheets. 4. Group the Qtr1 through Qtr4 worksheets. Use AutoSum to simultaneously total the values of the data in the range B3:E11. Simultaneously format the ranges B3:E3 and B11:E11 using the Accounting Number Format. 5. Type Monthly Totals in cell A11.
1. Press shift and select all worksheets 2. Select column A and click column width, type 18, click ok 3. click on a worksheet 4. Click sheet Qtr4 and shift, select range B3:E11 and on the home tab in the editing group, click AutoSum. Select range B3:E3 hold ctrl and select the range B11:E11. On the home tab in the number group, click the accounting format 5. Type Monthly totals in A11
Set Up a Watch Window Setup a Watch Window for the range B3:F3 on the Yearly Totals worksheet. Move the Watch Window below the dataset with the top-left corner in cell A14. Adjust the size of the Watch Window to enlarge the height down to row 25 and over into column G to fully display the information within the Watch Window. Then drag the column widths within the Watch Window to display the entire book name, sheet name, and formulas. Change the December Athletic Apparel value to 175000 in cell D3 in the Qtr4 worksheet.
1. Select B3:F3 on the formulas tab click watch window and add 2. Move the watch window 3. move where it says and drag 4. change the cell number
Create a Validation Rule 1. On the Qtr4 worksheet, create a validation rule for the range B4:D4 to allow decimal values that are less than or equal to $500,000. Do not close the Data Validation dialog box. 2. Create an input message using Sales Data as the title text and Enter the sales amount for the respective month. (including the period) as the input message. Do not close the Data Validation dialog box. 3. Create an error alert using the Warning style. Enter Potentially Invalid Data as the title text and The projected maximum is $500,000. (including the period) as the error alert. Close the Data Validation dialog box.
1. Select the range B4:D4. Then click the data tab, click data validation. Click the allow arrow and then click decimals. Click the data arrow, click less than or equal to. In the maximum group click 500000 2. go off the first rule
Create a Link to Another Workbook 1. With the e09h2Indiana workbook displayed, in cell B3, enter a formula to link to cell F3 in the open e09h2Indianapolis workbook on the Yearly Totals worksheet. Change the reference to cell F3 to a relative cell reference. 2. With the e09h2Indiana workbook displayed, in cell C3, enter a formula to link to cell F3 in the open e09h2Bloomington workbook's Yearly Totals worksheet, Change the reference to cell F3 to a relative cell reference. 3. With the e09h2Indiana workbook displayed, in cell D3, enter a formula to link to cell F3 in the open e09h2SouthBend workbook's Yearly Totals worksheet, Change the reference to cell F3 to a relative cell reference.
1.With the e09h2Indiana workbook selected, click cell B3 and then type =. Point to the excel icon on the tab at bottom of screen and click the e09h2Indiana thumbnail. With the yearly totals sheet displayed, click cell F3 and then CTRL+ENTER. take the money signs out. click in the formula bar.
Encrypt a Workbook with a Password Use the password Expl0r!ng to encrypt the workbook. Close the Backstage view, if necessary.
Click the file tab, click the protect workbook view, click the encrypt with password
Mark a Workbook as Final Mark the workbook as final.
Go to final and click mark the workbook as final
Protect a Worksheet Display the Qtr1 sheet. Protect the Qtr1 worksheet using the password Expl0r!ng. Ensure that users can only select locked and unlocked cells while the workbook is protected.
In review click on protect sheet
Unlock Cells 1. Group the Qtr1, Qtr2, Qtr3, and Qtr4 sheets. 2. With the worksheets grouped, select the range B3:D10 and unlock the cells. Ungroup the worksheets.
Select B3:D10, go to home tab, click format and then lock cell