Excel for Teachers Presented by Ruthanne Munger RHS
Excel for Teachers Presented by Ruthanne Munger RHS Library Computer Lab Friday, 1/6/17 – 1: 00
Introduction • Microsoft Excel can make your classroom record-keeping and data analysis a breeze. • Ruthanne Munger rbolling@rcs. k 12. in. us Twitter: @learninglady Teachers Pay Teachers: The Form Queen
Benefits • Consolidate data from several different sources to see the “Big Picture”. • See # or % passing and growth data, not just averages. • Color code to make trends and changes easy to see. • Easily use data to determine whole group, small group, and individual needs.
Today, you’ll learn: 1. Excel Basics ▫ Basic navigation, terminology, and data entry 2. Visual Data ▫ Setting up consolidated data ▫ Formulas ▫ Conditional Formatting
1. Excel Basics Home Tab • Font Settings ▫ Style and Size ▫ Fill and Text Color ▫ Borders • Alignment Settings ▫ ▫ Justification Orientation Wrap text Merge & Center • Number Settings ▫ Number Types ▫ Decimal Places Page Layout Tab • Margins • Orientation (landscape or portrait)
Terminology • Rows are represented by numbers along the side of the sheet. ▫ Resize by “grabbing” or doubleclicking bottom of row #. ▫ Insert/delete (Cells settings or highlight & right click) • Columns are represented by letters across the top of the sheet. ▫ Resize by “grabbing” or double clicking right side of column letter. ▫ Insert/delete (Cells settings or highlight & right click) • Cells are named for the column letter and row number that intersect to make it. To enter data, click in cell & type. Double click a cell to edit.
Task #1 1. Create a new, blank workbook. 2. Add headings in Row 1 for “Student”, “Score 1”, “Score 2”, and “Growth”. 3. Center headings in each column. 4. Add student names and scores 1 and 2 for at 7 students. * Use “Enter” to move down * “Tab” or Arrow keys to move left/right. 5. Add bolded, right-justified headings for “Average”, “# Passing”, and “% Passing” in column 1 below student names. 6. Make borders so grid lines will print. 7. Play with formatting and alignment.
2. Visual Data • • Formulas Sort & Filter Conditional Formatting Simple Graphs Overall (446) 510 500 490 480 470 460 450 440 430 420 410 Student 1 Student 2 Student 3 Student 4 Student 5 Student 6
Formulas & Functions • Formulas are equations that perform calculations in your spreadsheet. Formulas in a cell ALWAYS begin with an equals sign (=) followed by a function (what calculation you want) and input (which cells to include) in parentheses. Example: =AVERAGE(B 2: B 8) or =AVERAGE(B 2, B 3, B 4, B 5, B 6, B 7, B 8) • See available functions by clicking on or “fx” ▫ I use: SUM COUNTA AVERAGE COUNTIF • Click in the cell where you want the formula, type “=“, add your function (type or click) and your input (type cell range or drag through cells).
Task #2 Add formulas to fill in the rest of your data: 1. Cell D 2: =C 2 -B 2 2. Click in D 2. Grab square in bottom right corner and drag down to D 8. 3. Cell B 9: =AVERAGE(B 2: B 8) * Adjust decimal point with 4. Cell B 10: =COUNTIF(B 2: B 8, ”>79”) 5. Cell B 11: =B 10/COUNTA(B 2: B 8) * Change to percent with 6. Highlight B 9, 10, & 11. Grab square in bottom right corner and drag into Column C. Drag “Average” into Column D.
Conditional Formatting: Icons Add color or symbolic icons: 1. Highlight cells. 2. Click on “Conditional Formatting” and choose “Icon Sets” 3. Choose the icons you prefer. 4. To edit parameters, cick on “Conditional Formatting” again and choose “Manage Rules”. 5. Double click on Icon Set or choose “Edit Rule” and set cut scores. 6. Click “Apply” and “Okay”.
Conditional Formatting: Color Fill cells with colors: ▫ Highlight cells. ▫ Click on “Conditional Formatting” and choose “New Rule”. ▫ Choose the “Format only cells that contain” rule type. ▫ Set parameters and click “Format…” to choose text or fill color. ▫ Click “OK” to apply new rule.
Task #3 Add conditional formatting to color code your data: 1. Highlight cells B 2 through C 8. 2. Click on “Conditional Formatting” and choose “Icon Sets”. 3. Choose any set of 3 icons. 4. Select “Conditional Formatting” again and choose “Manage Rules”. 5. Double click on the “Icon Set” row. Set the following description: Icon Green Yellow 6. Value >= 79 >= 59 Type Number Click “OK”, “Apply”, and “OK”
Simple Graphs To add simple graphs: 1. Select the data you’d like to graph – including column headings. 2. Click on the “Insert” tab and choose the type of chart you’d like. Select “Recommended Charts” to preview different options. 3. Choose the chart you like (Column, Bar, Line, Pie, etc. ) and click “OK”. 4. The chart will appear on your sheet. 5. Grab and move or resize (when squares in corners are visible), copy, paste, etc. 6. There are many options for editing. Explore.
• Congratulations! Excel can do a whole lot more, but you have what you need to make your classroom data easy to compare and analyze. • Recommendation: Use it or lose it! • Questions? ▫ Click the ? on any Office screen and search for help. OR ▫ Check out www. customguide. com/cheat-sheets/ OR ▫ Email rbolling@rcs. k 12. in. us
- Slides: 15