PRC Student GuideMod 1: Review the Basics
Module One: Review the Basics
Welcome to the first lesson in the PRC’s Excel Spreadsheets Course 2. This lesson is a review of Excel’s fundamental elements and fills in any gaps you might have about the basics of working with Excel. You will create one spreadsheet and modify another. The second spreadsheet can be found on the accompanying student diskette. After completing this lesson, you should feel very comfortable with the vocabulary of Excel.
Topics
Starting Excel...... 1.
Excel Window Components...... 1.
Quick Access Toolbar...... 1.4
Mouse Symbols...... 1.
Selecting Cells...... 1.
Entering Information...... 1.
Quitting Excel...... 1.
Exercises
Exercise A - Create a Stock Portfolio Spreadsheet...... 1.
Exercise B - Complete a Pro Forma Income Spreadsheet...... 1.
Summary...... 1.
Objectives
- Use a variety of means to start Excel
- Use keyboard shortcuts to call Excel menu choices
- Customize the Quick Access Toolbar
- Identify the various mouse pointer symbols
- Select cells and enter information
- Use the components of the Save As… dialog box
Starting Excel
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. If you select New under the Office Button 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. You will learn more about templates.
Excel can also be started with an existing spreadsheet by double clicking on the icon of the spreadsheet or the icon of a shortcut to the spreadsheet. If you select Open under the Office Button menu after Excel has been started then you will be presented with the contents of a default folder on your hard drive. You will learn how to set the default folder in a later lesson.
If the file you want to open does not appear in the default folder you can navigate around your hard drive using the folders on the left side of the Open dialog box. The most common reason for not finding the file you want is the selection shown in the Files of type: window at the bottom of the Open dialog box. Figure 1.1 shows “All Excel Files” in that window. There is a drop down arrow on the right end of that window that is used to display different file types. Use it to display the type of file you need.
Figure 1.1 File | Open Dialog Box
All of the exercises in this course are contained on the PC hard drive or CD. Click on the My Computer icon on the left side of the dialog box, select the drive where the files are located (drive C: for the hard drive, and usually drive D: for the CD) 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 under the Office Button menu.
Excel 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.2 Spreadsheet window components
The most important objects in the spreadsheet window are the:
- Quick Acess Toolbar - which contains all the spreadsheet commands
- Office Button – which contains important file commands
- Ribbon - 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
The Quick Access Toolbar
There are so many features available in Excel 2007, and the Ribbon can only hold so many buttons, so some of the functions are difficult to get to. Fortunately, Excel 2007 has the Quick Access Toolbar. This is located just to the right of the Office Button. By default, it consists of Save, Undo, and Redo. However, this can be altered to include just about every function available in Excel. The easiest way to do this is to right click any command, and click on Add to Quick Access Toolbar.
Figure 1.3 The default Quick Access Toolbar
To add more buttons to the Quick Access Toolbar, click on the down arrow just to the right of the last button. This will open a drop-down menu with several functions that can be added, as shown in Figure 1.4. Note that if the Ribbon does not appear across the top of your Excel screen, it may be because the “Minimize the Ribbon” option is checked. Uncheck this option and the Ribbon should be displayed.
Figure 1.4 The Customize Quick Access Toolbar drop-down menu
This is still only a small fraction of the available commands though. To get more, click on More Commands… A dialog box similar to the one in Figure 1.5 will appear.
Figure 1.5 The Customize Quick Access Toolbar Dialog Box
From this dialog box you can add any command in Excel onto the Quick Access Toolbar. Click on the command you want to add in the box on the left, then click Add. To remove a command, click on it in the right box, then click Remove. The drop-down menu above the left box allows you to find the command you are looking for easier by displaying only those in a certain category.
Keyboard shortcuts do the same thing as the Ribbon icons; they provide shortcuts to specific functions within the menu system. There are hundreds of keyboard shortcuts available in Excel, so learning them is more difficult than learning all the icons on the ribbon. Microsoft has provided a little help.
In general, keyboard shortcuts are invoked by holding down the Control key and then pressing some other key. But what other key? Mousing over a button on the Ribbon or the Quick Access toolbar will show you the command key for that function in parentheses.
Mouse Symbols
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.
Figure 1.9 The Auto Fill mouse symbol
The 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
Selecting Cells
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 (16,384 cells)
Entire column (1,048,056 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.
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.
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.
Entering Information
To enter information into a cell, first select the cell then type the information you want entered. As you type, the information becomes visible in the cell and in the Formula bar, and three new buttons appear to the left of the Formula bar.
Figure 1.13 Handy Formula Bar buttons
The X button is used to cancel data entry and erase all the information 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.
Information entered into a spreadsheet is either a formula or something not a formula. Entries which are not formulas can be text, numeric constants, and numerics that behave like text. Collectively, these entries are called data.
A text entry will be displayed in its entirety even if it spills over into an adjacent empty cell. Text is left aligned by default. If the text entry is long and there are no empty cells to the right, the text will appear truncated after you accept it into the cell by pressing Enter.
If a numeric constant entry is too large for the column width it will display as ###### (pound sign) symbols. Increasing the width of the column will visually restore the entry. Numbers are right aligned by default.
There are times when you will want to enter a number as text. To enter Zip Codes as text, type a single quote first, then type the Zip Code. Numbers that contain hyphens, for example, SSN’s or phone numbers, are automatically treated as text. A cell can be formatted for certain specialized uses, like Zip Codes, so the single quote is not necessary. You will learn how to format cells this way, as well as how to format date entries, in the next lesson.
An entry which is a formula always begins with =. Formulas can contain text, numeric constants, cell references, arithmetic operators, comparison operators, and functions. Table 1.2 shows the keyboard symbols used for the various arithmetic operations you might want to perform in your spreadsheet.
Symbol / FunctionPlus + / Addition
Minus - / Subtraction
Asterisk * / Multiplication
Slash / / Division
Caret ^ / Exponentiation
Parentheses ( ) / Precedence
Table 1.2 Keyboard symbols used in formulas
Exponentiation is raising a base number by a power; e.g., 32 = 9.
Excel evaluates or calculates formulas according to a set of rules called the order of precedence. The order of precedence is exponentiation first, multiplication and division second, addition and subtraction third, and left to right fourth. The following example shows the results of applying these rules.
4+5*2+3 = 17
The last entry in Table 1.2 indicates that parentheses can be used to force a specific order of precedence when Excel evaluates a formula. The following example shows how powerful the parentheses can be in determining a result.
5*(2+3)=25
(5*2)+3=13
Both formulas use the same arithmetic operators, but different results are obtained by changing the order of precedence using parentheses.
Data is different from formulas in that it is static, unchanged by movement from one location in a spreadsheet to another. In contrast, formulas, because they contain cell references, are almost always changed by a movement to a new location. The reason this can cause confusion is that a formula displays the result of its calculation in the cell where it is located. While the cell looks like it contains a number, if that cell is moved, most of the time the displayed number will change because the formula is changed.
Quitting Excel
Microsoft uses the verb “Exit” to mean quit or stop. In the upper right corner of Figure 1.2 there are two buttons with an X in them. The lower one is used to exit the spreadsheet currently displayed; the upper one is used to exit the Excel program. If you have made any changes to the spreadsheet, using either button will cause Excel to display a caution asking whether or not you want to save the changes.
After a new spreadsheet opens, it is a good practice to immediately name the spreadsheet using the Save As… command under the Office Button. If you choose the Save command rather than the Save As… command, 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 students like to save their work on a USB flash drive. The picture in Figure 1.1 is almost exactly the same as the Save As... dialog box except that a file name will appear in the File name window at the bottom of the dialog box.
______
The following exercises will further solidify the topics taught in this lesson. The spreadsheet for Exercise B can be found on your computer hard drive.
Exercise A - Create a Stock Portfolio Spreadsheet
In this exercise you will create a stock portfolio spreadsheet that lists several stocks and their current value. Once you have created the spreadsheet, save it as 2.1stock_rev on the computer in the Excel 2 Files folder.
1Find the Excel 2 2007 Files Folder
2Open the file: 2.1stock.xlsx
3Using the Office Button, Save As… sequence, save the blank spreadsheet using the name 2.1stock_rev on the computer.
The basic structure of the spreadsheet is already set up for you.
4Starting in cell A7, enter the remaining stock information from Table 1.3
Table 1.3 Additional data for the stock portfolio spreadsheet
5Save your work thus far
Remove unwanted column
6Right click on the column heading for Column F
7On the pop-up Menu, select the Delete Column command
Adjust the column sizes
8Click and drag the mouse pointer across the column headings A through G to highlight all 7 columns
9Use Home Tab, FormatGroup,Format, AutoFit Column Widthon the to adjust column widths
Calculate total cost for each stock
10In cell D5 enter the formula =B5*C5 and press the Enter key
This instruction will be shortened to Let D5 = B5*C5
11Drag the fill handle on cell D5 down to cell D11 and release the mouse button
This instruction will be shortened to Fill D5 down to D11
12Let F5 = Current Trading Price * Number of Shares
13Fill F5 down to F11
14Let G5= Current Value – Total Cost
15Fill G5 down to G11
16Enter today’s date in cell B2
Add up your total stock profit and loss column
17Enter Portfolio Value in cell C2
18Let D2 = sum of all the Profit or Loss values
This can be done one of two ways:
Let D2 = G5+G6+G7+G8+G9+G10+G11 or Let D2 = SUM(G5:G11)
Your total should be 8760.
19The Current Trading Price of Ford just went down to 27.25. Update the contents of cell E7 with this new price.
Your total now should be 8403
Your completed spreadsheet should look like the one in Figure 1.14.
Figure 1.14 Exercise A completed spreadsheet