Module 4: Software Proficiency

SPREADSHEET FLUENCY USING EXCEL

• Introduction to Excel.
• Excel Tutorial
  1. Basic Spreadsheet Operations
  2. Working with Formulas
  3. Using Functions
  4. Working with Charts
  5. Advanced Excel and Practice
• Module 5: Using Spreadsheets to Support Teaching/Learning
  • Supporting Teacher Personal Productivity
  • Supporting Classroom Instruction
  • Putting It Into Practice
• Jump to another topic in module 4.

Introduction to Microsoft Excel

	The following are the revised Excel resources. Download them as needed for use throughout class.
	Basics of Excel ExcelBasics.xlsx PracticeExcelBasics.xlsx Intermedia Excel Techniques ExcelIntermediate.xlsx PracticeIntermediate.xlsx Creating and Interpreting Charts CreatingCharts.xlsx

EXCEL TUTORIAL

Download BASICEXCEL.XLSX to use throughout this section.

Understanding the screen

• columns/rows
• column/row headings
• name box
• formula bar

Navigating around screen

• keys
  • arrow keys (Move one cell at a time)
  • page-up/down (Move up/down by one screen.)
  • control-home/end (Move to beginning/end of a spreadsheet.)
  • end-arrow (Move to the far ends of a spreadsheet.)
• on-screen buttons
  • scroll bars
  • sheet tabs (Move to additional spreadsheets within the workbook/file.)

Entering/editing/deleting data

• How to enter data into a cell.
  1. Select a cell (click the cell).
  2. Type data.
  3. Accept the change by doing any one of the following:
     • Press enter.
     • Click the green check mark in the formula bar.
     • Press an arrow key.
     • Select another cell.

• How to edit cell contents.
  1. Select a cell by clicking it.
  2. Enter the edit mode by doing any one of the following:
     • Press F2.
     • Click into the formula bar.
     • Double-click the cell.
  3. Edit cell contents in formula bar.
  4. Accept the changes (press enter, click green check mark in the formula bar, or select another cell) OR press the escape button to not accept the changes.

• How to delete cell contents.
  1. Select a cell by clicking it.
  2. Press the delete key.

Using data fill

• How to create a month or day series.
  1. Enter the beginning of a series into one cell (e.g., Jan, January, Mon, Monday).
  2. Place cursor over the fill handle of the cell, the lower right-hand corner of the cell. The cursor will now turn into a black crosshair.
  3. Drag the fill handle down or across.
  4. Excel fills your cell selection with the series.
• How to create a numbers series.
  1. Enter two cells of the sequence (e.g., 1, 2).
  2. Grab the fill handle and drag.
• How to create a customized series.
  1. Enter two cells of the sequence (e.g., test 1, test 2).
  2. Grab the fill handle and drag.

Adjusting column (row) sizes

• Using the mouse
  1. Place the cursor on the border between two column (row) headings (e.g., between A and B). The cursor now turns into a bi-directional arrow.
  2. Press and hold the left mouse button.
  3. While holding the button, drag the column (row) to the desired size.
• Using the menus
  1. Select a cell in the column/row to be affected OR highlight the column/row by clicking its heading.
  2. Select format--column--width OR format--row--height from the menus.
  3. Enter the width (or height) in pixels.
• Sizing to existing data
  1. Double-click on the border between two column (row) headings.
  2. Excel now automatically adjusts width (or height) to fit longest (tallest) data item in the column (row).

Inserting/deleting rows/columns
(In Excel 2007: right-click to access these options.)

• To insert rows/columns:
  1. Place cursor in a cell. (Added column will be to the right of the cursor position, added row will be underneath the cursor position.)
  2. Select insert--rows/columns from the menus.
• To delete rows/columns:
  1. Select a row/column by clicking on its heading (e.g., column A or row 7).
  2. Select edit--delete from the menus OR press the delete key.

Formatting cells
(In Excel 2007: right-click to access these options.)

1. Select cells to be formatted by clicking and dragging.
2. Format the cells through one of the following methods:
   • Choose from the B, I, U, left, center, right buttons on the toolbar.
3. Select format--cells from the menus. Select from the following options:
   • number tab
   • alignment tab
   • font tab
   • border tab (Format outside border of selected cells.)
   • pattern tab (Format inside color and pattern of selected cells.)
   • protection tab (Specify cells whose contents will not be able to be modified when worksheet protection is enabled.)

Creating a title on a sheet

1. Type the text for the title into a cell along the left border of the sheet (e.g., A1).
2. Highlight the number of cells you would like the title centered across.
3. Press the merge and center button on the toolbars.
   (In Excel 2007: select the home tab, then select merge and center from the alignment group.)

Working with multiple sheets in a workbook Creating a new sheet within a workbook

