Excel 2000 in a Nutshell | O'Reilly Media

Content preview·Buy reprint rights for this chapter

Excel is one of a series of computer programs commonly referred to as "spreadsheet software." Spreadsheet programs have become quite popular, because they provide the ability to work with data, typically numeric data, by placing it in a series of rows and columns. The location where a specific row or column intersects is referred to as a cell. Each cell typically holds a specific value that could be text, numeric, logical, or a formula, as shown in Figure 1-1. Calculations can be performed on the values in specific cells. If the values used in a calculation change, Excel automatically recalculates.

Figure 1-1: Spreadsheet programs provide convenient methods for performing calculations and organizing related data

Excel provides a multitude of unique features, which are covered in detail in this book. Some of the most prominent features include:

Extensive File Compatibility: Although Excel workbooks have the file extension of XLS, Excel has the ability to open files from several different sources including all Microsoft Office products, HTML, and other major spreadsheet programs. Excel can also save workbooks in several different formats so that they can be opened by other programs. (See Chapter 3.)
Workbooks for Organizing Common Files: Excel uses workbooks to store multiple related worksheets (commonly referred to as spreadsheets by other programs) and charts. You can switch between different sheets in the workbook by clicking on the corresponding tab, as shown in Figure 1-2. By default, each workbook is created with three worksheets. Additional worksheets can be added using Insert → Worksheet and new charts are added using Insert → Chart. Refer to Chapter 6, for more information. The default worksheet names are Sheet1, Sheet2, and Sheet3.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

You will be installing Excel 2000 from either the Office 2000 or the Excel 2000 CD-ROM. When you insert either one of these CDs the installation process should begin automatically. The installation program will prompt you for your name and company name, request the CD key listed on the back of the CD case, and then prompt you for the components to load.

Office 2000 products provide a different approach to the installation process. Figure 1-3 shows the Excl installation options. The Selecting Features option presents a Windows Explorer tree where you select the method for installing each of the components. You can expand the component list by clicking on the + next to each list.

Figure 1-3 is from the installation of the Office 2000 Professional CD. If you purchased Excel as a separate product, you will not have the option of selecting the other Office products.

Figure 1-3: Select the Excel 2000 components to install

You can choose to install each Excel component in a number of ways:

Run from My Computer: This installs the selected component on your hard drive.
Run all from My Computer: Taking things one step further, this option installs the software and all of its components on your hard drive. This eliminates the need to keep the CD handy because everything is installed on your machine, but it does require more hard disk space.
Run from Network: You won't see this if you're installing on a stand-alone PC. This option lets you run Excel from a network server, and not from the local drive. You don't want to choose this unless you have continuous access to the network server, or you risk not being able to run the software later.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

When you open Excel using the icon or the Start Menu option, the program opens and presents a " blank" workbook. The word blank is emphasized because the workbook is opened based upon the default settings. If you want to overwrite those settings you can create a template called book.xlt . You can find more information about creating templates in Chapter 3.

If you select an existing workbook to open either using the Windows Explorer or My Computer the selected workbook is opened up within Excel. In order to do this, locate the folder that contains the workbook you want to open and double-click on the desired workbook. Again, keep in mind that if Excel is already open the selected workbook is opened although there is really only one instance of Excel running. The program remains open until you close all Excel workbooks.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Excel provides a multitude of commands that can be used to design your worksheet. These commands are available from the ten different menus covered extensively in the corresponding chapters within this book. This section provides an overview of these menus and provides some important shortcuts and tasks.

The File menu provides the means for dealing with the actual workbook file. This menu contains options for opening, closing, saving, and printing the workbook. Basically, this menu contains all of the options needed to deal with the actual workbook file -- whereas, the other menus provide the commands for manipulating the contents of the workbook.

The first six icons on the Standard toolbar are the most frequently used File menu commands. Although the commands are the same, the Print command on the toolbar and File → Print produce slightly different results. The File → Print option opens the Print dialog where you are able to verify the print settings. The Print command prints the selected print area to the default printer. Another difference exists between the New command on the Standard toolbar and the File → New option. File → New opens a dialog where you can select a template to apply to the new workbook and the New command on the toolbar opens a blank workbook based upon the default workbook settings. For more information about these menu options, refer to Chapter 3.

