Pivot Table of a Teacher's Gradebook

psdbenj

New Member
Joined
Feb 8, 2008
Messages
11
I'm trying to make a pivot table that summarizes a teachers grade book of letter grades. Each column of the gradebook contains a different assignment. So far, I have to make a pivot table for each assignment. Dragging more than 1 assignment title into the row or column fields area of the pivot table makes it confusing to read. The only solution I can come up with is making a really long spreadsheet and displaying each assignment below the previous assignment. What am I missing. Surely, there must be a way to summarize a teachers gradebook of letter grades with a pivot table.
Thanks,
Ben
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Hi, Ben.

I think the solution you found is the best way. The original data set up is non-normalized and changing to normalized is the way to go - it will make all data handling easier.

regards, Fazza
 

psdbenj

New Member
Joined
Feb 8, 2008
Messages
11
Thanks Fazza. Now the next question: Is there an easy/good way to normalize a teacher's gradebook? Teachers would kill me if I asked them to enter their assignment grades by adding a new row for every assignment instead of working across the spreadsheet. Probably have to step up to Access for this one.
Thanks for your help,
Ben
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Yes, Ben, it would be best if the teachers don't have to change what they do. If that layout is (understandably) much better for them, set up the rest of the spreadsheet to suit.

There are two basic choices, either have another worksheet that has the normalized data OR have some SQL do the work. The SQL approach might be unnecessarily sophisticated (though if there were not too many columns could be good solution), so, elect to take the extra worksheet route.

The idea being leave the current worksheet for data entry, have a new (maybe hidden) worksheet that has a copy of the data except in a normalized format and then the pivot table running from the normalized data. VBA code can create the normalized data, say on deactivation of the data entry worksheet, and update the pivot table.

Can you provide a sample of data?

How many columns and rows are there?

regards, Fazza
 

psdbenj

New Member
Joined
Feb 8, 2008
Messages
11

ADVERTISEMENT

Unfortunately, they are both variable. i.e. The number of students in each class varies and the number of assignments in each class varies too. But, I think I get the idea. Unfortunately, I don't know VBA but I think I can record a macro that would do the trick. If it wouldn't be too much trouble, I'd love to see sample VBA that would automatically copy the first column of student IDs (say 30 students) and the associated assignment (say from 3rd and 4th column) of the traditional teacher gradebook. I appreciate you taking the time to answer my questions so far. If I'm pushing my luck with this last request, let me know.
Thanks,
Ben
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Ben,

A variable number of rows & columns is expected. I was mainly wondering if there are 50 or 50,000 rows, for example. And 20 or 200 columns. Just to get a feel for the scale of the task as it might influence the approach. (If there is a very large amount of data I would go for the SQL approach - straight from the source data to the pivot table without the extra worksheet.)

To write any code is infinitely better with some sample data. Can you provide some sample data, preferably a screen shot sort of image like exist in many threads?

Cheers, Fazza
 

psdbenj

New Member
Joined
Feb 8, 2008
Messages
11

ADVERTISEMENT

Okay, here's a sample. Again, much appreciated!

excelscreenshot.gif

moz-screenshot.jpg
moz-screenshot-1.jpg
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
I don't see the image, Ben, just a place holder and then

I don't know why I don't see the image - could be security setttings where I am; maybe others see it OK.

There is a sticky thread at the top of each page that has info on posting HTML to show worksheet info. Maybe you can try that?

Or otherwise explain in words if it is as simple as it should be. Such as the first row has headers and they are exactly "teacher", "student", "assignment1", "assignment2", "assignment3", etc and the data is immediately underneath. That is, data from cell A2. "teacher" and "student" are text entries and then there are grade entries in the other cells.

F
 

Sal Paradise

Well-known Member
Joined
Oct 23, 2006
Messages
2,457
As he/she is linking from his/her hard disk, nobody can see said images save possibly for him/her. I second using one of the HTML makers.

(yay for gender ambiguity)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,333
Messages
5,595,560
Members
413,996
Latest member
mabelO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top