Illustrated Excel 2016 Module 7: SAM Project 1b Jennifer Grayson 1

Subject:MS ExcelPrice:19.87 Bought4

Share With

Illustrated Excel 2016 Module 7: SAM Project 1b

Jennifer Grayson

1. Christina is the manager of West End Pets, a local pet store that began selling products

online on January 1st. Christina is helping the store owner analyze the web store invoice data

for the first week of online sales. To make her work easier, she decides to format the existing

worksheet data into a table.

Switch to the Week of January 1 sheet, and then format the range A4:I34 as a table with

headers using the Table Style Dark 9 table style. (Hint: Depending on your version of Office,

the table style may be written as Orange, Table style Dark 9.)

Add a style to a table.

Include a header row.

2. The store owner has decided that the employee number information isn't necessary for

invoice records. Delete the Employee # column from the table. (Hint: Delete the table column, not the

worksheet column.)

Delete a column.

3. Christina needs to calculate the shipping costs for each invoice. The company charges a flat

rate shipping charge of $5. Shipping charges are waived for invoices of $100 or more.

In cell H5, create a formula using the IF function and structured references to determine the

shipping based on the following criteria:

a. If the value in the Amount column ([Amount]) is less than 100, the shipping charge is 5.

b. If not, the shipping charge is 0.

If the formula in cell H5 does not automatically fill the table column, fill the remaining cells

with the formula you just created.

Enter a formula into a cell.

Copy a formula into a range.

4. Christina also needs to calculate the total amount for each invoice.Insert a new table column to the right of the Shipping column.

Insert a new table column.

5.Enter the column label Total in cell I4.

Enter a column label.

6.Use structured references to enter a formula without using a function in cell I5 that

calculates the total by adding the values in the Amount ([Amount]) and the Shipping

([Shipping]) columns.

If the formula in cell I5 does not automatically fill the table column, fill the remaining cells

with the formula you just created.

Enter a formula into a cell.

7. Format the Total table column using the Accounting number format with two decimal places

and $ as the symbol.

 

Format cells using a number style.

8. Eleanor Diaz canceled her order.

Delete row 24, which contains Eleanor Diaz's invoice, from the table.

Delete a record from a table.

9. Christina wants to check if any records were entered twice by mistake and delete any she

finds. Remove any duplicate records from the table. (Hint: There should be one duplicate record.)

Remove any duplicate records 

10. Christina needs to distribute invoice information to the online sales director. To make it

easier to compare sales for each day of the week, she decides to organize the data first by

date, then by amount.

Sort the table on two levels using the following criteria:

a. 1st Level: Date in ascending (Oldest to Newest) order

b. 2nd Level: Amount in ascending (Smallest to Largest) order

Sort a table on multiple ranges.

11. Christina received a late invoice from Mary Carlson and needs to enter her information into

the table.

Add a record to the last row of the table using the data shown in bold in Table 1 in the

Instructions file.

Add a record to a table.

12. The final table should display the shipping and total information.

Add a Total row to the table. In cell H34, update the total row functions to display the sum of

the values in the Shipping column.

Insert a total row in a table.

Enter a formula into a cell.

13.Christina was informed by a customer service representative that Holly Gennaro requested a

change of her last name to her married last name of McClain. Christina needs to update the

table to incorporate this change.

In the range B5:B33, use the Find and Replace feature to locate the last name Gennaro and

replace it with McClain (not including a period).

Replace occurrences of a word.

14. Because the worksheet will print on multiple pages, Christina thinks it best to display the

invoice numbers and the first and last names on each printed sheet.

Add the range A:C as print titles that repeat at the left of each printed page.

Repeat rows and columns in Print Layout.

15. Before Christina hands over the workbook she wants to identify the name of the store in the

header.Enter the text West End Pets as a header in the center header section of the worksheet.

Switch back to Normal View, if necessary.

Add a header to a worksheet.