Graphing Challenge in Excel 2007

5 pts.
Tags:
Excel 2007
Excel 2007 functions
Excel 2007 workbooks
Graphical User Interfaces
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

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following