4002book.
4002book.fm Page 2 Friday, March 24, 2006 11:36 PM You know you’re really old if you remember the paper spreadsheets that companies used to use to do their number crunching. The look of Microsoft Excel resembles those abacuses of papyrus, but the advantages to using Excel are numerous, not to mention obvious. You don’t have to be a mathematician or have an accountant’s brain to use it. Excel does most of the hard stuff for you.
4002book.fm Page 3 Friday, March 24, 2006 11:36 PM Understanding Excel’s Interface 3 your screen will say Microsoft Excel – Book1. Book1 is the name temporarily assigned to a new workbook before you save it. Before you learn the names and purposes of all the boxes and columns, let’s first look at the different bars at the top of the Excel screen.
002book.fm Page 4 Friday, March 24, 2006 11:36 PM 4 Chapter 1 FIGURE 1.1 Navigating in Excel and Understanding the Interface The Five Bars at the Top of the Screen Standard Toolbar Menu Bar Title Bar Formatting Toolbar Formula Bar The toolbars include tooltips, which display when you rest your mouse pointer on any button. These tips tell you what the button does. If you don’t see tooltips displayed, choose Tools Options.
4002book.fm Page 5 Friday, March 24, 2006 11:36 PM Understanding Excel’s Interface 5 EXERCISE 1.1 Moving and Resizing Toolbars 1. From the View menu, choose Toolbars Formula Auditing. The Formula Auditing toolbar is displayed as a floating toolbar. 2. Move your mouse pointer to the top edge of the toolbar, until you see a double-headed black arrow. Click and hold this arrow and drag the edge of the toolbar up to make it taller. Then drag it down to restore it to its original shape. 3.
4002book.fm Page 6 Friday, March 24, 2006 11:36 PM 6 Chapter 1 Navigating in Excel and Understanding the Interface At the top of the task pane is a title bar that shows the name of the task pane you are seeing, and it has a black downward-pointing arrow to the right of it. Click that arrow to see the various choices, such as the New Workbook task pane and the Clip Art task pane. Table 1.1 briefly explains the purpose of each task pane.
4002book.fm Page 7 Friday, March 24, 2006 11:36 PM Understanding Excel’s Interface TABLE 1.1 7 Elements of the Task Pane (continued) Task Pane Purpose Clipboard This pane allows you to see up to 24 items stored in your Clipboard when you copy or cut data. New Workbook You use this pane when you create a new blank workbook or want to start a new workbook based on a template. Shared Workspace/ Document Updates Use these panes when you want to create a workspace for multiple users.
4002book.fm Page 8 Friday, March 24, 2006 11:36 PM 8 Chapter 1 FIGURE 1.2 Navigating in Excel and Understanding the Interface Sheet tabs allow you to quickly move from sheet to sheet. Tab Scrolling Buttons Sheet Tabs Horizontal Scrollbar Vertical Scrollbar When you have a large number of sheets, a quick way to move from one to another is to right-click the tab scrolling buttons and pick the sheet you want from the list that pops up.
4002book.fm Page 9 Friday, March 24, 2006 11:36 PM Navigating in Excel FIGURE 1.3 9 The Name box displays the cell address of the active cell. The lines you see defining the cells are called gridlines. They do not print by default, but are intended more for clarity when you view the data onscreen. When you click any cell, you see it is outlined in black, as shown in Figure 1.3.
4002book.fm Page 10 Friday, March 24, 2006 11:36 PM 10 Chapter 1 Navigating in Excel and Understanding the Interface EXERCISE 1.2 (continued) 4. Click Sheet3’s tab to see the February sales sheet. 5. Hold down the Ctrl key on your keyboard and tap the Page Up key. This is the keyboard shortcut to move to the previous sheet, so this moves you back to Sheet2. 6. Press Ctrl+Page Down to move back to Sheet3. 7.
4002book.fm Page 11 Friday, March 24, 2006 11:36 PM Navigating in Excel EXERCISE 1.3 (continued) It is important to note that if you click into a cell beyond the data area and just put one space in that cell, or enter data in that cell and then delete it, Excel will see this as the last cell that contains data, so Ctrl+End is not always reliable. 6. Click your mouse pointer in cell A3 to make that the active cell. Press Ctrl+→ to move to cell E3, which is the last cell in that row containing data. 7.
4002book.fm Page 12 Friday, March 24, 2006 11:36 PM 12 Chapter 1 Navigating in Excel and Understanding the Interface Excel has many keyboard shortcuts, and it is not possible to show you all of them. However, they are all included in Excel’s Help files. From the Help menu, choose Microsoft Excel Help and the Excel Help task pane will appear. In the Search For box, type keyboard shortcuts and click the green arrow button. Then click the Keyboard Shortcuts links.
4002book.fm Page 13 Friday, March 24, 2006 11:36 PM Adding and Editing Data in a Workbook FIGURE 1.4 Labels Cells can contain labels or values. Label Labels Values EXERCISE 1.4 Adding Data to a Worksheet 1. Open the file Exercise 1-1.xls. 2. Move to Sheet2 and click cell A9. On the Status bar at the bottom of your screen, on the left end you see the word Ready. This means the cell is now the active cell and ready for your input. 3. Type the word Florence.
4002book.fm Page 14 Friday, March 24, 2006 11:36 PM 14 Chapter 1 Navigating in Excel and Understanding the Interface EXERCISE 1.4 (continued) 6. In cells B9 through E9 enter the numbers shown here, pressing your Tab key after you type each one: In B9, type 450, and then press Tab to move to C9. Type 550.5, and then press Tab to move to D9. Type 675.95, and then press Tab to move to E9. Type 1000. 7.
4002book.fm Page 15 Friday, March 24, 2006 11:36 PM Adding and Editing Data in a Workbook 15 To the right of the Undo button is a small downward-pointing dropdown arrow. Click this arrow to open a list of all you’ve done recently, so you can undo more than one action if necessary. Exercise 1.5 illustrates ways to edit all or part of the content of a cell. EXERCISE 1.5 Editing Data in a Worksheet 1. Still inside Exercise 1-1.xls, select cell A9 by clicking it one time.
4002book.fm Page 16 Friday, March 24, 2006 11:36 PM 16 Chapter 1 Navigating in Excel and Understanding the Interface Managing Cells Because all cells in a column must be the same width and all cells in a row must be the same height, cell size management doesn’t include too many options. Most cell size management is governed by the specifications set on the row or column that contains the cell. You can work around some of this by merging cells, a process that is covered in Chapter 2.
4002book.fm Page 17 Friday, March 24, 2006 11:36 PM Adding and Editing Data in a Workbook 17 EXERCISE 1.6 (continued) The illustration here shows that the value 21000 that was in C7 has now moved up into C6, and that 12000.75 has moved up into C7. When you delete a cell, the gap must be filled. When you simply clear the contents of a cell, the cell still remains but is empty. 7. Click the Undo button so that 21000 is back in cell C7, as shown in the “Before” part in the illustration. 8.
4002book.fm Page 18 Friday, March 24, 2006 11:36 PM Chapter 1 18 Navigating in Excel and Understanding the Interface If numbers are too wide for a column, Excel will replace the digits with # signs if the cell to the right of it has something in it. However, if the cell to the right is empty, Excel will cut off the digits that exceed the width of the column, rounding the number off if necessary. It is important for you to know that what you see in a cell is not always what Excel sees in that cell.
4002book.fm Page 19 Friday, March 24, 2006 11:36 PM Adding and Editing Data in a Workbook 19 EXERCISE 1.7 (continued) 5. Drag the right edge of the column until all of the text in cell D7 is visible. Then drag the edge back to the left until the # signs reappear. When you drag the column headers with the resizing pointer, you see a tooltip that tells you the exact column width. The first number you see is the size in characters; the second number, in parentheses, is the size in pixels. 6.
4002book.fm Page 20 Friday, March 24, 2006 11:36 PM 20 Chapter 1 Navigating in Excel and Understanding the Interface EXERCISE 1.8 (continued) 3. Notice that Week Three is now cut off. Click any cell in column D. 4. From the Format menu, move down to Column and choose Autofit Selection. The column width adjusts to fit the widest item in that column. This is the same as double-clicking the gridline to the right of any column header, as we did in Exercise 1.7. 5.
4002book.fm Page 21 Friday, March 24, 2006 11:36 PM Adding and Editing Data in a Workbook 21 As I’m sure you’re aware, if you want to delete all the data in a column or row but leave the empty column or row in the worksheet, you just select the cells that have the data in them and press the Delete key, or use the Edit Clear command, or use the right-click shortcut menu.
4002book.fm Page 22 Friday, March 24, 2006 11:36 PM 22 Chapter 1 Navigating in Excel and Understanding the Interface EXERCISE 1.10 Hiding and Unhiding Rows and Columns 1. Move to Sheet1 in Exercise 1-1.xls and click any cell in column D. 2. From the Format menu, choose Column Hide. Notice column D is now hidden and the column headers show A, B, C, E. 3. If you immediately go back to the Format menu and choose Column Unhide, the column you just hid would return to view.
4002book.fm Page 23 Friday, March 24, 2006 11:36 PM Adding and Editing Data in a Workbook 23 Be aware that many of the actions performed in the exercises in this section cannot be undone by simply clicking the Undo button. If you move a sheet to the wrong location, delete the wrong sheet, rename a sheet incorrectly, and similar mistakes, and then try to backtrack using the Undo button, you will notice that it does nothing. In these cases, you will have to manually correct the action.
4002book.fm Page 24 Friday, March 24, 2006 11:36 PM 24 Chapter 1 Navigating in Excel and Understanding the Interface Once the worksheets have self-explanatory names and are categorized by color, the next important step is to put them in some logical order. For example, sheets that include data from different time periods should be in chronological order, or sheets that have no real logical order based on their contents should be in alphabetical order.
4002book.fm Page 25 Friday, March 24, 2006 11:36 PM Adding and Editing Data in a Workbook EXERCISE 1.12 (continued) 3. Your sheets should now be in order, beginning with January and ending with March. 4. Click the February Sales tab to make that sheet active. Right-click the tab and choose Insert. 5. When the Insert dialog box comes up, make sure Worksheet is selected and click OK. 6. You now have a new blank worksheet named Sheet1 inserted to the left of the February Sales sheet.
4002book.fm Page 26 Friday, March 24, 2006 11:36 PM 26 Chapter 1 Navigating in Excel and Understanding the Interface EXERCISE 1.12 (continued) June Sales 2nd Qtr Sales July Sales August Sales September Sales 3rd Qtr Sales October Sales November Sales December Sales 4th Qtr Sales Yearly Totals You may need to use the tab scrolling buttons to the left of your sheet tabs to see all of your tabs (see Figure 1.2). 17.
4002book.fm Page 27 Friday, March 24, 2006 11:36 PM Adding and Editing Data in a Workbook 27 Alternatively, sometimes you don’t want to permanently delete the sheet but instead just want it out of view temporarily. Exercise 1.13 shows you how to hide worksheets and then how to unhide them again. EXERCISE 1.13 Hiding and Unhiding Sheets 1. Click the 1st Qtr Sales sheet tab in Exercise 1-1.xls. 2. From the Format menu choose Sheet Hide. The sheet tab no longer appears. 3.
4002book.fm Page 28 Friday, March 24, 2006 11:36 PM 28 Chapter 1 Navigating in Excel and Understanding the Interface you to the dropdown box where you can choose different file format types when saving. Be sure to look through the type choices and understand them. EXERCISE 1.14 Using Save and Save As 1. Inside the current workbook that you have been editing, select File Save As. 2. You will see this dialog box: Choose a Location Create a New Folder Name the File Change the File Type 3.
4002book.fm Page 29 Friday, March 24, 2006 11:36 PM Moving and Copying Data 29 EXERCISE 1.14 (continued) 8. You are now inside the Chapter One folder, which is inside the Excel 2003 MOS Projects folder. 9. Now, it is time to name the file. In the File Name box near the bottom of the Save As box, type Exercise 1-14 to replace what is already there. 10. As you can see, the Save As Type box contains Microsoft Office Excel Workbook (*.xls). Click the dropdown arrow at the right end of that box.
4002book.fm Page 30 Friday, March 24, 2006 11:36 PM Chapter 1 30 Navigating in Excel and Understanding the Interface EXERCISE 1.15 Methods of Copying and Pasting 1. Open Excel and select File Open. Browse to find the file you saved at the end of the last exercise, named Exercise 1-14.xls, and open it. (If you did not do the last exercise or think you may have omitted some steps up to this point, extract Exercise 1-14.xls from the zipped file for this chapter.) 2. Go to the January Sales sheet.
4002book.fm Page 31 Friday, March 24, 2006 11:36 PM Moving and Copying Data 31 EXERCISE 1.15 (continued) 11. Move to the March Sales sheet and select cell A4 and paste. Note when you paste data into a cell that already contains data, the new data overwrites the old. 12. Follow the instructions in Exercise 1.14 to save the file (using the Save As command) into your Chapter One folder, but this time, name the file Exercise 1-16.xls. Leave the file open for the next exercise.
4002book.fm Page 32 Friday, March 24, 2006 11:36 PM 32 Chapter 1 Navigating in Excel and Understanding the Interface EXERCISE 1.16 (continued) 7. Cut the data, and then click cell A7 and paste. 8. Move to the March Sales sheet, and then highlight the data in cells A12 through E12 and cut it. 9. Right-click cell A7 and choose Insert Cut Cells. This pastes the cells in a new row above the cell you were in when you chose the Insert command.
4002book.fm Page 33 Friday, March 24, 2006 11:36 PM Moving and Copying Data 33 other formatting attached to the cells. Column widths must be pasted separately. After you have pasted the data, you will have to leave the range selected and choose Edit Paste Special, then select Column Widths in the Paste Special dialog box that opens. FIGURE 1.8 This is an example of a noncontiguous selection of cells and ranges. Exercise 1.
4002book.fm Page 34 Friday, March 24, 2006 11:36 PM 34 Chapter 1 Navigating in Excel and Understanding the Interface EXERCISE 1.17 (continued) 11. Move back to the March Sales sheet and you’ll see that the data you cut is gone. 12. Go to the 1st Qtr Sales sheet and select (highlight) the range B3:E3 (which may already be selected because pasting it there was the last action you did on that sheet). With that range selected, press the Delete key on your keyboard to remove it. 13.
4002book.fm Page 35 Friday, March 24, 2006 11:36 PM Moving and Copying Data EXERCISE 1.18 (continued) 3. Now try to copy that selection. You see this error message: 4. Click OK to clear the message. 5. You cannot copy noncontiguous selections. Try to cut it instead. You see this similar message: 6. Click OK to clear the message. 7. With the noncontiguous range selected, press Delete on your keyboard and the cells will be cleared. 8. Click the Undo button to bring the deleted data back. 9.
4002book.fm Page 36 Friday, March 24, 2006 11:36 PM 36 Chapter 1 Navigating in Excel and Understanding the Interface EXERCISE 1.18 (continued) 10. To deselect the selection, simply click in any cell that is not part of the selection. 11. Close the file and click Yes when Excel asks if you want to save it. This will overwrite the previous copy. Using the Office Clipboard The Windows Clipboard is rather limited; it holds only the last item you cut or copied.
4002book.fm Page 37 Friday, March 24, 2006 11:36 PM Moving and Copying Data EXERCISE 1.19 (continued) 4. Move to the January Sales sheet. Use one of the methods you learned earlier to get into Edit mode in cell A1 and change the content of the cell to read January Monthly Sales Report. Reminder: To get into Edit mode in a cell, do one of the following: Double-click the cell. Click the cell and press F2. Click the cell; then click in the Formula bar. 5.
4002book.fm Page 38 Friday, March 24, 2006 11:36 PM 38 Chapter 1 Navigating in Excel and Understanding the Interface EXERCISE 1.19 (continued) 15. Once you have completed all of the monthly sales sheets, move your mouse over the taskpane text that reads “Monthly Sales Report.” You will see a dropdown arrow to the right of the text. Click this arrow and choose Delete to remove this item from the Office Clipboard. 16. Move to the 1st Qtr Sales sheet and click cell A3.
4002book.fm Page 39 Friday, March 24, 2006 11:36 PM Moving and Copying Data FIGURE 1.9 39 Mouse pointers can make a big difference in Excel. White Plus Sign Black Four-Headed Arrow Black Plus Sign It’s definitely easier to understand these pointers in action, so you’ll get a chance to practice using them in Exercise 1.20. EXERCISE 1.20 Using the Different Mouse Pointers to Select, Move, and Copy Data 1. Open Excel with a new blank workbook showing. 2.
02book.fm Page 40 Friday, March 24, 2006 11:36 PM 40 Chapter 1 Navigating in Excel and Understanding the Interface EXERCISE 1.20 (continued) 8. Select cell A1 again and, without holding the mouse button down, move your pointer over cell Al’s edge until you see the black four-headed arrow. Click with this pointer and drag the cell over to cell F7. As you drag, the pointer changes to a white diagonal arrow and a tooltip shows the cell name you are over.
4002book.fm Page 41 Friday, March 24, 2006 11:36 PM Summary 41 EXERCISE 1.20 (continued) 16. Select cells A9 and A10, which now contain 307 and 308. Now use the black plus sign on the fill handle on A10 to drag both cells down to A15. When you select multiple cells, Excel recognizes the series and fills the series by default. 17. Click the Options button and choose Copy Cells. Now instead of filling the series, Excel just copies 307 and 308 over and over. 18.
4002book.fm Page 42 Friday, March 24, 2006 11:36 PM 42 Chapter 1 Navigating in Excel and Understanding the Interface Exam Essentials Know the terminology. Get familiar with the terms used in Excel so the MOS scenarios make sense to you. Remember to use tooltips to identify the names of everything onscreen. Learn the quickest way to do everything. Try all the methods for moving around the worksheets and find the ones that work best for you. Get familiar with where everything is located.
4002book.fm Page 43 Friday, March 24, 2006 11:36 PM Sample MOS Scenarios MOS Scenario 2: Add Worksheets and Find Data Rename the worksheet to March 2005, then add two more sheets and name them March 2004 and March 2003. Rearrange the sheets so they are in chronological order and make each tab a different color. Copy all of the data from the March 2005 sheet and paste it into both of the other sheets. Find all occurrences of The French Corner and change them to The French Riviera on all three sheets.
4002book.
