Building Charts and Graphs with Excel



Building Charts and Graphs with Excel

Pie Chart of Categorical Data

A Pie Chart shows how a 100% of a quantity goes into different categories.

Step 1

Enter the data in the spreadsheet. The data that goes into the chart are the response categories and the counts for each response.

Sometimes the data is sorted by frequency before entering so that the chart will progress from the largest to the smallest category.

| |A |B |C |

|1 |Conducting Appraisals |No. | |

| |since Training | | |

|2 |Much Easier |5 | |

|3 |Easier |8 | |

|4 |Same |3 | |

|5 |Difficult |1 | |

|6 |More Difficult |0 | |

|7 | | | |

Step 2

Highlight the table. Include row and column labels.

Move the mouse to cell A2; Press the mouse button and move the mouse to Cell B7 while still pressing on the mouse button. Release the button.

Step 3

Start the Chart Wizard.

Press the Chart on the Standard Toolbar, or on the Menu Bar, select Insert/Chart.

Step 4

Choose Pie as Chart type. (In my example, I selected the chart in the upper left corner as Chart sub-type. ) You can use the “Press and Hold” button below the diagrams to view a sample of your selection.

Click Next and confirm the data range.

Click Next for the Chart Options box.

Under the title tab, give the chart a title. (I used Performance Appraisals).

Choose the Legend Tab to locate the position of the Legend, if you choose to use one.

Choose the Data Labels Tab to indicate how to label the segments. If you do not want a legend, try, ”Show Label and Percent.” You can also show a preference for leader lines here.

Click Next and specify the location of the chart. (I choose to include it on the existing sheet.)

Click Finish and the chart is inserted.

Step 5

Use the Chart Toolbar to edit the finished product.

Note: if you change the data in your reference table, there is an automatic change in Chart that reflects the change.

Building Charts and Graphs with Excel

Bar Chart of Categorical Data

The bar chart is particularly useful in comparing data by categories.

Step 1

Enter the data in the spreadsheet. The data that goes into the chart are the response categories and the counts for each response.

Sometimes the data is sorted by frequency before entering so that the chart will progress from the largest to the smallest category.

| |A |B |C |D |

|1 |Average Units Sold (per person) by Product Line |

|2 |Product |Before |After | |

| | |Training |Training | |

|3 |B41 |12.34 |18.52 | |

|4 |Ba42 |2.03 |3.05 | |

|5 |B41F |5.64 |12.66 | |

|6 |C21 |8.76 |14.32 | |

|7 |Other |1.05 |3.09 | |

|8 | | | | |

Step 2

Highlight the table. Include row and column labels.

Step 3

Start the Chart Wizard.

Press the Chart on the Standard Toolbar, or on the Menu Bar, select Insert/Chart.

Step 4

Choose Column as Chart type. (In my example, I selected the chart in the upper left corner as Chart sub-type. ) You can use the “Press and Hold” button below the diagrams to view a sample of your selection.

Click Next and confirm the data range and get a preview.

Click Next for the Chart Options box.

Under the title tab,

give the chart a title. (I used Average Units Sold (per person) by Product Line )

give the horizontal axis (Category) a title. (I used Product)

give the vertical axis (Value) a title (I used Average Sold/Person)

Choose the Legend Tab to locate the position of the Legend.

Choose the Data Labels Tab to indicate how to label the segments. (I used None.)

If you want to show how the table relates to the chart, use the Data Table Tab.

Click Next and specify the location of the chart. (I choose to include it on the existing sheet.)

Click Finish and the chart is inserted.

Step 5

Use the Chart Toolbar to edit the finished product.

Note: if you change the data in your reference table, there is an automatic change in Chart that reflects the change.

The Chart Toolbar

[pic]

|1 |2 |3 |4 |5 |6 |7 |8 |

1. With this button, select the object on the chart that you wish to format.

2. Click this button to produces the Format Chart Area dialog box. For whatever object you selected (1), you can format fonts, borders, colors, etc.

3. Clicking this button allows you to change the chart type. For example, change a pie chart to a bar chart.

4. Click to remove or restore a legend.

5. This button refers to the data table. A data table is a grid in a chart and represents the numeric data used to create the chart. They can be displayed in line, area, column and bar charts.

6. Read the data by row

7. Read the data by column.

8. These buttons will print information on the chart at an angle.

Excel Functions: Statistical Functions

A function has three main components:

1. = sign

2. function name

3. a range of cells (called the function arguments)

The functions can be accessed using the Function Wizard which is on the Standard Toolbar.

As you can see, there are several categories of functions. We will use a few of the statistical functions.

[pic]

The left column displays categories of functions and the right column displays specific functions.

Step 1: Select a category and function. Click on next.

