Over the past couple of days I began to examine in depth the task behind writing-up one of the last assignments of the semester, the report on our 360-degree evaluation. The more I got into what was required, I saw how poorly the data was formatted as it had been received. I learned how to do a few things which I will document here for future reference.
The Questions in Columns
One thing we all noticed was that the questions from our surveys were difficult to read. They appeared in the first row, and extended beyond 400-columns. The data was so “wide” that older versions of Excel, Google Docs, and Apple’s Numbers didn’t want to open it. What’s required to make the data much more approachable is a transposition.
To transpose the data means to switch it’s orientation on the spreadsheet; what goes across will now go down. This not only made the data much easier to read because the questions were easier to read, but the side-scrolling from left-to-right was now up-and-down. Like magic, only columns A-L were taken up, after I added three for tracking and sorting.
To transpose, cut your data, and re-paste it into a new sheet using “Paste Special.” Among the options is one for transposition.
Creating a radar plot is what was suggested for this exercise and I did find it helpful when comparing the median of my results in each of the 10 categories against those of my co-workers. What you needed to do was several steps.
1. Turn the text description into a number. You can do this with a formula or else use copy/replace. I chose the “5” for the “outstanding” rating.
2. Isolate the results for your ratings from the 58 questions. I did this by sorting my original (transposed) data, and using labels to separate the questions from the evidence “checks.” Then I calculated the median for each person for each of the 10 areas using a formula with the median function.
3. Make sure when you move your data between locations or sheets you paste special as “values.” Since I didn’t expect this data to change from further calculations, I made copies of the data to be able to keep the original and work with new versions where I might want to do more math.
4. Select your data to make the radar chart. So what you’ll have in the columns are the 10 leadership areas, and then columns for each respondent. These values you compute from the raw data from the 58 questions. You’ll get a chart with everyone’s data, which for me was fine, with only four respondents total (including myself).
5. You can generate another radar chart with aggregate data. I wanted to compare “everyone” against my own ratings. So I again found the median for the other three respondents and then charted again. This is what you see above. I chose the “filled” style with 50% transparency set. I found this view was easy for me to compare my own ratings against the group.
Consequently, I have never used doughnut plots and learned they are good for the same applications as pie charts, but for when you have more than one data set.
Exploring Instances of Evidence
The most interesting part of the data for me was the tally of where folks saw my leadership. Our choices of course were reports from others, personal observations, documents, projects/activities, and other sources. I found these the most difficult to fill-out and tedious. No doubt my colleagues did, too.
But the results either suggest it was a royal pain and people marked what they wanted (one possible scenario) or else people really did have varying opinions about how they knew what leadership traits I express. I used a pivot table to calculate the number of instances in each type of evidence source using the “raw” data from the transposed sheet. For instance, with “reports from others,” I marked that 37 times. My direct report, however, only marked this 19 times. My boss? 22. And a lateral colleague? 58 times!!
To create a pivot table, you select a lot of data, and the pivot will “summarize” it for you, using a number of different ways. Go to Data > Pivot Table to create the pivot. I then copied the data, and pasted it (paste special) in another area of the sheet as values. Then I could form my table which I later copied into Word.
As a fan of the work by Dr. Edward Tufte, I bought/read his book Beautiful Evidence over 2 years ago, as I’m a fan of infographics and knowing better ways to present information. Among the things he details in this book is information on sparklines. You can read all about them here, without buying the book. When I first encountered them, something really resonated with me because of the common sense behind such a concept. He used blood glucose readings as an example, and that piqued my interest (again) because I’m a diabetic.
So, I used the block-style sparklines in my analysis of the instances of evidence. My copy of Excel 2011 (Macintosh) includes built-in support for Tufte’s sparklines now. Tufte prefers them for larger data sets, but I immediately saw what they offered: a visual comparison between the lowest and highest values. Without reading the numbers, I can see who voted the “highest” for each of the different types of evidence (and conversely, who voted the lowest).
In the end, I probably exercised my Excel-noodle more than I had anticipated but was happy I was able to bend Excel to my will in coming up with a report that used all-digital tools for being able to make sense of the data we were able to collect. I am also thankful for the feedback I received. I’m curious as to what systems are available to automate this process for businesses (I imagine they must exist). Making sense of your leadership skills was a good learning exercise, but it’s too valuable to require this much time. It’d be great to have a web-based solution that would collect the data, crunch the numbers, and spit out some nice graphs. Perhaps it will be a future Pearson product just for schools. 😉