• Navigate between sheets
  1. Press the sheet tab you desire on the lower left of the Excel screen.
• Rename an existing sheet
  1. Right-click over a sheet tab.
  2. Select rename from the pop-up menu.
• Create a new sheet
  1. Right-click over a sheet tab.
  2. Select insert--worksheet from the pop-up menu.

Download WORKSHOPS.XLSX. Use your skills to format this spreadsheet.

Working with Formulas
(Tutorial Part 2)

Download CREATINGFORMULAS.XLSX to use throughout this section.

Referencing a cell (the simplest formula)

1. Click a cell where the formula will be entered.
2. To start any formula, type =.
3. Enter the cell reference(s) in one of the following ways:
   • Type the cell name.
     Example: =B4
   • Hit an arrow key.
   • Select a cell with the mouse.
4. Finish by hitting enter.

Creating formulas

1. Click a cell where the formula will be entered.
2. To start any formula, type =.
3. Create the formula using one of the following methods:
   • Type the cell references separated by mathematical operators.
     Example: =B5+C5+C5
   • Use the arrow keys to navigate to a cell. Add a mathematical operator. Repeat as needed.
   • Use the mouse to select a cell. Add a mathematical operator. Repeat as needed.
4. Finish by hitting enter. OR press ESC to cancel.

Understanding mathematical operators on the PC

• List of common mathematical operators:
  • addition: +
  • subtraction: -
  • multiplication: *
  • division: /
  • parentheses: ( )
• Order of operations:
  • You will recall that regardless of what order they listed in a formula, mathematical operations are performed in MDAS/PEMDAS order (parentheses, exponents, multiplication, division, addition, and then subtraction). If you are adding/subtracting AND multiply/dividing in the same formula, use parentheses to avoid error! For example, if you want to take the average of several cells (add them up and divide by number of scores), you would want to do the addition first, and then divide by number.
    • Example:
      B2+B3+B4/3 will actually take B4/3 and then add B2 and B3 to it.
      The proper formula should be (B2+B3+B4)/3

Cut (copy) and paste

1. Highlight cells to be cut (copied).
2. Perform a cut (copy) as usual.
3. Highlight the first cell where you want to paste.
4. Hit enter (OR select paste if you want to paste multiple times).

"Smart" copying with formulas

1. Highlight the cell(s) containing a formula to be copied.
2. Perform the copy and paste operation using one of the following methods:
   1. standard copy and paste procedure
      • Perform a copy as usual.
      • Highlight a range of cells where you desire the formula(s) to be pasted.
      • Hit enter (OR select paste if you want to paste multiple times).
   2. use the fill handle
      • Drag the fill handle of the cell containing the formula.
   • Note: Notice that, as you paste a formula in multiple rows (columns), Excel adjusts the cell references to reflect the appropriate row/column.

Creating an absolute cell reference
Sometimes when copying and pasting (smart copying) formulas (or functions), you do not want Excel to automatically change the cell references to reflect different columns/rows during the paste. In this case you need to create an absolute cell reference.

1. After creating a formula, press F4 to toggle between variations of $'s. (A $ in front of the column and a $ in front of the row in a cell reference means that pasting will not adjust this cell's name. Example: $C$10)

Download FORMULASPRACTICE.XLSX to test your skills and provide you with practice in using formulas.

Using Functions
(Tutorial Part 3)

Download CREATINGFUNCTIONS.XLSX to use in the next section.

Utilizing functions

1. Background information
   • Syntax for a function
     • Start with the = sign, followed by a function name and a cell range.
     • = [function name] [cell range]
   • Commonly used functions
     • sum, average, max, min, count
   • Specifying longer cell ranges
     • B2+B3+B4+B5 can be written as B2:B5
     • B2:B5 and C2:C5 can be written as B2:C5

2. How to create a function
   1. Click a cell where the function will be entered.
   2. Enter a function using any of the following methods:
      • Type a common function directly into the cell.
      • Select insert--function from the menus.
      • Press the paste function button on the toolbar.
        (In Excel 2007: press paste function button next to the formula bar.)

3. Mix and match
   • You can "mix and match" formulas and functions. For example:
     • =sum(B2:B5)/3
     • =sum(B2:B10)/count(B2:B10)

FUNCTIONSPRACTICE.XLSX: Test your skills and practice using functions.

Avoiding a circular reference

One error that a spreadsheet creator has to avoid is a circular reference. A circular reference refers to a formula that refers to the cell in which the formula resides within the formula. In almost all cases, this is an error. To correct the error, note the cell in which the formula resides; then, edit the formula (click into the formula bar OR press F2) and delete the name of the cell in which the formula resides. Example:
      cell A1: 5
      cell B1: 10
      cell C1: =A1+B1+C1