Step 2: Specify the arguments or range; the range may be contiguous (enter using the mouse by point and drag on the spreadsheet or type in the range); the range may be discontinuous; the values should be separated with commas (e.g., B2, C4, D5).

Specify other parameters, as required. See the Basic Statistics Functions Sheet.

The general syntax for an Excel function is:

FUNCTION(argument 1, argument2,….)

[Arguments are values that are used in completing a calculation; often they specify a cell or range of cell where you have entered data.]

Note the small box in the upper right hand corner of the following dialog box. This button is called the Collapse Dialog Box. It is a tool that makes easier the entry of the arguments

[pic]

You can always use the Help button (lower left corner) to see the specific requirements for entering the values.

SUM

You don’t need the Function Wizard for this one. Just use the Sum button (Σ) on the Standard Tool Bar.

Place you cursor under a row of numbers and it will sum the number above.

If you wish to place the sum in a different location:

1. Put your cursor in the cell where you want the sum to appear.

1. Click the Sum button.

1. In the cell, use the mouse to indicate the range of values to be summed.

1. Press Enter and the sum appears.

AVERAGE

1. Enter your test scores.

1. Select the cell where you want the answer to appear.

1. Click the function Box on the Standard Toolbar.

1. Click Statistical on the left side of the window.

1. Click AVERAGE on the right side of the window.

1. Click OK.

1. The cursor will be in the box “Number 1” Enter the data. The best way is to use the mouse to select the data.

1. Click OK.

STDEV (Standard Deviation of a Sample)

Move to a new location to where the value of the standard deviation will appear.

Repeat the steps for AVERAGE, except on the right of the Paste Function Box, scroll down until you find STDEV.

(Use the Decrease Decimal button on the Formatting Tool Bar to reduce the number of decimal points in the answer; in this case, you only need one or two values following the decimal point.)

CORREL (Correlation Coefficient)

1. Enter the data.

1. Repeat the steps above, finding CORREL on the right side of the Paste Function dialog box.

Enter the values in, for example, column A as Array 1.

Enter the values in column B as Array 2

LINEST

For the equation of the line of best fit: slope of the line fit and the constant:

1. Select two cells for the answer (the answer is an array of 2 values).

1. LINEST array 1 is B2: B15

array 2 is A2: A15

1. If you want the slope and intercept, leave the next two space blank or put True in each.

1. To indicate an array, press: Control Shift Enter

[After entering the formula, place the cursor after the formula in the dialog box & press ctrl+shift+enter]

1. The slope m and the y-intercept b are returned for the equation: y = mx + b

Building Formulas

Formulas can be constructed to perform your own unique calculations. In writing a formula you define the arithmetic used to calculate values displayed in a worksheet

Common Arithmetic Operations

|Operation | Operator |Example | Explanation |

| | | | |

|Add |+ |A3+8 |Adds 8 to the value in cell A3 |

|Subtract |- |A3-8 |Subtract 8 from the value in cell A3 |

|Multiply |* |A3*D4 |Multiples the value in D4 by the value in cell A3 |

|Divide |/ |C6/c7 |Divides the value in C6 by the value in cell C7 |

|Exponents |^ (shift 6) |D4^2 |Raises the value in D4 to the second power |

| | |D4^0.5 |Finds the square root of the value in D4 |

Order of Operations

1. Parentheses

1. Exponents

1. Multiplication/Division

1. Addition/Subtraction

Building Formulas

1. Select a cell where you want your result to appear

1. Begin the formula by typing the = sign

1. Type arithmetic functions using cell locations rather than the actual numbers. You can use the mouse and point to the location.)

1. Press the Enter button.

Example

On the following sheet, the formula for a weighted average is displayed. We are weighting Test 1 as 45% of the average and Test 2 as 55% of the average.

4. The formula was actually entered in cell D3 and this cell displays the result of the formula.

5. The actual formula was also written to the right so that you can see how it is formulated. It also appears above in the dialog box where you can edit it.

6. It begins with the = sign

7. .45 multiplies the value in cell B3 plus .55 times the entry in C3

8. Once you enter the formula in a cell, you can paste down and copy the formula to cells that follow.

1. Select the cells containing the data you want to copy.

1. To fill in adjacent cells, Hold the button down and drag across the cells you want to fill and then release the mouse button.

1. On the Edit menu, point to Fill and then click Down, Right, Up, or Left.

[pic]

Copying Formulas

3. I like to use the Copy and Paste buttons on the Standard Toolbar.

4. Note that the formula treats cell locations as relative; thus, for example, if you have a formula in Cell A10 that sums a column of values in A3 through A7, and you paste the formula to cell B10, the formula will now sum the values in B3 through B7.

Absolute References

Sometime you do not want Excel to change cell references. If you want to point to the same cell every time, you must use an absolute reference.

