Microsoft Excel 2010 In Depth.pdf

Microsoft ®

Excel 2010

Bill Jelen, MrExcel

800 East 96th Street

Indianapolis, Indiana 46240

MICROSOFT ® EXCEL ® 2010 IN DEPTH

retrieval system, or transmitted by any means, electronic, mechanical, photo-

copying, recording, or otherwise, without written permission from the publisher.

No patent liability is assumed with respect to the use of the information con-

tained herein. Although every precaution has been taken in the preparation of

this book, the publisher and author assume no responsibility for errors or omis-

sions, nor is any liability assumed for damages resulting from the use of the

information contained herein.

ISBN-13: 978-0-789-74308-4

ISBN-10: 0-789-74308-6

Library of Congress Cataloging-in-Publication data is on file.

Printed in the United States of America

First Printing: June 2010

Associate Publisher

Greg Wiegand

Acquisitions Editor

Loretta Yates

Managing Editor

Sandra Schroeder

Project Editor

Seth Kerney

Copy Editor

Barbara Hacha

Trademarks

All terms mentioned in this book that are known to be trademarks or service

marks have been appropriately capitalized. Que Publishing cannot attest to the

accuracy of this information. Use of a term in this book should not be regarded

as affecting the validity of any trademark or service mark.

Indexer

Cheryl Lenser

Proofreaders

Apostrophe Editing Services

Debbie Williams

Warning and Disclaimer

Every effort has been made to make this book as complete and as accurate as

possible, but no warranty or fitness is implied. The information provided is on

an “as is” basis. The author and the publisher shall have neither liability nor

responsibility to any person or entity with respect to any loss or damages aris-

ing from the information contained in this book or from the use of the CD or

programs accompanying it.

Technical Editor

Bob Umlas

Publishing Coordinator

Cindy Teeters

Multimedia Developer

Dan Scherf

Bulk Sales

Que Publishing offers excellent discounts on this book when ordered in quantity

for bulk purchases or special sales. For more information, please contact

U.S. Corporate and Government Sales

1-800-382-3419

corpsales@pearsontechgroup.com

For sales outside of the U.S., please contact

International Sales

international@pearsoned.com

Interior Designer

Anne Jones

Cover Designer

Anne Jones

Page Layout

Mark Shirar

Jake McFarland

Nonie Ratcliff

DEDICATION

To Bob Jelen

CONTENTS AT A GLANCE

Introduction 1

I Changes in User Interface

1 The File Menu Becomes the Backstage View 7

2 The Ribbon Interface and Quick Access Toolbar 23

3 Using Other Excel Interface Improvements 39

4 Customizing the Ribbon 57

5 KeyboardShortcuts 65

6 The Excel Options Dialog 87

7 The Big Grid and File Formats 109

II Calculating with Excel

8 UnderstandingFormulas 123

9 ControllingFormulas 143

10 UnderstandingFunctions 165

11 Using Everyday Functions: Math, Date and Time, and Text Functions 189

12 Using Powerful Functions: Logical, Lookup, and Database Functions 277

13 Using Financial Functions 343

14 Using Statistical Functions 389

15 Using Trig, Matrix, and Engineering Functions 503

16 Connecting Worksheets, Workbooks, and External Data 571

17 Using Super Formulas in Excel 599

18 Using Names in Excel 619

19 Fabulous Table Intelligence 643

III Business Intelligence

20 SortingData 667

21 Removing Duplicates and Filtering 679

22 Using Automatic Subtotals 697

23 Using Pivot Tables to Analyze Data 711

24 Using Slicers and Filtering a Pivot Table 731

25 Mashing Up Data with PowerPivot 753

26 Using What-If, Scenario Manager, Goal Seek, and Solver 791

27 Automating Repetitive Functions Using VBA Macros 817

28 More Tips and Tricks for Excel 2010 857

29 Tour of the Best Add-Ins for Excel 871

IV Visual Presentation of Data

30 FormattingWorksheets 875

31 Using Data Visualizations and Conditional Formatting 925

32 Using Excel Charts 953

33 UsingSparklines 983

34 Using SmartArt, Shapes, WordArt, and Text Boxes 997

35 Using Pictures and Clip Art 1023

V Sharing

36 Printing 1039

37 Excel Web App and Other Ways to Share Workbooks 1059

38 Saving Time Using the Easy-XL Program 1079

Index 1097

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: