Instructions
Hands on – Part 1
Course Objectives:
· CO5: Organize, format, and manage data efficiently using cell names, data tables, custom formats, formulas, cell styles, and protected cells, with print and display settings to enhance presentation and usability.
Estimated time to Complete: 20 minutes
Description:
Assigning names to cells and referring to the cells in a formula.
Required Resources:
· Access to Microsoft Excel.
Deliverable:
After completing the assignment, upload the spreadsheet to the Canvas module.
Setup:
Access to Microsoft Excel.
Lab Steps:
Creating Cell Names
1. Create a spreadsheet with the following data:
|
Mortgage Payment Calculator |
|||
|
Date |
19-Oct-25 |
Rate |
3.88% |
|
Item |
House |
Term |
15 |
|
Price |
$519,000.00 |
Monthly Payment |
|
|
Down Payment |
$115,000.00 |
Total Interest |
|
|
Loan Amount |
Total Cost |
||
2. To create names based on row titles, select the range of cells that include the Price, Down Payment, and Load Amount along with their values.
3. Click the “Create from Selection” button on the Formulas tab to display the Create Names from Selection dialog box.
4. Click the OK button to name the cells selected in the right column of the selection.
5. Click on the Price amount. Notice that the name of the cell is “Price” and is displayed in the upper-left side of the menu bar.
Enter the Loan Amount Formula Using Cell Names
1. Select the cell next to the Loan Amount.
2. While entering the formula, double-click the cells to include in the formula to use the names of the cells rather than the cell references. Enter the formula “=Price-Down_Payment”.
3. Click the Enter box to assign the formula to the selected cell.
4. Create a name for the Down Payment and Loan Amount cells.
5. Create names for the Rate and Term cells.
6. Select the cell next to the Monthly Payment. Enter the PMT function “=-PMT(Rate/12, 12*Term, Loan_Amount)”.
7. Create a name for the Monthly Payment cell.
8. Select the cell next to the Total Interest. Enter the formula “=12*Term*Monthly_Payment-Loan_Amount”.
9. Create a name for the Total Interest cell.
10. Select the cell next to the Total Cost. Enter the formula “=price + total_interest”.
11. Format the cells with amounts to be of type currency.
12. Now, enter a new term of 10 years instead of 15 years. Notice that the calculations are updated based on the term of 10 years.
Uploading completed worksheet
1. Save the workbook to your local drive.
2. Upload the saved spreadsheet to Canvas.
Part 2
Course Objectives:
· CO5: Organize, format, and manage data efficiently using cell names, data tables, custom formats, formulas, cell styles, and protected cells, with print and display settings to enhance presentation and usability.
Estimated time to Complete: 20 minutes
Description:
Creating data tables.
Required Resources:
· Access to Microsoft Excel.
Deliverable:
After completing the assignment, upload the spreadsheet to the Canvas module.
Setup:
Access to Microsoft Excel.
Lab Steps:
Creating a Data Table
1. Select a cell and type the following data table section title under the Mortgage Payment Calculation section.
|
Varying Interest Rate Schedule |
|||
|
Rate |
Monthly Payment |
Total Interest |
Total Cost |
2. Select a cell and click the Format Painter button on the Home tab to copy the format of the cell, and then click another cell and apply the copied format.
Create a Percentage Series Using the Fill Handle
1. Type the first two percentages in the series, each in an individual cell.
|
3.00% |
|
3.13% |
2. Select the two cells containing the percentages
3. Drag the fill handle through the last cell in the desired series—do not lift your finger or release the mouse button
4. Lift your finger or release the mouse button to generate the percent series
5. Click the Increase Decimal button on the Home tab to increase the number of decimal places
Enter the Formulas in the Data Table
1. Choose a cell next to the 3.00% under Monthly Payment. Enter the formula: “=-PMT(A13/12, 12*Term, Loan_Amount)”
2. Chose the cell next to the 3.00% under Total Interest. Enter the formula: “=-PMT(A13/12, 12*Term, Loan_Amount)”
3. Chose the cell next to the 3.00% under Total Cost. Enter the formula: “=Price + C13”
4. Use the file handle to calculate the variable interest rate for the other rates.
Uploading completed worksheet
1. Save the workbook to your local drive.
2. Upload the saved spreadsheet to Canvas.
Part 3
Course Objectives:
· CO5: Organize, format, and manage data efficiently using cell names, data tables, custom formats, formulas, cell styles, and protected cells, with print and display settings to enhance presentation and usability.
Estimated time to Complete: 20 minutes
Description:
Printing sections of a worksheet.
Protecting and unprotecting cells in a worksheet.
Required Resources:
· Access to Microsoft Excel.
Deliverable:
After completing the assignment, upload the spreadsheet to the Canvas module.
Setup:
Access to Microsoft Excel.
Lab Steps:
Printing Sections of the Worksheet
1. Set up a the Monthly Payment Calculator worksheet for printing by click the Page Setup Dialog Box Launcher on the Page Layout tab to display the Page Setup dialog box.
2. If necessary, click the Page tab to display the Page sheet and then click Fit to in the Scaling area to set the worksheet to print on one page.
3. Click the Sheet tab and then click “Black and white” in the Print area to select the check box.
4. Click the OK button to close the Page Setup dialog box
Set the print area of the worksheet
1. Select the Varying Interest Rate Schedule data range to set as the print area, and then click the Print Area button on the Page Layout tab to display the Print Area menu.
2. Click “Set Print Area” on the Print Area menu to set the range of the worksheet which Excel should print.
3. To clear the print area, click the Print Area button on the Page Layout tab to display the Print Area menu and then click the “Clear Print Area” command to reset the print area to the entire worksheet.
To Name and Print Sections of a Worksheet
1. If necessary, select the range to name, click the Name box in the formula bar, and type the range name “Varying_Interest_Rate_Schedule”.
2. Press the ENTER key to create a range name.
3. Select the desired range to print.
4. Click File on the ribbon to open Backstage view and then click Print to display the Print screen
5. Click the “Print Active Sheets” button in the Settings area and select Print Selection to select the desired item to print.
6. Capture a screen shot of the spreadsheet and upload it to Canvas.
Protect a Worksheet
1. Select the Mortgage Payment Calculator range of cells.
2. Right-click the selected ranges to display a shortcut menu and mini toolbar.
3. Click Format Cells on the shortcut menu to display the Format Cells dialog box.
4. Click the Protection tab and then click Locked to remove the check mark.
5. Click the OK button to close the Format Cells dialog box.
6. Deselect the ranges.
7. Click the Protect Sheet button on the Review tab to display the Protect Sheet dialog box.
8. When Excel displays the Protect Sheet dialog box, ensure that the “Protect worksheet and contents of locked cells” check box at the top of the dialog box and the first two check boxes in the list contain check marks so that the user of the worksheet can select both locked and unlocked cells.
9. Click the OK button to close the Protect Sheet dialog box
Uploading completed worksheet
1. Save the workbook to your local drive.
