New Perspectives Excel 2019 | Module 6: SAM Project 1b Sato Instrument Collection MANAGING YOUR DATA WITH DATA TOOLS GETTING STARTED   Open the file NP_EX19_6b_ FirstLastName _1

Subject:MS ExcelPrice:20.01 Bought9

Share With

New Perspectives Excel 2019 | Module 6: SAM Project 1b

Sato Instrument Collection

MANAGING YOUR DATA WITH DATA TOOLS

GETTING STARTED

 

Open the file

NP_EX19_6b_

FirstLastName

_1.xlsx

, available for download from the

SAM website.

?

Save the file as

NP_EX19_6b_

FirstLastName

_2.xlsx

by changing the “1” to a “2”.

?

If you do not see the

.xlsx

file extension in the Save As dialog box, do not type it. The

program will add the file extension for you automatically.

?

With the file

NP_EX19_6b_

FirstLastName

_2.xlsx

still open, ensure that your first and

last name is displayed in cell B6 of the Documentation sheet.

?

If cell B6 does not display your name, delete the file and download a new copy from the

SAM website.

PROJECT STEPS

1.

Kaito and Naomi Sato started collecting musical instruments as a hobby a few years ago

and are now organizing their collection in an Excel workbook. Naomi asks for your help in

updating and analyzing the data.

Switch to the

Brass

worksheet. Unfreeze the top row of the worksheet.

2.

Sort the data in the Brass table first in descending order by the Purchase Date field and

then in descending order by the Est. Value field.

3.

Insert a Total Row in the Brass table, and then use the Total row to calculate the total of

the values in the Price and Est. Value fields.

4.

Naomi wants to highlight the brass instruments she and Kaito bought on the same date.

Apply a conditional formatting rule to the range E3:E10 that uses

Green Fill with Dark

Green Text

for duplicate values.

5.

Switch to the

Percussion

worksheet. Freeze the first column of the worksheet.

6.

Format the range B2:G10 as an Excel table with headers using the

Gold, Table Style

Medium 12

table style. Enter

Percussion

as the name of the table.

7.

Naomi and Kaito bought a new snare drum last week, and Naomi wants to include it in

the Percussion table.

Add the record shown in Table 1 to the end of the Percussion table.

Table 1: New Record for the Percussion Table

B

C

D

E

F

G

11

Snare

drum

Sousa

Good

2/16/22

100

150

New Perspectives Excel 2019 | Module 6: SAM Project 1b

 

New Perspectives Excel 2019 | Module 6: SAM Project 1b

8.

Naomi and Kaito have more string instruments than any other type. Naomi wants to

summarize the string instrument data using subtotals to show the value of each type.

Switch to the

Strings

worksheet and add subtotals as follows:

a.

Sort the table by the Instrument field in ascending order.

b.

Convert the table to a normal range.

c.

Insert subtotals into the range B2:G12, with the subtotals appearing at each

change in the Instrument column value. The subtotals should use the SUM function

and include subtotals for the Price and Est. Value fields.

9.

Switch to the

Wind

worksheet and remove the duplicate record based on the values in

the Instrument, Manufacturer, and Condition columns. The duplicate record has an

incorrect Purchase Date of 7/11/2020.

10.

The data bars in the last two columns make some of the numbers hard to read and could

coordinate better with the formatting of the Wind table.

Change the Data Bars conditional formatting for the range F3:G12 to use the

Gradient

Fill Orange Data Bar

format.

11.

Naomi wants to calculate the totals for the instrument collection data. Switch to the

All

Instruments

worksheet, and then create the following formulas:

a.

In cell B40, use the

COUNTA

function with a structured reference to count the

values in the Instrument column of the Instruments table.

b.

In cell F40, use the

SUM

function with a structured reference to total the values in

the Price column of the Instruments table.

c.

In cell G40, use the

SUM

function with a structured reference to total the values in

the Est. Value column of the Instruments table.

12.

Naomi and Kaito are preparing to sell some of the instruments, and Naomi wants to

include the sales date in the Instruments table

Add a table column to the end of the table.

Your workbook should look like the Final Figures on the following pages. Save your changes, close

the workbook, and then exit Excel. Follow the directions on the SAM website to submit your

completed project.

 

New Perspectives Excel 2019 | Module 6: SAM Project 1b

Final Figure 1: Brass Worksheet

Final Figure 2: Percussion Worksheet

Final Figure 3: Strings Worksheet

Final Figure 4: Wind Worksheet

Final Figure 5: All Instruments Worksheet