Consolidating Data - Exercise

Consolidating Data - Exercise

Consolidating Data

Duration: 15 to 25 minutes.

In this exercise, you will consolidate students' grades on four different exams to show their average, lowest, and highest grades on a summary sheet.

1. Open Consolidate.xlsx from your Excel2013.3/Exercises folder.
2. Students' grades on four exams are stored on the first four sheets in this workbook. Use the Consolidate command to show the average, lowest, and highest grade for each student on the sheet named "Consolidate".

Solution:

1. To show the average grade for each student:
1. On the sheet named "Consolidate", select cells B2:B11.
2. On the DATA tab, in the Data Tools group, click the Consolidate command:
3. In the Consolidate dialog box, set the Function to Average and then click the red arrow to the right of the Reference data entry box:
4. Navigate to the sheet named "Exam 1" and select cells B2:B11 and then click the red arrow at the right of the Consolidate - Reference dialog box:
5. Back in the Consolidate dialog box, click Add:
6. Repeat steps C, D, and E, selecting cells B2:B11 on the sheets named "Exam 2", "Exam 3", and "Exam 4".
7. Now that all four series of data have been added to the All references box, click OK:
2. To show the lowest grade for each student:
1. On the sheet named "Consolidate", select cells C2:C11.
2. On the DATA tab, in the Data Tools group, click the Consolidate command:
3. In the Consolidate dialog box, set the Function to Min. Note that the series of data from the prior exercise already appears in the All references box. Were we using different data, you could delete these references and add others. As we are using the same data in this exercise, simply click OK:
3. To show the highest grade for each student:
1. On the sheet named "Consolidate", select cells D2:D11.
2. On the DATA tab, in the Data Tools group, click the Consolidate command:
3. In the Consolidate dialog box, set the Function to Max. Once again, as we are using the same data as in the prior exercises and it already appears in the All references box, simply click OK: