Excel 2019 Skills Approach – Ch 1 Skill Review 1.2

COURSE NAME COSA_2_F21_T/R

Actions

Steps required for project completion

1. DOWNLOAD

Click the Start File link, then complete the project using the instructions below.

REQUIRED:

Start File

Resources

…or, use the File Download Wizard

2. UPLOAD

Save your in-progress and completed work by dragging your file onto this section or clicking below.

Upload my file

3. SUBMIT

Click the “Grade my file” button to submit and grade attempt 1 of 3.

NOTE: You must upload a file before you can submit it for grading.

Grade my file

PROJECT SUB-VIEW:

Instructions

Previous Attempts

Best Practices

PROJECT INSTRUCTIONS

|

• WINDOWS  MAC

In this project you will create a new workbook to track the cost of books for your college classes. For each book, you will enter the purchase price, the potential sell-back price, and the cost difference. You will calculate totals using AutoSum. You will then create a new workbook from a template, and practice changing the zoom level and arranging the workbooks.

• Skills needed to complete this project:

Entering and Editing Text and Numbers in Cells (Skill 1.4)

Navigating a Workbook (Skill 1.2)

Inserting Data Using AutoFill (Skill 1.7)

Applying Number Formats (Skill 1.5)

Using the Status Bar (Skill 1.15)

Using AutoSum to Insert a SUM Function (Skill 1.13)

Entering Simple Formulas (Skill 1.10)

Using the Recommended Charts Feature (Skill 1.9)

Exploring Charts (Skill 1.8)

Creating a New Workbook Using a Template (Skill 1.17)

Checking Spelling (Skill 1.19)

Arranging Workbooks (Skill 1.18)

Changing the Zoom Level (Skill 1.16)

Important: Download the resource files needed for this project from the Resources link. If they download in a zipped folder, make sure to extract the files after downloading the resources zipped folder. Visit the SIMnet instant help for step-by-step instruction.

OPEN THE START FILE EX2019-SKILLREVIEW-1-2. THE START FILE WILL BE RENAMED AUTOMATICALLY TO INCLUDE YOUR NAME. CHANGE THE FILE NAME IF DIRECTED TO DO SO BY YOUR INSTRUCTOR AND SAVE IT.

IF THE WORKBOOK OPENS IN PROTECTED VIEW, CLICK THE ENABLE EDITING BUTTON IN THE MESSAGE BAR AT THE TOP OF THE WORKBOOK SO YOU CAN MODIFY IT.

THE NEW WORKBOOK OPENS WITH ONE SHEET (SHEET1). CELL A1 IS SELECTED.

IN CELL A1, TYPE THE TITLE FOR THE WORKSHEET: TEXTBOOKS

Enter data in the worksheet as follows:Table EX 1.6 displays different textbooks and their corresponding costs, values, and the difference for each book.ABCD

3

BookCostValueDifference

4

Book18545

5

Book210080

6

Book3250125

7

Book480.9524.25

Use AutoFill to add two additional books to the list.

CLICK CELL A4, HOLD DOWN THE LEFT MOUSE BUTTON AND DRAG THE MOUSE TO CELL A7. RELEASE THE MOUSE BUTTON. THE CELL RANGE A4 THROUGH A7 SHOULD NOW APPEAR SELECTED.

CLICK THE FILL HANDLE (LOCATED AT THE LOWER RIGHT CORNER OF THE SELECTED CELL RANGE).

DRAG DOWN TO CELL A9, AND RELEASE THE MOUSE BUTTON.

EXCEL ADDS BOOK 5 AND BOOK 6 TO THE LIST.

Book 5 cost $125.00 and can be sold for $75.00. Book 6 cost $175.00 and can be sold for $100.00. Add these data to the worksheet.

CLICK CELL B8 AND TYPE: 125

PRESS TAB AND TYPE: 75

CLICK CELL B9 AND TYPE: 175

PRESS TAB AND TYPE: 100

PRESS ENTER.

Modify the status bar to display the minimum value.

RIGHT-CLICK ANYWHERE ON THE STATUS BAR.

CLICK MINIMUM TO ADD A CHECKMARK.

CLICK ANYWHERE TO DISMISS THE MENU.

• Use the status bar to check the total value of the books and the minimum value.
• CLICK CELL C4. PRESS AND HOLD SHIFT AND CLICK CELL C9. RELEASE THE SHIFT KEY.
• LOOK AT THE STATUS BAR AND FIND THE SUM VALUE (449.25).
• LOOK AT THE STATUS BAR AND FIND THE MIN VALUE (24.25).
• Use AutoSum to calculate total cost and total value. The totals should be placed in cells B10 and C10.
• CLICK CELL A10 AND TYPE: TOTAL
• PRESS TAB.
• CELL B10 SHOULD BE SELECTED. PRESS AND HOLD SHIFT AND CLICK CELL C10. RELEASE THE SHIFT KEY.
• ON THE HOME TAB, IN THE EDITING GROUP, CLICK THE AUTOSUM BUTTON.
• Change the number format for the cost and value numbers to the Accounting Number Format.
• CLICK CELL B4. PRESS AND HOLD SHIFT. CLICK CELL C10. RELEASE SHIFT.
• ON THE HOME TAB, IN THE NUMBER GROUP, CLICK THE ACCOUNTING NUMBER FORMAT BUTTON.
• Enter a formula in cell D4 to calculate the difference between the cost and the value for Book1. The formula will use only relative references because in the next step, you will use AutoFill to copy the formula down the column.

