Module One: Get Started with Excel



Module One: Review the BasicsWelcome 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 TOC \t "Section Hdr,1" Starting Excel1. PAGEREF _Toc491914743 \h 2Excel Window Components1. PAGEREF _Toc491914744 \h 3Quick Access Toolbar1.4Mouse Symbols1. PAGEREF _Toc491914746 \h 6Selecting Cells1. PAGEREF _Toc491914747 \h 7Entering Information1. PAGEREF _Toc491914748 \h 8Quitting Excel1. PAGEREF _Toc491914749 \h 9Exercises TOC \t "Exercise Hdr,1" Exercise A - Create a Stock Portfolio Spreadsheet1. PAGEREF _Toc494532191 \h 11Exercise B - Complete a Pro Forma Income Spreadsheet1. PAGEREF _Toc494532192 \h 14Summary1. PAGEREF _Toc494532193 \h 16ObjectivesUse a variety of means to start ExcelUse keyboard shortcuts to call Excel menu choicesCustomize the Quick Access ToolbarIdentify the various mouse pointer symbolsSelect cells and enter informationUse the components of the Save As… dialog box Starting ExcelIf 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 BoxAll 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 ComponentsThe 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.Quick Access Toolbar Zoom Slide bar SliderZZooOffice ButtonRibbonStatus BarSheet NameRow HeadingColumn HeadingCell Name BoxFormula BarFigure 1.2 Spreadsheet window componentsThe most important objects in the spreadsheet window are the:Quick Acess Toolbar - which contains all the spreadsheet commandsOffice Button – which contains important file commandsRibbon - which displays command shortcut buttonsFormula bar - where changes are made to cell contentsColumn/Row headings - the way to select entire rows or entire columnsThe Quick Access ToolbarThere 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 ToolbarTo 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 menuThis 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 BoxFrom 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 SymbolsThe 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 symbolThe 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 symbolThe 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 symbolThe 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 symbolThe 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 symbolDrag - 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 CellsBefore 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 cellMultiple cellsEntire row (16,384 cells)Entire column (1,048,056 cells)Entire spreadsheetExtend the current selectionClick once in the desired cellClick once and drag to the last cellClick once in the row headingClick once in the column headingClick once on the Select-AllHold down the Shift key and click once in the new cellTable 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 InformationTo 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 buttonsThe 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.SymbolFunctionPlus +AdditionMinus -SubtractionAsterisk *MultiplicationSlash /DivisionCaret ^ExponentiationParentheses ( )PrecedenceTable 1.2 Keyboard symbols used in formulasExponentiation 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 = 17The 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=13Both 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 ExcelMicrosoft 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 SpreadsheetIn 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 Folder2Open the file: 2.1stock.xlsx3Using 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 spreadsheet5Save your work thus farRemove unwanted column6Right click on the column heading for Column F7On the pop-up Menu, select the Delete Column commandAdjust the column sizes8Click and drag the mouse pointer across the column headings A through G to highlight all 7 columns9Use Home Tab, Format Group, Format, AutoFit Column Width on the to adjust column widths Calculate total cost for each stock10In cell D5 enter the formula =B5*C5 and press the Enter keyThis instruction will be shortened to Let D5 = B5*C511Drag the fill handle on cell D5 down to cell D11 and release the mouse buttonThis instruction will be shortened to Fill D5 down to D1112Let F5 = Current Trading Price * Number of Shares13Fill F5 down to F1114Let G5 = Current Value – Total Cost15Fill G5 down to G1116Enter today’s date in cell B2Add up your total stock profit and loss column17Enter Portfolio Value in cell C218Let D2 = sum of all the Profit or Loss valuesThis 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 8403Your completed spreadsheet should look like the one in Figure 1.14.Figure 1.14 Exercise A completed spreadsheet20Press Ctrl+Home to return to cell A121Save your workIn a later lesson you will format this spreadsheet and it will become more readable.Congratulations! You have successfully completed Exercise A.Exercise B - Complete a Pro Forma Income SpreadsheetIn this exercise you will fill out a company’s pro forma income statement. Pro forma is an accounting term meaning that the statement follows certain accepted rules and practices. The statement you create is relatively straightforward, but all pro forma statements follow the structure of your spreadsheet. The spreadsheet can be found on your lab diskette as 2.1income.xls. Make changes to the spreadsheet and save it as 2.1income_rev.xls.1Close all open Excel documents.2Using the Office Button, Open sequence, open 2.1income on the student disketteTo preserve the original spreadsheet, save the spreadsheet using a new name.3Using the Ofiice Button, Save As… sequence, save the document as 2.1income_rev on the student disketteA pro forma income statement has four columns in it. The first is a column of labels, or row titles, identifying the numeric entries in that row. The second column is expenses. Subtotals might also appear in this column. The third column is revenues or income. The fourth column is the net column in which the entries equal revenues minus expenses. At the bottom of this column is the Net Income for the company.Enter expenses4Enter 19,200 for Minus: Returns & Allowances. This should go in the expense column; i.e., the first column to the right of the row title. 5Enter 1,200,000 for Plus Purchases6Enter 404,000 for Minus: Ending Inventory7Enter 250,000 for Salaries & Wages8Enter 24,000 for Commissions9Enter 50,000 for Advertising10Enter 12,500 for Sales Promotions11Enter 10,110 for Depreciation12Enter 54,690 for Other13Enter 24,000 for Interest ExpenseEnter revenues14Enter 424,000 for Beginning Inventory This should go in the revenues column; i.e., the second column to the right of the row title. 15Enter 19,000 for Interest IncomeEnter formulas that calculate subtotals16Total Operating Expenses = sum of all Operating ExpensesEnter formulas that calculate the entries in the net column16Net Sales = Gross Sales – Minus: Returns & Allowances17Net Cost of Goods Sold = Beginning Inventory + Plus: Purchases – Minus: Ending Inventory18Gross Profit on Sales = Net Sales – Net Cost of Goods Sold19Net Operating Income = Gross Profit on Sales – Total Operating Expenses20Net Other Revenue & Expenses = Interest Income – Interest Expense21Net Income Before Taxes = Net Operating Income + Net Other Revenue & Expenses22Income taxes = 40% of Net Income Before Taxes23Net Income = Net Income Before Taxes – Income TaxesYour completed spreadsheet should look like the one in Figure 1.15.Figure 1.15 Exercise B completed spreadsheet38Press Ctrl+Home to return to cell A139Save your workIn a later lesson you will format this spreadsheet and it will become more readable.Congratulations! You have successfully completed Exercise B.SummaryYou have reviewed how to...Use a variety of means to start ExcelUse the Ribbon and keyboard shortcuts to call Excel menu choicesCustomize the Quick Access ToolbarIdentify the various mouse pointer symbolsSelect cells and enter informationUse the components of the Save As… dialog boxIn the next lesson you will complete a comprehensive tour of the Format Cells dialog boxNOTES ................
................

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

Google Online Preview   Download