Office 2010 Library E-Book

John Walkenbach

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Serie: Bible
• Sprache: Englisch
• Veröffentlichungsjahr: 2010

Seitenzahl: 5418

Beschreibung

A must-have collection of Office 2010 application Bibles, written by the world?s leading experts Talk about a bargain! Office 2010 Library offers enormous savings on four invaluable resources that boast nearly 5,000 pages and cover the core Office programs: Excel, Access, PowerPoint, and Word. The world?s leading experts of these applications provide you with an arsenal of information on the latest version of each program. Three CDs are also included that feature bonus material, including helpful templates, worksheets, examples, and more to enhance your Microsoft Office 2010 experiences. * Features four essential books on the most popular applications included in the Office 2010 suite: Excel, Access, PowerPoint, and Word * Excel 2010 Bible?serves as an indispensable reference for Excel users, no matter your level of expertise, and updates you on the latest Excel tips, tricks, and techniques * Access 2010 Bible?offers a thorough introduction to database fundamentals and terminology * PowerPoint 2010 Bible?shows you how to use the new features of PowerPoint 2010 and make successful presentations * Word 2010 Bible?begins with a detailed look at all the new features in Word 2010 and then expends to cover more advanced, intricate topics Office 2010 Library presents you with all-encompassing coverage that you won?t find anywhere else!

Leseprobe

Excel® 2010 Bible

Table of Contents

Part I: Getting Started with Excel

Chapter 1: Introducing Excel

What Is Excel Good For?

What's New in Excel 2010?

Understanding Workbooks and Worksheets

Moving around a Worksheet

Introducing the Ribbon

Using Shortcut Menus

Customizing Your Quick Access Toolbar

Working with Dialog Boxes

Using the Task Pane

Creating Your First Excel Worksheet

Chapter 2: Entering and Editing Worksheet Data

Exploring the Types of Data You Can Use

Entering Text and Values into Your Worksheets

Entering Dates and Times into Your Worksheets

Modifying Cell Contents

Applying Number Formatting

Chapter 3: Essential Worksheet Operations

Learning the Fundamentals of Excel Worksheets

Controlling the Worksheet View

Working with Rows and Columns

Chapter 4: Working with Cells and Ranges

Understanding Cells and Ranges

Copying or Moving Ranges

Using Names to Work with Ranges

Adding Comments to Cells

Chapter 5: Introducing Tables

What Is a Table?

Creating a Table

Changing the Look of a Table

Working with Tables

Chapter 6: Worksheet Formatting

Getting to Know the Formatting Tools

Using Different Fonts to Format Your Worksheet

Changing Text Alignment

Using Colors and Shading

Adding Borders and Lines

Adding a Background Image to a Worksheet

Using Named Styles for Easier Formatting

Understanding Document Themes

Chapter 7: Understanding Excel Files

Creating a New Workbook

Opening an Existing Workbook

Saving a Workbook

Using AutoRecover

Specifying a Password

Organizing Your Files

Other Workbook Info Options

Closing Workbooks

Safeguarding Your Work

Excel File Compatibility

Chapter 8: Using and Creating Templates

Exploring Excel Templates

Understanding Custom Excel Templates

Chapter 9: Printing Your Work

Printing with One Click

Changing Your Page View

Adjusting Common Page Setup Settings

Adding a Header or Footer to Your Reports

Copying Page Setup Settings across Sheets

Preventing Certain Cells from Being Printed

Preventing Objects from Being Printed

Creating Custom Views of Your Worksheet

Part II: Working with Formulas and Functions

Chapter 10: Introducing Formulas and Functions

Understanding Formula Basics

Entering Formulas into Your Worksheets

Editing Formulas

Using Cell References in Formulas

Using Formulas in Tables

Correcting Common Formula Errors

Using Advanced Naming Techniques

Tips for Working with Formulas

Chapter 11: Creating Formulas That Manipulate Text

A Few Words about Text

Text Functions

Advanced Text Formulas

Chapter 12: Working with Dates and Times

How Excel Handles Dates and Times

Date-Related Worksheet Functions

Time-Related Functions

Chapter 13: Creating Formulas That Count and Sum

Counting and Summing Worksheet Cells

Basic Counting Formulas

Advanced Counting Formulas

Summing Formulas

Conditional Sums Using a Single Criterion

Conditional Sums Using Multiple Criteria

Chapter 14: Creating Formulas That Look Up Values

Introducing Lookup Formulas

Functions Relevant to Lookups

Basic Lookup Formulas

Specialized Lookup Formulas

Chapter 15: Creating Formulas for Financial Applications

The Time Value of Money

Loan Calculations

Investment Calculations

Depreciation Calculations

Chapter 16: Introducing Array Formulas

Understanding Array Formulas

Understanding the Dimensions of an Array

Naming Array Constants

Working with Array Formulas

Using Multicell Array Formulas

Using Single-Cell Array Formulas

Chapter 17: Performing Magic with Array Formulas

Working with Single-Cell Array Formulas

Working with Multicell Array Formulas

Part III: Creating Charts and Graphics

Chapter 18: Getting Started Making Charts

What Is a Chart?

Understanding How Excel Handles Charts

Creating a Chart

Hands On: Creating and Customizing a Chart

Working with Charts

Understanding Chart Types

Learning More

Chapter 19: Learning Advanced Charting

Selecting Chart Elements

User Interface Choices for Modifying Chart Elements

Modifying the Chart Area

Modifying the Plot Area

Working with Chart Titles

Working with a Legend

Working with Gridlines

Modifying the Axes

Working with Data Series

Creating Chart Templates

Learning Some Chart-Making Tricks

Chapter 20: Visualizing Data Using Conditional Formatting

About Conditional Formatting

Specifying Conditional Formatting

Conditional Formats That Use Graphics

Creating Formula-Based Rules

Working with Conditional Formats

Chapter 21: Creating Sparkline Graphics

Sparkline Types

Creating Sparklines

Customizing Sparklines

Specifying a Date Axis

Auto-Updating Sparklines

Displaying a Sparkline for a Dynamic Range

Chapter 22: Enhancing Your Work with Pictures and Drawings

Using Shapes

Using SmartArt

Using WordArt

Working with Other Graphic Types

Using the Equation Editor

Part IV: Using Advanced Excel Features

Chapter 23: Customizing the Excel User Interface

Customizing the Quick Access Toolbar

Customizing the Ribbon

Chapter 24: Using Custom Number Formats

About Number Formatting

Creating a Custom Number Format

Custom Number Format Examples

Chapter 25: Using Data Validation

About Data Validation

Specifying Validation Criteria

Types of Validation Criteria You Can Apply

Creating a Drop-Down List

Using Formulas for Data Validation Rules

Understanding Cell References

Data Validation Formula Examples

Chapter 26: Creating and Using Worksheet Outlines

Introducing Worksheet Outlines

Creating an Outline

Working with Outlines

Chapter 27: Linking and Consolidating Worksheets

Linking Workbooks

Creating External Reference Formulas

Working with External Reference Formulas

Avoiding Potential Problems with External Reference Formulas

Consolidating Worksheets

Chapter 28: Excel and the Internet

Understanding How Excel Uses HTML

Understanding the Different Web Formats

Opening an HTML File

Working with Hyperlinks

Using Web Queries

Other Internet-Related Features

Chapter 29: Sharing Data with Other Office Applications

Copying and Pasting

Copying from Excel to Word

Embedding Objects in a Worksheet

Embedding an Excel Workbook in a Word Document

Chapter 30: Using Excel in a Workgroup

Using Excel on a Network

Understanding File Reservations

Sharing Workbooks

Tracking Workbook Changes

Chapter 31: Protecting Your Work

Types of Protection

Protecting a Worksheet

Protecting a Workbook

VB Project Protection

Related Topics

Chapter 32: Making Your Worksheets Error-Free

Finding and Correcting Formula Errors

Using Excel Auditing Tools

Searching and Replacing

Spell Checking Your Worksheets

Using AutoCorrect

Part V: Analyzing Data with Excel

Chapter 33: Getting Data from External Database Files

Understanding External Database Files

Importing Access Tables

Retrieving Data with Query: An Example

Working with Data Returned by Query

Using Query without the Wizard

Learning More about Query

Chapter 34: Introducing Pivot Tables

About Pivot Tables

Creating a Pivot Table

More Pivot Table Examples

Learning More

Chapter 35: Analyzing Data with Pivot Tables

Working with Non-Numeric Data

Grouping Pivot Table Items

Creating a Frequency Distribution

Creating a Calculated Field or Calculated Item

Filtering Pivot Tables with Slicers

Referencing Cells within a Pivot Table

Creating Pivot Charts

Another Pivot Table Example

Producing a Report with a Pivot Table

Chapter 36: Performing Spreadsheet What-If Analysis

A What-If Example

Types of What-If Analyses

Manual What-If Analysis

Creating Data Tables

Using Scenario Manager

Chapter 37: Analyzing Data Using Goal Seeking and Solver

What-If Analysis, in Reverse

Single-Cell Goal Seeking

Introducing Solver

Solver Examples

Chapter 38: Analyzing Data with the Analysis ToolPak

The Analysis ToolPak: An Overview

Installing the Analysis ToolPak Add-in

Using the Analysis Tools

Introducing the Analysis ToolPak Tools

Part VI: Programming Excel with VBA

Chapter 39: Introducing Visual Basic for Applications

Introducing VBA Macros

Displaying the Developer Tab

About Macro Security

Saving Workbooks That Contain Macros

Two Types of VBA Macros

Creating VBA Macros

Learning More

Chapter 40: Creating Custom Worksheet Functions

Overview of VBA Functions

An Introductory Example

About Function Procedures

Executing Function Procedures

Function Procedure Arguments

Debugging Custom Functions

Inserting Custom Functions

Learning More

Chapter 41: Creating UserForms

Why Create UserForms?

UserForm Alternatives

Creating UserForms: An Overview

A UserForm Example

Another UserForm Example

More on Creating UserForms

Learning More

Chapter 42: Using UserForm Controls in a Worksheet

Why Use Controls on a Worksheet?

Using Controls

Reviewing the Available ActiveX Controls

Chapter 43: Working with Excel Events

Understanding Events

Entering Event-Handler VBA Code

Using Workbook-Level Events

Working with Worksheet Events

Using Non-Object Events

Chapter 44: VBA Examples

Working with Ranges

Working with Workbooks

Working with Charts

VBA Speed Tips

Chapter 45: Creating Custom Excel Add-Ins

What Is an Add-In?

Working with Add-Ins

Why Create Add-Ins?

Creating Add-Ins

An Add-In Example

Part VII: Appendixes

Appendix A: Worksheet Function Reference

Appendix B: What's on the CD-ROM

Appendix C: Additional Excel Resources

Appendix D: Excel Shortcut Keys

Excel® 2010 Bible

John Walkenbach

Excel® 2010 Bible

Published byWiley Publishing, Inc.10475 Crosspoint BoulevardIndianapolis, IN 46256www.wiley.com

Copyright © 2010 by Wiley Publishing, Inc., Indianapolis, Indiana

Library of Congress Control Number: 2010922573

Published simultaneously in Canada

ISBN: 978-0-470-47487-7

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, [phone], fax [phone]. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, [phone], fax [phone], or online at http://www.wiley.com/go/permissions.

Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or warranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including without limitation warranties of fitness for a particular purpose. No warranty may be created or extended by sales or promotional materials. The advice and strategies contained herein may not be suitable for every situation. This work is sold with the understanding that the publisher is not engaged in rendering legal, accounting, or other professional services. If professional assistance is required, the services of a competent professional person should be sought. Neither the publisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Website is referred to in this work as a citation and/or a potential source of further information does not mean that the author or the publisher endorses the information the organization or Web site may provide or recommendations it may make. Further, readers should be aware that Internet Web sites listed in this work may have changed or disappeared between when this work was written and when it is read.

