Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm probably missing something very obvious, but I have the following data:

CS   20
CS   10
ENG  20
ENG  10
ENG  30

What I want is to get the average of each department. So it should look like the following:

CS   15
ENG  20

I have about 4000 rows of data and don't want to have to split the data into 40+ sheets. Thanks!

share|improve this question
Alternatively, I could do this all through bash scripting. But it would be nice to know how to do it in Excel – tifkin Oct 28 '12 at 23:49
up vote 0 down vote accepted

A pivot table will do this for you:

enter image description here

Make sure your columns have headings.

  1. Go to Insert / PivotTable
  2. Accept the defaults on the first page for the data source
  3. Select the range containing the course and score
  4. Drag the Course column from the list presented to the columns field
  5. Drag the Score column from the list presented to the data field
  6. You will then be given a pivot table with totals, so change the dropdown in the top left to "Average of Score"

I'll add additional images if this isn't easy to follow.

share|improve this answer
Ah, Pivot Tables are what I was looking for. I didn't quite understand your directions, but I found this youtube video quite helpful: – tifkin Oct 29 '12 at 1:07

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .