Practice Problems: Data Visualization in Excel

1: Create a line chart for the closing prices for all years, and a stock chart for the high/low/close
prices for August 2013 in the Excel file S&P 500.
2: Create a pie chart showing the breakdown of occupations for each year in the Science and
Engineering Jobs Excel file, and contrast these with simple column charts.
3. The Excel file Facebook Survey provides data gathered from a sample of college students.
Create a scatter diagram showing the relationship between Hours online/week and Friends.
4. A national homebuilder builds single-family homes and condominium-style townhouses. The
Excel file House Sales provides information on the selling price, lot cost, type of home, and
region of the country (Midwest, South) for closings for 1 month. Construct a scatter diagram
showing the relationship between sales price and lot cost.
5. Create a bubble chart for the first five colleges in the Excel file Colleges and Universities for
which the x-axis is the Top 10% HS, y-axis is Acceptance Rate, and bubbles represent the
Expenditures per Student.
6. Construct a column chart for the data in the Excel file State Unemployment Rates to allow
comparison of the June rate with the historical highs and lows. Would any other charts be better
to visually convey this information? Why or why not?
7. The Excel file Internet Usage provides data about users of the Internet. Construct stacked bar
charts that will allow you to compare any differences due to age or educational attainment and
draw any conclusions that you can. Would another type of charts be more appropriate?
8. Construct an appropriate chart to show the proportion of funds in each investment category in
the Excel file Retirement Portfolio.
9. In the Excel file Banking Data, apply the following data visualization tools:
a. Use data bars to visualize the relative values of Median Home Value.
b. Use color scales to visualize the relative values of Median Household Wealth.
c. Use an icon set to show high, medium, and low bank balances, where high is above $30,000,
low is below $10,000, and medium is anywhere in between.
10. Apply three different colors of data bars to lunch, dinner, and delivery sales in the Excel file
Restaurant Sales to visualize the relative amounts of sales. Then sort the data (hint: use a custom
sort) by the day of the week beginning on Sunday. Compare the non-sorted data with the sorted
data as to the information content of the visualizations.
11. For the Store and Regional Sales database, apply a four-traffic light icon set to visualize the
distribution of the number of units sold for each store, where green corresponds to at least 30
units sold, yellow to at least 20 but less than 30, red to at least 10 but less than 20, and black to
below 10.
12. For the Excel file Closing Stock Prices,
a. Apply both column and line sparklines to visualize the trends in the prices for each of the four
stocks in the file.
b. Compute the daily change in the Dow Jones index and apply a win/loss sparkline to visualize
the daily up or down movement in the index.
13. The Excel file Economic Poll provides some demographic and opinion data on whether the
economy is moving in the right direction. Convert this data into an Excel table and filter the
respondents who are homeowners and perceive that the economy is not moving in the right
direction. What is the distribution of their political party affiliations?
14. Open the Excel file Store and Regional Sales database.
a. Sort the data by units sold, high to low
b. Sort the units sold using an icon set, where green corresponds to high sales levels, yellow to
medium sales, and red to low sales. The sort should show all the green icons first, followed by
yellow, and then red.
15. Sort the data in the Excel file Automobile Quality from highest to lowest number of problems
per 100 vehicles using the sort capability in Excel.
16. Use Excel’s filtering capability to (1) extract all orders for control panels, (2) all orders for
quantities of less than 500 units, and (3) all orders for control panels with quantities of less than
500 units in the Purchase Orders database.
17. In the Sales Transactions database, use Excel’s filtering capability to extract all orders that
used PayPal, all orders under $100, and all orders that were over $100 and used a credit card.
18. Use PivotTables to construct a cross-tabulation for the purpose of the loan and credit risk in
the Excel file Credit Risk Data. Illustrate the results on a PivotChart.
19. Use PivotTables to construct a cross-tabulation for marital status and housing type in the
Excel file Credit Risk Data. Illustrate the results on a PivotChart.
20. Create a PivotTable to find the average amount of travel expenses for each sales
representative in the Excel file Travel Expenses. Illustrate your results with a PivotChart.
21. Use PivotTables to find the number of loans by different purposes, marital status, and credit
risk in the Excel file Credit Risk Data. Illustrate the results on a PivotChart.
22. Create a PivotTable for the data in the Excel file Weddings to analyze the wedding cost by
type of payor and value rating. What conclusions do you reach?
23. The Excel File Rin’s Gym provides sample data on member body characteristics and gym
activity. Create PivotTables to find:
a. a cross-tabulation of gender and body type versus BMI classification
b. average running times, run distance, weight lifting days, lifting session times, and time spent
in the gym by gender.
Summarize your conclusions.
24. Create useful dashboards for each of the following databases. Use appropriate charts and
layouts (for example, explain why you chose the elements of the dashboards and how a manager
might use them.
a. President’s Inn
b. Restaurant Sales
c. Store and Regional Sales
d. People’s Choice Bank
25. A marketing researcher surveyed 92 individuals, asking them if they liked the new product
concept or not. The results are shown below:
Yes No
Male 30 50
Female 6 6
Convert the data into percentages. Then construct a chart of the counts and a chart of the
percentages. Discuss what each conveys visually and how the different charts may lead to
different interpretations of the data.