Excel Practice 3
Here is a video demonstrating the skills in this practice. Please note it does not exactly match the instructions:
Complete the following Practice Activity and submit your completed project.
For Excel Practice 3, we will use Excel to manage the Technology Inventory at the college, where you work as a Data Analyst. Key skills in this practice are Flash Fill, Advanced Functions, and Conditional Formatting.
- Start Excel. Click Open, then Browse to where your starter files are located. Open the starter file Starter_Excel_Practice3.
Name the workbook as Yourlastname_Yourfirstname_Excel_Practice_3.
- We will use Flash Fill to separate the data in column D. In cell E4, type Department and then press Tab. In cell F4 type Division and then press enter. These are our column labels for the flash fill.
- In cell E5 type 150 and press enter. With cell E6 as the active cell, go to the Home Tab, Editing Group, Choose the arrow next to Fill and Choose Flash Fill. Notice how the contents from column D are automatically filled to column E, and only the Department was filled.
- With cell F5 as the active cell, type 80 and press enter. With cell F6 selected, press the shortcut key for Flash Fill – Ctrl + E. Notice how the two-digit division code was filled for column F.
- Delete column D.
- AutoFit columns B through E. If necessary deselect wrap text before auto fitting.
- Merge and Center cell A1 across the range A1:F1. Then apply the Heading 1 cell style.
- Merge and Center cell A2 across the range A2:F2. Then apply the Heading 2 Style.
- Select cell B26. On the Formulas Tab, in the function library group, choose Insert Function and search for the SUM function, select Go and then select OK. Apply the SUM function to the range A5:A24. Before searching for the SUM function in the Search Box, you may need to clear out any existing text.
- Click cell B27, and using the same method as above apply the AVERAGE function to the range C5:C24.
- Click in cell B28, and using the same method apply the MEDIAN function to the range C5:C24.
- Click cell B29, and using the same method apply the MIN function to the range C5:C24.
- Click cell B30, and using the same method apply the MAX function to the range C5:C24.
- Select the range B27:B30 and apply the Accounting Number Format with no decimal places.
- Select column C and set the width to 50 pixels.
- ## symbols should display in some of the cells in column C. This means that the column is not wide enough to display the underlying value. Select columns B:C and AutoFit the columns. Notice how the ## go away and the underlying value displays.
- Select the range A4:F4 and apply the 40% – Accent 1 Themed cell style and center the text.
- Select cell A26, change the font size to 14, apply Bold and Italic, and select the Accent 1 Cell Style under Themed Cell Styles. Align-right the text.
- Select the range A27:A30 and on the Home Tab, Cells Group, choose the arrow next to Format and launch the Format Cells dialog bo