Introduction to Charting

• Charts which are not properly labeled are useless and will be graded as such.
• You should be able to create all types of charts/graphs.
• Creating charts
  • He demonstrates how to create a number of charts.
  • Notice totals included in charts are, for the most part, incorrect.
  • He shows how to move charts to their own worksheet.
• Exploring chart types
  • He discusses some of the different chart types.
  • Selecting the right type of chart is difficult
    • It depends on the information you wish to convey.
    • He suggests sticking to four basic types.
    • Column, Line, Bar and Pie
      • Column and Bar are best for small data.
      • Pie is better for a single set of data.
      • Line is best for a larger table of data.
  • As we discuss in finite math, it is easy to misrepresent data.
• Formatting charts
  • He demonstrates ways to change the format of various charts.
  • He discusses the option menus on the right hand side of a chart.
  • Make sure that you do the following with charts
    • Change the title to something that has meaning.
    • Add axis titles to give units, if needed.
    • Make sure legends are present if needed.
• Working with axes, labels, gridlines and other chart elements
  • He gives the names for the areas and elements of a chart.
  • He also shows how to change many of these elements.
  • Pay attention to this section.
  • And spend a little time exploring these options.
• Creating in-cell charts with sparklines
  • I am not so thrilled by these.
  • But they are available.
  • Sort of like conditional formatting, make sure that you use it sparingly.
  • It does give you a quick representation of data.

Building Some Charts

• Start with This worksheet
  • I grabbed this data from https://mycpa.cpa.state.tx.us/allocation/HistSales.jsp.
  • I used a pivot table, which we will discuss later to build the table.
  • The table shows In State Construction Revenue for the state of Texas, by quarter for the years 2002-2013.
• Construct a new worksheet.
  • Right click on Sheet 1 at the bottom of the workbook.
    • Select Rename
    • Type in Raw Data
    • 
  • Click on the plus sign at the bottom of the page to create a new sheet.
    • 
    • Rename this sheet Working Data
  • We will talk more about multiple worksheets later.
• Copy the titles for rows and columns from the Raw data sheet to the working data sheet.
  • Just highlight each, copy and paste.
  • 
• Now construct the new set of data
  • We need to scale the data, numbers in the billions are just too big.
  • Click in cell B5 of the Working data sheet.
  • Enter an =
  • Go to the Raw Datasheet and click in cell B5
  • Enter /
  • Click in cell B18
  • Hit the F4 key to make B18 an absolute reference
  • Hit enter.
  • The formula in cell B5 of the Working data sheet should be
  • 
  • Format this as currency
  • Copy this formula to cells B5:E16, paste both formula and formatting.
• Build totals for the table:
  • Build a total for each year in column F
  • Build a total for each quarter in row 17
  • Color the data in column F and row 17 light blue to indicate it is different from the rest of the table.
  • 
• Create a Pie chart showing total sales by quarter.
  • Select cells A4:E4
  • Holding the control key, select cells A17:E17
  • On the INSERT tab, select the Insert Pie dropdown and then a 3-D Pie chart.
  • 
  • On the DESIGN tab of the CHART TOOLS work area, select the Quick Layout drop down and click on Layout 6
  • 
  • Click on the title and change it to Quarterly Sales, 2002-2013
  • Click on the + sign (chart elements) to the right
    • Select Data Labels
    • Select the more arrow
    • Select Data Callout
    • Do this again and select Outside End
  • Finally, grab the Q2 sector and pull out.
    • This might take just a bit of messing to get right.
  • 
• Draw a bar chart for 2010 through 2013
  • Select A4:F4 and A13:F16
  • Insert a Clustered Bar Chart
  • Notice how one bit of data is way out of proportion? Why
  • Delete this chart
  • 
  • Select A4:E4 and A13:E16
  • Insert a Clustered Bar Chart
  • 
  • Change the title to be Construction Sales
  • Select the + button on the right hand side and activate Axis Titles
  • On the x axis, enter the title Sales in Billions
  • Delete the Y axis title.
  • 
• Create a 3D column chart for the entire dataset (A4:E16)
  • 
  • Notice this chart is very busy and hard to read.
  • Change this chart to a line chart
    • Select Change Chart Type on the FORMAT tab
    • Select line chart.
    • 
    • Select Line With Markers
  • I used quick layout 12 for my formatting
    • Add a title.
    • And axis titles.
    • 
  • Finally, insert some spark lines in column G. and Row 18
  •