For my next trick, I will be creating a chart that focuses on plotting NON NUMERICAL categories on the vertical axis of a graph. Now, obviously, there are numbers underlying the graph data, but not visible to the user. Away we go.
In this example I will be rating 5 different areas. Each area will fall on the X axis. In this example, the possible ratings range from 0-4. The tricky part is that the these ratings correspond to a non-numeric value.
0-1 = Low
1-2 = Moderate
2-3 = Above Average
3-4 = High
I will also be plotting an overall aggregate rating based on an average of all the scores. To accomplish all this I have layered a bar chart and 2 line charts on top of each other. Isn’t it pretty!
Here is a file with the graph:
Note 1:
The blue cells in row 4 are the actual input values for your chart. In this case the inputs should fall between 0-4. These figures are graphed using a line chart, however, I have removed the lines between each point.
Note 2:
The aggregate score is calculated as the average of the ratings. This score could be calculated in any fashion (ie. mean, median, mode, weighted average, etc). The important thing is that this score represents your overage rating. In row 5 we see this score repeated in all 6 columns. The reason I have 6 columns is purely for visual affect. The dummy rows create space on both sides of the graph so that the aggregate score stands out. The word “aggregate” is a data label. I added in 1 data label to the middle aggregate point. This way the word moves up and down with the line.
Note 3:
This area refers to a line chart, which creates the horizontal grid lines that separate my data into thelow, moderate, above average and high ranges. There is one series for each line. Now, you ask, why on earth didn’t you just use the build in excel grid lines? Well my friends, because I wanted labels on the side for each area. To accomplish this I added in data labels for each line graph (similar to the aggregate score), however only for the points on the left hand side. These labels are linked to my original data in case I want rename the field. Try it if you don’t believe me. I moved them over slightly so they appear between the two lines. By setting the graph up this way I have a vertical axis with non-numeric values.
Note 4:
As I hope you know by now, I am a graph super nerd. For this reason, I added in another graph, this time a bar chart, purely for visual reasons. I wanted each area to have a visually distinct area within the graph. The 6 values in row 11 create the bars. The fancy blue bars go all the way to the top and therefore have a max value of 4. I added in a fading background for effect. Looks pretty sharp if you ask me.
So here it is, the multilayer, non-numeric line / bar chart. Any queries or questions let me know.
Cheers!
