IT204 Assignment 7 Instructions
While working through the exercises in this assignment, explore the various options in each tab and think about how you will use them for future school assignments or in the workplace.
For assignment 7, open the file for week 7 labeled “Assignment 7 Excel.xlsx”.
This spreadsheet already has data you can use.
On the Excel page, add a second worksheet by clicking the + symbol after Sheet 1 at the lower left. Copy the data from page one onto page two. (This preserves the original spreadsheet.) Right click on the sheet 2 tab and rename it as Employee Table. Right click and choose a tab color.
On sheet 2, expand the columns by placing your cursor between the columns on the header row and dragging or double clicking. Select all of the columns and rows and create a table from the Insert tab, Tables, Table and click My table has headers. You will now see drop drop-down arrows for each column. Click the arrow on the last name column and Sort A-Z. Save this as a screenshot and copy it into a MS Word document. You can also use the Snipping tool by holding down the Windows key, Shift and S. Label the screenshot as “table” using a bold 14 point font. (Along with other screenshots, you will upload the document for your assignment 7 submission.)
Click the arrow on the Department header and click the select all to turn it off. Now, select the Marketing and Sales departments to see only the who works there. Now, select the Salary column. On the Styles tab, use Conditional Formatting to select Highlight Rules, Greater Than, and enter 50,000. Highlight the Salary column and choose Accounting from the drop- down arrow on the Number tab. If necessary, make the column wider by double clicking or dragging the cell’s edge on the column header. Take a screenshot and save your table onto your Word document. Label as “Conditional Formatting” using a bold 14 point font.
Now, you create information using a pivot table. On sheet 1, select any cell. From the Insert tab, Tables, choose Pivot Table. Make sure New Worksheet is selected and click OK. Rename the sheet Pivot Table and choose a tab color. Click in the pivot table box and choose Employee ID, Department and Salary. On the pivot table you just created, select all of the salaries, but do not include the total. From the Styles tab on the Home ribbon, choose Conditional Formatting, Top/Bottom Rules/Above Average and choose a color. Take a screenshot and add it to your Word document. Label as “Pivot Table” using bold 14 point font.
To keep the top row or left column visible while scrolling, you can use Freeze Frame. Use sheet one. From the View tab, Window option, click the down arrow of the Freeze Pane option and choose Freeze Top Row. Scroll down to row 24 and you will see that the header row stays visible. Take a screenshot and add it to your Word document. Label as “Freeze Frame” using a bold 14 point font.
On sheet 1, click the column header for Salary to select the entire column. Click Filter on the Data ribbon. A down arrow option will appear on the Salary header. Select Number Filter and choose the Between option. Enter Greater than 45,000 and Less than 65,000. Screenshot the results and add it to your Word document. Label as “Filter’ using a bold 14 point font.
Put your name and date at the top of the Word document and submit as Assignment 7.
Employee ID Last Name First Name Department Salary
212-05 Allison James Security 38,500
990-16 Robinson Jerry Sales 78,900
722-16 Kalid Lee Sales 68,700
245-14 Oslow Sven Marketing 45,600
238-14 Ekbert Mary Marketing 49,800
456-21 Lambert Jill Events 41,500
212-05 Lowe Bill Security 38500
097-21 Smith Lou Events 62750
129-14 Patel Parth Marketing 61250
197-16 Lemmuel Sam Sales 73900
267-11 Andersen Leslie Administrative Asst. 41600
312-11 Johnson Gilbert Administrative Asst. 38400
216-15 Long Steve Logistics 43500
302-15 Elbert Robert Logistics 41,250
175-21 Lemual Lester Marketing 33,450
782-16 Lee Lennie Sales 63,100
906-16 Smith Wanda Sales 50,100
107-13 O’Dell Juele Service 46,750
201-13 Datsun Blue Service 39,800
222-13 Wang Sue Service 47,600
367-11 Lewis Jack Administrative Asst. 42,400
289-09 Oslow Craig Custodial 33,750
221-09 Lee Jennifer Custodial 38,400