Microsoft Excel 2010 In Depth

1151 Pages·2010·66.21 MB·English

by  Bill Jelen

Microsoft®
Excel 2010
Bill Jelen, MrExcel
800 East 96th Street
Indianapolis, Indiana 46240
MICROSOFT® EXCEL® 2010 IN DEPTH
Copyright © 2010 by Que Publishing
Associate Publisher
All rights reserved. No part of this book shall be reproduced, stored in a
retrieval system, or transmitted by any means, electronic, mechanical, photo- Greg Wiegand
copying, recording, or otherwise, without written permission from the publisher.
Acquisitions Editor
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 Loretta Yates
this book, the publisher and author assume no responsibility for errors or omis-
Managing Editor
sions, nor is any liability assumed for damages resulting from the use of the
information contained herein. Sandra Schroeder
ISBN-13: 978-0-789-74308-4 Project Editor
ISBN-10: 0-789-74308-6
Seth Kerney
Library of Congress Cataloging-in-Publication data is on file.
Copy Editor
Printed in the United States of America
Barbara Hacha
First Printing: June 2010
Indexer
Trademarks
All terms mentioned in this book that are known to be trademarks or service Cheryl Lenser
marks have been appropriately capitalized. Que Publishing cannot attest to the
Proofreaders
accuracy of this information. Use of a term in this book should not be regarded
Apostrophe Editing Services
as affecting the validity of any trademark or service mark.
Debbie Williams
Warning and Disclaimer
Technical Editor
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 Bob Umlas
an “as is” basis. The author and the publisher shall have neither liability nor
Publishing Coordinator
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 Cindy Teeters
programs accompanying it.
Multimedia Developer
Bulk Sales Dan Scherf
Que Publishing offers excellent discounts on this book when ordered in quantity
Interior Designer
for bulk purchases or special sales. For more information, please contact
Anne Jones
U.S. Corporate and Government Sales
1-800-382-3419 Cover Designer
[email protected]
Anne Jones
For sales outside of the U.S., please contact
Page Layout
International Sales
Mark Shirar
[email protected]
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 Keyboard Shortcuts 65
6 The Excel Options Dialog 87
7 The Big Grid and File Formats 109
II Calculating with Excel
8 Understanding Formulas 123
9 Controlling Formulas 143
10 Understanding Functions 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 Sorting Data 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 Formatting Worksheets 875
31 Using Data Visualizations and Conditional Formatting 925
32 Using Excel Charts 953
33 Using Sparklines 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
CONTENTS
Introduction 1
I Changes in User Interface
1 The File Menu Becomes the Backstage View 7
Understanding “In” Versus “Out” Commands 7
Using Backstage View 8
Pressing the Esc Key to Close Backstage View 8
Using the Four Quick Commands in the Left Navigation 8
Opening Recent Files 9
One-Click Access to Recent Files 11
Recovering Unsaved Workbooks 13
Clearing the Recent Workbooks List 14
Getting Information About the Current Workbook 14
Correcting Special States Such as Disabled Macros and Links 15
Excel’s Automatic Trusting of a Document 16
Opening a File in the Protected View Sandbox 16
Marking a Workbook as Final to Prevent Editing 16
Finding Hidden Content Using the Document Inspector 17
Creating a New Workbook from a Template 18
Printing and Print Preview 18
Sharing Your Workbook Using Save & Send 20
Getting Updates and Help 21
2 The Ribbon Interface and Quick Access Toolbar 23
Using the Ribbon 23
Using Dialog Launchers and the 80/20 Rule 24
Using Flyout Menus and Galleries 25
The Ribbon Is Constantly Changing 27
Harnessing Contextual Ribbon Tabs 27
Resizing Excel Changes the Ribbon 29
Solving Common Ribbon Problems 30
You Cannot Find a Particular Command on the Ribbon 30
You Still Cannot Find the Command on the Ribbon 30
The Ribbon Takes Up Too Many Rows 31
You Do Not Like Where Something Is Located on the Ribbon 32
You Cannot See All Your Favorite Commands at Once 32
Using the Quick Access Toolbar 32
Changing the Location of the Quick Access Toolbar 33
Adding Favorite Commands to the Quick Access Toolbar 34
Knowing Which Commands Can Be on the Quick Access Toolbar 34
Removing Commands from the Quick Access Toolbar 34
Customizing the Quick Access Toolbar 35
Using the Excel Options to Customize the Quick Access Toolbar for All Workbooks 36
Customizing Icons for the Current Workbook Only 36
Filling Up the Quick Access Toolbar 37
Rearranging Icons on the Quick Access Toolbar 37
Resetting the Quick Access Toolbar 37
Assigning VBA Macros to Quick Access Toolbar Buttons 37
3 Using Other Excel Interface Improvements 39
Using Live Preview 40
Previewing Paste Using the Paste Options Gallery 41
Accessing the Gallery After Doing a Paste 42
Accessing the Paste Options Gallery from the Right-Click Menu 44
Accessing the Paste Options Gallery from the Paste Drop-Down 47
Using the Mini Toolbar to Format Selected Text 47
Getting the Mini Toolbar Back 49
Disabling the Mini Toolbar 49
Expanding the Formula Bar 50
Zooming In and Out on a Worksheet 51
Using the Status Bar to Add Numbers 52
Switching Between Normal View, Page Break Preview, and Page Layout View Modes 52
Using the New Sheet Icon to Add Worksheets 54
Dragging a Worksheet to a New Location 54
Inserting a Worksheet in the Middle of a Workbook 55
4 Customizing the Ribbon 57
Performing a Simple Ribbon Modification 57
Using a More Complex Ribbon Modification 59
Hiding/Showing Ribbon Tabs 62
Adding a New Ribbon Tab 62
Sharing Customizations with Others 63
Resetting Customizations 63
Questions About Ribbon Customization 63
5 Keyboard Shortcuts 65
Using New Keyboard Accelerators 65
Selecting Icons on the Ribbon 67
Selecting Options from a Gallery 67
Navigating Within Drop-Down Lists 68
Backing Up One Level Through a Menu 68
Dealing with Keyboard Accelerator Confusion 68
Selecting from Legacy Dialog Boxes 68
Using the Shortcut Keys 69
Using Excel 2003 Keyboard Accelerators 76
Invoking an Excel 2003 Alt Shortcut 77
Determining Which Commands Work in Legacy Mode 78
6 The Excel Options Dialog 87
Introducing the Excel Options Dialog 87
Getting Help with a Setting 90
Using AutoRecover Options 91
New Excel 2010 Options for Internationalization 91
New Excel 2010 Options for Performance 92
New Excel 2010 Options for Security 94
Ten Options to Consider 97
Five Excel Oddities 98
Guide to Excel Options 99
7 The Big Grid and File Formats 109
Excel Grid Limits 109
Why Are There Only 65,536 Rows in My Excel 2007 Spreadsheet? 110
Other Limits in Excel 2010 111
Tips for Navigating the Big Grid 113
Using Shortcut Keys to Move Around 113
Using the End Key to Navigate 113
Using the Current Range to Navigate 114
Using Go To to Navigate 114
Understanding the New File Formats 114
A Brief History of File Formats 115
Using the New Binary File Format: BIFF12 116
Using the New XML File Formats: XLSX and XLSM 116
Version Compatibility 118
Opening Excel 2010 Files in Excel 2002 or 2003 120
Minor Loss of Fidelity 120
Significant Loss of Functionality 121
Creating Excel 2010 File Formats in Excel 2003 121
Opening Excel 2010 Files in Excel 2007 122
II Calculating with Excel
8 Understanding Formulas 123
Getting the Most from This Chapter 123
Introduction to Formulas 124
Formulas Versus Values 124
Entering Your First Formula 125
Building a Formula 125
The Relative Nature of Formulas 126
Overriding Relative Behavior: Absolute Cell References 127
Using Mixed References to Combine Features of Relative and Absolute References 128
Using the F4 Key to Simplify Dollar Sign Entry 129
Three Methods of Entering Formulas 132
Enter Formulas Using the Mouse Method 133
Entering Formulas Using the Arrow-Key Method 133
Entering the Same Formula in Many Cells 135
Copying a Formula by Using Ctrl+Enter 135
Copying a Formula by Dragging the Fill Handle 136
Double-Click the Fill Handle to Copy a Formula 137
Use the Table Tool to Copy a Formula 138
9 Controlling Formulas 143
Formula Operators 143
Order of Operations 144
Stacking Multiple Parentheses 146
Understanding Error Messages in Formulas 147
Using Formulas to Join Text 149
Joining Text and a Number 150
Copying Versus Cutting a Formula 151
Automatically Formatting Formula Cells 154
Using Date Math 155
Troubleshooting Formulas 157
Highlighting All Formula Cells 157
Seeing All Formulas 158
Editing a Single Formula to Show Direct Precedents 160
Using Formula Auditing Arrows 160
Tracing Dependents 162
Using the Watch Window 162
Evaluate a Formula in Slow Motion 162
Evaluating Part of a Formula 163
Excel in Practice: Moving the Formula ToolTip 164
10 Understanding Functions 165
Working with Functions 167
The Formulas Tab in Excel 2010 167
Finding the Function You Need 168
Using AutoComplete to Find Functions 169
Using the Function Wizard to Find Functions 169
Getting Help with Excel Functions 170
Using In-Cell ToolTips 170
Using the Function Arguments Dialog 171
Using Excel Help 172
Using AutoSum 172
Potential Problems with AutoSum 173
Special Tricks with AutoSum 174
Using the AutoSum Drop-Down 175
Using the New General-Purpose Functions in Excel 176
LikeSUBTOTAL, but Better: AGGREGATE() 176
Added in Excel 2007: IFERROR() 177
Using Conditional Formulas with Multiple Conditions: SUMIFS(),AVERAGEIFS(), and
COUNTIFS() 178