Project 2E Plants

In the following project, you will edit a worksheet detailing the current inventory of plants at the Pasadena facility. Your completed worksheet will look similar to Figure 2.57.
PROJECT FILES

For Project 2E, you will need the following file: e02E_Plants

You will save your workbook as: Lastname_Firstname_2E_Plants
PROJECT RESULTS
Apply 2A skills from these Objectives:

• 1. Use Flash Fill and the SUM, AVERAGE, MEDIAN, MIN, and MAX Functions
• 2. Move Data, Resolve Error Messages, and Rotate Text
• 3. Use COUNTIF and IF Functions and Apply Conditional Formatting
• 4. Use Date & Time Functions and Freeze Panes
• 5. Create, Sort, and Filter an Excel Table

1. Start Excel. From your student files, locate and open e02E_Plants, and then Save the file in your Excel Chapter 2 folder as Lastname_Firstname_2E_Plants

2. To the right of column B, insert two new columns to create new blank columns C and D. By using Flash Fill in the two new columns, split the data in column B into a column for Item # in column C and Category in column D. As necessary, type Item # as the column title in column C and Category as the column title in column D. Delete column B.

3. By using the Cut and Paste commands, cut column C—Category—and paste it to column H, and then delete the empty column C. Apply AutoFit to columns A:G.

4. In cell B4, insert a function to calculate the Total Items in Stock by summing the Quantity in Stock data, and then apply Comma Style with zero decimal places to the result.

5. In each cell in the range B5:B8, insert functions to calculate the Average, Median, Lowest, and Highest retail prices, and then apply the Accounting Number Format to each result.

6. Move the range A4:B8 to the range D4:E8, apply the 40% – Accent4 cell style to the range, and then select columns D:E and AutoFit.

7. In cell C6, type Statistics and then select the range C4:C8. In the Format Cells dialog box, merge the selected cells, and change the text Orientation to 25 Degrees. Format the cell with Bold, a Font Size of 14 pt, and then change the Font Color to Blue-Gray, Text 2. Apply Middle Align and Align Right.

8. In the Category column, Replace All occurrences of Vine Roses with Patio Roses.

9. In cell B10, use the COUNTIF function to count the number of Specialty plant types in the Category column.

10. In cell H13, type Stock Level In cell H14, enter an IF function to determine the items that must be ordered.

If the Quantity in Stock is less than 50 the Value_if_true is Order Otherwise the Value_if_false is OK Fill the formula down through cell H42.

11. Apply Conditional Formatting to the Stock Level column so that cells that contain the text Order are formatted with Bold Italic and with a Color of Green, Accent 6.

Apply conditional formatting to the Quantity in Stock column by applying a Gradient Fill Green Data Bar.

12. Format the range A13:H42 as a Table with headers, and apply the style Table Style Light 20. Sort the table from A to Z by Item Name, and then filter on the Category column to display the Specialty types.

13. Display a Total Row in the table, and then in cell A43, Sum the Quantity in Stock for the Specialty items. Type the result in cell B11. Click in the table, and then on the Design tab, remove the total row from the table. Clear the Category filter.

14. Merge & Center the title and subtitle across columns A:H, and apply Title and Heading 1 styles, respectively. Change the theme to Mesh, and then select and AutoFit all the columns.

15. Set the orientation to Landscape. In the Page Setup dialog box, center the worksheet Horizontally, insert a custom footer in the left section with the file name, and set row 13 to repeat at the top of each page. Display the Print Preview. Apply the Fit All Columns on One Page setting.

16. As the Tags, type plants inventory, Pasadena As the Subject, type your course name and section number. Be sure your name displays as the Author.

17. Save your workbook.

If directed by your instructor to do so, submit your paper printout, your electronic image of your document that looks like a printed document, or your original Excel file.

If required by your instructor, print or create an electronic version of your worksheet with formulas displayed. In the upper right corner of the Excel window, click Close.