Excel – Filtering and Grouping
Excel – Filtering and Grouping
Use filtering and grouping to analyze data in a large worksheet.
Student Start File
Assignment 8.5 Original File.xlsx
Instructions for Workbook
1. Create two more worksheets by copying the data in Filter 1 worksheet to two new worksheets named Filter 2, Filter 3, Pivot and Filter 4..
Instructions for Filter 1
1. Insert 10 rows at the top of the worksheet.
2. Copy headers on row 11 to Row 1.
3. Using Advanced Filtering:
a. Define data and criteria ranges using Advanced Filtering Command.
b. Select People from:
i. Saginaw, Zip Code 48603
ii. Bay City, Zip Code 48706
iii. Frankenmuth
Instructions for Filter 2
1. Insert 10 rows at the top of the worksheet.
2. Copy headers on row 11 to Row 1.
3. Using Advanced Filtering:
a. Define data and criteria ranges using Advanced Filtering Command.
b. Select People from:
i. Saginaw, Zip Code 48603
ii. Bay City, Zip Code 48706
iii. Frankenmuth
c. Select Job Codes = 213 or 203
Instructions for Filter 3
1. Insert 10 rows at the top of the worksheet.
2. Copy headers on row 11 to Row 1.
3. Using Advanced Filtering:
a. Define data and criteria ranges using Advanced Filtering Command.
b. Select People from:
i. Saginaw
ii. Bay City
iii. Frankenmuth
c. Select People hired from the beginning of 1995 to the end of 1998
i. Need to know a couple of tricks, first need the date data in date format and need columns to complete the criteria. Paste special is a hind to convert the date.
Instructions for Pivot
1. Select data for analysis and insert a Pivot Table by creating a new worksheet called PivotTable.
2. Determine how many employees live in what cities by:
a. Dragging the City Field to the Row Area in the PivotTable Worksheet
b. Dragging the First_Name Field to the Value Area
3. Filter by Job_Class_Code by dragging the field Job_Class_Code to the Filters Area. Click “Select Multiple Items” in the drop down box.
4. Play with the pivot table and see what analysis you can perform.
Instructions for Filter 4
I believe there is an easier way to analyze data than using the advanced filter dialogue box. Here are the instructions to do that.
1. Clean up your dates from text.
2. Select all the data in the table.
3. Format as a table (Home Styles Format as Table. Use Table Style Light 9. One caution is to only select the headers and data in your worksheet. If you select more, it may take a considerable amount of time to create the table.
4. Freeze headers. A2 is the active cell. View Window Freeze Panes Freeze Top Row.
5. Filter the following using the drop down boxes in the columns.
d. Select People from:
i. Saginaw
ii. Bay City
iii. Frankenmuth
e. Select People hired from the beginning of 1995 to the end of 1998.(use Custom Date Filters)
"You need a similar assignment done from scratch? Our qualified writers will help you with a guaranteed AI-free & plagiarism-free A+ quality paper, Confidentiality, Timely delivery & Livechat/phone Support.
Discount Code: CIPD30
Click ORDER NOW..
