Educational Technology & Design Page 1 of 3 Spreadsheet Outline Spring 2010 Spreadsheet Outline I) Introduction & Examples 1) Introduction PPT 2) Show examples from previous semesters II) Getting Started 1) Open MS Excel and Save new spreadsheet (workbook) 2) Go to View -> Normal III) Entering Data (Labels and Values) 1) Confirm that the Formula bar is visible (View menu > Formula Bar) 2) Display Standard and Formatting toolbars (drag to arrange as necessary) 3) Entering data a) Enter headings (labels) in row 1 (including Total Points & Percent) b) Enter 15 student names (Last Name, First Name) in column A, row 2-16 c) Sorting Names ⇒ Select range of cells first to avoid sorting the entire worksheet ⇒ Click Sort symbol on toolbar or use Data menu > Sort d) Adjusting column widths & row heights ⇒ Drag vertical line separating columns ⇒ Drag horizontal lines separating rows ⇒ Double-click these lines to auto fit column width or row height e) Leave row 17 blank, enter Points Possible in row 18 f) Enter Average label in row 19 g) Enter assignment scores for each student (don’t exceed points possible and save periodically) IV) Entering Formulas 1) Identify the need to add the grades in row 2 2) Demonstrate entering a mathematical formula in cell H2: =B2+C2+D2+E2+F2+G2 a) If an error is made when entering formulas, press the ESCAPE key 3) Explain how this can be shortened using a Function, =Sum (B2:G2) by inserting =; selecting SUM formula; selecting cells to be summed; hit return. Entering Functions 4) Review the types of common mathematical functions used in a spreadsheet. a) Sum, Average, Count, etc. 5) Calculate data as required by assignment using functions & formulas: a) Calculating the total points per student ⇒ Use function in cell H2 =Sum(B2:G2) ⇒ Use the Autosum feature in cell H3 (a) Click once to select cell H3 (b) Click the Autosum button. Press Enter to accept the formula. b) Fill the function down the H column ⇒ Select cell H3. Move to lower right of cell until cursor changes. Click & drag down to cell H16. Notice the relative copy of the function. 6) Save Included in this outline… • Introduction • Getting started • Enter data (labels & values) • Inserting functions & formulas • Formatting options • Creating charts & graphsEducational Technology & Design Page 2 of 3 Spreadsheet Outline Spring 2010 7) Calculate the total points possible a) Highlight cells B18 through G18. b) Click the Autosum button on the toolbar. Press Enter to accept the formula. 8) Calculate the average in each column a) Select Cell B19. Use the Average function: ⇒ Insert menu > Function… > Average ⇒ Drag the function window to right, if necessary, to reveal column B ⇒ Highlight the cells to be included in the average (B2 thru B16) ⇒ Click the OK button b) Fill this formula to the right thru column H 9) Save 10) Calculate the percentage in each row a) Discuss formula for calculating a % value (H2 divided by H18) ⇒ Total Score will change with each student ⇒ Total Possible will ALWAYS remain the same. b) Demonstrate problem using Fill Down c) Select Cell I-2 and enter =H2/$H$18 (the dollar signs indicate that the reference to H18 is always the same) d) Fill Down e) Select cell I3 to examine the formula ⇒ Notice how H2 changed to H3 ⇒ $H$18 did NOT change f) Format cells as percentages ⇒ Select cells I-2 to I-16. ⇒ Click the % symbol in the Formatting toolbar to format cells as % ⇒ Click on the Decrease Decimal button in the Formatting toolbar to reduce the number of decimals. ⇒ Using this method, format the percentages in column H and the averages in row 19 to one decimal place. 11) Save V) Formatting Options 1) Format column headings a) Highlight cells (labels) in row 1 b) Use the Formatting toolbar to apply bold style c) Explore Fill Color and Font Color options as well d) Alternating fill color ⇒ Select alternating rows using the Control key ⇒ Change the Fill Color (use colors to enhance legibility!) 2) Changing font face and size a) Formatting toolbar 3) Changing column widths and row heights a) Drag or double-click line separating columns/rows b) Highlight a series of rows or columns; move one line and all remaining will be the same height and width 4) Changing the contents of a cellEducational Technology & Design Page 3 of 3 Spreadsheet Outline Spring 2010 a) Double-click a cell to make changes directly in the cell. b) Single-click a cell and make changes in the formula bar. c) Changing the text orientation (so labels in row 1 consume less space) ⇒ Select row 1 (all cells in row 1) ⇒ Format menu > Cells > Alignment tab VI) Creating a Chart Visually representing the data in the workbook 1) Selecting data range: a) Select all cells between columns A–G in rows 18–19 b) Using the Control key (PC) or Command  key (Mac) highlight cells A1–G1 2) Inserting chart Steps for Macintosh platform (Skip if PC/Windows) a) Insert menu > Chart ⇒ Type of Chart: Column > Cluster Column b) Move the chart so that it is not overlapping the gradebook data c) Customizing chart in the Formatting Palette ⇒ From the titles pull down menu choose ⇒ Chart Title and name it Student performance ⇒ Horizontal (Category) Axis - name Assignments ⇒ Vertical (Value) Axis – name Points d) Explore other chart options e) Chart Data manipulation ⇒ Chart menu > Source data; Chart options… Steps for Windows/ PC platform f) Insert menu ⇒ Column 2-D Column > Clustered Column g) Customizing Chart (chart must be selected to see chart tools) ⇒ Select Layout tab from the Chart Tools menu (a) Click Chart Title icon: name it Student performance (b) Click Axis Titles icon: (i) name the Primary Horizontal Axis Assignments (ii) name the Primary Vertical Axis Points 3) Chart Formatting a) Changing the chart colors ⇒ Double-click on any part of the chart, including text boxes ⇒ Select desired color & other options ⇒ Can use the formatting toolbar as well ⇒ Single click chart elements, then use the formatting