Online College Courses for Credit

+
Data Visualization

Data Visualization

Rating:
Rating
(0)
Author: Sophia Tutorial
Description:

Select the most appropriate visual representation for financial information.

(more)
See More
Fast, Free College Credit

Developing Effective Teams

Let's Ride
*No strings attached. This college course is 100% free and is worth 1 semester credit.

37 Sophia partners guarantee credit transfer.

299 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 32 of Sophia’s online courses. Many different colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial

what's covered
In this lesson, you will learn how to select the most appropriate spreadsheet chart for displaying your financial information and how strong technology skills can help you make this choice. Use of charts like this can help you improve your productivity skill to get the most from your money. Specifically, this lesson will cover:
  1. Getting Started with Charts
    1. Categorical Data
    2. Frequency Tables
  2. Standard Column Charts
    1. Construction Made Easy
    2. Bar Charts
    3. Pareto Charts
  3. Stacked Column Charts
  4. Pie Charts

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.

Technology: Skill Reflect
How familiar are you with charts in a tool like Excel or Google Sheets? Have you used them at work? In your personal life? What was it about them that made them beneficial?

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.

fx A B
1 Household Assets Market Value
2 Primary residence $0
3 Automobile $5,800
4 Bicycle $400
5 Other $500
6 Furniture $1,100
7 Electronics $1,800
8 Other Household Items $300
9 Total $9,900

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: a button showing three bars in a column chart

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

a column chart showing the market value of several household assets
Column Chart for Market Value of Household Assets

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:
a button showing letters of the alphabet, used for sorting data

An example of the Sort by drop-down menu:

a popup window with title that reads sort range from A1 to B8; a box labeled data has header row is checked; a button labeled Z to A is also checked
Enter Sorting Parameters in the Popup Window

The finished Pareto chart looks like this:

a Pareto column chart of market value versus household assets; the highest value is automobile on the far left and the lowest value is primary residence on the far right
Pareto Chart for Market Value of Household Assets

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:

fx A B C D
1 Variable Expense Actual Projection Difference
2 Home Repairs $400 $175 $225
3 Clothes $50 $0 $50
4 Groceries $500 $390 $110
5 Takeout Food $300 $410 -$110
6 Auto Repairs $50 $18 $32
7 Gasoline $100 $122 -$22
8 City Water $80 $77 $3

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:

a stacked column chart showing variable expenses; labels include actual, projection, and difference; takeout food and gasoline have bars that extend in the negative direction while all other frequencies are positive
Stacked Column Chart for Variable Expenses

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.

Technology: Apply Your Skill
You think you spend too much money eating out. You want a quick, visual way to compare those expenditures to others like car payments and rent. Which column chart would be the best choice in this situation? Why would you make this particular selection?


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!

fx A B C
1 Annual Income Dollars -----
2 Employment $41,300
3 Annual Expenses Dollars Percentage
4 Housing $14,400
5 Food $12,700
6 Utilities $2,500
7 Health Care $2,800
8 Transportation $4,200
9 Miscellaneous $1,500
10 Savings $3,200
11 Total Expenses $41,300 100%

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!

a pie chart of annual expenses; the largest wedge or sector is housing while the smallest wedge is miscellaneous
Pie Chart for Annual Expenses


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.
summary
In this tutorial, you learned how to visualize financial information using charts. Getting started with charts is made easy with the help of spreadsheets. Strong technology skills are the key to being able to use these helpful tools. Spreadsheets can take categorical data from frequency tables and quickly display the data in standard charts such as column charts, bar charts, and pie charts. You can also tap into the computing power of spreadsheets to create custom charts such as Pareto charts to show how values ascend or descend.

With the advent of spreadsheets, creating charts has never been easier. Spreadsheets have raised the efficiency and productivity for all types of money management, both personal and professional.


Source: THIS WORK IS ADAPTED FROM “REPRESENTING DATA GRAPHICALLY,” PROVIDED BY LUMEN LEARNING IN ACCORDANCE WITH CREATIVE COMMONS LICENSE CC BY ATTRIBUTION 4.0 INTERNATIONAL.

Terms to Know
Bar Chart

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

Column Chart

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

Frequency Table

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

Pareto Chart

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

Pie Chart

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

Stacked Chart

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