CIOS Template - Cysewski



Lesson 1: Basic Worksheet Skills

Mini-Lecture

This lesson is to get you using Excel. One of the key purposes of Excel is to help people think. The checkbook and the loan calculator can provide tools to make decisions and to analyze consequences. By entering different amounts, interest rates, and loan terms, you can see the consequences of your decisions. Sometimes consequences can be depressing! The exercises covering Editing and Absolute and Relative Reference are to prepare for future Excel lessons and to illustrate areas of confusion.

Remember the distinction between concepts and exercises. The concepts are bulleted and are for information. The exercises are numbered and should be done in numerical order. When all the numbered steps are completed the lesson is done. Do not skip steps. If you are not sure how to complete an exercise follow the numbered steps closely and also look at the illustrations. Do not submit a lesson that is not complete, ask questions and keep trying until the lesson is finished. Remember I expect that all numbered steps will be completed. From my experience it is necessary to have hands-on experience to truly learn a computer application. These lessons are to provide the hands-on experience to learn how to use Excel.

Lesson 1 Lesson Outline

Lesson Concepts

• Spreadsheet Terms

• What is a spreadsheet?

• Designing Spreadsheets

• What is Microsoft Excel?

• How does Microsoft Excel Work

• Cells

• Cell Content

o Text

o Values

o Formulas

▪ Functions

▪ Ranges

• Selection

• Formula and Value View

Confusions and Concepts

• Selecting, Drag and Drop, and Auto fill

• Cut, Copy, and Paste vs. Insert and Delete

• Editing a Cell

• Absolute and Relative Reference

• Cell Layers

o Formula

o Value

o Format

Excel Exercises

• Selecting Ranges of Cells

• Selecting Columns and Rows

• Selecting Non-Contiguous ranges

• Selecting, Drag and Drop, and Autofill

• Basic Formula Practice

• Five Formula Exercise

• Creating Formulas using different ranges

• Grade Spreadsheet

• Checkbook Exercise

• Loan Compare Example

• Editing a Cell Problem

• Absolute and Relative Reference

Excel Concepts

• Selection Procedures

• Select/Drag and Drop/AutoFill Cursor Distinction

• Insert/Delete vs. Cut/Copy/Paste

• Inserting and Deleting have to do with the cells themselves

• Copying, Cutting, Clearing etc. have to do with the contents of the cells

• Ranges

• Non-contiguous Ranges

• Fill Right/Fill Down and AutoFill

• Relative and Absolute Reference

• Formulas, Values, and Formats

• Functions

• Absolute and Relative Reference

• Editing Cells

Spreadsheet Terms

• "What if"

• Rows

• Columns

• Cell Identification A1..Z26 etc.

• Values

• Labels

• Formulas

• Functions

• Ranges

• Relative Reference

• AutoFill

• Drag and Drop

• Fill Right

• Fill Down

• Special Keys

• The = key

• The * key

• The : key

What is a spreadsheet?

A Brief History of Spreadsheets and the Microcomputer

• VisiCalc

• Lotus 1-2-3

• Microsoft Excel

Designing a spreadsheet

• "What If" analysis

• Decision Support

• IMPORTANT WARNING!!

Excel will always look right even when it is wrong!

What is Microsoft Excel?

• Spreadsheet

• Chart or Graphing Tool

• List Management (Database)

• Forms or table publishing

• Programming Environment

Excel Exercises

I recommend that you name your Workbooks after the Lesson you are working on and that you also include your name in the Workbook, or at least your initials. I also recommend that you name the Worksheets in your Workbook with the page and lesson number that you are working on. You name the Workbook when you first save the Workbook. You can double click a Worksheet tab to name it.

Naming Workbooks and Worksheets

[pic]

1. Double Click a Worksheet tab and name it for the page you are working on. As you do your exercises I will expect that your Workbooks will be named for the Lesson and that you name the Worksheet tabs for the page of the exercise that you are working on.

[pic]