For general information on our other products and services or to obtain technical support, please contact our Customer Care Department within the U.S. at [phone], outside the U.S. at [phone] or fax [phone].

Trademarks: Wiley, the Wiley logo, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other countries, and may not be used without written permission. Excel is a registered trademark of Microsoft Corporation in the United States and/or other countries. All other trademarks are the property of their respective owners. Wiley Publishing, Inc. is not associated with any product or vendor mentioned in this book.

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books.

This book is dedicated to Wormpicker, a gentleman and a scholar who one day will be a fine old-time fiddler.

About the Author

John Walkenbach is a bestselling Excel author who has published more than 50 spreadsheet books. He lives amid the saguaros, javelinas, rattlesnakes, bobcats, and gila monsters in Southern Arizona — but the critters are mostly scared away by his clawhammer banjo playing. For more information, Google him.

About the Technical Editor

Niek Otten started in data processing in 1967 in an insurance company. He ran into VisiCalc in 1980 and has been addicted to spreadsheets ever since. His first acquaintance with Excel (version 1!) was in 1985 on a Macintosh. Since 2005, Niek has been self-employed. He answers questions about Excel in newsgroups and forums, reviews and edits Excel books, writes articles, and develops Excel-related software, such as a high-performance actuarial add-in. Niek has been a Microsoft Most Valuable Professional (MVP) for Excel since 2001.

Credits

Project Editor

Rebecca Senninger

Acquisitions Editor

Katie Mohr

Technical Editor

Niek Otten

Senior Copy Editor

Teresa Artman

Editorial Manager

Leah Cameron

Editorial Director

Mary C. Corder

Vice President and Executive Group Publisher

Richard Swadley

Vice President and Publisher

Andy Cummings

Project Coordinator

Katie Crocker

Media Development Project Manager

Laura Moss

Media Development Assistant Project Manager

Jenny Swisher

Media Development Associate Producers

Josh FrankShawn PatrickDoug KuhnMarilyn Hummel

Proofreading and Indexing

Linda SeifertBroccoli Information Management

Thanks to everyone who bought the previous editions of this book. Your suggestions have helped make this edition the best one yet.

Thanks for purchasing Excel 2010 Bible. If you're just starting with Excel, you'll be glad to know that Excel 2010 is the easiest version ever.

My goal in writing this book is to share with you some of what I know about Excel, and in the process, make you more efficient on the job. The book contains everything that you need to know to learn the basics of Excel and then move on to more advanced topics at your own pace. You'll find many useful examples and lots of tips and tricks that I've accumulated over the years.

Is This Book for You?

The Bible series from Wiley Publishing, Inc. is designed for beginning, intermediate, and advanced users. This book covers all the essential components of Excel and provides clear and practical examples that you can adapt to your own needs.

In this book, I've tried to maintain a good balance between the basics that every Excel user needs to know and the more complex topics that will appeal to power users. I've used Excel for more than 20 years, and I realize that almost everyone still has something to learn (including myself). My goal is to make that learning an enjoyable process.

Software Versions

This book was written for Excel 2010 for Windows. Much of the information also applies to Excel 2007, but if you're using an older version of Excel, I suggest that you put down this book immediately and find a book that's appropriate for your version of Excel. The user interface changes introduced in Excel 2007 are so extensive that you'll probably be hopelessly confused if you use an earlier version.

Conventions This Book Uses

Take a minute to scan this section to learn some of the typographical and organizational conventions that this book uses.

Excel commands

Excel 2010 (like Excel 2007) features a “menu-less” user interface. In place of a menu system, Excel uses a context-sensitive Ribbon system. The words along the top (such as File, Insert, Page Layout, and so on) are known as tabs. Click a tab, and the Ribbon displays the commands for the selected tab. Each command has a name, which is (usually) displayed next to or below the icon. The commands are arranged in groups, and the group name appears at the bottom of the Ribbon.

The convention I use is to indicate the tab name, followed by the group name, followed by the command name. So, the command used to toggle word wrap within a cell is indicated as:

Home⇒Alignment⇒Wrap Text

You'll learn more about using the Ribbon user interface in Chapter 1.

Filenames, named ranges, and your input

Input that you make from the keyboard appears in bold. Named ranges appear in a monospace font. Lengthy input usually appears on a separate line. For example, I may instruct you to enter a formula such as the following:

=”Part Name: “ &VLOOKUP(PartNumber,PartList,2)

Key names

Names of the keys on your keyboard appear in normal type. When two keys should be pressed simultaneously, they're connected with a plus sign, like this: “Press Ctrl+C to copy the selected cells.” Here are the key names as I refer to them throughout the book:

Alt

down arrow

Num Lock

right arrow

End

Scroll Lock

Caps Lock

Home

PgDn

Shift

Ctrl

Insert

PgUp

Tab

Delete

left arrow

up arrow

The four “arrow” keys are collectively known as the navigation keys.

Functions

Excel built-in worksheet functions appear in uppercase, like this: “Note the SUM formula in cell C20.”

Mouse conventions

You'll come across some of the following mouse-related terms, all standard fare:

• Mouse pointer: The small graphic figure that moves onscreen when you move your mouse. The mouse pointer is usually an arrow, but it changes shape when you move to certain areas of the screen or when you're performing certain actions.

• Point: Move the mouse so that the mouse pointer is on a specific item: for example, “Point to the Save button on the toolbar.”

• Click: Press the left mouse button once and release it immediately.

• Right-click: Press the right mouse button once and release it immediately. The right mouse button is used in Excel to pop up shortcut menus that are appropriate for whatever is currently selected.

• Double-click: Press the left mouse button twice in rapid succession.

• Drag: Press the left mouse button and keep it pressed while you move the mouse. Dragging is often used to select a range of cells or to change the size of an object.

What the Icons Mean

Throughout the book, you'll see special graphic symbols, or icons, in the left margin. These call your attention to points that are particularly important or relevant to a specific group of readers. The icons in this book are as follows:

Note

This icon signals the fact that something is important or worth noting. Notes may alert you to a concept that helps you master the task at hand, or they may denote something that is fundamental to understanding subsequent material. n

Tip

This icon marks a more efficient way of doing something that may not be obvious. n

Caution

I use this symbol when a possibility exists that the operation I'm describing may cause problems if you're not careful. n

Cross-Reference

This icon indicates that a related topic is discussed elsewhere in the book. n

On the CD

This icon indicates that a related example or file is available on the companion CD-ROM. n

New Feature

This icon indicates a feature that is new to Excel 2010. n

How This Book Is Organized

Notice that the book is divided into six main parts, followed by four appendixes.

Part I: Getting Started with Excel: This part consists of nine chapters that provide background about Excel. These chapters are considered required reading for Excel newcomers, but even experienced users will probably find some new information here.

Part II: Working with Formulas and Functions: The chapters in Part II cover everything that you need to know to become proficient with performing calculations in Excel.

Part III: Creating Charts and Graphics: The chapters in Part III describe how to create effective charts. In addition, you'll find a chapter on the conditional formatting visualization features, and a chapter on the new Sparkline graphics.

Part IV: Using Advanced Excel Features: This part consists of ten chapters that deal with topics that are sometimes considered advanced. However, many beginning and intermediate users may find this information useful as well.

Part V: Analyzing Data with Excel: Data analysis is the focus of the chapters in Part IV. Users of all levels will find some of these chapters of interest.

Part VI: Programming Excel with VBA: Part VI is for those who want to customize Excel for their own use or who are designing workbooks or add-ins that are to be used by others. It starts with an introduction to recording macros and VBA programming and then provides coverage of UserForms, add-ins, and events.

