Sampling and Data – Excel Tools Instruction

Pie Charts and Bar Graphs

  • In a pie chart, categories of data are represented by wedges in a circle and are proportional in size to the percent of individuals in each category.
  • In a bar graph, the length of the bar for each category is proportional to the number or percent of individuals in each category. Bars may be vertical or horizontal.

Pie Charts in Excel

  1. Highlight columns of cells (hold ‘Ctrl‘ button if columns not adjacent)
  2. Click Insert, Charts and the first 2-D pie
  3. Click on the new pie chart and icon + to make changes
  4. Right click on wedge, choose ‘Add Data Labels
Table 1.4 Ethnicity of Students at De Anza College Fall Term 2007 (Census Day)
Ethnicity Frequency Percent
Asian 8,794 36.1%
Black 1,412 5.8%
Filipino 1,298 5.3%
Hispanic 4,180 17.1%
Native American 146 0.6%
Pacific Islander 236 1.0%
White 5,978 24.5%
Other 2,338 9.6%
Step 1 & 2: Highlight column of cells and create 2-D Pie
Step 3 & 4: Click icon + and add Data Labels

Bar graphs in Excel

  1. Highlight columns of cells
  2. Click Insert, Charts and the first 2-D Column
  3. Click on the new bar graph and icon + to make changes
  4. Right click on wedge, choose ‘Add Data Labels
Step 1 & 2: Highlight column of cells and insert 2-D Chart
Step 3 & 4: Click on New Bar Graph + icon to make changes and Add Data Labels

Frequency, Relative Frequency and Cumulative Relative Frequency

  • A frequency is the number of times a value of the data occurs.
  • A relative frequency is the ratio (fraction or proportion) of the number of times a value of the data occurs in the set of all outcomes to the total number of outcomes. Relative frequencies can be written as fractions, percents, or decimals.
  • A cumulative relative frequency is the accumulation of the previous relative frequencies.

Frequency in Excel

Twenty students were asked how many hours they worked per day. Their responses, in hours, are as follows: 5; 6; 3; 3; 2; 4; 7; 5; 2; 3; 5; 6; 5; 4; 4; 3; 5; 2; 5; 3.

Enter all data in column A. Create a column for data categories (column B).

  1. Enter =COUNTIF in cell C2, click column A, comma and B2
  2. Click Enter
  3. Click C2, move the mouse to the right bottom corner, a little + appears
  4. Click on the little + and drag down to repeat cell
Step 1 & 2: Enter =COUNTIF, select column A, column B, then click Enter
Step 3 & 4: Click C2 and use + in bottom right corner to drag down and repeat cell

Relative Frequency in Excel

Twenty students were asked how many hours they worked per day. Their responses, in hours, are as follows: 5; 6; 3; 3; 2; 4; 7; 5; 2; 3; 5; 6; 5; 4; 4; 3; 5; 2; 5; 3.

  1. In cell D2, enter =, click cell C2, enter /, and click C8 F4 (F4 holds a cell constant)
  2. Click Enter, 0.15 shows in D2
  3. Click D2, move the mouse to the right bottom corner, a little + shows up
  4. Click on the little + and drag down to repeat cell
Step 1: In cell D2, enter =, click cell C2, enter /, and click C8 F4
Step 2, 3 & 4: Click Enter. Then click D2, click and drag + in bottom right corner to repeat cells

Cumulative Relative Frequency in Excel

Twenty students were asked how many hours they worked per day. Their responses, in hours, are as follows: 5; 6; 3; 3; 2; 4; 7; 5; 2; 3; 5; 6; 5; 4; 4; 3; 5; 2; 5; 3.

  1. In cell E2, enter =, click D2 and Enter
  2. In cell E3, enter =SUM($D$2:D3)
  3. Click Enter, 0.4 shows in E3
  4. Click E3, move the mouse to the right bottom corner, a little + shows up
  5. Click on the little + and drag down to repeat cell
Step 1 & 2: In cell E2, enter =, click D2 and Enter, then in cell E3, enter =SUM($D$2:D3)
Step 3, 4 & 5: Click Enter, click E3, then click and drag + in bottom right corner to repeat cells

Pivot Table

  • A Pivot Table helps to arrange and summarize complex data.

Pivot Table for Frequency in Excel

Twenty students were asked how many hours they worked per day. Their responses, in hours, are as follows: 5; 6; 3; 3; 2; 4; 7; 5; 2; 3; 5; 6; 5; 4; 4; 3; 5; 2; 5; 3.

  1. Enter all data in column A
  2. Click Insert, Pivot Table
  3. Click on the area of Table/Range, highlight all data
  4. Click on the area of Location, click on cell C1 on where the pivot table will display
  5. Click OK
Step 1 & 2: Enter all data in column A then Click Insert, Pivot Table
Step 3, 4 & 5: Click on the area of Table/Range, highlight all data then click Location, click on cell C1 where pivot table will display, click OK
  1. Click on Working hours, hold and drag down to Rows area
  2. Click on Working hours, hold and drag down to Values area
  3. Click on the drop-down icon, Value Field Setting
  4. Choose Count Numbers, click OK
Step 6, 7 & 8: Click on Working hours, hold and drag down to Rows area, repeat the same for Values area then click on the drop-down icon, Value Field Setting
Step 9: Choose Count Numbers, click OK

Media Attributions

License

Share This Book