Microsoft Excel 2010 Step by Step eBook - X-Files

it-

PUBLISHED BY Microsoft Press A Division of Microsoft Corporation One Microsoft Way Redmond, Washington 98052-6399 Copyright ? 2010 by Curtis Frye All rights reserved. No part of the contents of this book may be reproduced or transmitted in any form or by any means without the written permission of the publisher. Library of Congress Control Number: 2010924442

Printed and bound in the United States of America.

1 2 3 4 5 6 7 8 9 QWT 5 4 3 2 1 0

A CIP catalogue record for this book is available from the British Library.

Microsoft Press books are available through booksellers and distributors worldwide. For further information about international editions, contact your local Microsoft Corporation office or contact Microsoft Press International directly at fax (425) 936-7329. Visit our Web site at mspress. Send comments to mspinput@ .

Microsoft, Microsoft Press, Access, Encarta, Excel, Fluent, Internet Explorer, MS, Outlook, PivotChart, PivotTable, PowerPoint, SmartArt, SQL Server, Visual Basic, Windows and Windows Mobile are either registered trademarks or trademarks of the Microsoft group of companies. Other product and company names mentioned herein may be the trademarks of their respective owners.

The example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted herein are fictitious. No association with any real company, organization, product, domain name, e-mail address, logo, person, place, or event is intended or should be inferred.

This book expresses the author's views and opinions. The information contained in this book is provided without any express, statutory, or implied warranties. Neither the authors, Microsoft Corporation, nor its resellers, or distributors will be held liable for any damages caused or alleged to be caused either directly or indirectly by this book.

Acquisitions Editor: Juliana Aldous Developmental Editor: Devon Musgrave Project Editor: Valerie Woolley Editorial Production: Online Training Solutions, Inc. Technical Reviewer: Bob Dean; Technical Review services provided by Content Master, a member of CM Group, Ltd. Body Part No. X16-88507

it-

Contents

Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . vii Introducing Microsoft Excel 2010 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix Modifying the Display of the Ribbon . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxv Features and Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxxi Using the Practice Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxxiii Getting Help . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxxvii

1 Setting Up a Workbook

1

Creating Workbooks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2

Modifying Workbooks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7

Modifying Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

Customizing the Excel 2010 Program Window . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15

Zooming In on a Worksheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16

Arranging Multiple Workbook Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17

Adding Buttons to the Quick Access Toolbar . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18

Customizing the Ribbon . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20

Maximizing Usable Space in the Program Window . . . . . . . . . . . . . . . . . . . . . . 23

Key Points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27

2 Working with Data and Excel Tables

29

Entering and Revising Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30

Moving Data Within a Workbook . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34

Finding and Replacing Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38

Correcting and Expanding Upon Worksheet Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43

Defining Excel Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48

Key Points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53

What do you think of this book? We want to hear from you!

Microsoft is interested in hearing your feedback so we can continually improve our books and learning resources for you. To participate in a brief online survey, please visit:

learning/booksurvey

iii

it-

iv Contents

3 Performing Calculations on Data

55

Naming Groups of Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56

Creating Formulas to Calculate Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60

Summarizing Data That Meets Specific Conditions . . . . . . . . . . . . . . . . . . . . . . . . . . . 70

Finding and Correcting Errors in Calculations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74

Key Points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81

4 Changing Workbook Appearance

83

Formatting Cells . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84

Defining Styles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90

Applying Workbook Themes and Excel Table Styles . . . . . . . . . . . . . . . . . . . . . . . . . . 94

Making Numbers Easier to Read . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101

Changing the Appearance of Data Based on Its Value . . . . . . . . . . . . . . . . . . . . . . . 106

Adding Images to Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113

Key Points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119

5 Focusing on Specific Data by Using Filters

121

Limiting Data That Appears on Your Screen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122

Manipulating Worksheet Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128

Selecting List Rows at Random . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128

Summarizing Worksheets with Hidden and Filtered Rows . . . . . . . . . . . . . . . 129

Finding Unique Values Within a Data Set . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132

Defining Valid Sets of Values for Ranges of Cells . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135

Key Points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141

6 Reordering and Summarizing Data

143

Sorting Worksheet Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144

Organizing Data into Levels . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153

Looking Up Information in a Worksheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160

Key Points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165

7 Combining Data from Multiple Sources

167

Using Workbooks as Templates for Other Workbooks . . . . . . . . . . . . . . . . . . . . . . . 168

Linking to Data in Other Worksheets and Workbooks . . . . . . . . . . . . . . . . . . . . . . . 175

Consolidating Multiple Sets of Data into a Single Workbook . . . . . . . . . . . . . . . . . . 180

Grouping Multiple Sets of Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184

Key Points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187

it-

Contents v

8 Analyzing Alternative Data Sets

189

Defining an Alternative Data Set . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190

Defining Multiple Alternative Data Sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194

Varying Your Data to Get a Desired Result by Using Goal Seek . . . . . . . . . . . . . . . . 198

Finding Optimal Solutions by Using Solver . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201

Analyzing Data by Using Descriptive Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207

Key Points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209

9 Creating Dynamic Worksheets by Using PivotTables

211

Analyzing Data Dynamically by Using PivotTables . . . . . . . . . . . . . . . . . . . . . . . . . . . 212

Filtering, Showing, and Hiding PivotTable Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222

Editing PivotTables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 236

Formatting PivotTables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242

Creating PivotTables from External Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 250

Key Points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257

10 Creating Charts and Graphics

259

Creating Charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260

Customizing the Appearance of Charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267

Finding Trends in Your Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 274

Summarizing Your Data by Using Sparklines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 276

Creating Dynamic Charts by Using PivotCharts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 281

Creating Diagrams by Using SmartArt . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 286

Creating Shapes and Mathematical Equations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293

Key Points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 301

11 Printing

303

Adding Headers and Footers to Printed Pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 304

Preparing Worksheets for Printing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 309

Previewing Worksheets Before Printing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312

Changing Page Breaks in a Worksheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312

Changing the Page Printing Order for Worksheets . . . . . . . . . . . . . . . . . . . . . 314

Printing Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 318

Printing Parts of Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 322

Printing Charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 326

Key Points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 327

it-

 ................
................

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

Google Online Preview   Download