iamliterate - msexcel

Integrating Spreadsheets

Spreadsheets are powerful organizational tools. They can be used for an incredible amount of things. (For more information on the background of spreadsheets, see Wikipedia's article at http://en.wikipedia.org/wiki/Spreadsheets

Basic Components

A spreadsheet is comprised of a grid of locations that can stored different information. Each spot on the grid is called a cell and is identified or named by its row and column (A4 is the first column and 4th row.) Cells can contain several different types of data including: text, times, dates, numbers, currency and formulas. In its simplist form, a spreadsheet can display information much like a table in a word processor does. The power of a spreadsheet comes in using formulas. If you have a row of data, you can use a command similar to =sum(a1:a5) to add the cells in a1,a2,a3,a4,and a5 and put the answer in the cell with the formula.

Entering Data

MS Excel is basically an intelligent table. You will find working in a spreadsheet easiest if you either have your data in a table or know what format you want it to look like.

1. You need an Open Worksheet.
2. You then need to enter your data into a table.
a. Be sure to include Column Headings.
b. Be sure to include Row Headings.
3. Format the table using the Format > Cells menu
See - using the chart wizard

Displaying Data

It is important to choose the best way to display data to provide the most meaningful information. Each type of graphic is best suited for specific types of data.
Pie Charts best represent parts of a whole. (The data should add up to 100%)
Bar Graphs best compare data sets. (Comparing how group A and group B scored)
Line Graphs best represent changes(usually over time.)
Scatter Plots plot each individual data point and you visually can look for trends

Using Pie Charts

The Pie Chart represents parts of the whole. It can take actual numbers or already calculated percentages to display the data. The example here uses an exploded pie chart with percentages calculated embedded in the worksheet with the data.

Using Bar Graphs

Bar graphs can use the same data, but are set up to make it easier to compare between individual parts

Using Line Graphs

The line graph in the example is using data from 4 different people. Each person is graphed individually and it is easier to see overlaps. You can also see trends over time visually. This has 4 sets of data; each chart is capable of displaying multiple sets of data.

Note also that you can adjust the Y Axis to create misleading data.

Using Scatter Plots

This scatter plot is used to demonstrate using a random number generator to generate 302 points

An additional use of a scatter plot is to plot apparently random numbers and see trends or groupings.

How to Make A Chart

Starting the Chart Wizard

Select (highlight) the data that you want to chart.

On Standard toolbar (the top one), click Chart Icon.

A dialog box will open up at the first step.

Step 1 – Chart Type

  • Stay on Standard Type, and choose the type of chart you want.
    • The Subtypes are different ways of displaying the line data.
    • The left types are without points, with points, and three dimensionally. The others are stacked and 100% stacked.
    • If you click on each type it tells you what it shows.

  • Press and Hold to View Sample.
    • If you are not sure it will show the data the way you want, click this bar and it will give you a temporary preview of the chart.

Step 2 – Chart Source Data

  • Data range tab
    • – allows you to select certain values from your chart without including all of it. It is easier to select this ahead of time by highlighting the cells you want.
    • – If you want to enter them in yourself, use the following formula
      • =[SheetName]!$[CellLetterTopLeft]$[CellNumberTopLeft]:$ [CellLetterBottom Right]$[CellNumberBottom Right]
      • =Sheet1!$A$11:$E$15
    • – At this stage you can choose how you want your data displayed, whether it is the changes in the rows or the columns that you want to show most clearly.

  • Series Tab
    • Useful for Renaming the lines in your legend
    • Can tweak the data lines here too.

Step 3 – Chart Options

  • Titles - Give a title to the chart and to the x and y axes
  • Axes – how will the data on each axis be named?
  • Gridlines – do you want each increment of change shown, or do you just need to see the trend?
  • Legend – where will you place the legend?
  • Data Labels – each bar or line will be labeled with its value.
  • Data table – you can include the data that your chart is based on.

Step 4 - Chart location

  • As a sheet of its own
  • As an object in another sheet or your data sheet

Once your chart is made you can still change your chart format by double-clicking the element you want to change.

Other Aspects

Integrating with Students

Assessing Bias in Graphs and Charts

As with most forms of communication, they can be manipulated. A Bar Graph can be manipulated to show that test scores of 57 and 60 appear that the 60 is 3 times better than the 57. This is done by adjusting the values on the X axis to start at 56. Often charts in newspapers have truncated the chart to highlight differences or similarities even if they are minor.

Other useful features in MS Excel


=sum(a1:a5) - Sums all numbers in cells a1 through a5
=sum(a1,b1,c1,a2,b2,c2) - Sums all numbers in cells a1,b1,c1 and a2,b2,c2
=RAND() - returns a random decimal number between 0 and 1. (You can multiply by a number to get whole numbers and you can show only the whole number portion)

Data Collection

A quick word on data collection.
If you create a paper based survey, then you need to do the work and collate the data before you can quantitatively analyze the data.
You can create a Spreadsheet and ask people to answer your questions in your excel spreadsheet - not usually an option
You can provide a paper survey that you code and enter into a spreadsheet.
You can use an online survey tool to collect the data.
InspireData - new software available through www.inspiration.com
Survey Monkey - online data collection; however, the free versions are limited.
Forecast - A new web2.0 free survey tool - http://fo.reca.st/surveys/home

Once you have your data collected, you can then use the power of spreadsheets to chart / graph the existing data, or perform summary activities to be able to chart or graph the new meta data.

Additional Resources

Atomic Learning.com has a series of free video tutorials on MS Excel