From Barnes & Noble

The Barnes & Noble Review
Whatever your level of Excel experience, John Walkenbach’s Excel 2003 Bible will make you far more productive. It’s a complete course in Excel that’ll serve your needs well, whether you have upgraded to Excel 2003 or are still working with Excel 2002 or 2000.

Walkenbach may be the industry’s leading spreadsheet maven. He’s written more than 30 spreadsheet books and spent years as a consultant specializing in Excel application development, creating custom functions, debugging macros, and optimizing complex workbooks.

He also created the widely praised Power Utility Pak add-in. (Judge it for yourself. The software’s on this book’s accompanying CD-ROM, along with loads of bonus resources -- including 500 pages of additional Office 2003 content.)

Excel 2003 Bible begins with a soup-to-nuts introduction to Excel fundamentals: editing and formatting worksheet data, and so forth. Next, Walkenbach takes a far deeper look at formulas and functions. His coverage includes advanced naming techniques, manipulating text strings, counting cells, displaying using date/time serial numbers, creating frequency distributions, building conditional sums using multiple criteria, and much more. He also presents example-rich explanations of financial formulas such as PMT, RATE, NPER, and PV.

You’ll find in-depth coverage of charting and graphics; and Excel data analysis -- including database connectivity, PivotTables, What-if Analysis, Goal Seeking, Solver, and the Analysis Toolpak. Next, Walkenbach turns to advanced features such as conditional formatting, data validation, linking and consolidation, and Web queries. There’s a full chapter on collaboration: everything from file reservations and sharing to mailing and routing workbooks.

Don’t miss Walkenbach’s chapter on eliminating spreadsheet errors. (According to recent research, substantive mistakes are found in at least 86 percent of spreadsheets.) Here, Walkenbach covers both obvious errors -- #DIV/0!, #N/A, et cetera -- and the fundamentals of spreadsheet auditing.

VBA programming is the most powerful way to customize Excel for your own needs or those of others. Many Excel books satisfy themselves with a chapter on recording automated macros. Not this one. It covers everything from code entry to custom functions, UserForms to controls, events to building your own add-ins. There’s a full chapter of sample code: working with ranges, efficient loops, automatically generating charts, and much more.

Excel 2003 isn’t a massive update, but some of Microsoft’s improvements are significant. For example, Excel 2003 offers far deeper support for XML. New XML toolbars and taskpanes make it easy to import, export, and refresh XML content.

Excel 2003 Bible covers all that, starting with a practical introduction to XML and its business uses. You’ll learn two ways to import XML data: by using a map, and by importing to a list. You’ll learn how to export XML data to a wide range of locations, as well as the issues associated with saving files to XML format.

Simply put, if it can be done with Excel 2003, this book will show you how. Bill Camarda

Bill Camarda is a consultant, writer, and web/multimedia content developer. His 15 books include Special Edition Using Word 2000 and Upgrading & Fixing Networks for Dummies, Second Edition.

From the Publisher

• The most comprehensive guidebook available on the most popular spreadsheet program, fully updated to include all-new "X" features
• Written by the leading Excel guru known as "Mr. Spreadsheet," John Walkenbach, who has written more than thirty books and 300 articles on related topics and maintains the popular Spreadsheet Page at www.j-walk.com/ss
• The definitive reference book for beginning to advanced users, featuring expert advice and hundreds of examples, tips, techniques, shortcuts, work-arounds, and more
• Covers expanded use of XML and Web services to facilitate data reporting, analysis, importing, and exporting information
• Explores Excel programming for those who want advanced information
• CD-ROM includes all templates and worksheets used in the book, as well as sample chapters from all Wiley Office "X" related Bibles and useful third party software, including John Walkenbach's Power Utility Pak

Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.

Product Details

• Table of Contents
• Read a Sample Chapter

Table of Contents

Read a Sample Chapter

Excel 2003 Bible

John Wiley & Sons

Chapter One

This chapter serves as an introductory overview of Excel. It is intended primarily for those who have no experience with this product. But even if you're already familiar with Excel, you may find a few new wrinkles here. If you're moving up from Excel 2002, you'll also find a quick summary of the new features in Excel 2003.

What Is It Good For?

Excel, as you probably know, is a spreadsheet program and is part of the Microsoft Office suite. Several other spreadsheet programs are available, but Excel is by far the most popular.

Much of the appeal of Excel is due to the fact that it's so versatile. Excel's forte, of course, is performing numerical calculations, but Excel is also very useful for non-numerical applications. Here are just a few of the uses for Excel:

* Number crunching: Create budgets, analyze survey results, and perform just about any type of financial analysis you can think of.

* Creating charts: Create a wide variety of highly customizable charts.

* Organizing lists: Use the row-and-column layout to store lists efficiently.

* Accessing other data: Import data from a wide variety of sources.

* Creating graphics and diagrams: Use Excel AutoShapes to create simple (and not-so-simple) diagrams.

* Automating complex tasks: Perform a tedious task with asingle mouse click with Excel's macro capabilities.

Understanding Workbooks and Worksheets

The work you do in Excel is performed in a workbook file, which appears in its own window. You can have as many workbooks open as you need. By default, workbooks use an XLS file extension.

Each workbook is comprised of one or more worksheets, and each worksheet is made up of individual cells. Each cell contains a value, a formula, or text. Each worksheet is accessible by clicking the tab at the bottom of the workbook. In addition, workbooks can store chart sheets. A chart sheet displays a single chart and is also accessible by clicking a tab.

Newcomers to Excel are often intimidated by all of the different elements that appear within Excel's window. You'll soon see that the Excel screen really isn't all that difficult to understand after you learn what the various pieces do.

Figure 1-1 shows you the more important bits and pieces of Excel. As you look at the figure, refer to Table 1-1 for a brief explanation of the items shown in the figure.

Moving Around a Worksheet

This section describes various ways to navigate through the cells in a worksheet. Every worksheet consists of rows (numbered 1 through 65,536) and columns (labeled A through IV). After column Z comes column AA; after column AZ comes column BA, and so on. The intersection of a row and a column is a single cell. At any given time, one cell is the active cell. You can identify the active cell by its darker border, as shown in Figure 1-2. Its address (its column letter and row number) appears in the Name box. Depending on the technique that you use to navigate through a workbook, you may or may not change the active cell when you navigate.

Tip

The row and column headings of the active cell are displayed in different colors to make it easier to identify the row and column of the active cell.

Navigating with your keyboard

As you probably already know, you can use the standard navigational keys on your keyboard to move around a worksheet. These keys work just as you would expect: The down arrow moves the active cell down one row, the right arrow moves it one column to the right, and so on. PgUp and PgDn move the active cell up or down one full window. (The actual number of rows moved depends on the number of rows displayed in the window.)

Tip

You can scroll through the worksheet without changing the active cell by turning on Scroll Lock. This can be useful if you need to view another area of your worksheet and then quickly return to your original location. Just press Scroll Lock and use the direction keys to scroll through the worksheet. When you want to return to the original position (the active cell), press Ctrl+Backspace. Then, press Scroll Lock again to turn it off. When Scroll Lock is turned on, Excel displays SCRL in the status bar at the bottom of the window.

The Num Lock key on your keyboard controls how the keys on the numeric keypad behave. When Num Lock is on, Excel displays NUM in the status bar, and the keys on your numeric keypad generate numbers. Most keyboards have a separate set of navigational (arrow) keys located to the left of the numeric keypad. These keys are not affected by the state of the Num Lock key.

Table 1-2 summarizes all the worksheet movement keys available in Excel.

Navigating with your mouse

To change the active cell by using the mouse, click another cell; it becomes the active cell. If the cell that you want to activate is not visible in the workbook window, you can use the scrollbars to scroll the window in any direction. To scroll one cell, click either of the arrows on the scrollbar. To scroll by a complete screen, click either side of the scrollbar's scroll box. You also can drag the scroll box for faster scrolling.

