GRAFA ONLY EXCEL

Excel Lesson 5

Overview

 Watch the Excel Lesson 5 Video

 Using the L5_Titan_Inventory_Summary Excel file, complete the worksheet using the instructions

below saving it as “firstName_lastName_L5_Titan_Inventory_Summary”.

 Complete the Lesson 5 Quiz using the completed worksheet.

 Upload the competed “firstName_LastName_L5_Titan_Inventory_Summary” to the “Lesson 5 Excel

Submission Link”.

Objectives

1. Duplicate spreadsheets.

2. Consolidate data using 3-D referencing.

3. Highlight data using conditional formatting.

Lesson Instructions

Background: You will be completing a Summary Inventory worksheet for the Titan Off-Campus Shops based

on focused product inventories of individual stores. You will also be highlighting inventory values that fall

below acceptable amounts. The workbook you will use is the L5_Titan_Inventory_Summary file. This

workbook contains inventory worksheets for individual stores (which are complete and you will leave alone),

and a Focused Products Inventory sheet for one of the stores (which you will use). Open the file and

complete the following instructions. (Note: when no specific cell reference is given, you are to use your best

judgment based upon how the worksheet is set up for you to decide what cells are involved or where a result

should be placed.)

1. Re-save the file to either your desktop or other storage device using the name

“firstName_lastName_L5_Titan_Inventory_Summary”. (Note firstName and LastName are your own

first and last names).

2. Create three additional “Focused Products” worksheets, one for the Placentia Store , one for the

Harbor Store, and one to be used as a summary, by making copies of the “Focused Products –

Chapman” worksheet.

3. Correct the new worksheets subtitles by replacing the words “Chapman Store”, to “Placentia Store”,

“Harbor Store”, and “Summary” respectively. Also, re-name the worksheet tabs to “Focused Products

– Placentia”, and “Focused Products – Harbor”, and “Focused Products-Summary”.

4. Re-order the four “Focused Products” worksheets so that the Summary is first, followed by Chapman,

then Harbor, and finally Placentia.

5. Replace the inventory quantities on the Focused Products – Harbor, and Focused Products – Placentia

sheets to the values below.

Lesson 5, P a g e | 2

Harbor Placentia

Sweatshirts - Standard 38 16

Sweatshirts - Premium 3 2

Baseball Hats 14 19

T-Shirts Standard 20 2

T-shirts Premium 31 29

Sweatpants 21 23

Shorts 2 3

Mugs 18 13

6. On the Focused Products – Summary sheet, replace the quantity for Sweatshirts-Standard (cell C4),

with a 3-D reference function which adds up the quantities of the three “Focused Products” sheets.

On the Summary sheet, copy this formula down for the column (through C11). (Be sure not to include

the three additional worksheets which are showing all the inventory that are also included in this

workbook)

7. Apply conditional formatting to the Quantity column on the Focused Products – Summary sheet so

that if the inventory levels drop below 10, they will appear with a red background and a red font color.

Project Complete.

 Take Lesson 5 Quiz.

 Close and upload completed workbook to Lesson 5 Excel Submission Link.