uses of excel
DESCRIPTION
I presented this at a professional development conference to show teachers the many uses of MS Excel in the classroom. It covers everything from wrapping text and merging cells to calculating and interpreting the standard deviation for test data. Each slide is hyperlinked to a video I created ( with a software called Webinaria), of that function being performed. Each slide also contains a hyperlink to the example spreadsheets and the presentation handout.TRANSCRIPT
Uses of Excel
Analyzing and Manipulating Classroom data
In This Presentation
Applications of Excel for viewing/manipulating data sets
Basic and advanced commands in Excel (insert, sort & filter, autosum, etc.)
A brief explanation of the descriptive statistics that will be used
Mani interesting furry animals…including the majestic møøse
About Me• Education: BA Sociology (Oklahoma State Univ.
‘03) MS Sociology (Univ. of North Texas ‘06)
• Teachering: US history (8th) ‘06-’08English (8th) ‘08-Spring ‘11
• Presently: Middle School Library Assistant
• Likes: donuts (all kinds, I’m not picky)• Dislikes: not having donuts
Quick Demo
• Highlight/color code rows/column• Resize rows/columns• Hide/unhide column(s) and/or row(s)
Basic commands cont'd• Wrap text (resize cells to fit/show all words)
Select the entire spreadsheet by left clicking on the square where the tabs and columns meet
Right click > “Format cells” > The box that pops up will have several tabs at the top click on “alignment”
In the middle of the box, you'll see an option to “wrap text” > Click the box next to “wrap text” > Click “OK”
Merge cells (turned several cells into one large cell) Select all the cells you wish to merge Right click > “Format cells” > The box that pops up
will have several tabs at the top click on “alignment” In the middle of the box, you'll see an option to
“merge cells” > Click the box next to “merge cells” > Click “OK”
Even More Basic Commands• Sort & Filter Arranges all your data in numerical or alphabetical
order (ascending or descending). Highlight the column you wish to sort or filter > click
on “Sort & Filter” > A box will drop-down and ask you how you would like to sort > Click on your choice
• Find & Select Enables you to find a single piece of data out of the
entire workbook. Very useful on large data sets with a lot of variables
Click on “Find & Select” at the top right > click on ”find” > In the box that appears click on “Options” > next to “Within” change the search parameter to “workbook” > Type in your wayward variable and click ”Find All”.
An Advanced Basic Command(?)
• Hyperlinking – Perhaps, you don’t want to go through all of your
various directories to find a test document Or maybe, you need a quick reference to source
material on the web In each of these cases, hyperlinking is a handy tool Right-Click on the cell that you wish to hyperlink ->
click on hyperlink at the bottom -> find the file or the webpage that you would like to link to, highlight it -> Click ‘OK’Caution: when hyperlinking a file, the file has to be in the same directory as one you assigned the hyperlink. In the case of hyperlinking a webpage, this will only work if your computer has an active Internet connection
Advanced Commands• Autosum defaults to adding the selected
columns• The drop down menu gives you six more
options Sum (default) Average Count Numbers (Number of cells that contain a value
in a selection) Min (lowest value in a selection) Max (highest value in a selection) More Functions
Measures of Variation
• We tend to get hung up on the test score average.
• It’s also useful to know how “alike” the test scores are.
• The most effective MoVs for classroom data are the range and standard deviation
Range• Select the dataset > click "Sort & Filter" in top
right corner > "Sort A to Z“• Now, your dataset will be in numerical order,
scroll to the bottom - note the "bottom-est" value > scroll up, note the "top-est" value
• Subtract the lowest value ("top-est") from the highest value ("bottom-est"); the result = the range
• The range should always be larger than the standard deviation. It's more telling also - 10 points of range will represent a letter grade
The Normal/Bell Curve
• Standard deviation (STDEV) is a fancy way to show how "alike" your data is/are
• Basically, it shows the "average difference" of your data
• A higher value indicates a bigger spread of values (in the case of test scores, this might mean some students didn't fully absorb your lesson *gasp* - that you need to appeal to more modalities when preparing your lessons (visual, aural, etc.))
Standard Deviation
Manual Input• This may come as a shock, but Microsoft didn't
automate every function• One example is range (maximum value minus
minimum value) which is a simpler version of SD
• To calculate range on a given set of values select the...err...selection (row - start with the first blank cell to the right of your selection, columns - start at the first blank cell directly below the selection)
• In the blank cell adjacent to the selection type: SUM(MAX(*COORDINATES*)- MIN (*COOR -DINATES*)) . where *COORDINATES* is the row/ column of your selection.
• Again, higher values means something's amiss
Insert
Wrap Text and Merge Cells
More Basic Commands
Sum
STDEV
Manual Input
A Simpler Method
Insert
WT-MC
SD
Behavior
Assessment
Handout