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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related searches
- template for writing a business plan
- free business proposal template pdf
- free marketing plan template microsoft word
- business plan template word
- template for conclusion paragraph
- business plan template free
- simple business plan template pdf
- business proposal template word
- blank business plan template free
- business plan template word document
- printable business plan template free
- startup business plan template excel