Section 1.3.1.1: File Task List

Create a new workbook based upon a template: Chapter 3
Open an existing file: Chapter 3.
Open a recently used file: Chapter 3
Save a workbook: Chapter 3
Specify the portion of the worksheet to print: Chapter 3
Print the selected portion of a worksheet: Chapter 3

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Before you start using Excel it is important to understand some of its basic terminology. Although many of the elements used in Excel are found in other Microsoft Windows programs, Excel does introduce a few new ones. For example, if you have used other Windows programs, the concepts of windows and the status bar are probably nothing new. But, if you are not familiar with spreadsheet programs, then formulas, functions, cells, and rows may present a degree of unfamiliarity.

This chapter provides an under-the-hood look at Excel by describing some of the basic features you will encounter. In this chapter, we explore the following main topics:

The Excel Window: Basically everything that happens within Excel occurs within this window.
The External Side of Excel: Use templates to create a default look for you workbooks.
Using Styles: Change the appearance of the data in your worksheet consistently using styles.
Customizing in Excel: Design your menus and toolbars to contain the commands you frequently use.

Just like every other Microsoft Windows program, everything that happens in Excel occurs within the Excel window. The Excel window is made up of all the standards windows features, as shown in Figure 2-1.

Figure 2-1: The Excel window resembles all other Microsoft programs

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Just like every other Microsoft Windows program, everything that happens in Excel occurs within the Excel window. The Excel window is made up of all the standards windows features, as shown in Figure 2-1.

Figure 2-1: The Excel window resembles all other Microsoft programs

Title Bar. The title bar displays across the top of the Excel window. It indicates the name of the current workbook. On the right side of the windows there are icons that minimize, maximize, or close the Excel window. When you minimize the window, the window is closed but it remains active and can be opened by selecting the corresponding icon on the taskbar. When you maximize the window the window is expanded to cover your entire monitor screen. When you select the close button the current workbook is closed; if there are no other workbooks open Excel is also closed.
Menu Bar. Contains a list of eight different menus. When you click on one of the words on the menu a list of additional menu items display. If you have a chart selected the Data menu is replaced with a Chart menu. Chapter 3 through Chapter 10 discuss the various options available on the menu bar.

Just like other toolbars available within Excel, the Menu bar can be moved by simply clicking on it and dragging it to the desired location.

Excel 2000 comes with new adaptive menus designed to provide access to commonly used commands and suppress those used less frequently. You can tell a menu is suppressed when the last item in the list is an arrow. You can expand the menu by clicking on the arrow. These adaptive menus are designed to make the menus less confusing by suppressing the less commonly used menu options. You can eliminate the adaptive menus, which we recommend, by selecting Tools

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Besides the menus and toolbars there are some external elements that affect the way Excel works. In order to effectively use Excel, you need to have a firm grasp on the concepts of worksheets, workbooks, and templates.

Even if you have used other spreadsheet software packages, if you are new to Excel the concept of workbooks and worksheets may be a little foreign. Excel uses the concept of a book that contains multiple related pages and creates workbooks that contain related sheets or worksheets. Each workbook contains different chart sheets.

You can change the way Excel opens by using switches. For example, if you always look at the Sales workbook, you may want to create a shortcut that opens up the workbook when you run Excel (C:\Program Files\Microsoft Office\Office\EXCEL.EXE or C:\AnnualSales\Sales.xls). You can find more information about using switches in Chapter 3.

By default a workbook is created with three worksheets. Workbooks have an extension of .xls . By default, Excel names workbooks Book1, Book2, etc. and worksheets are name Sheet1, Sheet2, etc. You can rename a workbook when you save it and you can rename a worksheet by right-clicking on the sheet tab and selecting the Rename option. You can switch between the worksheets by clicking on the corresponding sheet tab at the bottom of the window. Additional worksheets can be added using Insert → Worksheet (refer to Chapter 6). Chart sheets are created when you create a new chart using Insert → Chart and indicate that you want it placed on a separate sheet (refer to Chapter 6).

Although you can give a worksheet any unique name, you should avoid trying to alter the file extension (characters that appear after the period). Microsoft Windows recognizes all files with an extension of .xls as Excel workbooks and files with extensions of .xlt as Excel templates. If you click on a file with either one of these extensions it will be opened up within Excel.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

There are essentially three methods that can be used to select a style for a cell:

Formatting Toolbar icons
Style Tool
Format → Style

If you give the workbook to someone who doesn't have the fonts you used, Excel will attempt to open the workbook using a similar font. Unfortunately, you do not always get the desired results. Therefore, on workbooks you are going to distribute electronically use the standard Windows fonts: Arial, Courier New, Symbol, Times New Roman, and Wingdings.

There are three style icons that are placed on the Formatting Toolbar as a default: Comma Style, Currency Style, and Percent Style. To apply one of these styles, simply select the range you want to apply the style to and then select the appropriate icon.

If you have used styles in Word you probably remember that there is a Style Tool that always indicates the style of the current selection. Although it is initially hidden, that feature also exists within Excel. If you have any intention of working with styles in your workbooks I would highly recommend that you add the Style Tool to your toolbars, as illustrated in Figure 2-4.

Figure 2-4: You can use the Style Tool to select the desired style

You need to use the Customize dialog (Tools → Customize) to add the Style Tool to one of the toolbars, as outlined in the following steps:

Display the Customize dialog by selecting Tools → Customize.
Select the Commands tab.
Click on the Format category in the Categories list.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Although adaptive menus provide some customization of your environment by automatically suppressing the options you don't use, they are not the only type of customization available within Excel. By using the Tools → Customize command you can customize your Excel environment even more. Keep in mind that as you make some of these customizations some of the options selected affect all Office programs; others -- such as toolbar and menu customizations -- remain local to the program they are used in.

Make sure you check out the context menus that are available throughout Excel. These menus display when you right-click on a particular location. They contain options that are relevant to your current location. For example, if you right-click on a cell you see options for cutting, copying, pasting, adding a comment, formatting the cell, etc.

Use the Tools → Customize command to perform three different types of customizations to your Excel environment:

Tools → Customize → Options: Set general customization options that govern how Excel's personalized (adaptive) menus work and how icons appear on menus and toolbars.
Tools → Customize → Toolbars: Use this tab to create new toolbars, rename and delete existing toolbars, and reset default toolbars.
Tools → Customize → Commands: This tab lets you add, remove, and modify commands in Excel's menus and toolbars. It's also used to create new menus.

Access the Customize dialog quickly by right-clicking on any toolbar and choosing Customize from the context menu or by selecting View

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The File menu provides various options for dealing with workbook files. One of the most useful features available on this menu is the ability to print the contents of a specific worksheet. Unfortunately, printing within Excel 2000 is not as intuitive as it may appear. Before you select the File → Print option you need to make sure you specify the portion of your worksheet that you want to print using the File → Print Area option. You can verify the desired page layout by selecting File → Print Preview. If you don't like the margins or page breaks, you can make modifications directly on the Print Preview dialog.

If you have access to email, instead of printing a hardcopy of your workbook to give to another individual, you can use the File → Send To options to send a copy of the workbook to others. If you have access to Microsoft NetMeeting you can even send it to the participants in an online discussion, as long as you are the host of the discussion.

Excel 2000 works well with many different applications by providing the ability to both open files from these applications and save Excel workbooks in a compatible format. The File menu also provides options for dealing with various file types. Although Excel is typically used to work with workbooks, the ability to open and save a variety of different types of files allows you to share important information with various applications and earlier versions of Excel as well.

This chapter frequently mentions workbooks and worksheets. For more information about these terms, refer to Chapter 2.

With each version of Excel, there have been modifications made to the format that workbooks are saved in. Each version of Excel has been backwards compatible, meaning that it could always open and save in the format of a previous version. Because of this, Excel 2000 can open files from all previous versions of Excel, but, with the exception of Excel 97, earlier versions of Excel will not be able to open Excel 2000 formatted workbooks. Excel 2000 uses the same file format as Excel 97, which is why the two versions are compatible. Excel can also open and save to several other formats, as described later in this section. Unless otherwise stated, Excel has the ability to both open and save in the following specified formats using the File

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

