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.

Sheet1

Sales

Marketing

Security

Events

Marketing

Sales

Administrative Asst.

Logistics

Marketing

Lee

Sales

Smith

Sales

Service

Service

Administrative Asst.

Oslow

Lee

Custodial