You are here:
- Home »
- Video gallery »
- Gathering and using evidence for learning »
- Display results disaggregated by ethnicity and gender
Related videos
Display results disaggregated by ethnicity and gender
« Return to Disaggregating data
Key content
Student achievement data is often reported for whole populations (for example: cohorts, year levels, whole class). This is called aggregate data. It is not, however, until the data is disaggregated that patterns, trends and other important information are uncovered. Disaggregating data simply means looking at achievement results or teacher judgments by specific subgroups of students.
This short tutorial will show you how you can create a graph in Excel to illustrate test results for a class, broken down by ethnicity and gender.
You can use the Excel spreadsheet below to practise disaggregating data using pivot tables.
Cleaned and matched writing data BOY and EOY and combined (Excel 2007 23 KB)
Transcript
Creating bar graphs in Excel (results by ethnicity/gender).
This short tutorial will show you how you can create a graph in Excel to illustrate test results for a class, broken down by ethnicity and gender.
Highlight the cells within your Excel spreadsheet that contain the data you wish to graph. Make sure you only select the data and the headers and do not select any blank cells.
In this example, the graph will show average test scores for a class, broken down by ethnicity and gender.
From the insert menu, select the option “PivotTable” and within the PivotTable options, select PivotChart.
The PivotChart dialogue box will appear.
If you have selected and highlighted your columns, they will already be selected here. Keep the option “New Worksheet” selected and click on the OK button.
You can now start building your graph. On the right of the screen, you will see the PivotTable field list. These are the three variables that you are going to include on your graph: gender, ethnicity, and scores.
Drag the Score field to the Values area at the bottom of the screen.
A bar graph should now appear. Initially the values will be set to Sum of Score, but for this graph, it will be more useful to have average scores.
To do this, click on the arrow to the right of Sum of Score and select Value Field Settings from the pop-up menu.
From the list of calculations, select the option Average to replace Sum. Click on the OK button.
To add ethnicity and gender to your graph, go back to the PivotTable Field List at the top of the screen and tick the boxes for gender and ethnicity.
You will now see a bar graph showing average scores for a class broken down by gender then ethnicity.
If you prefer to break it down by ethnicity then gender, you can change this by switching the data columns around.
Do this by highlighting the ethnicity column, column B, and dragging it to the left of gender.
To try different formatting options, go to the Design menu and try the different chart layouts and chart styles.