Tip

If your mouse has a wheel on it, you can use the mouse wheel to scroll vertically. Also, if you click the wheel and move the mouse in any direction, the worksheet scrolls automatically in that direction. The more you move the mouse, the faster the scrolling. If you prefer to use the mouse wheel to zoom the worksheet, select Tools [right arrow] Options, click the General tab, and then select the Zoom on Roll with IntelliMouse check box.

Using the scrollbars or scrolling with your mouse doesn't change the active cell. It simply scrolls the worksheet. To change the active cell, you must click a new cell after scrolling.

Using the Excel Menus and Toolbars

If you've used other software, you will have no problem adapting to Excel. Its user interface (that is, the menus and toolbars) offers few surprises, and they work just like the other programs you've used.

In many cases, you can issue a particular command in several different ways. For example, if you want to save your workbook, you can use the menu (the File [right arrow] Save command), a shortcut menu (right-click the workbook's title bar and click Save), a toolbar button (the Save button on the Standard toolbar), or a shortcut key combination (Ctrl+S). The particular method you use is up to you.

Using menus

Excel, like most other Windows programs, has a menu bar located directly below the title bar (see Figure 1-3). This menu bar is always available and ready for your command. The Excel menus change, depending on what you're doing. For example, if you're working with a chart, the menus change to give you options that are appropriate for a chart. This all happens automatically, so you don't even have to think about it.

Using the menu is quite straightforward. Click the menu that you want to open, and it drops down to display menu items. Click the menu item to issue the command.

Tip

To issue a menu command from the keyboard, press Alt and then the menu's hot key. (The hot key is the underlined letter in the menu.) You can then press the appropriate hot key for a command on the menu. For example, to issue the Print command on the File menu, press Alt+F, followed by P.

Some menu items lead to an additional submenu; when you click the menu item, the submenu appears to its right. Menu items that have a submenu display a small triangle. For example, the View [right arrow] Toolbars command has a submenu, as shown earlier in Figure 1-3. Excel's designers incorporated submenus primarily to keep the menus from becoming too lengthy and overwhelming to users.

Sometimes, you'll notice that a menu item appears grayed out. This simply means that the menu item isn't appropriate for what you're doing. Nothing happens if you try to select such a menu item.

Menu items that are followed by an ellipsis (three dots) always display a dialog box. Menu commands that don't have an ellipsis are executed immediately. For example, the File [right arrow] Open command results in a dialog box because Excel needs more information about the command. Excel doesn't need any more information to execute the File [right arrow] Print Preview command, so Excel performs this command immediately, without displaying a dialog box.

Tip

The Excel menu bar is actually a toolbar in disguise. Consequently, you can move it to a new location if you prefer. To move the menu bar, just click the set of vertical gray dashes at the left side of the menu bar and drag it to its new location. You can drag the menu bar to any of the window borders or leave it free-floating.

Tip

When you click a menu, you may find that not all of the menu items are displayed. If this is the case, the adaptive menu option is in effect. I highly recommend that you turn off this option. To do so, choose View [right arrow] Toolbars [right arrow] Customize. In the Customize dialog box, click the Options tab and make sure that a check mark is next to Always Show Full Menus. Note to Microsoft: This is, without a doubt, the dumbest option you guys have ever come up with!

Using shortcut menus

Besides the omnipresent menu bar, Excel features a slew of shortcut menus, which you access by right-clicking just about anything within Excel. Shortcut menus don't contain every relevant command, just those that are most commonly used for whatever is selected.

As an example, Figure 1-4 shows the shortcut menu that appears when you right-click a cell. The shortcut menu appears at the mouse-pointer position, which makes selecting a command fast and efficient. The shortcut menu that appears depends on what you're doing at the time. For example, if you're working with a chart, the right-click shortcut menu contains commands that are pertinent to what is selected.

Using shortcut keys

Some menu items also have shortcut keys associated with them. For example, the File [right arrow] Save command's shortcut key combination is Ctrl+S. As you use Excel, you'll find that learning the shortcut keys for commands you use often can save you a lot of time.

The best way to learn the shortcut keys is to watch for them on the Excel menus. The most useful ones display next to the menu item when you open the menus.

Using toolbars

Excel includes convenient toolbars that provide another way of issuing commands. In many cases, a toolbar button is simply a substitute for a menu command. For example, the Copy button is a substitute for Edit [right arrow] Copy. Some toolbar buttons, however, don't have a menu equivalent. One example is the AutoSum button, which automatically inserts a formula to calculate the sum of a range of cells.

To learn what the toolbar buttons do, you can hold the mouse pointer over a toolbar button (but don't click it). A small box that tells you the name of the button appears. Often, this provides enough information for you to determine whether the button is what you want. If these toolbar tips do not display, choose Tools [right arrow] Customize to display the Customize dialog box. Click the Options tab, and place a check mark next to Show ScreenTips on Toolbars.

Table 1-3 lists some of Excel's more useful built-in toolbars.

Hiding or showing toolbars

By default, Excel displays two toolbars (named Standard and Formatting). You have complete control over which toolbars are displayed and where they are located. In addition, you can create custom toolbars, made up of buttons that you find most useful.

To hide or display a particular toolbar, choose View [right arrow] Toolbars or right-click any toolbar. Either of these actions displays a list of common toolbars (but not all toolbars). The toolbars that have a check mark next to them are currently visible. To hide a toolbar, click it to remove the check mark. To display a toolbar, click it to add a check mark.

For control over all toolbars, select Tools [right arrow] Customize. In the Customize dialog box, click the Toolbars tab to display a list of all available toolbars. Place a check mark next to the toolbars that you want to be displayed.

Moving toolbars

Toolbars can be moved to any of the four sides of the Excel window, or they can be free-floating. A free-floating toolbar can be dragged on-screen anywhere that you want. You also can change a toolbar's size simply by dragging any of its borders. To hide a free-floating toolbar, click its Close button.

Note

When a toolbar isn't free-floating, it's said to be docked. A docked toolbar is stuck to the edge of the Excel window and doesn't have a title bar. Therefore, a docked toolbar can't be resized.

To move a docked toolbar, click the toolbar's vertical gray dashes and drag. To move a free-floating toolbar, click and drag the toolbar's title bar. When you drag a toolbar toward the window's edge, it automatically docks itself there. When a toolbar is docked, its shape changes to a single row or single column.

Working with Dialog Boxes

Many Excel commands display dialog boxes. In fact, all menu items that end with an ellipsis (three dots) display a dialog box. A dialog box is simply the Excel way of getting more information from you. For example, if you choose View [right arrow] Zoom (which changes the magnification of the worksheet), Excel can't carry out the command until it finds out from you what magnification level you want.

The Excel dialog boxes vary in how they work. A few of them can remain on-screen as you work (for example, the Find dialog box, which appears when you select Edit [right arrow] Find). But most of Excel's dialog boxes must be dismissed before you can do anything. If the dialog box obscures an area of your worksheet that you need to see, simply click the dialog box's title bar and drag the box to another location.

When a dialog box appears, you make your choices by manipulating the controls. When you're finished, click the OK button (or press Enter) to continue. If you change your mind, click the Cancel button (or press Esc), and nothing further happens-it's as if the dialog box never appeared.

Understanding dialog box controls

Most people find working with dialog boxes to be quite straightforward and natural. If you've used other programs, you'll feel right at home. The controls can be manipulated either with your mouse or directly from the keyboard.

Figure 1-5 shows the Print dialog box, which contains most of the common dialog box controls you'll encounter. Table 1-4 describes these controls and a few others you may encounter.

Continues...

---

Excerpted from Excel 2003 Bible by John Walkenbach Excerpted by permission.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.

---