With each version of Excel, there have been modifications made to the format that workbooks are saved in. Each version of Excel has been backwards compatible, meaning that it could always open and save in the format of a previous version. Because of this, Excel 2000 can open files from all previous versions of Excel, but, with the exception of Excel 97, earlier versions of Excel will not be able to open Excel 2000 formatted workbooks. Excel 2000 uses the same file format as Excel 97, which is why the two versions are compatible. Excel can also open and save to several other formats, as described later in this section. Unless otherwise stated, Excel has the ability to both open and save in the following specified formats using the File → Save As or the File → Open options:

Microsoft Excel Files: Saves in a format that can be used by users of both Excel 2000 and Excel 97.
Microsoft Excel 5.0/95 Workbook: Saves the selected workbook in the appropriate format for Excel 5.0/95. Any features that are specific to Excel 97 or Excel 2000 are removed from the workbook when it is saved.
Microsoft Excel 97-2000 & 5.0/95 Workbook: Saves the workbook in a file that can be used by Excel 97-2000 and Excel 5.0/95 users, although, Excel 97-2000 features that exist in the workbook are not available for the Excel 5.0/95 user.
Microsoft Excel 4.0/3.0/2.1 Worksheet: Saves only the first worksheet of the workbook in a format that can be opened by Microsoft Excel 4.0/3.0/2.1 and removes all Excel 97-2000-specific features from the worksheet.
Microsoft Excel 4.0 Workbook

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The Edit menu provides several different options for moving, locating, and modifying the contents of your worksheets. Each of these options is covered in detail within this chapter.

You can use the Edit → Links and Edit → Object options to work with OLE objects (Object Linking and Embedding) that have been inserted or linked to within your worksheet. If you need to insert a bunch of related data values into a row or column within your worksheet, such as a list of dates, you can select the Edit → Series option to have Excel quickly create the list of values.

Many of the options available on the Edit menu use the clipboard to store copied or cut information until it is pasted into another location.

To go along with the standard Windows Clipboard that is used by almost every Windows application, Office 2000 has added a new clipboard commonly referred to as the Office Clipboard. Both clipboards can store anything that you copy from Excel, namely text, graphics, and objects.

The Windows Clipboard is a temporary storage space available to all Windows programs. Text and graphics that are cut or copied in a Windows application are temporarily stored in the clipboard. Remember, temporary means that it is only there until you copy or cut in another location or shut down your computer. Once the information has been stored in the clipboard it can be pasted into any Windows application that accepts that type of data. For example, you can copy data from an Excel worksheet and paste it into a Word document.

