Chapter 9 – Charts

INTRODUCTION

A big part of analysis is visually appealing graphs and charts. While we will not dive into Pivot Tables and Charts in this class, there are some essential basic charts Excel can create for us to visualize data and to help our users understand data.

  • Create and understand PIE, BAR, and COMBO charts based on data in a table

Guided Exercises

Open the Chapter09_Charts file.

The CustomerAnalysis worksheet shows what products each customer of a furniture store purchased during 2015. You would like to create a visual to show how many transactions each customer had as well as how much revenue each customer brought into the store. Before you can do that, the data need to be summarized.

  1. In cell H6, use a function to return the number of transactions the customer in cell G6 had during 2015. Fill the function down through cell H10 to return the number of transactions for each customer.
  2. In cell I6, use a function to return the total revenue the customer in cell G6 brought in during 2015. Fill the function down through cell I10 to return the total revenue for each customer. Format as Accounting.
  3. Now that the data is summarized, a chart can be created. Select the data in cells G5:I10 so that customers’ names and column headings are selected as well as the corresponding values.
  4. Insert a Clustered Column – Line on Secondary Axis chart.
    • Position it over the shaded area in cells G12:M27.
    • Change the Chart Style to Style 2.
    • Change the Chart Title to 2015 Customer Transactions. Make the font size 20pt and change the font color to Blue, Accent 1, Darker 25%.
    • Add Axis Titles to the Primary Vertical and Secondary Vertical axes.
      • Change the axis title on the left to read # of Transactions. Change the font to 12pt, Century Gothic.
      • Change the axis title on the right to read Revenue. Change the font to 12pt, Century Gothic.

The completed Clustered Column – Line on Secondary Axis chart should look like the following image:

The ProductsByCategory worksheet contains a list of a store’s products. You have been asked to create a chart to show how many items are in each category. First, the data need summarized.

  1. In cell G3, use a function to return the number of items in the category indicated in cell F3. Fill the function down to cell G9 to return the number of items in each category.
  2. A column chart is good for showing how various categories of items compare to each other. Select cells F3:G9 and insert a Clustered Column chart. Place it over the shaded area in cells F11:J25.
    1. Change the Chart Title to Items per Category.
    2. Change the Chart Style to Style 9.
    3. Change the colors to Colorful Palette 3.
    4. Format the Chart Area so that the fill color is Light Gray, Background 2, Darker 50%.
    5. Format the Border Color to Orange, Accent 2 with a width of 3 pt.
    6. Make the chart have rounded corners.

The completed Clustered Column chart should resemble the image below:

  1. A pie chart is good for showing how each category compares to the whole set of data, especially if you’d like to display percentages.
    • Select cells F3:G9 and insert a 3-D Pie Chart. Place it over the shaded area in cells F30:J44.
    • Change the Chart Title to Items per Category.
    • Change the Chart Style to Style 7.
    • Add Data Labels in the form of Data Callouts.
    • Remove the Legend.
    • Pull the Living Room and Bath data callouts to the side so they are not crowding the title.

If you are going to stop working here, make sure your file is saved. When you close it, if there is any unsaved work, Excel will prompt you to save. Make sure the most recently worked file is in your IU OneDrive.

More Practice

Work you should do before our next class

Open the Chapter09_Charts file.

The EmployeeAnalysis worksheet contains transaction data for the salespeople of a store. You would like to create a visual to show the employees’ number of transactions and amount of revenue that each employee brought in to the business. First, the data need to be summarized.

  1. In cell E5, use a formula to calculate the revenue for the transaction in row 5. Fill your formula down column E to find the revenue for each transaction. Format the values as Currency.
  2. In cell H5, use a function to find the number of transactions for the employee whose ID number is in cell G5. Fill the function down to cell H25 to find each employee’s number of transactions.
  3. In cell I5, use a function to find the total revenue brought in by the employee whose ID number is in cell G5. Fill the function down to cell I25 to find each employee’s total revenue. Format as Accounting.
  4. Select cells G4:I25 and insert a Clustered Column – Line on Secondary Axis chart. Place it over the shaded area in cells K4:Q23.
    • Change the Chart Style to Style 7.
    • Change the Chart Title to Salesperson Data.
    • Double-click on one of the revenue values on the right side of the chart. The Format Axis pane should appear. Change the Major Units to 2500 and press Enter.
    • Add Axis Titles to the Primary Vertical and Secondary Vertical axes.
      • Change the axis title on the left to read # of Transactions. Change the font size to 12pt.
      • Change the axis title on the right to read Revenue. Change the font size to 12pt.
    • Format the chart border to have a blue color and a width of 3 pt.

The completed Clustered Column – Line on Secondary Axis chart should resemble the following image:


The DeptAnalysis worksheet contains transaction data from 2015. You want to summarize the data for each department so that you can create a visual that shows what percent of the revenue each department’s products brought in to the company.

  1. In cell K4, use a function to find the number of transactions for the department given in cell J4. Fill the function down to cell K10 to find the number of transactions for each department.
  2. In cell L4, use a function to find the total revenue from the sales of items in the department given in cell J4. Fill the function down to cell L10 to find the total revenue for each department. Format the values as Accounting.
  3. The visual should just show each department’s percent of the revenue, so select cells J3:J10, hold down the Ctrl key of your keyboard, and select cells L3:L10. You can release the Ctrl key.
    • Insert a 2-D Pie Chart and place it over the shaded area in cells J12:O31.
    • Change the title of the chart to Revenue by Department. Make the font 16 pt.
    • Change the fill color of the Chart Area to Light Gray, Background 2.
    • Add Data Labels in the form of Data Callouts.
    • Remove the Legend.
    • Change the colors of the chart to Colorful Palette 2.

The completed 2-D Pie Chart should resemble the following image:

 

Note

There are no solution worksheets in this file since you were given pictures of each of the completed charts.

 

License

K160: Introduction to Modeling and Solving Business Problems 1e Copyright © 2024 by Trustees of Indiana University. All Rights Reserved.