Challenge It
In this challenge activity, you will complete a project that incorporates many of the key skills learned in the Excel Unit. For this project, you are an IT manager responsible for managing the technology inventory for your college.
- Open the Excel spreadsheet Starter_Excel_Challenge. Save the file to your flash drive or other safe location as designated by your instructor and name the file Lastname_Firstname_Excel_Challenge.
- Merge and center the text in cell A1 across the range A1:G1 and apply the cell style Heading 2.
- Merge and center the text in cell A2 across the range A2:G2 and apply the cell style Heading 4.
- Apply the Title cell style to the range A11:F11.
- Autofit all cell contents, both column widths and row heights, if necessary.
- In cell B4, construct a function to SUM the total quantity in stock.
- In cell B5, construct a function that will find the AVERAGE price (or value) of all of the items. Format as currency with two decimal places.
- In cell B6, construct a function that will find the MEDIAN price (or value) of all items. Format as currency with two decimal places.
- In cell B7, construct a function that will find the lowest of MIN price (or value) for all items. Format as currency with two decimal places.
- In cell B8, construct a function that will find the highest of MAX price (or value) for all items. Format as currency with two decimal places.
- Format the Values in column C as currency with two decimal places.
- Select the range A4:B9, and move it to F4:G9.
- Insert a new column after column B and label it Item Type.
- Insert another new column before Column C and label it Item #. Your new Column C should be Item # and Column D should be Item Type.
- In cell C12, use Flash fill to list the Item # from column B in column C.
- In cell D12, use Flash Fill to list the Item Type from column B in column D.
- Select column B and delete it. If necessary, move cells G4:H9 back over to cells F4:G9.
- In cell G9, construct a function that will count the number of Laptop Categories.
- Select the range F4:G9 and move to the range back to A4:B9 and apply the 20% Accent 1 cell style.
- In cell F12, construct a function that will determine if the stock level is ok, or needs to be checked. The threshold for the stock level is 7. If the quantity is less than 7, then it needs to be checked. Use “Check” and “OK” as the true and false values. Use the fill handle to fill the function to cell F47.
- Use conditional formatting, highlight cells rule, text that contains “Check”. Format the text with Light Red Fill with Dark Red Text.
- Run Spelling and Grammar check.
- Rename Sheet1 to Inventory.
- Add a new sheet and name it Summary.
- On the Summary Sheet, in cell A1 type PVCC Summary.
- In cell A2, construct a formula that will display the current day and time.
- Merge and center the text in cell A1 across the range A1:B1 and apply the cell style Heading 4.
- Merge and center the text in cell A2 across the range A2:B2 and app