ATTENDANCE.XLS: Use functions to complete this basic spreadsheet, and locate and correct a circular reference. (When this spreadsheet opens, you will get an error message due to the existence of a circular reference. Click "cancel" in response to the error message when this spreadsheet opens.)

Working with Charts
(Tutorial Part 4)

Download CREATINGCHARTS.XLSX to use in the next section.

Using the chart wizard
(In Excel 2007: select the insert tab, then select various tools from the charts group. The chart will be created immediately.)

1. Select all cells to be included in the chart.
   • Trick: To select non-adjacent columns/rows:
     1. Highlight the first column/row as usual.
     2. Press and hold the control key as you highlight non-adjacent columns/rows.

2. Click "chart wizard" button from the toolbar OR select insert--chart from the menus.

3. Complete all of the steps in the chart wizard dialogues, as follows:
   1. Step 1: In chart type, click the type of chart you desire. In chart sub-type, click the variation of chart desired. Press the view sample button to preview the chart.
   2. Step 2:
      • data range tab (If the data are not displayed properly in the chart, check the rows or columns option to change the way data are displayed.)
      • series tab (Change the "name" or legend of a data series, the "value" or region of cells that make up a data series, and x-axis labels as needed.)
   3. Step 3: Type any titles you want to add.
      • titles tab (Set titles for chart, X axis, Y axis.)
      • axes tab (Turn X/Y axes on/off and set basic options.)
      • gridlines tab (Set options for displaying gridlines on axes.)
      • legend tab (Turn chart legend on/off and set options.)
      • data labels (Permits actual value of data to be listed in the chart. Turn on/off and set options.)
      • data table (Lists actual data under the bars of a chart. Turn on/off.)
   4. Step 4: Select "as new sheet" or "as object in sheet" (default) option.
      • new sheet (This option creates a new sheet within workbook, and places the chart in that sheet. Ideal if chart is to be printed out as a separate page away from data.)
      • object in sheet (This option places the chart as a graphic in the current sheet. It can then be resized/moved as a usual graphic. Ideal if chart is to be printed on the same page with data.)
        • Trick: When you resize a chart, hold the control key down to proportionally resize without distorting.

Editing and formatting charts after creation
(In Excel 2007: either right-click over any particular section of the chart to edit it OR click the chart to highlight it, and select from the extra chart tools (design, layout, or format tabs) that appear on the ribbon when the chart is selected.)

1. Click chart to highlight it.
2. Edit/format the chart by using one of the following methods:
   • Use the floating chart toolbar.
   • Right-click OR double-click over a section of the chart you wish to change. Select options from the pop-up that appears. Note that different options will appear depending which part of the chart you right-click.
     • Note: Preview your chart carefully. Many times you have to format one of the axes on a chart in order to view all of the information. In those cases, select format--axis--font AND format--axis--alignment to make the axis information readable.

Advanced Techniques and Practice
(Tutorial Part 5)

ADVANCEDEXCEL.XLSX: Get introduced to some advanced Excel techniques that can be very useful for teachers.

GRADEBOOK.XLSX: Practice all of the skills learned in Excel to complete this electronic gradebook application.

Module 5: Teaching with Technology

Using Spreadsheets
to Support Teaching/Learning

• Increases time efficiency over using a calculator.
• Allows preparation of a template complete with formulas into which data can be entered without having to rethink the computations.
• Facilitates changing the focus from computation to higher order concepts.
• Allows creation of charts and graphs from data.
• Permits answering of "what if" questions.
• Facilitates creation of products of high quality and professional appearance.

Some examples of activities:

• electronic grade book
• grade chart for posting
• budget/checkbook for classroom or school club
• attendance records
• performance assessment checklists

Supporting Classroom Instruction

Some examples of activities:

• Demonstrate any concept in which numbers can be derived.
• Create a timeline.
• Create a chart/graph.
• Record and analyze data from classroom activities/projects or experiments.

Putting It Into Practice

In class, let's download the following files to practice Excel skills. At the same time, learn how a teacher can use spreadhseets to teach content in the classroom.

Population.xlsx: Create a chart to understand the populations of several countries. Savings.xlsx: Complete these spreadsheets to demonstrate to students skills about saving money. Time Management.xlsx: Complete these spreadsheets to demonstrate to students skills about time management. Various.xlsx: Complete these spreadsheets to demonstrate to see how a teacher could use spreadsheets to teach content to students. Credit Card and Loan.xlsx: Complete these spreadsheets to learn about credit cards and loans. Healthy Eating.xlsx: Complete these spreadsheets to learn about nutrition and healthy eating.

JUMP TO A TOPIC IN MODULE 4:

Exploring office productivity suites.

Presentation software fluency using Word.

Presentation software fluency using PowerPoint.

Spreadsheet fluency using Excel.

Module 1.

Topics.

TED 121

Dr. Steve