MOAC – Excel Lesson 7 Working with Data Student Assessment Instructions
Competency Assessments
Project 7-1: Analyze Semiannual Sales Data
Litware, Inc., has divided its sales representatives into two teams that are in competition for sales rewards. The sales report worksheet has been color coded to identify team members. In this exercise, you will sort the team data.
GET READY. LAUNCH Excel if it is not already running.
1. OPEN the Semi Annual Sales data file for Lesson 7.
2. Click the Data tab to make it active.
3. Select A4:H12. The data range should include the column headings but not the monthly totals.
4. Click Sort in the Sort & Filter group. My Data Has Headers should be selected by default. If not, select it.
5. In the Sort dialog box, select Total (or Column H) in the Sort by field. In the Sort On field, select Values. Select Largest to Smallest (descending) in the Order field. Click OK. The sales representative with the highest total sales is listed first. The rest are listed in descending order.
6. With the data still selected, click Sort. Sort by Sales Representative. Sort on Font Color. In the Order fields, select Red and On Top. Click OK. The red team is listed first. Within the red team, representatives are listed in descending order in terms of sales.
7. SAVE the workbook as Semi Annual Sales 7-1. CLOSE the file.
LEAVE Excel open for the next project.
Project 7-2: Ensuring Data Integrity
Create a workbook that you will use to collect survey responses from a random sample of students at your college. Your survey will consist of ten questions, and you will survey ten students.
GET READY. LAUNCH Excel if it is not already running.
1. Click the File tab and open a new blank workbook.
2. Select A2, key Survey Questions, and press Tab.
3. Key Student 1 and press Tab. Key Student 2 and press Tab.
4. Select B2:C2. Use the fi ll handle to complete the series to Student 10 (cell K2).
5. Select A3 and key In what year did you begin college? Press Enter.
6. Key Have you met with an advisor this year? Press Enter.
7. Key How many hours per week do you study? Press Enter.
8. Select B3:K3. On the Data tab, click Data Validation.
9. On the Settings tab, in the Allow box, select Whole number. In the Data box, select less than or equal to. In the Maximum field, enter the current year in 20XX format.
10. Click the Input Message tab. In the Input message box, key Enter year in 20XX format.
Click OK. The input message should be displayed when you close the dialog box.
11. Select B4:K4. Click Data Validation.
12. Click the Settings tab if necessary. In the Allow box, select List. In the Source box, key Yes, No. Click OK. A drop-down arrow should be displayed next to the active cell.
13. Resize the columns if necessary.
14. SAVE the workbook as Survey 7-2 and then CLOSE the file.
LEAVE Excel open for the next project.
Proficiency Assessment
Project 7-3: Filter Data on Multiple Criteria
The Litware sales manager needs to filter the sales report data in a variety of ways so that he can use it in team meetings to acknowledge those who have achieved sales objectives and to motivate the teams. Create the filters for the sales manager.
GET READY. LAUNCH Excel if it is not already running.
1. OPEN Sales Teams from the data files for this lesson.
2. Select A4:H12. Click Filter. Click the arrow in the Total column.
3. Click Number Filters and then click Greater Than. Key 100,000 in the dialog box. Click OK. Four sales representatives are displayed.
4. Click Filter to display all data. Select any cell that contains data and create a filter to
display the Red Team’s statistics. (Hint: Because entire rows are color coded, you do
not have to select the data. Data does not have to be sorted when you filter for color.)
5. SAVE the workbook as Red Team.
6. Click Filter to display all data. Click Filter again to display the filter arrows.
7. Click a filter arrow and display the Blue Team’s statistics.
8. SAVE the workbook as Blue Team. CLOSE the workbook.
LEAVE Excel open for the next project.
Project 7-4: Sort and Filter Using Conditional Formatting
Each year Fortune Magazine surveys employees and publishes a list of the ten best employers based on employee ranking. The Top Ten worksheet contains additional information about the top ten companies in terms of their size (number of employees), percentage of minorities, and percentage of women. In this exercise, you will sort using conditional formatting to determine how many women in the workforce are employed by Top Ten companies.
GET READY. LAUNCH Excel if it is not already running.
1. OPEN the Top Ten data file for this lesson.
2. Select the data range, including the column headings. Click Sort on the Data tab.
3. Sort the data by % Minorities. Click Cell Icon in the Sort On field.
4. Under Order, place the green fl agged data (highest) on top. Click OK. Because you sorted by one criterion, the highest is on top, but the red and yellow are intermixed.
5. Click Sort to add a second criterion to sort on yellow fl ags, which represent the middle range.
6. With the data range selected, click Filter. Arrows are added to the column headings.
7. Click the filter arrow in the % Women column. Choose to filter by color.
8. Select the green arrow. Women comprise more than 60 percent of the workforce in two of the top ten companies.
9. SAVE the workbook as Top Ten 7-4. CLOSE the workbook.
LEAVE Excel open for the next project.
Mastery Assessment
Mastery Assessment
Project 7-5: Subtotal Data
As a motivational tool, Litware’s sales manager wants to group the teams and enter a subtotal as well as the grand total. In this exercise, you will complete this task.
GET READY. LAUNCH Excel if it is not already running.
1. OPEN the Semi Annual Sales data file for this lesson.
2. Select the data range only and sort by font color with the Blue Team on top.
3. Clear contents and formatting from the Totals row.
4. Select the Blue Team and group the rows. Group the Red Team.
5. Insert a column to the left of column A. Merge and center the title and subtitle to include the new column.
6. Key Team in A4. Select B4:B14.
7. Click the Format Painter and format column A. In column A, key Red or Blue to identify the salesperson’s team.
8. Create team subtotals in the Total column.
9. Collapse the outline to Level 2 so that only the team totals and grand total are displayed.
10. SAVE the workbook as Teams 7-5, and then CLOSE the file.
LEAVE Excel open for the next project.
Project 7-6: Create a Table in a Worksheet
The Records Management Director at Contoso has asked you to create a table within the Salary workbook.
GET READY. LAUNCH Excel if it is not already running.
1. OPEN the Salary data file for this lesson.
2. Sort the data by Job Category and then by Job Title.
3. Click Format as Table on the Home tab. Select Table Style Light 10 on the Quick Styles list.
4. In the Format As Table dialog box, select the records management personnel data as the data for the table.
5. Apply Table Style 10.
6. Add a Total Row to the table.
7. Rename the table column labels to match those in the worksheet (Last Name, First Name, Job Category, Job Title, Hours, and Salary).
8. SAVE the workbook as Records Management Table. CLOSE the workbook.
LEAVE Excel open for the next project.
1