CLICK CELL D4.

TYPE: =

CLICK CELL B4.

TYPE: –

1. CLICK CELL C4.

PRESS ENTER.

Use AutoFill to copy the formula to calculate the difference for books 2 through 6 and the total.

1. CLICK CELL D4.

CLICK THE AUTOFILL HANDLE. HOLD DOWN THE LEFT MOUSE BUTTON AND DRAG TO CELL D10. RELEASE THE MOUSE BUTTON.

Use the Quick Analysis tool to insert a pie chart representing the cost of books.

1. SELECT CELLS A3:B9. CLICK CELL A3, HOLD DOWN THE LEFT MOUSE BUTTON, AND DRAG THE MOUSE TO CELL B9. RELEASE THE MOUSE BUTTON. THE CELL RANGE A3 THROUGH B9 SHOULD NOW APPEAR SELECTED.

THE QUICK ANALYSIS TOOL BUTTON SHOULD APPEAR NEAR THE LOWER RIGHT CORNER OF THE SELECTED CELL RANGE. (IF THE QUICK ANALYSIS TOOL BUTTON IS NOT VISIBLE, MOVE YOUR MOUSE CURSOR OVER THE SELECTED CELL RANGE AGAIN, WITHOUT CLICKING. THIS ACTION SHOULD MAKE THE BUTTON APPEAR.)

CLICK THE QUICK ANALYSIS TOOL BUTTON, AND THEN CLICK CHARTS.

CLICK PIE TO INSERT A PIE CHART.

OBSERVE HOW EACH BOOK IS ASSIGNED A COLOR IN THE CHART LEGEND.

1. THE SIZE OF EACH PIE PIECE REPRESENTS THE COST OF EACH BOOK RELATIVE TO THE TOTAL COST OF ALL THE BOOKS. BOOK 3 IS THE MOST EXPENSIVE BOOK AND IS REPRESENTED BY THE LARGEST PIECE OF THE PIE.

Change the chart title.

CLICK THE COST CHART TITLE ONCE TO SELECT IT.

TYPE: COMPARATIVE COST OF BOOKS

1. PRESS ENTER.

SAVE THE WORKBOOK. DO NOT CLOSE IT OR EXIT EXCEL.

Create a new file from a template called Personal Monthly Budget. NOTE: If you are unable to find the Personal Monthly Budget template or are unable to complete this step due to your school’s computer lab restrictions, download the template from the Resources link. Open the template and move on to step 18.

CLICK THE FILE TAB TO OPEN BACKSTAGE VIEW.

CLICK NEW.

Open a new file based on the Personal Monthly Budget template. (This step may require an active Internet connection.)

THE PERSONAL MONTHLY BUDGET TEMPLATE MAY APPEAR IN THE LIST OF TEMPLATES AUTOMATICALLY. IF IT DOES NOT, YOU WILL NEED TO SEARCH FOR IT. IN THE SEARCH ONLINE TEMPLATES BOX, TYPE: PERSONAL MONTHLY BUDGET

CLICK THE TEMPLATE PREVIEW.

CLICK CREATE.

The workbook based on the Personal Monthly Budget template opens. This is a complex workbook using many advanced techniques. The good news is that you don’t have to understand how this workbook was created in order to use it.

CLICK THE PERSONAL MONTHLY BUDGET WORKSHEET TAB TO BEGIN WORKING.

SCROLL THROUGH THE WORKSHEET TO REVIEW ALL THE BUDGET INCOME AND EXPENSES. EDIT CATEGORY NAMES AND VALUES TO REFLECT YOUR PERSONAL BUDGET.

IF YOU THINK YOU MIGHT LIKE TO COME BACK TO THIS WORKBOOK LATER FOR YOUR PERSONAL USE, THIS IS A GOOD POINT TO SAVE IT. BE SURE NOT TO CLOSE THE FILE. USE THE FILE NAME: [YOUR INITIALS]PERSONAL MONTHLY BUDGET

1. You should still have two workbooks open: the textbooks workbook from the beginning of the project and the Personal Monthly Budget workbook based on the template. Arrange the workbooks so you can see both at the same time.

ON THE VIEW TAB, IN THE WINDOW GROUP, CLICK THE ARRANGE ALL BUTTON.

IN THE ARRANGE WINDOWS DIALOG, CLICK THE VERTICAL RADIO BUTTON. CLICK OK.

1. The Personal Monthly Budget workbook should be active. If not, click the title bar for that window. Review the Personal Monthly Budget sheet and change the zoom level to 50% so you can see more of the data at once.

IF NECESSARY, CLICK THE PERSONAL MONTHLY BUDGET TAB.

ON THE VIEW TAB, IN THE ZOOM GROUP, CLICK THE ZOOM BUTTON.

IN THE ZOOM DIALOG, CLICK THE 50% RADIO BUTTON FOR THE ZOOM OPTION YOU WANT. CLICK OK.

SAVE AND CLOSE THE EX2019-SKILLREVIEW-1-2 FILE. IF YOU MADE CHANGES TO THE PERSONAL MONTHLY BUDGET WORKBOOK AND YOU PLAN TO CONTINUE USING IT, BE SURE TO SAVE THE CHANGES AND THEN CLOSE IT.

UPLOAD AND SAVE YOUR EX2019-SKILLREVIEW-1-2 PROJECT FILE.

SUBMIT PROJECT FOR GRADING.