You view the contents of the Windows Clipboard using the Clipboard Viewer program. This is a standard Windows program that can typically be found by selecting Start → Programs → Accessories. For more information, refer to Windows 98 in a Nutshell (O'Reilly & Associates).

The Office Clipboard, on the other hand, is only available for Office programs: Excel, Word, Access, Outlook, and PowerPoint. The Office Clipboard lets you copy up to 12 different items to the clipboard, and is only activated when you do one of the following:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The Windows Clipboard is a temporary storage space available to all Windows programs. Text and graphics that are cut or copied in a Windows application are temporarily stored in the clipboard. Remember, temporary means that it is only there until you copy or cut in another location or shut down your computer. Once the information has been stored in the clipboard it can be pasted into any Windows application that accepts that type of data. For example, you can copy data from an Excel worksheet and paste it into a Word document.

You view the contents of the Windows Clipboard using the Clipboard Viewer program. This is a standard Windows program that can typically be found by selecting Start → Programs → Accessories. For more information, refer to Windows 98 in a Nutshell (O'Reilly & Associates).

The Office Clipboard, on the other hand, is only available for Office programs: Excel, Word, Access, Outlook, and PowerPoint. The Office Clipboard lets you copy up to 12 different items to the clipboard, and is only activated when you do one of the following:

Copy and/or cut two different items consecutively in the same Office program.
Copy one item, paste the item, and then copy another item in the same Office program.
Copy the same item twice in succession.

When the Office Clipboard is activated, it displays as shown in Figure 4-1. You can quickly see the contents of each item in the clipboard by dragging the mouse across the icon. If the clipboard contains 12 items and you try to cut or copy another item, the clipboard prompts you to overwrite the first item in the clipboard or to not make the selected cut or copy.

Figure 4-1: Office Clipboard

Each time something is added to the activated Office Clipboard, the item is also copied to the Windows Clipboard. That way, if you open a non-Microsoft Office program you will be able to paste the last thing you cut or copied.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Excel allows you to select how a workbook is viewed or printed. This type of setting is called a view. Excel provides some basic view modes that are available on the View menu for all workbooks that you open. For example, the Normal option displays the entire contents of the workbook, with the exception of any cells that may be hidden. If you select the Page Break Preview option you are able to see exactly where the page breaks will occur in your worksheet if you print it, and make any desired modifications.

Excel also allows you to create custom view modes for each workbook that you open. These view modes are custom created for each workbook using the View → Custom Views option. For example, you might want to set a custom view that hides the individual sales figures for each employee and only displays the totals.

With each custom view you create, Excel stores the following information:

Active cell
Active worksheet
Widths of all columns within each worksheet in the workbook
Display options that are specified when you select Tools → Options
Size and position of the Excel window
Selected cells within the active worksheet
Hidden rows, hidden columns, and filter settings for a workbook, if you select the "Hidden rows, columns and filter settings" checkbox on the Add View dialog that displays when you create a new custom view (see View → Custom Views)
Print settings, if you select the Print settings checkbox on the Add View dialog that displays when you create a new custom view

View → Normal

Display the worksheet in standard mode so that modifications can be made. Typically you will use this option to switch back from the View

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The real power of an Excel worksheet lies in the many things you can add to it. The most powerful functionality that can be added to a worksheet is a formula. If you didn't use formulas in your worksheet all you'd have is just a big sheet of numbers. Formulas use a combination of the various functions available in Excel, including cell references, numeric and text values, and operators (such as +, -, etc). Although this chapter discusses the dialog that displays when you select Insert → Function, there is extensive information about functions covered in Part 3 of this book.

In addition to manipulating values, Excel provides the ability to create a graphical representation of data using charts. Not only can you select from one of the built-in chart types, you even create custom charts. Remember that after the chart is created, it needs to be modified using the options on the Chart menu covered in Chapter 10.

Insert → Cells

This option allows you to quickly add additional cells, rows, or columns at the location of the active cell, (the currently selected cell within your worksheet) using the Insert dialog shown in Figure 6-1. The number of cells, rows, or columns inserted is based on the number selected. For example, to insert three cells above the selected cell, highlight that cell and the two below it and select the shift cells down option.

Figure 6-1: Insert Dialog

Select one of the radio buttons to indicate how the blank cells should be inserted in the worksheet. Keep in mind that if you select to shift the cells right or down, the same movement will continue for the cells that follow. For example, if you shift cells right, the cells will be shifted right all the way across the worksheet. For example, in Figure 6-1, the contents of cells A7-A9 would be moved to cells B7-B9. The B column cell contents move to the corresponding cells in column C. This cell contents of all cells in the corresponding rows are moved right. Selecting the "Entire row" radio button inserts the row above the selection, just like selecting Insert

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Insert → Cells

This option allows you to quickly add additional cells, rows, or columns at the location of the active cell, (the currently selected cell within your worksheet) using the Insert dialog shown in Figure 6-1. The number of cells, rows, or columns inserted is based on the number selected. For example, to insert three cells above the selected cell, highlight that cell and the two below it and select the shift cells down option.

Figure 6-1: Insert Dialog

Select one of the radio buttons to indicate how the blank cells should be inserted in the worksheet. Keep in mind that if you select to shift the cells right or down, the same movement will continue for the cells that follow. For example, if you shift cells right, the cells will be shifted right all the way across the worksheet. For example, in Figure 6-1, the contents of cells A7-A9 would be moved to cells B7-B9. The B column cell contents move to the corresponding cells in column C. This cell contents of all cells in the corresponding rows are moved right. Selecting the "Entire row" radio button inserts the row above the selection, just like selecting Insert → Rows. Selecting the "Entire column" radio button inserts the column to the left of the selection, just like selecting Insert → Columns.

The actual size of each worksheet in Excel is fixed. A worksheet always contains 256 columns and 65,536 rows. Therefore, when you insert a new row or column a row or column is removed from the end of the worksheet. If the last row or column of the worksheet contains a value, you cannot insert a row or column. This can come in handy if you want to ensure that no one adds a column to your worksheet, simply place a value in column 256 (or row 65,536 to keep rows from being inserted.) You can move to the last row or column quickly by using Edit

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Insert → Rows

This is a quick method for inserting new rows in your worksheet. Make sure you have selected a cell in the row below where you want the new row inserted before selecting Insert → Rows. As the new rows are inserted, the rows below are moved down. To insert multiple rows simultaneously, simply select the desired number of rows before selecting Insert → Rows. As shown in Figure 6-2, where cells B4-B7 are highlighted you only need to highlight one cell in each row to indicate the number of rows you want to add. When Insert → Row is selected, four blank rows will be inserted above row 4. The new rows are always added above the active cell. The active cell is the first cell that was selected, in Figure 6-2, this cell is the one in the selection that is not highlighted (cell B4).

Figure 6-2: Highlight the number of rows you want to insert. Excel will add four new rows about between the Interest Rate and Monthly Payment rows

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Insert → Columns

This is a quick method for adding new columns to your worksheet. Select a cell in the column to the right of where you want to insert the new column before selecting Insert → Columns. To insert multiple columns simultaneously, simply select cells from the desired number of columns, as shown in Figure 6-3, to the right of where you want the new columns before selecting the option. For example, in Figure 6-3, three columns will be inserted between columns A and column B.

Figure 6-3: Highlight cells from the number of columns you want to insert

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Insert → Worksheet

When Excel creates a new workbook there are three worksheets that are added by default, unless you change the default setting by selecting Tools → Options and specifying a different number on the General tab (refer to Chapter 8). You can add additional worksheets to your workbook at anytime using Insert → Worksheet. If you want to add more than one, simply hold down the Ctrl key and click on the desired number of worksheet tabs before selecting this option.

When you add new worksheets to a workbook, Excel simply adds each new worksheet in front of the currently selected sheet. You can rearrange the order of the worksheets with Edit → Move or Copy. When you use the Edit → Move or Copy option, you are able to specify the exact order for the sheets currently available in the workbook.

Keep in mind you can customize the properties of the sheet that is added to your workbook by creating a worksheet template called sheet.xlt and placing it in the XLStart folder. To create a worksheet template, take a workbook and delete all but one worksheet. Make the desired modifications to the worksheet such as name of worksheet, column widths, named styles, etc. You can even place text on the worksheet template. Once you create you worksheet template, place it in one of the default template locations:

C:\windows\Profiles\user_name\Application Data\Microsoft\Templates.
The XLStart folder where Excel looks for the book.xlt template used to create workbooks when you select the New icon on the toolbar or the Workbook icon on the New dialog. This folder is typically located in

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Insert → Chart

Provides the showiest option within Excel, by helping you create a graphic representation of the data within your worksheet, workbook or multiple workbooks. By creating charts you are able to give others the ability to quickly compare values within your workbook.

Your worksheet should contain the data you want to place on the chart, before selecting Insert → Chart. You can modify the data as needed once the chart is created and the changes will be reflected in the chart. Also, before selecting Insert → Chart, you can highlight the data in your worksheet that you want to appear on the chart. This allows you to quickly see how the data will look as you are determining which chart type you want to select during the first step of the Chart Wizard. Just remember, the data should come before the chart. That way you don't have to retrofit you data into a chart that is not designed to hold it all.

When you select Insert → Chart, the Chart Wizard displays as shown in Figure 6-4. The Chart Wizard leads you through four different steps to create your new chart. Each step is a different dialog with multiple tab options. You can scroll between each of the steps using the Forward and Back buttons.

With all of the different selections available with the Chart Wizard, it can seem a little overwhelming. Don't be too concerned about the process because once the chart is created you can modify each of the selections as needed using the options on the Chart menu.

The first step in the Chart Wizard process is the selection of the type of chart you want to create from one of the two tabs. Each of the chart types listed on the Standard Types tab has multiple sub-types that can be selected. If you selected the chart data before selecting Insert → Chart you can quickly see what your data will look like on a particular chart type by selecting the type and then clicking the "Press and Hold to View Sample" button. If you select one of the Custom Type tab charts a sample of the chart with your selected data displays in the preview pane.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Insert → Page Break

Inserts page breaks in the worksheet at the specified location. Depending upon the location of the active cell, Excel either inserts a horizontal page break (between columns) or a vertical page break (between rows).

To insert a horizontal page break, select the cell in the first row of the worksheet that is to the right of where you want to insert the page break. To insert a vertical page break, select the cell in the first column of the worksheet below where you want the page break. You can remove a page break by selecting the correct cell, just like adding the page break and selecting Insert → Remove Page Break. To remove a vertical page break, you need to select a cell below the page break. Select a cell to the right of a page break for a horizontal page break, as shown in Figure 6-8 since B10 is selected the vertical page break between columns A and B will be removed. The Remove Page Break option only appears on the Insert menu when the active cell is either below or to the right of a page break. In other words, this option is only there if it is possible to remove a page break, such as shown in Figure 6-8.

Figure 6-8: Select Cell to Right of a Vertical Page Break to Remove it

The easiest way to see the page breaks for the worksheet is to select View → Page Break Preview. You can also move and remove page breaks in this mode.

If the active cell is not in the first row or column of the worksheet, Excel creates both a horizontal and vertical page break at that location. If you did not want both page breaks, the easiest way to remove one is with View

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Insert → Function

One of the most powerful options available within Excel is the ability to quickly perform a complex calculation by using one of the built-in functions that are available. Functions provide the ability to quickly perform complex calculations. For example, you can select a function to determine what you payment will be on a loan; you simply tell Excel what the interest rate, number of payments, and loan amount is and Excel uses the function to calculate your payment. The values that you supply to the function are commonly referred to as the function's arguments. For more information about functions and formulas, refer to Chapter 11.

The Insert → Function option adds a built-in function into the active cell to create a formula. When you select Insert → Function or Shift+F3 the Paste Function dialog, shown in Figure 6-9, displays a list of currently installed functions.

Figure 6-9: Paste Function Dialog

The functions listed on the Paste Function dialog are sorted into different categories to make it easier to locate the type of function you are looking for. Excel remembers the functions you have used most recently and lists them under the Most Recently Used category.

Excel provides a multitude of different functions that can be added to your formulas. You can add even more functions by selecting Tools → Add-Ins and then selecting the add-ins you want. (For example, Excel comes with an add-in called Analysis ToolPak that provides additional functions that can be added to Excel. All of the functions provided with Excel are covered in detail in Part III. Additional functions can be added by purchasing third-party packages, or even created using VBA (refer to Appendix A).

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Insert → Name

The Insert → Name option provides options for creating and working with named ranges of cells. By naming cells you don't have to remember the exact cell location where the information you want exists; you simply remember the name of the cell. For example, if cell C56 contains the total salaries for 1999, you could name that cell Salary to make it easier to reference. Once you name a cell, you can use the cell name in a function instead of the cell reference. For example, to determine the total costs, you could sum the columns name Salary, Rent, Utilities, and Supplies.

Define Option

The Define option allows you to assign a name to a specific cell or range of cells. Select the cells you want to name and then select Insert → Name → Define or Shift+F3 to display the Define Name dialog shown in Figure 6-12. By defining names for cells, you can select the name for use in a Formula, you can also quickly jump to a named location by selecting it from the Name box.

Figure 6-12: Define Name Dialog

Type the name for the selected cell or range of cells in the field under Names in workbook and select the Add button. Excel uses the specified name to refer to the range indicated in the "Refers to" field. For example, in Figure 6-12, you could type Costs for the range name in the "Names in workbook" field. If you want to add additional names, you can select the Collapse Dialog button next to the "Refers to" field and highlight a new range, or you can manually type the new range in the field. If you type the range in the field, start the range with the equal sign.

You can also create a name that represents a formula or a constant value. To do this type the desired name in the "Names in workbook" field and type the contents of the formula or constant value in the "Refers to" field. This can be a handy feature if you need to use the same formula at multiple locations within the worksheet.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Insert → Comment

Select the cell where you want to place the comment and then select either Insert → Comment or Shift+F2 to insert a comment box for the cell. When the comment is created it contains your name, or the name that was used when Excel was installed on your system, and space for you to enter the a comment, as shown in Figure 6-18. You can modify the entire comment, including modifying or removing the name.

When you insert a comment in a cell a red triangle displays in the upper-left corner of the cell, as shown in Figure 6-18. When you drag the mouse cursor across a cell that contains a comment, it displays on the worksheet as long as you have not hidden the comments. If you do not want to see the comment indicators you can hide them on the View tab of Tools → Options.

If you are sharing workbooks with other users, it is a good idea to have a name assigned for each user's comments.

Figure 6-18: Comment Box Displays on the Worksheet

You can see all the comments in the worksheet by selecting View → Comments. Keep in mind that if you eliminated the comment indicators on the worksheet with Tools → Options, as soon as you select View → Comments again the comment indicators will display again on the worksheet.

To remove a comment in a cell, right-click on the desired cell with the mouse and select the Delete Comment option. Of course, if you are using the Tools

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Insert → Picture

If you want to add graphics to your worksheet, such as the company logo, or a picture, you need to use Insert → Picture. Excel provides a variety of different types of graphics that can be added to your worksheet using the various options available on the Picture menu. Most of these features are available for all Microsoft Office products.

Clip Art

Insert → Picture → Clip Art allows you to select a picture, sound file, or motion image from the Clip Gallery application that is available to other Microsoft Office applications. When you select this option, the Insert ClipArt dialog displays, as shown in Figure 6-19.

Figure 6-19: Insert ClipArt Dialog

The various clips available are sorted into categories to make it easier to locate what you are looking for. If you cannot find the clip you are looking for you can search Microsoft's web site for more clips by selecting the Clips Online option. Keep in mind that if you insert a motion clip it will only appear animated if you save your workbook as a web page and view it through your web browser.

You can add more clips to the Clip Gallery by selecting the Import Clips option. This is a handy feature to use if you want to make your images available to other Microsoft Office Applications.

From File

Excel also allows you to add standard graphic files to your worksheet, by selecting the From File option. When you select this option the Insert Picture dialog opens so you can select the desired graphic file. The Insert Picture dialog resembles the Open dialog that displays when you select File

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Insert → Object

Excel allows you to insert an OLE (Object Linking and Embedding) object directly into your worksheet. This means that you are placing an object from another windows program into your worksheet. When you select the object, Excel provides you the ability to use the other programs editing tools to modify it. For example, Figure 6-23 shows a sample of a Microsoft Map object that has been embedded in a worksheet. When the object is added, the Microsoft Map toolbars display providing the ability to modify the object directly within Excel.

Figure 6-23: Microsoft Map Object with Editing Tools displayed within Excel

When you select Insert → Object, the Object dialog displays as shown in Figure 6-24. There are two different tabs that allow you to either create a new OLE object by selecting one of the object types listed on the Create New tab, or insert an existing object by locating the desired object on the Create from File tab.

Figure 6-24: Object Dialog

Only the programs on your machine that support OLE objects will be listed on the Create New tab. When you select one of the programs on the Create New tab, the program environment opens within your worksheet so you can create your OLE image. For example, in Figure 6-23 a map of Mexico was inserted directly into the worksheet using the Microsoft Map control.

If you select the Display as Icon checkbox on either tab an icon that corresponds to the image type displays in your worksheet. You need to click on the icon to actually see the image.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Insert → Hyperlink

Excel allows you to create hyperlinks, links to other files, within your worksheet. The hyperlinks can be linked to text or an image. When you select Insert → Hyperlink, or Ctrl+K the Insert Hyperlink dialog displays as shown in Figure 6-25. The Insert Hyperlink dialog access the Most Recently Used (MRU) list for Windows. The MRU list keeps track of the files and web pages that you have recently viewed. You can create a link by selecting one of this files or web pages or you can type in a new link.

Figure 6-25: Insert Hyperlink Dialog

The I

Table of Contents

Section 1.3.1.1: File Task List