MAKING EXCEL CHARTS

SAMPLE TABLE / CHARTS: (Population and Health Spending by Province / Territory and Year 2001-2003)

1. Creating an Excel chart from a simple 2 variable table

2. Creating an Excel chart from a simple 3 variable table

3. Creating an Excel chart from a multi-variable table

CREATING AN EXCEL CHART FROM A SIMPLE 2 VARIABLE TABLE

• highlight data to be charted (in this case it would be from B1 to N2)
  
  

• go to Chart Wizard and choose Chart Type 

   

  • Under Data range = the cells chosen  
    in the 2 variable table worksheet the cells chosen are "B1 to N2"
    ='2 variable table & chart'!$B$1:$N$2

    • choose row if the column headings are to form the X axis
      (in the 2 variable table worksheet the Provinces (row) form the X-axis)
       

    • choose column if the data from a given column forms the X axis
       

  • Titles: added as desired
     

  • To change axis format:

    • right click on desired axis to "Format Axis"

    • "Scale" allows changes in display
      eg./ size of major and minor units if numeric, number of tick-marks to show number of units if text, etc.)

  
  

  • Under Series = Legend item(s)  
    value of each Legend item may occur automatically or may need to be specified

    • in the first column graph, we named Series 1 as 2001
      the value shows automatically: ='2 variable table'!$B$2:$N$2

    • if the values for the X axis do not show as desired:

      • click on bottom box Category (X) axis labels

      • a "wizard" box will appear to accept your selected data range
        (highlighting the selected range automatically enters this data into this "wizard" box) 
        ie./  "2001" = cell A2
        therefore, the value showing in the box would be: ='2 variable table'!$A$2

       

BACK TO TOP

CREATING AN EXCEL CHART FROM A SIMPLE 3 VARIABLE TABLE

• highlight data to be charted    (in this case it would be from B1 to N4)
  
  
  

• go to Chart Wizard and choose Chart Type
  

  • Under Data range = the cells chosen   
    in the 3 variable table worksheet the cells chosen are A1 to N4
    ='3 variable table'!$A$1:$N$4

    • choose row if the column headings are to form the X axis
      (in the 3 variable table worksheet the Provinces (row) form the X-axis)
       

    • choose column if the data from a given column forms the X axis
       

    • Titles: added as desired
       

    • To change axis format:

      • right click on desired axes to "Format Axis"

      • "Scale" allows changes in display
        eg./ size of major and minor units if numeric, number of tick-marks to show number of units if text, etc.)

   

  • Under Series = Legend items   

    • value of each Legend item may occur automatically or may need to be specified
      in the 3 variable table worksheet, NFLD is cells B2 to B4
      ='3 variable table & chart'!$B$2:$B$4

    • if the values for the X axis do not show as desired:

      • click on bottom box "Category (X) axis labels"

      • a "wizard" box will appear to accept your selected data range
        (highlight the labels in your table and these will be show as the selected range)
        eg./ for 2001-2003 along the X axis, the value showing in the "wizard" box would be:
        ='3 variable table & chart'!$A$2:$A$4
         

BACK TO TOP

CREATING AN EXCEL CHART FROM A MULTIPLE VARIABLE TABLE USING PIVOT TABLES

• Edit data if required to filter out specific elements (see "multi-variable table" worksheet vs. "edited multi-variable table" worksheet where annual  cumulative sums for all Canada have been removed)
  

• highlight data to be charted
  

• Choose DATA (menu option) => "Pivot table and Pivot Chart Report .."
  

• "what kind of report do you want to create?" = "PivotChart with PivotTable"

  • "range of cells" = range of cells for data selected
    - in this case range = $A$1:$D$40

  • this either creates the two worksheets like that which we named "pivot table" and "pivot chart"
    or embeds both in a current worksheet

  • a screen opens with the "pivot table" fields overlaying the "pivot chart" template

  • drag the desired pivot table fields into the desired pivot chart cells (pl. click to see)
    if: "Province" dragged to "Series"
        "Year" dragged to bottom (Category Fields) and
        "Health Budget" dragged to data box, the following Pivot Chart is created (pls. click to see)
       OR see Pivot Table - Budget and Pivot Chart - Budget worksheets
    

BACK TO TOP