Objectives Trainee will be able to: • Enter and edit text and numbers in an Excel spreadsheet. • Use Excel to create, enter, and calculate a formula on a spreadsheet. • Resize and format columns and rows of an Excel spreadsheet. • Save and print an Excel spreadsheet. Prerequisites Ability to perform basic functions in a Windows operating system. Ability to describe and use features unique to the Gates Library Computer. Ability to identify and use common elements of the Microsoft Office programs. Duration 50 minutes Supplies Excel 2000 Quick Guide Blank floppy or Zip disk to save work (optional) Handouts Creating a Monthly Budget Labels, Values, and Formulas Time Saving Excel Shortcuts (optional) Topic Overview Topics Estimated Time Getting Started with Excel 2000 5 Features Unique to Excel 10 Creating a Monthly Budget 35 Getting Started with Excel Estimated time: 5 minutes  Supply: Excel 2000 Quick Guide Open Microsoft Excel. Microsoft Excel is a spreadsheet program you can use to create budgets, schedules, and simple databases, such as an address book. The Excel screen is arranged in rows and columns. Cells hold the text and numbers you type. You can use the mouse, the arrow keys, and the Tab key to move from cell-to-cell. If you are creating a document that is largely text, such as a letter or a resume, a word processing program is used (e.g. Word or Word Perfect). Excel is used to create spreadsheets - documents used to organize numbers and data. Features Unique to Excel  Handout: Labels, Values, and Formulas Estimated time: 10 minutes Excel has several unique features. These features include the spreadsheet, cells, the name box, row and column headings, and the formula bar. The following section details these unique Excel features. The Spreadsheet A spreadsheet is an application commonly used for budgets, forecasting, and other finance-related tasks that organizes data values using cells, where the relationships between cells are defined by formulas. A change to one cell can produce changes to related cells. Each workbook (file) contains three spreadsheets when you begin. Workbooks can contain numerous spreadsheets, thus you can organize many types of information in one file. Spreadsheets store data, charts and macros. Spreadsheets are made of 256 lettered columns and 65,536 numbered rows. Cells A cell is an addressable (named and numbered) storage unit for information. Each row and column in an Excel spreadsheet is unique, so each cell is uniquely identified—for example, cell B17, at the intersection of column B and row 17. Each cell is displayed as a rectangular space that can hold text, a value, or a formula. You can type 255 characters in each cell. Active Cell The highlighted cell on the spreadsheet that displays the current focus of operation is the active cell. The active cell is surrounded by a heavy border and is identified by the cell address, which is where the column and row intersect. The Name Box Located in the top left corner, the name box displays the address of the selected cell. If you type a cell address in the name box and press ENTER you will go to that cell. Row Headings Row headings label the rows. Rows are a series of cells arranged horizontally. Numbers identify rows. Clicking a row heading selects an entire row. Column Headings Columns headings label the columns. Columns are a series of cells arranged vertically. Letters identify columns. Clicking a column heading selects an entire column. Formula Bar Located on the top of the spreadsheet, below the toolbars, the formula bar displays text from the selected cell. If there is a formula in the cell, the formula, not the answer to the formula, is displayed in the formula bar. Activity: Entering Text, Editing Text, and Moving Around 1. Click cell A1 to select it. 2. Type your first and last name in cell A1. Notice how the text appears in both the cell and the Formula Bar. 3. Use the arrow keys to move to cell D1 and type today's date. a. Select row 1 by clicking the number 1 on the row heading on the left edge (the row will change color). b. Move the mouse pointer to any part of the row's horizontal edge. The mouse pointer changes to a diagonally pointing arrow. c. Click and drag row 1 to row 4. Hint: The cell must be active to move it. 5. Click the cell with your name (cell A4). 6. Click and drag cell A4 back to cell A1. 7. Select the date and move it back to the original position in row 1. 8. Double-click cell A1. Notice the blinking vertical insertion point in the cell. You are now in edit mode. 9. While in edit mode, add your middle name to cell A1. Activity: Calculating a Sum 1. Click cell A3. 2. Type the number 1 in A3, 2 in A4, and 3 in A5. 3. In cell A6 type a formula that will instruct Excel to automatically sum the values of cells A3, A4, and A5. a. Type the formula =A3 + A4 + A5 in cell A6. b. Press Enter. Hint: When entering formulas, after you type an equals sign, instead of typing the cell names (i.e. A3, A4) click the you want to sum. Excel will put that cell reference in your formula. Another way to calculate a sum in Excel is to use the AutoSum button. Select cells A3, A4 and A5 by clicking on A3 and dragging the mouse pointer to A5 (the cells will be grayed). Now click the AutoSum button,. 4. Close the spreadsheet and start with a clean sheet - no need to save your work yet. a. Click File and choose Close. b. When asked if you would like to save the spreadsheet click No. c. Click Review Questions You'll find the answers at the end of the lesson. 1. What are some of the similarities between Microsoft Word and Microsoft Excel? 2. What are two ways to calculate a sum? 3. How do you change the contents of a cell? Creating a Monthly Budget Using Excel Estimated Time: 35 Minutes Those were the basics. As you move through this lesson you will pick up more skills, as well as practice the ones you have gone over. In the next activity you will create a monthly budget. An electronic budget is a great tool for keeping yourself organized and current. With an electronic budget you can easily update information as well as communicate changes (e-mail, disk, etc.). Activity: Creating a Monthly Budget Supply: Blank floppy or Zip disk to save work (optional)  Handout: Creating a Monthly Budget  Handout: Time Saving Excel Shortcuts (optional) Follow the directions on the Creating a Monthly Budget handout to create your own personal budget. In the Classroom: Each trainee should have a copy of the instructions (Creating a Monthly Budget Handout). This activity can be facilitated by the trainer or self-directed by the trainees. Trainees can work individually or in pairs. Upon completion, ask for volunteers to share their final product. Review Questions You'll find the answers at the end of the lesson. 4. How do you move the data from one cell to another? 5. What is the AutoSum button 6. How do you select an entire row? 7. What are you telling Excel when you type a equal sign (=) into a cell? If You Have Time The next activities examine further formatting functions of Microsoft Excel. For these activities you will need a new Excel worksheet. Activity: Using Autofill and Freeze Pane AutoFill — Fill in a series of numbers, formulas, dates, or other items 1. In cell A6 type January. You will now automatically fill in the preceding months below January. 2. Select cell A6 (the first cell in the range you want to fill) drag the fill handles over the range you want to fill. Hint: To acquire the fill handle, select the beginning cell in the range (in this case A6) and move the mouse pointer to the black square in the lower right corner of the active cell. The mouse pointer changes to a +. Drag the mouse down (or to the right) to fill in increasing order. To fill in decreasing order, drag up or to the left. 3. In cell B6 type `2001. You will now autofill the dates Hint: When typing numbers as values (numbers that will not be in a formula; a label or year for example) proceed the number with an apostrophe (`). 4. Autofill 2001 in the B row to cell BH. Cell BH should be 2007. Freeze Pane - Keep row and column labels visible as you scroll There will be times when your spreadsheet expands beyond the view of your desktop. For spreadsheets like these, Excel enables you to “freeze” your column and/or row headings - allowing you to always know what column or row you are in. This feature is essential when working with numerous headings. • To freeze the top horizontal pane, select the row below where you want the split to appear. • To freeze the left vertical pane, select the column to the right of where you want the split to appear. • To freeze both the upper and left panes, click the cell below and to the right of where you want the split to appear. After you have selected the row and/or column you want to freeze, on the Window menu, click Freeze Panes. If You Remember Nothing Else Microsoft Excel is a spreadsheet program you can use to create budgets, schedules, and simple databases, such as address books. The Excel screen is a spreadsheet made of cells that hold text and numbers arranged in rows and columns. Review Answers 1. Toolbars, menus, print, save, spell check, help, etc. 2. You can type the cell locations separated by plus (+) signs. You can also use the AutoSum function. 3. Double-click the cell to be edited to enter the edit mode. You can edit in the cell directly or from the formula bar. 4. Click and drag, or cut and paste. 5. To quickly add a series of numbers. 6. Click a row heading (a number). 7. You are telling Excel to act on what you enter, rather than display what you type.
4. Move row 1 down three rows to row 4. To move row 1:
Unless a range of cells is selected, AutoSum will by default, sum the cells above the active cell, if no data is there, it will sum the cells to the left of the active cell. If no range is selected, AutoSum only sums cells that continuously have data (i.e. if there is data in cells A1 and A3-A5, only the data in cells A3 through A5 will be summed).
on the Standard toolbar to open a new workbook.
used for?
The AutoFill function of Excel is a quick and handy function for “finishing what you started.” For example, if you are typing in the months of the year, beginning with January, you can use the AutoFill to automatically finish the list. This function works for numbers and formulas as well.
Documents
| Excel Lesson Plan |
This lesson helps learners use Excel to create, enter, and calculate a formula on a spreadsheet, enter and edit text and numbers in an Excel spreadsheet, and generally manage a budget with Excel features.
|
|
Contribute to this topic
Do you have an article, presentation, or other content to share on this topic?
You can post it on this topic page. Find out more about submitting documents in the Member Center.
Ratings You must be signed in to rate this item
|
Average (0 Votes)
|
Comments