Excel Basics - Radford



Table of Contents

basics

Definitions: Menu 2

Reference a single cell 2

Reference a range of cells 2

Rename worksheet 2

formatting worksheets

column headings 2

wrapPing text iN cells 3

Center text in headings 3

Resize Columns 4

Setup Page 4

Insert Rows or Columns 4

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 13

Pearson Formula 13

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

[pic]

Rename worksheet:

[pic]

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

[pic]

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

[pic]

[pic]

Center text in headings:

Resize columns:

Setup page:

[pic]

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

2. Select Format>Cells

3. Select the Number tab

4. Select Percentage

5. Click OK

Create template: Save this page as a template before 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)

[pic]

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 |

|10 |5 |8 |7 |35 |7 |81 |

|11 |6 |3 |9 |7 |  |  |

|12 |11 |5 |63 |5 |

|12 |=A10+A11 |=B10-B11 |=C10*C11 |=D10/D11 |=E10*E10 |=SQRT(F10) |

| |A |B |C |D | | |

|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 |14 |Standard Deviation |

| | |formula, i.e. "A") | | |

|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)

[pic]

-----------------------

The cell selected in this picture is referred to as A1

[pic]

The cells selected in this picture are referred to as A1:A3

The cells selected in this picture are referred to as A1:C2

1. At bottom of page double click the Sheet Name (in this case Sheet1) to highlight it.

2. Type new name. (Class & Section, for example)

1. Type headings in the cells at the top of the columns.

2. Tab to get to next cell. (Do not worry if all your text does not show at this point)

3. Do not put more than one subject heading per cell.

4. Make sure to include headings for Course, Section, and StudentID

1. Click on row 1 (This selects the whole row)

2. Click Format > Cells (On menu at top)

3. Select Alignment tab

4. Click Wrap text box

5. Click OK

[pic]

[pic]

1. Put mouse pointer on vertical line between columns where you want to resize until it becomes a double arrow

2. Drag column edge to make larger or smaller to hold text.

[pic]

[pic]

With row still selected (see #1, previous instruction), click on the button indicated on your toolbar.

(If you can’t see it, click on

View >Toolbars >Formatting on the menu)

1. On menu go to File > Page Setup

2. Click Margins tab

3. Change Left and Right to .5

4. Click Page tab and select Landscape

5. Click OK

[pic]

To format another worksheet the same way,

3. Click the blank gray square. Right Click

4. Copy

[pic]

[pic]

[pic]

Basic steps for each formula:

1. Select cell in which you want the answer to appear - Type in your formula by performing Steps 2 thru 10

2. =

3. your formula name (AVERAGE, MAX, MIN, SUM, or STDEV)

4. (

5. Select the cells you are computing by dragging mouse [pic] from upper right to lower left cell

6. )

7. Hit “Enter” key Note: Average and Standard Deviation will show #DIV/0! until data is put in cells

8. Select answer cell again

9. Move mouse until pointer becomes a

10. Drag mouse horizontally under the columns of data to put formula in adjacent cells to calculate each column

[pic]

1. Make a column at the far left of your data to hold the breakpoints of your scoring levels.

For example, if your scoring range is 1-2 (failing) , 3-4 (below average), 5-6 (average), 7-8 (above average), and 9-10 (excellent) you would put in your column the numbers 2, 4, 6, 8, 10.

2. Go to Tools > Data Analysis

3. Select Histogram

4. Click OK

[pic]

[pic]

[pic]

[pic]

[pic]

5. Click in the Input Range box

6. Select the cells containing the data by clicking on the upper left corner cell and dragging the mouse pointer to the lower right corner cell

7. The cell range you chose will

appear in the Input Range box

8. Click on Bin range box

9. Select the cells where you put

the scoring levels by clicking the top data cell (not the column heading) and dragging the mouse to the last scoring cell.

The cell range you chose will

appear in the Bin box

[pic]

[pic]

10. Click the Output Range circle

11. Click the Output Range box

12. Click the box next to the word Scoring

The cell reference will appear in the

Output Range box

(in this example, $N$1)

13. Click OK.

The Bin (your scoring range breakpoints) and the Frequency of the scores in each level will appear on the worksheet beginning in the cell you selected for the Output Range box.

[pic]

[pic]

[pic]

1. Rows:Select the row below where you want to

insert the new row by clicking the row number

Columns: Select the column past the place you

want the new column inserted by clicking the

column letter

2. Click Insert on menu

3. Click Rows or Columns on the menu

[pic]

[pic]

[pic]

To Do Cusom Filtering

1. Click the first down arrow

2. Select phrase that represents the sort of information you want

3. Click the second down arrow

4. Select the criteria from the information in that column

1. On menu click Data > Filter > AutoFilter

2. A drop down arrow will appear next to each column heading

3. Click the arrow next to the criteria you want to filter

4. Select a criterion from the list or click (Custom…)

5. The list will show just the rows (students) you want according to the filter you applied

6. To take the filter off, click Data > Filter >AutoFilter again

[pic]

[pic]

[pic]

[pic]

1. Click the blank gray square on the worksheet that has all your formulas to select the whole sheet

2. Right Click

3. Click Copy

4. Select a Blank Worksheet (in this case, Sheet2)

5. Click the same gray square on the new worksheet

6. Right click

7. Click Paste

[pic]

5. Type a comma (, )

6. Select the second column of scores

7. Type a parenthesis )

8. Hit “Enter” key on keyboard.

The answer will appear in the cell you selected.

[pic]

1. Create two columns with the Instructors’ scores; corresponding scores should appear on

the same row.

2. Select cell where you want to see the answer

(degree of correspondence)

3. Type =PEARSON(

4. Select first column of scores (framed in blue in the illustration)

[pic]

1. Go to File >New

2. Click General Templates

3. Select the template that you saved in the previous step.

[pic]

Format a new worksheet from your template:

1. Go to File >Save As

2. Enter a File name

3. Click on the down arrow and select Templates (*.xlt)

4. Click Save

5. The Templates folder of Excel is where it will automatically be saved to make it available as a pre-setup page for future use. You can also choose to save it in another file if you wish.

[pic]

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download