Appendixes: The book has four appendixes that cover Excel worksheet functions, the contents of the book's CD-ROM, other Excel resources, and Excel shortcut keys.

How to Use This Book

Although you're certainly free to do so, I didn't write this book with the intention that you would read it cover to cover. Rather, it's a reference book that you can consult when:

• You're stuck while trying to do something.

• You need to do something that you've never done.

• You have some time on your hands, and you're interested in learning something new about Excel.

The index is comprehensive, and each chapter typically focuses on a single broad topic. If you're just starting out with Excel, I recommend that you read the first few chapters to gain a basic understanding of the product and then do some experimenting on your own. After you become familiar with Excel's environment, you can refer to the chapters that interest you most. Some readers, however, may prefer to follow the chapters in order.

Don't be discouraged if some of the material is over your head. Most users get by just fine by using only a small subset of Excel's total capabilities. In fact, the 80/20 rule applies here: 80 percent of Excel users use only 20 percent of its features. However, using only 20 percent of Excel's features still gives you lots of power at your fingertips.

What's on the Companion CD

This book contains many examples, and the workbooks for those examples are available on the companion CD-ROM, arranged in directories that correspond to the chapters. Refer to Appendix B for a complete list of the files.

In addition, the CD-ROM contains an electronic version of this book. It's a searchable PDF file that's a perfect companion for your notebook computer when you take your next cross-country flight.

Part I: Getting Started with Excel

The chapters in this part are intended to provide essential background information for working with Excel. Here, you'll see how to make use of the basic features that are required for every Excel user. If you've used Excel (or even a different spreadsheet program) in the past, much of this information may seem like review. Even so, it's possible that you'll find quite a few tricks and techniques.

IN THIS PART

Chapter 1

Introducing Excel

Chapter 2

Entering and Editing Worksheet Data

Chapter 3

Essential Worksheet Operations

Chapter 4

Working with Cells and Ranges

Chapter 5

Introducing Tables

Chapter 6

Worksheet Formatting

Chapter 7

Understanding Excel Files

Chapter 8

Using and Creating Templates

Chapter 9

Printing Your Work

Chapter 1: Introducing Excel

IN THIS CHAPTER

Understanding what Excel is used forWhat's new for Excel 2010Learning the parts of Excel's windowIntroducing the Ribbon user interface, shortcut menus, and dialog boxesNavigating Excel worksheetsIntroducing Excel with a quick hands-on session

This chapter serves as an introductory overview of Excel 2010. If you're already familiar with a previous version of Excel, reading this chapter is still a good idea. You'll find that Excel 2010 is very similar to Excel 2007. However, both Excel 2007 and Excel 2010 are different from every previous version — very different.

What Is Excel Good For?

Excel, as you probably know, is the world's most widely used spreadsheet program, and is part of the Microsoft Office suite. Other spreadsheet programs are available, but Excel is by far the most popular and has become the world standard.

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-numeric 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 graphical dashboards: Summarize a large amount of business information in a concise format.

• Creating graphics and diagrams: Use Shapes and the new SmartArt to create professional-looking diagrams.

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

What's New in Excel 2010?

When a new version of Microsoft Office is released, sometimes Excel gets lots of new features. And sometimes it gets very few new features. In the case of Office 2010, Excel got very few new features. Here's a quick summary of what's new in Excel 2010, relative to Excel 2007:

• 64-bit version: If your hardware (and Windows version) supports it, you can install the 64-bit version, which lets you create larger workbooks. Most people do not require the 64-bit version, and using it might cause some add-ins to not function.

• Sparkline charts: Create small in-cell charts to summarize a range of data graphically. See Chapter 21.

• Slicers: A new way to filter and display data in pivot tables, by clicking buttons. See Chapter 35.

• New pivot table formatting options: You have more control over the appearance of pivot table reports. See Chapter 35.