Data Visualization

before you start

If you have a spreadsheet available, keep it open in a separate browser tab so you can practice the skills presented in this tutorial. Look for the sections marked Try It as you work.

1. Getting Started with Charts

Have you ever stared intently at the contents of a spreadsheet only to find that all the numbers seem to look the same? You’re not alone. Tracking your financial progress involves a significant amount of information, especially numerical information, or data. Analyzing all that data in tabular form can be overwhelming, especially when the data changes across months, years, and even decades.

To help you interpret your financial data, spreadsheet programs offer built-in charts (also called graphs) so you can make comparisons across quantities. It’s likely you’ve seen charts before in your math and statistics courses.

1a. Categorical Data

Categorical data is information that can be grouped into several different categories. In economics and finance, the data points are often quantitative, usually expressed as dollars. The categories might be months of the year, types of expenses, investment choices, and so on.

1b. Frequency Tables

We usually begin by summarizing categorical data into a frequency table. A frequency table has two columns. One column lists the categories, and the other lists the frequencies with which the items in the categories occur. In the table below, the category (column A) is Household Assets and the frequency (column B) is numbers of dollars.

Tables like this are not new to us. In previous tutorials, we already saw how to build frequency tables in the form of budgets and balance sheets.

Frequency tables are useful, but sometimes we need an even more intuitive way of displaying data. This is where charts come in.

term to know

Frequency Table

A table that lists items and the number of times the item is counted.

2. Standard Column Charts

A column chart is a graph that displays a column for each category with the height of each column indicating the frequency of that category. The vertical y-axis has a scale and measures the frequency of each category, often dollars. The horizontal x-axis with the categories typically does not have a scale.

term to know

Column Chart

A categorical chart with frequency values along the vertical axis and categories along the horizontal axis.

2a. Construction Made Easy

Constructing a column chart in a spreadsheet is most easily described using an example. Let’s use the market value of household assets that we just studied:

step by step

Follow these steps to create a column chart using a spreadsheet.

1. Click and drag on the data in both columns (categories and frequencies). Be sure to capture the column titles too. Exclude any totals for now.
2. In the ribbon on the Home page, select Insert. In the drop-down menu, click Chart. Some spreadsheet programs have a Chart section right on the Home page. Look for the symbol below.
3. The spreadsheet program will place the chart that you last used on the screen. If it’s not a column chart, visit the Chart Editor on the right of your screen.
4. In the Chart Type drop-down menu, choose Column.
5. The column chart displays in a new window and comes complete with chart title, x- and y-axis, axis labels, and axis titles.

Note: some programs have a Chart section:

Here’s the completed column chart after following the steps.

2b. Bar Charts

A bar chart is a column chart oriented horizontally instead of vertically.

try it

Can you think of a situation where a bar chart tipped horizontally would be more effective than a column chart? Open your spreadsheet program and try building the previous example as a bar chart.

term to know

Bar Chart

A categorical chart with frequency values along the horizontal axis and categories along the vertical axis.

2c. Pareto Charts

In finance, it’s often useful to reorder frequency values from largest to smallest, or vice versa. Such arrangements make it easier to compare similar values in the chart, even without gridlines. This type of organization is called a Pareto chart.

The steps for creating a Pareto chart are the same as that of a column chart, with one exception: we must first sort the frequency table by value.

step by step

To create a Pareto chart, follow these steps.

1. Click and drag on the data in both columns (categories and frequencies). Be sure to capture the column titles too. Exclude any totals for now.
2. Right click and choose Sort Range from the drop-down menu. In some spreadsheet programs, visit the Sort section under the Data page for more options. See the button below.
3. In the popup window, do the following:
   1. Select “Data has header row.” This ensures that your titles do not get sorted with your data.
   2. In the “Sort by” drop-down, choose the column that contains your frequencies (numerical values).
   3. Click A to Z to sort values in ascending order, or Z to A to sort in descending order.
   4. Press the Sort button.
4. Finally, follow the same steps for inserting a column chart using the table of sorted values.

Note: some programs have a Sort section:

Here's an example of the “Sort by” drop-down menu:

The finished Pareto chart looks like this:

The owner of these assets can now clearly see that their automobile is their most expensive household asset.

try it

Now build a Pareto chart of your own using your spreadsheet program. You can use the previous example or create one of your own. What troubles, if any, did you encounter with the sort feature? How did you correct them?

term to know

Pareto Chart

A column chart or bar chart that lists frequency values from largest to smallest, or vice versa.

3. Stacked Column Charts

Column charts may also have two or more frequency values associated with a given category. This allows you to quickly see relationships between frequency values. We call these stacked charts.

Stacked charts are useful when comparing projections to actual figures in finance. Consider this monthly budget:

The steps for creating a stacked column chart are the same as a standard column chart. The key difference is just choosing the stacked chart type from the Chart Editor on the right side of your screen (or from the Chart section on the Home page in certain spreadsheet programs).

Here’s the completed stacked column chart. Notice how it calls attention to the data from column D of the frequency table:

The yellow bands represent the difference between actuals and projections. Yellow bands above the $0-line represent a surplus of money where spending was less than expected. Yellow bands below the $0-line represent shortages.

The stacked chart helps us see that although the budget had some insufficient predictions for takeout food and gasoline, its surpluses will more than make up for those two shortages. This month's budget should be in good shape, especially since home repair expenses were well below the prediction.

term to know

Stacked Chart

A column chart that has two or more frequency values associated with each category.

4. Pie Charts

We use pie charts to show relative sizes of each frequency value. Unlike column charts, pie charts can more effectively show a part-to-whole relationship.

A pie chart is a circle made up of different-sized wedges, similar to a pie or pizza. The relative size of each wedge corresponds to the relative frequencies of the categories. Often pie charts list their frequencies or relative frequencies (percents) in the chart right next to the pie slices.

Knowing how your expenses are distributed is helpful when managing your spending. It can help you make smart, productive choices. Let’s display the expenses in this annual budget as a pie chart to see the distribution. Pie charts can increase your productivity because the chart fills in the percentages for you!

To create a pie chart in a spreadsheet, follow the steps from the previous examples. For this data, click and drag on all columns and rows that contain expenses. Include the category names, but exclude the total expense. Select Insert + Chart and choose pie chart from the Chart Editor.

The finished pie chart is below. Notice how the percentages and category labels are included automatically. That’s a real time saver!

try it

Open your spreadsheet program and create a pie chart of your own. You can use any type of information that you find interesting, financial or otherwise. What is the biggest advantage of pie charts that you notice?

big idea

Do not overcomplicate your graphs. You might be tempted to add fancy features which don’t complement your data. For example, using three-dimensional column charts is usually not as effective as using its two-dimensional counterpart. Avoid unnecessary noise so the meaning of your data is clear. Remember, the stronger your technology skills, the more likely you are to choose the best chart for the data. With the best selection, you can improve your productivity and we all want that.

term to know

Pie Chart

A categorical chart that shows each frequency value as a proportion of a circle.