2. To add a Worksheet to a Workbook right click on a Worksheet tab and choose Insert. Choose Worksheet from the General tab. A new Worksheet will be added to your Workbook. You will need to rename your Worksheet so that it is meaningful. Name the new Worksheet for the Exercise that you are doing. A Workbook can have over a hundred worksheets.

[pic][pic]

Selecting Ranges of Cells

This exercise will demonstrate a variety of ways to select ranges of cells. I have named this Workbook 01Lesson.xls. I have named the Worksheet tab in the Workbook Lesson 1-3.

Selection before Action is a key concept in Excel as well as in Microsoft Windows and the Macintosh.

To use Excel it is necessary to select ranges of cells.

Using Drag to Select Cells

[pic]

1. Start with a Blank Worksheet

3. Click on Cell B4 and, with the mouse button held down, drag to Cell E7

4. Practice Clicking and Dragging to select ranges of cells

Using Shift-Click to select a range of Cells

1. Click on Cell B4 and with the Shift Key held down click on Cell E7

Practice using Shift-Click to select ranges of cells

1. Click on Cell B4, and with the Shift Key held down use the cursor keys to move to Cell E7

5. Practice using the Shift Cursor selection technique.

Selecting Entire Columns of Cells

Selecting Ranges of cells is different from selecting rows and columns. It is important to not confuse the two different processes.

1. Select Column B by clicking on Column Heading B. Look at the cursor as you select the entire column..

[pic]

2. Select Column D through Column F by clicking on Column Heading D and dragging to column heading F.

[pic]

Selecting Entire Rows of Cells

Selecting Ranges of cells is different from selecting rows and columns. It is important to not confuse the two different processes.

1. Select Row 4 by clicking on Row Heading 4. Look at the cursor as you select the entire row. You have selected 256 cells.

[pic]

6. Select Row 3 through Row 8 by clicking on Row Heading 3 and dragging through Row Heading 8.

[pic]

7. Experiment with selecting Rows and Columns

Selecting Non-Contiguous Ranges of Cells

Sometime it is convenient to create ranges of cells that are separated. In order to select non-contiguous cells it is necessary to use the Ctrl Key. To practice Non-Contiguous selections do the following exercise.

Selecting separate Columns and Rows

1. Start with a Blank Worksheet

8. Select Columns A.

9. Press the Ctrl Key and Select Rows 1 and 2

10. Columns A and Rows 1 and 2 should all be selected

[pic]

Selecting 2 or more separate blocks of cells

1. Select Cells C3:F4

11. Press the Ctrl Key and Select Cells G11:I17

12. Blocks C3:F4 and G11:I17 should both be selected

[pic]

Drag and Drop, and Autofill Cursor Exercise

It is easy to get confused between Selection, Drag and Drop, and AutoFill. This exercise will demonstrate the difference between these three different Mouse Actions.

Auto Fill (Cross-Hair Cursor)

[pic] [pic]

AutoFill Cursor Drag and Drop Cursor

1. Start with a Blank Worksheet

13. In Cell B2 enter January. Using the AutoFill Handle in the lower right corner of Cell B2 drag to Cell B13. The Months of the Year should be entered into Cells B2:B13

[pic]

Drag and Drop (Arrow Cursor)

1. Highlight (select) the range of Cell B2:B13 that contain that months of the year,

2. Click on the edge of the selection, the cursor should look like an arrow, and drag and drop the selection to C3.

3. Hold down the CTRL Key and repeat step 2. The range should be copied instead of moved to the new location.

[pic] [pic]

Basic Formula Practice

[pic]

1. In Cell B3 enter the value 100

14. In Cell C3 enter the value 10

15. In Cell A5 enter the label Addition

16. In Cell A6 enter the label Subtraction

17. In Cell A7 enter the label Multiplication

18. In Cell A8 enter the label Division

19. Widen column A so that the labels will show

20. In Cell B5 enter the formula =B3+C3

21. In Cell B6 enter the formula =B3-C3

