1

Excel Basics: Step by Step

Table of Contents

basics

Definitions: Menu

Reference a single cell

Reference a range of cells

Rename worksheet

formatting worksheets

column headings

wrapPing text iN cells

Center text in headings

Resize Columns

Setup Page

Insert Rows or Columns

Format Cell Data As Percent………………………………………………………………………………………5

Templates and Worksheets

Create Template...... 6

Format another worksheet in the same workbook...... 7

Insert worksheet...... 7

Formulas and Filters

Inserting formulas...... 8

Easy Formulas for data analysis...... 9

Applying a filter

Pearson Formula

Definitions: Menu = the selection list at the top (File, Edit, etc.)

Cells=the squares that make up the body of the worksheet

Rows = the horizontal cells

Columns = the vertical cells

Fields =column headings

Reference a single cell: Letter of column + Number of row

Reference a range of cells: Cell reference:Cell reference

Rename worksheet:

Column headings:(note: these are your Course Ids, Student Ids, components or standards)

Wrapping text in cells(a space-saving measure):

Center text in headings:

Resize columns:

Setup page:

Insert rows or columns:(If you need more room for your data)

Format cell data as percent:

  1. Select the cell(s) you want to have the percent format
  1. Select Format>Cells

3. Select the Number tab

4. Select Percentage

5. Click OK
Create template:Save this page as a templatebefore you enter student data

so you won’t have to do it again! (It’s like a cookie cutter)

Format another worksheet in the same workbook

(You can put all your classes in the same workbook on different sheets)

Insert worksheet:

Click Insert > Worksheet on the menu at the top

Inserting formulas:(This can be done in your template, just leave rows below your column

headings for your anticipated number of students-one row per student plus a blank row at the bottom)

Easy Formulas for / / Formulas go in the cell where you want the results to appear.
They will show up here at the top when you type it in the cell.
Data Analysis!
The Problems
A / B / C / D / E / F
Addition / Subtraction / Multiplication / Division / Number 2 / Square Root
10 / 5 / 8 / 7 / 35 / 7 / 81
11 / 6 / 3 / 9 / 7
12 / 11 / 5 / 63 / 5 / 49 / 9
The Formulas (for above)
12 / =A10+A11 / =B10-B11 / =C10*C11 / =D10/D11 / =E10*E10 / =SQRT(F10)
A / B / C / D
20 / 34 / 343
21 / 56 / 453
22 / 23 / Average Formula / 543 / Sum Formula
23 / 37.6666667 / =AVERAGE(A20:A22) / 1339 / =SUM(D20:D22)
24
25 / 30 / 36
26 / 40 / 79
27 / 35 / Count Formula / 23
28 / 67 / (How many cells have a number in them) / 42
29 / 98 / 79 / MAXFormula=MAX(D25:D29)
30 / 5 / =COUNT(A25:A29) / 23 / MIN Formula=MIN(D25:D29)
31
32 / A / 12
33 / B / 23
34 / A / COUNTIF Formula / 15
35 / C / (How many cells have the text specified in the formula, i.e. "A") / 14 / Standard Deviation
36 / A / 27 / Formula
37 / 3 / =COUNTIF(A32:A36,"A") / 6.457553716 / =STDEV(D32:D36)

Frequency distribution(How many students were in each category of performance):

Step1

Frequency distribution: Step 2

Frequency distribution: Step 3

Frequency distribution: Step 4

Applying a filter(show some students by criteria you choose):

Pearson formula(Comparing two scoring assessments on the same material) :

=PEARSON(range1, range2)