VOices Blog Members Home Events Shopping Cart
Skip to main content
VOices
posted on Apr 23, 2018
excel%2bbasics-350px.jpg

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.

Create a Chart

To make a chart, highlight the data you want to chart and click the Insert tab. The Charts group is in the middle.

2018_05_25_Excel_charts_-01.PNG

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 chart

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.

 2018_05_25_Excel_charts_-02.PNG

Then select the Insert tab and the column chart button.

2018_05_25_Excel_charts_-03.png

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.

2018_05_25_Excel_charts_-04.png

After you select the chart type, your chart will appear on the screen. It will need some work before it is ready to save.

2018_05_25_Excel_charts_-05.PNG

Format your Chart

When you click on your chart, you will see additional tabs appear at the top of your screen, labelled Chart Tools.

2018_05_25_Excel_charts_-06.PNG

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.

2018_05_25_Excel_charts_-07.png

This chart needs a vertical axis title, to explain what the numbers mean.

2018_05_25_Excel_charts_-09.png

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.

2018_05_25_Excel_charts_-10.png

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.

2018_05_25_Excel_charts_-12.png

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.

So what?

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.

Bar Charts

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:

2018_05_25_Excel_charts_-13.png

Scatter Chart

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.

2018_05_25_Excel_charts_-14.PNG

Select the data, then choose scatter chart. The top left is pretty much the only one I recommend for general use.

2018_05_25_Excel_charts_-15.png

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:

2018_05_25_Excel_charts_-16.PNG

So what?

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.

Summary: Two Chart Types

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.

Take it to the next level

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.

Excel 101

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.

Excel 201: Pivot Tables and Charts

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:

 

Filed under: Operations
new comment comment
view archives archives
RSS rss