Create an absolute reference by typing a $ symbol in front of the row and column designator.

$C$3 is an absolute reference to cell C3.

If you type $C3, the row number changes, but the column designator does not.

If you type C$3, the row number stays the same, but the column designator changes.

Analysis Tool Pack

Excel provides many features that support statistical analysis. To access these features, load the Analysis ToolPak. On the Menu bar, choose Tools/Data Analysis.

If Data Analysis does not appear on the bottom of this menu, you must load it. From the Menu bar, choose Tools/Add-Ins. From the list on the left, check Analysis ToolPak. Click OK

Descriptive Statistics

The Descriptive Statistics Feature give basic descriptive statistics for a set of data. It presents a table that includes the mean, median, standard deviation, etc.

Step 1

Beginning in Cell A1, enter the data.

| |A |B |C |

|1 |42 | | |

|2 |45 | | |

|3 |67 | | |

|4 |67 | | |

|5 |67 | | |

|6 |etc. | | |

|42 |45 |67 |67 |67 |68 |70 |70 |70 |

|72 |72 |73 |73 |75 |75 |75 |78 |78 |

|81 |82 |84 |86 |86 |91 |95 | | |

Step 2

From the menu bar, select Tools/Data Analysis/Descriptive Statistics. Click OK.

Step 4

In the Descriptive Statistics Dialog Box that appears, use the mouse to specify the ranges and check appropriate boxes.

Input Range $A$1:$A$26

Check Labels if you included them in the Range

Output Range $D$1 (allow space)

Check Summary Statistics

Click OK

Step 5 Format the Table

1. Widen the column with the function names.

1. See if you need to decrease the decimal values. Use the Decrease Decimal button on the Format Toolbar.

Excel Output

|Grade |Bin | | | | |

|42 |49 | |Grade | | |

|45 |59 | | | | |

|67 |69 | |Mean |73.68 | |

|67 |79 | |Standard Error |2.37 | |

|67 |89 | |Median |73.00 | |

|68 |99 | |Mode |67.00 | |

|70 | | |Standard Deviation |11.85 | |

|70 | | |Sample Variance |140.39 | |

|70 | | |Kurtosis |2.16 | |

|72 | | |Skewness |-0.97 | |

|72 | | |Range |53.00 | |

|73 | | |Minimum |42.00 | |

|73 | | |Maximum |95.00 | |

|75 | | |Sum |1842.00 | |

|75 | | |Count |25.00 | |

|75 | | | | | |

|78 | | | | | |

|78 | | | | | |

|81 | | | | | |

|82 | | | | | |

|84 | | | | | |

|86 | | | | | |

|86 | | | | | |

|91 | | | | | |

|95 | | | | | |

Building Charts and Graphs with Excel (Analysis ToolPak)

Histograms

The histogram is useful in examining the distribution of quantitative data.

Step 1

Start in cell A1 and enter the data in the spreadsheet. I like to sort the data to get an idea of how to set up the bins for Step 2.

| |A |B |C |D |

|1 |Grades |

|2 |Student |Test |Bin | |

| | |Grade | | |

|3 | |42 |49 | |

|4 | |45 |59 | |

|5 | |67 |69 | |

|6 | |67 |79 | |

|7 | |67 | | |

|8 | |etc. | | |

|42 |45 |67 |67 |67 |68 |70 |70 |70 |

|72 |72 |73 |73 |75 |75 |75 |78 |78 |

|81 |82 |84 |86 |86 |91 |95 | | |

Step 2

Enter the Bin values; start in C3. The Bin Values are the categories for which you want frequencies. For example, the first bin counts how many students made a score that is less than or equal to 49. Bin 2 counts the number of students who scored at least 50 and no more than 59.

They should be entered in ascending order.

Step 3

From the menu bar, select Tools/Data Analysis/Histogram.

Step 4

In the Histogram Dialog Box that appears, use the mouse to specify the ranges and check appropriate boxes.

Input Range $B$2:$B$27

Bin Range $C$2:$C$27

Check Labels [if you included them in the Range]

Output Range $D$3

Check Chart Output

Click OK

Step 5

Format the Chart

1. Double click on the Chart Area. Move it below the Frequency Table. Expand it by using the mouse to drag from the corners.

1. Click on the Plot Area. Remove the border and the background color.

1. Click on the Value Axis Title and then the Category Axis Title. Change the font size to 8. Change the Category Axis Title to Scores.

1. Click on the Legend. Delete it.

1. Click on the Series “Frequency” (the blue boxes). Click the Format Data Series on the Chart Toolbar. Go to the Options Tab and change the Gap Width to 0.

Bin Frequency

49 2 Number of Scores less than or equal to 49

59 0

69 4

79 12

89 5

99 2

[pic]

-----------------------

[pic]

[pic]

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download