PRC Student Guide Mod 1: Getting Started
Module One: Get Started with Excel
Welcome to the first lesson in the PRC’s Excel Spreadsheets Course 1. This lesson introduces you to Excel 2000. You will explore several pre-built sample spreadsheets and make minor modifications to each sample. The spreadsheets can be found on the accompanying student diskette. After completing this lesson, you will be able to describe the basic Excel working environment.
Topics
Spreadsheet Concepts 1.2
Types of Spreadsheets 1.3
Spreadsheet Window Components 1.4
Open an Existing Spreadsheet 1.5
Create a New Spreadsheet 1.6
Save a Spreadsheet 1.6
Keeping Track of the Mouse and the Mouse Pointers 1.6
Select Rows and Columns and Individual Cells 1.8
Delete and insert Cells 1.9
Enter Text and Numbers 1.10
Exercises
Exercise A - Change a Budget Spreadsheet 1.12
Exercise B - Modify an Auto Mileage Spreadsheet 1.15
Summary 1.19
Objectives
· Determine when to use a spreadsheet
· Open, modify, create and save Microsoft Excel spreadsheets
· Enter data, select cells and insert/delete rows and columns of information
Spreadsheet Concepts
A spreadsheet is a collection of rows and columns. In Microsoft Excel a spreadsheet can contain 256 columns and 65,536 rows of information. The columns are lettered A, B, C, etc., ending with IV. The rows are numbered 1 through 65536. The intersection of a row and column is called a cell. Every cell in the spreadsheet has an address created by combining the column letter with the row number. In the example shown in Figure 1.1, cell E6 is selected, the fifth column and the sixth row.
Figure 1.1 An empty spreadsheet layout
A cell in a spreadsheet can contain text or numbers or numbers entered as text and various formatting attributes. An example of entering a number as text would be a Zip Code or Social Security Number. To enter a number as text simply precede the number with a single quote (‘).
Another very important concept is that a cell contains formatting attributes as well as data. Some of the many formatting attributes include alignment, fonts, percents, dollar signs and decimal points. Without the proper formatting, numbers could appear without trailing zeroes or other unexpected results. It is always a good idea to format a new spreadsheet before entering data.
Types of Spreadsheets
A spreadsheet program like the one included in Microsoft Excel provides an easy way to create lists of information. Like a columnar pad, you can create phone lists, address lists, grocery lists and the lists could go on. Typically these lists would not include any formulas. Some people find creating these lists or tables in a spreadsheet to be easier than creating the same lists in a word processing program. See Figure 1.2 for some spreadsheet examples.
The real power of a spreadsheet is its ability to include formulas which automatically adjust as new numbers are entered. A formula can be as simple as 2+2 or as complex as calculating the internal rate of return on an investment. In a later lesson you will explore formulas and functions and how to use them.
Figure 1.2 Sample Spreadsheets
Spreadsheet Window Components
The Microsoft Excel spreadsheet window consists of many parts. Below is a picture of the spreadsheet window and all of its component parts. The more commonly used areas are highlighted.
Figure 1.3 Spreadsheet window components
The most important objects in the spreadsheet window are the:
· Menu bar - which contains all the spreadsheet commands
· Toolbar - which displays command shortcut buttons
· Formula bar - where changes are made to cell contents
· Column/Row headings - the way to select entire rows or entire columns
Moving around from cell-to-cell is easily accomplished by using the Arrow keys on your keyboard. The Tab key is also a good way to move between cells. The Tab key moves from left to right. To reverse the direction of the Tab key, right to left, combine the Tab key with the Shift key. Hold down the Shift key and tap the Tab key. This combination of keystrokes written as Shift+Tab
Figure 1.4 Navigation keys found on the keyboard
Open an Existing Spreadsheet
Microsoft Excel can be started by selecting it from among the programs offered in the Start Menu or by clicking on the icon of an existing spreadsheet.
After Microsoft Excel has been started, you can select New or Open under the File Menu. If you select Open you will be presented with the contents of a default folder on your hard drive.
Figure 1.5 File | Open Dialog Box
If the file you want to open does not appear here you can navigate around your hard drive using the folders on the left side of the dialog box, you can close the box and use the Find or Search function in the Start Menu, you can close the box and scan the list of Recently Used Files under the File menu, or you might use the drop down menu at the bottom of the dialog box to display All Files instead of just All Microsoft Excel Files.
All of the exercises in this course are contained on diskette. Click on the Desktop icon on the left side of the dialog box, select the drive where your student diskette is located (usually drive A:), and navigate to the folder containing the files used in this course. If you come back at a later time to do more work on your spreadsheet, you may find it in the list of Recently Used Files.
Create a New Spreadsheet
If you start Microsoft Excel by selecting it from among the programs offered in the Start Menu then you will be presented with a blank spreadsheet named Book1 (see Figure 1.3). If you select New under the File menu after Microsoft Excel has been started then you will be offered a choice of templates. Templates are pre-formatted spreadsheets, some serving very specific purposes such as invoicing.
Save a Spreadsheet
After a new spreadsheet opens, it is a good practice to immediately name the spreadsheet using the Save As… command. If you choose the Save command rather than the Save As… command, Microsoft Excel will still present the Save As… dialog box. This is because the new spreadsheet has never had a name before. In Windows, your new spreadsheet name can be up to 256 characters in length and the name can contain spaces.
It is always a good idea to frequently save your work. And it is also a good idea to preserve the original spreadsheet name of the class exercises. If you choose just the Save command, the original spreadsheet will be replaced by the one you have changed. The recommendation is to always use the Save As... command and give the modified spreadsheet a new unique name.
By using the Save As... command you will also be able to choose the location to save the spreadsheet. Many classroom computer labs are set up for students to save their work on a diskette usually located on the A: drive. The picture in Figure 1.5 is almost exactly the same as the Save As... dialog box except that a file name will appear in the File name box at the bottom of the dialog box.
Keeping Track of the Mouse and the Mouse Pointers
The mouse pointer in the spreadsheet program takes on many different shapes depending on where the mouse pointer is located. These shapes are visual clues as to what you can do at the specific position on your display screen. The mouse symbol pictured in Figure 1.6 indicates that your mouse pointer is in the spreadsheet work area. It is the most common of the mouse symbols.
Figure 1.6 Spreadsheet mouse symbol
The mouse symbol pictured in Figure 1.7 indicates that your mouse pointer is on a border between two rows. Note: There is a “hot spot” area just above “up arrow” button on the vertical scroll bar where the mouse pointer will change to this symbol. By dragging the small bar down, the display screen will be divided into two horizontal sections.
Figure 1.7 Row adjustment mouse symbol
The mouse symbol pictured in Figure 1.8 indicates your mouse pointer is on a border between two columns. Note: There is a “hot spot” area to the right of the “right arrow” button on the horizontal scroll bar where the mouse pointer will change to this symbol. By dragging the small bar to the left, the display screen will be divided into two vertical sections.
Figure 1.8 Column adjustment mouse symbol
The mouse symbol pictured in Figure 1.9 indicates that your mouse pointer is on the lower right hand corner of the selected cell. When you see this symbol you can drag the mouse and automatically fill new cells with new information. AutoFill will be featured in a later lesson.
Figure 1.9 The Auto Fill mouse symbol
The next mouse symbol pictured in Figure 1.10 can be used to Drag, Move or Copy the contents of a cell.
Figure 1.10 The move mouse symbol
Drag - indicates the mouse is on the border of a cell and you now have the capability of moving or copying the cell contents.
Move - by simply dragging the contents of the cell to a new location it will be moved to the new location.
Copy - holding down the Ctrl key while dragging the contents of the cell will duplicate the cell contents at the new location.
The last symbol is the classic symbol for entering text. The i-beam type symbol is displayed in the Formula bar when entering text, numbers or formulas. It is also visible in a cell immediately after double-clicking the cell.
Figure 1.11 The text entry mouse symbol
Select Rows and Columns and Individual Cells
Mastering how to select rows and columns of information is extremely important. Before you can modify data in any way, the data must be selected. Select First, then Change! Table 1.1 is a summary of the many ways to select cells.
To Select… / ActionSingle cell
Multiple cells
Entire row (256 cells)
Entire column (65,536 cells)
Entire spreadsheet
Extend the current selection / Click once in the desired cell
Click once and drag to the last cell
Click once in the row heading
Click once in the column heading
Click once on the Select-All
Hold down the Shift key and
click once in the new cell
Table 1.1 Techniques for selecting one or more cells.
Visually you can see which cells are selected. All selected cells will be highlighted with a colored background, except the first cell in your selection. The first cell in your selection will remain with a hollow or white background. Selecting a block or range of cells can be made easier if you drag the mouse diagonally towards the bottom right corner of the block of cells.
Figure 1.12 Selecting a range of cells B3 through D10.
Note: To deselect your current selection, click once anywhere else in the spreadsheet.
There is a small rectangular button, called Select-All, which sits above the row 1 heading and immediately to the left of the column A heading. Use this button to select the entire spreadsheet.
Delete and insert Cells
The command to remove or delete the contents of a selected cell(s) is Clear. The Clear command is located on the Edit menu. You must first select the cell or cells you want to clear. You will also find the Clear command on the right mouse button short-cut menu. Clicking once with the right mouse button while positioned inside of the target cell will display a short-cut menu.
Figure 1.13 The Edit Menu
The Delete key on the keyboard will also clear the contents of a cell. You could also use the Cut command located on the Edit menu. This is not the best choice since it will replace anything you may have saved on the clipboard.
To remove an entire column or row of information, first select the column or row heading. This should highlight the complete column or row. Selecting Delete from the Edit Menu will then delete the column or row and all remaining data will be shifted up (Delete row) or to the left (Delete column). The right mouse button short-cut Menu can also be used to invoke Delete Row or Delete Column commands.
Figure 1.14 The Insert Menu
Inserting rows and columns uses the same technique as described above. The commands Insert Row and Insert Column can be found on the Insert Menu or on the right mouse button short-cut Menu. After the insert, all existing data in the spreadsheet will be shifted down (Insert Row ) or to the right (Insert Column ).
Enter Text and Numbers
To enter text into a cell start by selecting the cell first, then typing the data you want entered. As you type, the data becomes visible in the cell and in the Formula bar and three new buttons appear to the left of the Formula bar.
Figure 1.15 Handy Formula Bar buttons
The X button is used to cancel data entry and erase all the data you just typed into the cell. The Checkmark button acts like the Enter key and causes the typed information to be entered into the cell. The Question mark button is a short-cut to Help. Once text has been entered into a cell, use the Enter key or the Checkmark button to accept the text into the cell. The text you typed will be displayed in its entirety even if it spills over into an adjacent empty cell. Text is left aligned.
Figure 1.16 The Enter Key will be one of these two possible shapes.
If the typed text is long and there are no empty cells to the right, the text will appear truncated after you accept it into the cell. Microsoft Excel has not lost the data. It has been visually cut-off because the column width is too small.