It is irresponsible to make decisions without data; however, data on its own is next to useless. You need to turn it into information first. One of the most powerful ways to do that is to create charts.
Charts are very important tools to visualize data, to perceive trends and relationships. They can help you figure out what is going on in the data, and help you communicate this to others.
To make a chart, highlight the data you want to chart and click the Insert tab. The Charts group is in the middle.
Excel has the ability to make a lot of different types of charts, but most people really only ever need three types. You should have a very good reason to use a different type. The reason needs to be rooted in illustrating a trend or a relationship in the data, not because it is pretty.
Column charts have text categories on the horizontal axis and numbers on the vertical axis. Below is a good dataset for a column chart. To make the chart, highlight all the data, including the headers.
Then select the Insert tab and the column chart button.
I recommend you stay away from 3-D column charts. They make the data hard to read. Also, the 2-D column chart which makes all the columns the same height has very limited uses. If you don’t know what those uses are, then that is a good clue that you shouldn’t use it. The top two on the left are the only column chart types which make sense 99% of the time.
After you select the chart type, your chart will appear on the screen. It will need some work before it is ready to save.
When you click on your chart, you will see additional tabs appear at the top of your screen, labelled Chart Tools.
Click the Design Tab. The button furthest to the left on this ribbon is called Add Chart Element. Click on that and you’ll see all the different chart elements available to you. Hover over the choices to see what they look like.
This chart needs a vertical axis title, to explain what the numbers mean.
You should also change the chart title, to make it more descriptive. Click the Format Tab. The group furthest to the left is called Current Selection. You can use this to select a chart element.
Once you have selected the Chart Title, you can change it to something more descriptive, like “Volunteer Hours per Month – 2015”
You can also do a lot with the format of any chart element, like change the colour, font, font size, border, and lots of other things. To activate the formatting sidebar, choose the chart element you want as above, then click Format Selection.
Formatting chart elements is a big rabbit hole, but mostly this allows you to make your chart pretty. Make sure you do not confuse a pretty chart with a clear one. A clear chart shows trends and relationships, no matter what colour or font you use.
This chart is a much easier way for you to see, and to communicate, that volunteers for this organization generally give between 400-500 hours per month. The peak months are January, April, September, and December. If peak demand is also during those months, then it makes perfect sense. If not, then that is worth looking into in more detail. Perhaps clients are being under-served in other months, or perhaps volunteers are being under-utilized during peak months. Under-utilized volunteers are at a higher risk for quitting.
A bar chart is just a column chart on its side. It is useful if your data labels (on the horizontal axis) are really long, or if you are going to present your chart in landscape mode, like on a computer screen. Here is an example of a good use of a bar chart because the data labels are really long:
A scatter chart is useful if you have numeric data on both axes. Here is a good example of data which would be best portrayed using a scatter chart. GNP is Gross National Product, an indicator of national wealth, and LExpM is male life expectancy.
Select the data, then choose scatter chart. The top left is pretty much the only one I recommend for general use.
In this case, you will need to create axis titles for both axes, as well as fix the chart title. Here is what the chart looks like once it is fixed up:
This graph allows you to really see the relationship between GNP and life expectancy. There are two interesting things about this graph. One is that there seems to be an upper limit to life expectancy. From $5,000 to $35,000 GNP, life expectancy is pretty constant. Another interesting thing is that some poor countries have quite high life expectancies, while in others it is quite low. It would be interesting to drill into the data to find out which countries have high life expectancies, and survey them to find out what they are doing right.
So, to recap, there are two chart types which will carry you through 95% of your data visualization needs. If you are plotting numbers vs. text, then use a column chart (or a bar chart if the labels are long). If you are plotting numbers vs. numbers, use a scatter chart.
To learn more about using Excel, come to an upcoming course. If this blog post taught you something, then come to Excel 101. If you already knew this information and want to bring your chart-making to the next level, come to Excel 201.
Click here to register Thursday, May 24, 2018. 9:00 a.m. to 12:00 p.m.
Click here to register Wednesday, September 19, 2018. 9:00 a.m. to 12:00 p.m.
Click here to register Thursday, June 21, 2018. 9:00 a.m. to 12:00 p.m.
Click here to register Wednesday, October 31, 2018. 9:00 a.m. to 12:00 p.m.
Like what you've read? Subscribe to our RSS feed so you never miss a post! We have a general RSS Feed for all VOices blog posts, as well as an Operations RSS Feed which will focus on topics related to organizational operations.
Related blog posts: