Excel Charts – Basic Skills Creating Charts in Excel.

Presentation on theme: "Excel Charts – Basic Skills Creating Charts in Excel."— Presentation transcript:

1 Excel Charts – Basic Skills Creating Charts in Excel

2 Basic Chart Elements Legend Gridlines X-Axis Y-Axis Data Series Data Points Y-AxisVertical axis on a chart. X-AxisHorizontal axis on a chart. Data SeriesColumns or rows of data from the worksheet. GridlinesLines in the plot area of the chart. Allows data markers to read more easily. LegendText that identifies the chart series. Data PointsValues from the worksheet.

3 Chart Types Excel has many different chart types to choose from. Generally, the end result of the data in a worksheet will determine the type of chart to use. Here are a few examples: When you want to…Use a… Show the significance of values in a specified time period; show the amount of change rather than time or rate of change. Area Chart Show values for comparison. Categories are usually shown on the x-axis and values on the y-axis. Bar Chart Column Chart Show trends over a specified time period.Line Chart Show one data series (one column of information).Pie Chart Show changes or frequencies of data in regard to a center point and each other. Radar Chart

4 After creating a worksheet in Excel, you can use charts to graphically represent the data. You can embed a chart on a worksheet or place a chart on a separate sheet within a workbook. There are two ways to create charts: The F11 function key The Chart Wizard 2 Ways to Create Charts

5 Using the F11 Key 1.Select the data to be charted. 2.Press the F11 key on your keyboard. 3.A Column Chart will appear.

6 Using the F11 Key (cont.) 1.To add additional chart elements, use the Chart Options command on the Chart menu. 2.To change the chart type, use the Chart Type command on the Chart menu.

7 Using the Chart Wizard 1.Select the data to be charted. 2.On the Standard toolbar, click the Chart Wizard button. Or, select the Chart command from the Insert menu.

8 Using the Chart Wizard (cont.) There are 4 steps associated with the Chart Wizard. When you have completed a step, click the Next button at the bottom of the dialogue box. Step 1 Select the chart category.

9 Using the Chart Wizard (cont.) Step 2 Plot data by columns or rows

10 Using the Chart Wizard (cont.) Step 3 Chart Formatting Options

11 Using the Chart Wizard (cont.) Step 4 Chart Placement

12 Review To create a chart using the F11 or Chart Wizard method: 1.Select the data to be charted. 2.Press either the F11 key or select the Chart Wizard icon. 3.When the chart is created, use the Chart menu or Chart toolbar to add or remove chart elements or change the chart type.

13 Creating a Run Chart Tracking Trends

14 A Run Chart will allow you to measure the performance of a process over a period of time. A Run Chart allows you to detect trends, shifts, or cycles in a process. What is a Run Chart

15 1. Gather Data. Collect at least 20-25 data points to detect meaningful patterns. How to Create a Run Chart

16 2. Select the data points to be charted. How to Create a Run Chart

17 3. Use the Chart Wizard to create the chart (select the Insert Menu and choose the Chart Command, or click the Chart Wizard icon on the Standard Toolbar. On Step 1 of the wizard, select the Line Chart type category, and then choose a line chart with markers from the sub-types on the right (row 2). Click the “Next” button to advance to the Step 2. How to Create a Run Chart

18 On Step 2 of the wizard, ensure the your data is plotted by rows. Click the “Next” button to advance to the Step 3. How to Create a Run Chart

19 On Step 3 of the wizard, add a chart title and labels to the X and Y axis. Click the “Next” button to advance to the Step 4. How to Create a Run Chart

20 On Step 4 of the wizard, choose a location for your chart. To place the chart on the same worksheet as the data, select the “As object in:” option. Click the dropdown arrow to choose the appropriate worksheet. Click the “Finish” button. How to Create a Run Chart

21 Your Run Chart has been created. How to Create a Run Chart

22

23 Creating a Column Chart Comparing Values

24 A Column chart illustrates comparisons among individual items. This type of chart compares values across categories. It is also available with a 3-D visual effect. In the following chart, categories are organized vertically, and values horizontally, to place focus on comparing the values. What is a Column Chart?

25 1. Select the data points to be charted. How to Create a Column Chart

26 2. Use the Chart Wizard to create the chart (select the Insert Menu and choose the Chart Command, or click the Chart Wizard icon on the Standard ToolColumn. On Step 1 of the wizard, select the Column Chart type category, and then choose a Column chart from the sub-types on the right. Click the “Next” button to advance to the Step 2. How to Create a Column Chart

27 On Step 2 of the wizard, ensure the your data is plotted by columns or rows. Click the “Next” button to advance to the Step 3. How to Create a Column Chart

28 On Step 3 of the wizard, add a chart title. Click the “Data Labels” tab to add values to your columns. How to Create a Column Chart

29 On the “Data Labels” tab, choose the “Value” option. Click the “Legend” tab to remove the legend. How to Create a Column Chart

30 On the “Legend” tab, de- select the “Show Legend” option. Click the “Next” button to advance to the Step 4. How to Create a Column Chart

31 On Step 4 of the wizard, choose a location for your chart. To place the chart on the same worksheet as the data, select the “As object in:” option. Click the dropdown arrow to choose the appropriate worksheet. Click the “Finish” button. How to Create a Column Chart

32 Your Column Chart has been created. The Chart menu gives you many options for working with your column chart. How to Create a Column Chart

33 Formatting the Column Chart series: At a slow rate, continuously click on a series in the chart until “resizing squares” surround it. How to Create a Column Chart

34 Formatting the Column Chart series: Right-click on the selected series. Choose the “Format Data Point” option from the menu. How to Create a Column Chart

35 Formatting the Column Chart series: When the Format Data Point dialogue box appears, choose the appropriate color from the palette on the right. Click OK. How to Create a Column Chart

36 Formatting the Column Chart series: The series is now formatted. Continue this process until all series in the chart have been formatted to the appropriate color. How to Create a Column Chart

37 Creating a Pie Chart

38 Pie charts have only one series of data, so you should use only one column or row of data. You can also use one column or row of labels. What is a Pie Chart?

39 1. Select the data points to be charted. How to Create a Pie Chart

40 If the cells you want to select for your chart are not in a continuous range, do the following: Select the first group of cells that contain the data you want to include. While holding down CTRL, select any additional cell groups you want to include. The nonadjacent selections must form a rectangle. How to Create a Pie Chart

41 2. Use the Chart Wizard to create the chart (select the Insert Menu and choose the Chart Command, or click the Chart Wizard icon on the Standard Toolbar. On Step 1 of the wizard, select the Pie Chart type category, and then choose a Pie chart from the sub-types on the right. Click the “Next” button to advance to the Step 2. How to Create a Pie Chart Using the Chart Wizard

42 On Step 2 of the wizard, ensure the your data is plotted by columns. Click the “Next” button to advance to the Step 3. The Chart Wizard

43 On Step 3 of the wizard, add a chart title. Click the “Titles” tab to add a title to your Pie chart. The Chart Wizard

44 On Step 3 of the wizard, add labels to your chart. Click the “Data Labels” tab to add percentages or actual values from your worksheet. Click the “Next” button to advance to the Step 4. The Chart Wizard

45 On Step 4 of the wizard, choose a location for your chart. To place the chart on the same worksheet as the data, select the “As object in:” option. Click the dropdown arrow to choose the appropriate worksheet. Click the “Finish” button. The Chart Wizard

46 Your Pie Chart has been created. Pie Chart

47 Pull out all of the slices in a pie chart: Click the pie, and then drag away from the center of the chart. Pie Chart

48 Pull only one slice in a pie chart: Click the pie, click the slice you want to move, and then drag the slice away from the center of the chart. Pie Chart

49 Creating a Histogram: Analyzing the Frequency of Data

50 A histogram is a chart that takes a collection of measurements and plots the number of measurements (frequency) that fall with a set of intervals (bins). A Pareto Chart is a variation of a histogram. Data in this type of chart is presented in an output table in descending order of frequency. Both charts can be created in Excel by using the Histogram tool in the Analysis Toolpak, which is located on the Tools menu. What is a Histogram?

51 The Analysis Toolpak is an Excel add-in (A supplemental program) program that is available when you install Microsoft Office or Excel. To use it in Excel, however, you need to load it first. On the Tools menu, click Add-Ins. In the Add-Ins available box, select the check box next to Analysis Toolpak, and then click OK. (If Analysis Toolpak is not listed, click Browse to locate it.) If you see a message that tells you the Analysis Toolpak is not currently installed on your computer, click Yes to install it. Click Tools on the menu bar. When you load the Analysis Toolpak, the Data Analysis command is added to the Tools menu. Analysis Toolpak

52 Enter the data you want to use as input for the Histogram tool in columns. Please note that you must enter quantitative data (item amounts or test scores) in each cell of the input columns. Step 1 Test Turn-Around Time Minutes 3112435115 32355542 41462123 43473449 542988102 75963755 24437323 117134584 36612737 485735107 68387761 91254467 56486947 37582231 3593348 How to Create a Histogram

53 In another column, enter the bin numbers you want to use for the analysis. The bin numbers need to be entered in ascending order. Please note that if you do not enter bin numbers, the Histogram tool automatically creates evenly distributed intervals using the minimum and maximum values in the input range. Bin numbers represent the intervals you want the tool to use for measuring the input data in the data analysis. Step 2 Minute Bin Sizes 0 10 20 30 40 50 60 70 80 90 110 120 130 How to Create a Histogram

54 On the Tools menu, click Data Analysis. Step 3 How to Create a Histogram

55 In the Analysis Tools box, click Histogram, and then click OK. Step 4 How to Create a Histogram

56 Step 5 – Entering the Input Range, Bin Range, and Output Range in the Histogram Dialogue Box: This is the location of the input data in your worksheet. This is the location of the bins in your worksheet. This box can be left blank and Excel will create evenly distributed intervals. This is the upper left cell of the range where you want the analysis to appear. How to Create a Histogram

57 Step 5 – Displaying the Output You can display the output of the analysis in a chart, cumulative percentages, or sorted (pareto). How to Create a Histogram

58 When you select the chart output option, the Histogram tool simultaneously generates a chart along with the frequency distribution table. Chart Analysis Output of Analysis in Chart Form

59 1.The histogram reports the number of input values that are equal to or greater than the bin value but less than the next bin value and displays it in the Frequency column. 2.The last value in the table reports the number of input values equal to or greater than the last bin value. 3.If you specified the Chart Output option in the Histogram dialog box, the Histogram tool simultaneously generates a chart along with the frequency distribution table. Things to Keep in Mind When Reviewing the Output of the Analysis

60

61 Creating a Scatter Chart Identifying the Relationship Between Variables

62 A Scatter chart identifies the possible relationship between the changes observed in two different sets of variables. This type of chart supplies the data to confirm a hypothesis that two variables are related. What is a Scatter Chart?

63 1. Select the data points to be charted. How to Create a Scatter Chart

64 2. Use the Chart Wizard to create the chart (select the Insert Menu and choose the Chart Command, or click the Chart Wizard icon on the Standard Toolbar. On Step 1 of the wizard, select the Scatter Chart type category, and then choose a Scatter chart from the sub-types on the right. Click the “Next” button to advance to the Step 2. How to Create a Scatter Chart

65 On Step 2 of the wizard, ensure the your data is plotted by columns. Click the “Next” button to advance to the Step 3. How to Create a Scatter Chart

66 On Step 3 of the wizard, add a chart title. Click the “Titles” tab to add titles to the X axis and Y axis. How to Create a Scatter Chart

67 On Step 4 of the wizard, choose a location for your chart. To place the chart on the same worksheet as the data, select the “As object in:” option. Click the dropdown arrow to choose the appropriate worksheet. Click the “Finish” button. How to Create a Scatter Chart

68 Your Scatter Chart has been created. How to Create a Scatter Chart