22. In Cell B7 enter the formula =B3*C3

23. In Cell B8 enter the formula =B3/C3

24. In Cell B3 and C3 enter different values to see the formulas recalculate

Making a formula using five different techniques

There are many ways to do a task. This exercise demonstrates five different ways to create a simple formula to sum a column of numbers

1. Start with a blank worksheet

25. Enter 10 numbers in Column A. Start in Cell A1 and enter numbers through Cell A10

26. Right click the Auto fill handle and drag to the right to column E. When you release the Mouse button your will get a context menu for the Auto fill procedure. Choose Copy Cells and you will fill the range with the selected range.

[pic]

27. In Cell A11, use the AutoSum tool to create the formula =SUM(A1:A10).

[pic]

28. In Cell B11, type the formula =SUM(B1:B10)

29. In Cell C11, type the formula =SUM( drag to select the range C1:C10 and then type the right parenthesis.

30. In Cell D11, type the = and then click on the Cell D1 and type the plus symbol. Click on Cell D2 etc. The formula should look like the following example.. =D1+ D2+ D3+ D4+ D5+ D6+ D7+ D8+ D9+ D10.

[pic]

31. In Cell E11, use the Insert/Function Command to create the formula =SUM(E1:E10).

32. The Worksheet should look like the example below. Notice the Smart Tag that shows the inconsistent formulas.

[pic]

33. Cells A11, B11, C11, and E11 use the Sum function. Cell D11 uses a formula without a function

Grade Spreadsheet Exercise (Function Practice)

This is a simple exercise to create a grade spreadsheet. The purpose of this exercise is to practice some basic spreadsheet skills.

[pic]

1. Create a new Excel Worksheet

34. In Cell B3 enter Student 1

35. Drag the AutoFill Handle in Cell B3 to Cell B18. Student should be listed from 1 to 16.

36. In Cell C2 enter Grade 1

37. Drag the AutoFill Handle in Cell C2 to Cell H2. Grades 1 to 6 should be entered

38. In Cell I2 enter Total

39. In Cell B19 enter Total.

40. In Cell B20 enter Maximum

41. In Cell B21 enter Minimum

42. In Cell B22 enter Count

43. In Cell B23 enter Average

44. Select entire Row 3 and give the Windows/Freeze Pane command

(The Screen should be frozen at row 3)

[pic]

45. Double Click the Intersection of Columns B and C to widen column B to best fit

[pic]

46. In Cell C3 Enter the Number 10

47. In Cell C4 Enter the Number 15

48. In Cell D3 Enter the Number 15

49. In Cell D4 Enter the Number 20

50. Highlight Range C3:D4 (All 4 number should be selected

51. Drag the AutoFill Handle to Cell H4

[pic]

52. With cell C3 to H4 selected drag the AutoFill Handle to Cell H18 ( A grid of numbers should be created for calculations)

53. Click cell I3 and click the AutoSum tool. A formula should be inserted into cell I3 to Sum cells C3:H3. The answer should be 135.

54. Autofill cell I3 to cell I18 (I18 not 118)

55. Select cell C19 and use the AutoSum Tool to create a formula to sum the range C3:C18

56. Select cell C20 and enter the formula =MAX(C3:C18)

57. Select cell C21 and enter the formula =MIN(C3:C18)

58. Select cell C22 and enter the formula =COUNT(C3:C18)

59. Select cell C23 and the formula =AVERAGE(C3:C18)

60. Highlight Cells C19:C23 and AutoFill the selection to Cell I23

61. Compare the Resulting Spreadsheet with the Example at the beginning of this exercise.

62. To see the formulas underlying the values scroll to lines 19 to 23 and give the CTRL~ (tilde) command. Each time you press CTRL~ Excel will switch from the Formula to the Value view of a spreadsheet.

[pic]

63. MAKE SURE THAT THE WORKSHEET IS IN VALUE VIEW AT THE END OF THE EXERCISE

Create a formula using different ranges

You can use functions and formulas that refer to ranges of cells. Formulas and Functions do not need to only refer to rows and columns.

1. Start with a Blank Worksheet

64. Enter the values in cells B2:D4 as illustrated below.

65. In Cell E6 enter the formula =SUM(B2:D4)

66. The formula with SUM the entire range.

67. When you double click the formula the Rangefinder will highlight cells in the formula.

[pic]

Spreadsheet Checkbook Exercise

Many useful spreadsheets can be simple to create. This is a simple spreadsheet that can be used for many tasks.

2. Start with a Blank Worksheet

68. In Cell A1 put Date

69. In Cell B1 put Check#

70. In Cell C1 put Description

71. In Cell D1 put Check

72. In Cell E1 put Deposit

73. In Cell F1 put Balance

74. In Cell G1 put Comment

75. In Cell F2 put 100 or any other number for beginning balance

76. In Cell F3 put the Formula =F2-D3+E3

77. Using Autofill, fill the formula in F3 down to F10

78. Use the Format/Cells/Number/Currency command or the Toolbar to format the range D2 to F10 as currency

79. Enter some sample checks and deposits to see how the worksheet works

80. Save the file for future use

The Checkbook Template with Values Showing

[pic]

The Checkbook Template with Formulas Showing

[pic]

Loan Compare Exercise

Many useful spreadsheets can be simple to create. This spreadsheet can be used to compare the ramifications of different loan proposals. This spreadsheet will allow you to compare two loans. There are two views of the spreadsheet: Showing Values and Showing Formulas.

1. Create the loan compare spreadsheet using the examples and try it out using different loan amounts.

2. Once you create the Loan 1 formulas and data you can Fill cells B2:B9 to column C and all the formulas will adjust because of relative reference.

[pic]

[pic]

Editing a Cell

When Excel is in Edit mode many commands are not available. It is easy to become confused when Excel is in Edit Mode.

1. Click on another sheet in the Workbook.

81. In Cell A1 type This is some text in cell A1 BEFORE pressing Enter or clicking the Check Box Use the Mouse to Look at the Menu Items. Many of the Menus will be Gray or unavailable.

[pic]

82. Press the Enter Key or click on the Check Box to Enter the information into Cell A1 and look at the Menus again. The Menus will be available for use.

[pic]

83. Click on Cell A1 and type some text. The original information in cell A1 will be replaced.

84. Click the Cancel button to return the text to the way that it was originally. If you do not Edit text in the Edit box what was originally in the cell will be replaced instead of edited.

[pic]

Absolute and Relative Reference

When using absolute reference the function refers to a specific cell such as $A$1 rather then to a relative location such as A1.

Relative and absolute is like the difference between describing a house by a specific address, 3rd and Lathrop (ABSOLUTE), as opposed to saying go three blocks south and one block north. (RELATIVE)

Values Showing

[pic]

Creating a Relative Reference Example

1. In Cell A1 Type Relative Reference

85. In Cells A2:A12 Autofill a series of numbers

86. In Cells C2:C12 Autofill a series of numbers

87. In cell B2 Enter the formula =A2*C2. Fill the formula down to B12

Creating an Absolute Reference Example

88. In Cell E1 Type Absolute Reference

89. In Cells E2:E12 Autofill a series of numbers

90. In Cells G2:G12 Autofill a series of numbers

91. In Cell F2 Enter the formula =E2*G2.

92. Highlight the Formula In The Formula Edit Bar and Hit the F4 Key. The formula should convert to an absolute Reference. It will like =$E$2*$G$2.

93. Fill the Formula Down to F12.

Switching From Formula to Value View

94. To Switch to Formula View Use the Control Tilde Key CTRL ~

[pic]

95. Switch back and forth from formula to value view of the worksheet. Leave the View in Value mode when done

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

Labels Illustrate Formulas in cells B5:B8

AutoSum Tool

Check Box

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

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

Google Online Preview   Download