Non-numeric Vertical Y Axis Graph

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:

CLICK HERE!

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!

The Excel Chart Speedometer!

Well guys, here is one of my favorites graphs. Again, as I mentioned in the intro, I am more of a crazy outside of the box thinker with excel, than a true coder. My strength lies in manipulating excel to create interesting, neat solutions.

Drum roll please… the Speedometer.

I looked online forever for instructions on how to create a completely automated speedometer using an excel chart. Alas, I couldn’t find anything. So, I made it myself. Once you know how the graph works, its actually pretty simple, but creating it was someone difficult.

Here is a file with the graph:

CLICK HERE!

What I have done is layered a “radar” graph on top of a pie chart. (Pause for thunderous applause). The pie chart represents the areas of the speedometer (in this case risk levels) and the radar graph creates the needle. Let’s take a look at the specifics on the spreadsheet.

Note 1:

This portion of the spreadsheet simply divides the pie chart in areas. The total areas add up to 100%. In this case I have divided up the top half of the pie into 4 zones corresponding to risk levels. Note, because we are only using the top half of the pie, the bottom unused portion takes up 50%. I have divided the remaining areas equally – 12.5% each. You could change the size of the pie depending on what you wanted. You could even have no specific areas. YOU ARE THE BOSS! I left extra spots on the spreadsheet if you wanted more than 4 areas.

Note 2:

Arrow % is simply where you want to arrow in your graph to be. In this sheet it is an input cell, however, in most cases this would likely be the result of a calculation. 50% represents the middle of the graph and a vertical line. Don’t believe me, try it!

Note 3 & 4:

Next, I had to create the needle. Originally, I started off with only a few possible needle points. I soon realized that for a speedometer to work, you need fairly precise measurement. If you scroll down column E you will see I have 1000 “possible needles”. Now, I suppose I need to get slightly technical to explain exactly how it works. As I just mentioned, column E has %’s listed from 50% to 100% and then following a big gap of 1000 cells (we don’t use the bottom of the graph, remember?), 0% to 50%. Column F is the actual needle data. These cells have a formula that says: look at our arrow %, if the number the same row in column E is equal to the arrow %, put a 1 in the cell. Otherwise put a zero. We round the input Arrow % cell to the 1^st decimal place because this is the accuracy of our corresponding needles in column E. The result is that every row in column F will be 0, except for 1 cell that equals of arrow %. The radar graph ends up looking like a line because all the 0’s are on top of each other, except for one point. A line is created between the 0 value and the 1 value. A line is also created from the 1 value to the next 0, but these are again layered on top of each other. The result is a needle that moves up and down depending on the Arrow % entered.

The words on the graph page are simply “Word Art” layered on top of the chart.

Well, I tried to my best to explain the graph, but, there may be some follow up questions. Let me know what you think. Is it neat? Am I dumb? Is there an easier solution? I look forward to any feedback. Also, this is the first time I have tried to link to google docs. Please comment if the link doesn't work. Lastly, if you would prefer a step by step guide, I could work on something like that.

Cheers!

Welcome to Excel Charts

Hey Dudes.

Welcome to my new blog that deals directly with excel charts.

First, I suppose I should tell you all a little bit about myself and my background.

Let me start by saying that I am an Excel nerd. I love using the program. I even have a tattoo that I created in Excel. If you are really nice, I just might tell you where. Most of talent lies in finding weird solutions, rather than being a straight coder or analyst. I have been using excel extensively since my undergraduate studies in business. I consider myself a high level expert using spreadsheets. I can also code in Visual Basics.

I have worked for an energy company, three banks and in a government position. At each stop I have further developed my skills.

In this blog I hope to share my most creative charts and have fun doing it. I hope to find a few followers who share my passion for excel and finding crazy ways of solving funky problems.

Lets roll.