Old Excel Day 4: Charts and Printing

dps-cones

Objectives

General:

  • Know how to effectively use charts in Excel
  • Know how to prepare a worksheet for printing

Specific:

  • Create and manipulate charts
  • Use different viewing options such as Freeze Panes and Split Screen
  • Prepare a document for printing using page layout and backstage view.

Outline

Hook: DP’s_Cones_Finished.xlsx

Introduction:

  • Names, positions, goals for class, etc.

Create and manipulate charts

  • Why are charts useful?
  • What kind of graphs/charts/plots do you use for finding relationships between data?
    • Scatter-plots
  • What kind of graphs/charts/plots do you use for comparing data?
    • Pi charts, bar graphs, column graphs
  • What kind of graphs/charts/plots do you use for finding the spread of the data?
    • Histogram

Scatter-plots

  • In the class file you have 3 tabs. The first is used to demonstrate and learn about scatter-plots.
  • Scenario: You started your own snow cone shack and called it DP’s Cones. You have been open for a few years and decided to create a survey to determine if you are charging the right price for your large snow cone. In the survey, the respondent will answer how many times they would buy a snow cone each month given a certain price. The law of demand says that as you charge higher prices, the demand for the product will go down. From the 30 responses you recieved, is this true? We are currently charging $1.50. Are we charging the right price?
  • Paste Special (Transpose)
    • Use paste special to paste the values, transposed, down the empty slots labeled “Price” and “Quantity”. So it looks like this:

capture

  • Insert a scatter-plot
    • Highlight the table you just created and insert a scatter-plot.
  • Add a trendline
    • Click on the data points and then right click and select “Add Trendline…”
    • For a demand trend-line, the best trend-line is normally an Exponential trend-line
  • Display equation on Chart
    • Check “Display equation on chart”
    • You can drag the equation so it doesn’t cover the data
  • Display R-squared
    • R-squared tells you how much of the variance in the y variable is explained by the independent variables.
    • The closer to 1 the R-squared is, the more the variation can be explained
      • In this case, a 0.9696 show a pretty strong relationship between the x and y variables
  • Switching the Y and X variables on a scatter-plot (2 methods)
    • Method 1:
    • Instead of highlighting the data and inserting the graph, go to the insert tab, and select the scatter-plot first.
    • Then go to select data in the data group.
    • Add a legend series.
    • Series X values is asking for the range of values you want to be shown on the x axis on the graph. In this case it is the quantity. So click in the input box and highlight the quantity range.
    • Series Y values is asking for the range of values you want to be shown on the y axis on the graph. In this case it is the price. So click in the input box and highlight the price range. You can delete the “=-1” thing if it is in the input box.
    • Press ok
    • Method 2:
    • After inserting graph, go to Chart Design tab
    • Click “Switch Row/Column”
  • Revenue graph
    • Revenue = Price * Quantity
    • So use a function to calculate the revenue at each price.
    • Create a scatter plot that shows revenue at each price. Revenue needs to be on the y axis.
    • Add a trend-line, this time a polynomial should work.
    • Change major units in the axis options to say “.5 instead of 1. What does that do?
    • What is the equation? What is the R-squared?
    • Estimate the price at which revenue will be maximized.
      • Answer: About $2.50
    • So we need to be charging a whole dollar more!

Pie Charts and Histograms

  • Click on the next worksheet or press CTRL + PageDown
  • Scenario: We implemented a rewards program last year and had 200 people sign up! We kept track of their First Name, Last Name, Age, Gender, and Phone Number
  • Countif formula to fill out the Gender Chart
    • There should be 86 males and 116 females… yes I know, some of the female names are labeled “M” and visa-versa.
  • Make a pie chart
    • Select the gender chart and insert a pie chart.
    • Change the title to a more descriptive name
    • Add a data callout so it shows the percentages outside the pie.
  • Histograms (kinda tricky)
    • We want to see what age group signed up for our rewards program.
    • You will need the data analysis tool pak add-in to create a Histogram.
    • Underneath the cell with “Ages” in it you will need to add the increments you want to show up on your histogram.
      • I chose increments of 5… 10, 15, 20, 25, 30, 35
    • Click on the data tab. In the Analyze group, click on “Data Analysis.”
    • Find histogram, click it, and press ok.
      • Input range: the input range is the range of data you want to analyze… the range E16:E215.
      • Bin range: the range of increments we set up… J35:J40
      • Output range: where do you want to output the table? L34 works
      • Chart output: check this to get the chart as well as the table
  • Bonus!
    • The bonus section at the bottom is if you want to do a review of the vlookup, or index/match functions.
    • If you want to search the phone number, you’ll have to use index/match
      • =INDEX($B$16:G215,MATCH($K$47,$G$16:$G$215,0),2)

Printing (Sales Data Tab)

  • Viewing options
    • Freeze Panes
    • Split Screen
  • Printing
    • Page Breaks
    • Manipulating size of page
    • Page Break Preview
    • Page Layout
      • Print Titles
    • Header/Footer options

Conclusion

  • Recap:
    • Create and Manipulate charts
    • Viewing Options
    • Printing
  • Next time
    • Advanced Data Analysis
  • Office/Facebook

Example Questions

Why are charts useful?

How could we make this chart more readable?

What are some situations that this would be useful?

Example Activities and Files

Have students download dps_cones_start and follow along as you show them how to use charts and printing.

 Other Resources

Link to Old Day 4 Page