James H.

asked • 04/10/23

Excel: How to summarise results for each student and for each subject?

I'm trying to summarise data where each row of data is a particular test.

Each subject may have multiple tests and each student may have multiple subjects.

We don't know how many subjects per student or how many tests they will face.

Is there a way to summarise this data by student and as the secondary, by each subject (for each student)?

Example data:

Name Class Score

John English 77

Lisa English 49

Erik English 46

John Maths 23

Mark English 79

John Science 12

Jess English 69

Lisa Maths 47

Erik Maths 21

Jess Maths 12

Mark Maths 37

John English 82

Lisa English 61

....................................


end result ideally will look something like...

John English 80

Maths 23

Science 12

Lisa English 55

Maths 47

....................................

Is there a way of doing this in excel without knowing how many subjects for each student and number of tests, etc?

Matthew O.

tutor
Ok - I'll explain this in layman's terms first so you have an idea of what we are doing in theory. I will explain the reasoning, terms, and instructions below the layman's summary. -- We need to take the three fields that name up a students name (First, Middle, and Last) and combine those values to make a new column called "FULL NAME" -- Now we need to select ALL the data in one go - and have it exported to something called a PivotTable. -- At this point we can just drag and drop to the correct places of the PivotTable to have excel sort the data and display it in a new table. -- Anything that doesn't make up the "branches" will be added to a different part of the pivot table where it displays values. You will use this to place the test "scores" field - Excel will add these to a SUM unless you specify otherwise. --DONE! Ok, now more details you might want. First, what you are trying to do can not be done in the spreadsheet that is currently holding the data because there are too many unknowns - different number of subjects, different number of test, and so on. Because of this, we are going to take all that data and export something called a PivotTable. This table is what's going to give you the flexibility of these unknowns. We are going to export it into a new spreadsheet in this example, but you can also add it into an existing spreadsheet if necessary. But before we do that, we need to do something first to simplify this for ourselves later down the road. Because we don't expect, or ever want, the first, middle, and last names for any particular student to be separated from each other, we are going to combine them. We want to take the 3 fields that make up FIRST NAME, MIDDLE NAME, and LAST NAME into a single field called FULL NAME. This is called concatenating. This term is used in programming a lot and it essentially means "to link together". Once the data is concatenated, we are going to send everything off to the pivot table. - Make sure that all the data in the spreadsheet that you want included is selected - All the rows and columns. Now go to INSERT and click PIVOT-TABLE. You will see a pop-up - in this popup, as long as you highlighted all the non-empty rows and columns as described in the previous step, you only need to decide if you want to create a new spreadsheet or add it to an existing one. For your example we will create a new one - so make sure that option is selected and hit OK. At this point - on the right there will be a bunch of options to click - each of those are a different column (or row) of data from your original spreadsheet - We are ultimately creating something called a nested structure. - This can best be described as something similar to an oak tree. These trees have multiple branches that have unknown and varying quantities. Our branches are made up of the elements in our original spreadsheet, with each new element being one level further out from the central base that makes up the ROOT of the tree. We are going to drag the "Full Name" field into the tree ROOT as all the other data are just a new level of branches sprouting further from the base. The biggest branches in this example are our SUBJECTS element - as each FULL NAME will have one or multiple SUBJECTS to make up the first branches of our tree. A subject is meaningless without a FULL NAME to place it under, just like the branch of a tree is useless without the base. Also like a tree, something like TESTS, will NEVER sprout from the base of the tree as those branches are much smaller and only sprout from the largest branches (our SUBJECTS). So our FULL NAME (tree base) can have multiple SUBJECTS (large branches), which can have TESTS (small branches), which then sprout the data of TESTS into the value section. It doesn't matter that quantity of branches coming off of each higher element - sometimes only a single branch will sprout, and in another part of the tree it might be 12 branches that sprout. The only difference is that our TREE in the PivotTable will grow sideways, from left to right. - Ok now that you understand the tree metaphor we can continue: Drag the field for FULL NAME into the DROP ROWS HERE section of the pivot table. This will make your tree base (root) You should see the names now pop up on the left of the new pivot table. This is a good sign if you are doing it correctly. Now, you are going to drag the SUBJECT option over to the same DROP ROWS HERE section but make sure it is dropped to the RIGHT of the FULL NAME in the pivot table. Repeat with the TESTS option, making sure that this is to the right of the SUBJECT in the pivot table. Now you can see our sideways tree taking shape. Now that we have built our nested Structure, we need to drag the results of the tests into the DROP VALUES HERE part of the PivotTable - by default the pivot table will ADD (SUM) up the values to display, but this can be changed to get the average, mean, or most any other function. If you made it this far - AMAZING WORK. You should have a spreadsheet that has multiple amounts of subjects, tests, etc for each student! Congrats! Good luck! Hope this information helped.
Report

04/11/23

Matthew O.

tutor
I don't know what happened to the formatting - but it is not letting me edit it. Hope you can decipher it, my apologies!
Report

04/11/23

2 Answers By Expert Tutors

By:

Mohammad S. answered • 04/14/23

Tutor
New to Wyzant

Patient in Teaching a Math Graduate: Sharing "Math" Vision

See tutors like this
See tutors like this

Using Pivot Tables will be a best approach in this case, no matter how many students' data you have (even in thousands), Pivot Tables can summarize it with in seconds.


Select the whole data (Assumed to be in good order and in columns)

use the menu Insert>Pivot Table.

Existing or new worksheet and

select/place your data in suitable places,


and you are done.

Upvote • 0 Downvote
More
Report

Still looking for help? Get the right answer, fast.

Ask a question for free

Get a free answer to a quick problem.
Most questions answered within 4 hours.

OR

Find an Online Tutor Now

Choose an expert and meet online. No packages or subscriptions, pay only for the time you need.