I am working on a project, and was wondering if there is a quicker/better/easier way to do it than what I have been doing.

I have two sets of data - one for 2009 and one for 2010 - broken out by department (there are about 100 different departments with which I am working). The data sets contain two main things: department number and evaluation score for each employee in that department. So, I have, for each of the two years (in two separate lists), something that looks like this:

Dept #   Eval Score

1234      1.22

1234      1.22

1234      1.25

2345      1.19

2345      1.29

5678      1.00

5678      1.00

I am trying to make a scatterplot for each department that plots out each of the employee eval scores and the frequency of each one. I have done this before several times for a single year and for much larger units than department by creating a pivot table and getting the count for each score, then making a scattergram off of that. That has worked out thus far. Now, not only am I trying to do approximately 3 times more graphs than normal, I am also trying to merge the two years together into one graph. I am not sure first of all how to merge the data so that I can get a count, by year by score, by department of each evaluation score. If I can get this, I can create the graphs I think.

Another thing - is there a way to make something of a default graph or a custom graph, with all of the settings already in place? I want it to automatically be set at a certain scale on the x axis and have the marker points be a certain size. Is there a way to do this so that I do not have to go into each individual one and make the changes?

Any suggestions on this would be very much appreciated! I am stumped!



Software/Hardware used:
Excel 2007