Beginning Microsoft Excel 2010

394 Pages·2010·34.623 MB·English

by  Abbbott Katz (auth.)

Beginning Microsoft
Excel 2010

■ ■ ■
Abbott Katz

i
Beginning Microsoft Excel 2010
Copyright © 2010 by Abbott Katz
All rights reserved. No part of this work may be reproduced or transmitted in any form or by any
means, electronic or mechanical, including photocopying, recording, or by any information
storage or retrieval system, without the prior written permission of the copyright owner and the
publisher.
ISBN-13 (pbk): 978-1-4302-2955-1
ISBN-13 (electronic): 978-1-4302-2956-8
Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1
Trademarked names, logos, and images may appear in this book. Rather than use a trademark
symbol with every occurrence of a trademarked name, logo, or image we use the names, logos, and
images only in an editorial fashion and to the benefit of the trademark owner, with no intention of
infringement of the trademark.
The use in this publication of trade names, trademarks, service marks, and similar terms, even if
they are not identified as such, is not to be taken as an expression of opinion as to whether or not
they are subject to proprietary rights.
President and Publisher: Paul Manning
Lead Editor: Ben Renow-Clarke
Technical Reviewer: Simon Murphy
Editorial Board: Clay Andres, Steve Anglin, Mark Beckner, Ewan Buckingham, Gary Cornell,
Jonathan Gennick, Jonathan Hassell, Michelle Lowman, Matthew Moodie, Duncan Parkes,
Jeffrey Pepper, Frank Pohlmann, Douglas Pundick, Ben Renow-Clarke, Dominic
Shakeshaft, Matt Wade, Tom Welsh
Coordinating Editor: Kelly Moritz
Copy Editor: Janet Gokay
Compositor: MacPS, LLC
Indexer: BIM Indexing & Proofreading Services
Artist: April Milne
Cover Designer: Anna Ishchenko
Distributed to the book trade worldwide by Springer Science+Business Media, LLC., 233 Spring
Street, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax [phone], e-mail orders-
[email protected], or visit www.springeronline.com.
For information on translations, please e-mail [email protected], or visit www.apress.com.
Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional
use. eBook versions and licenses are also available for most titles. For more information, reference
our Special Bulk Sales–eBook Licensing web page at www.apress.com/info/bulksales.
The information in this book is distributed on an “as is” basis, without warranty. Although every
precaution has been taken in the preparation of this work, neither the author(s) nor Apress shall
have any liability to any person or entity with respect to any loss or damage caused or alleged to be
caused directly or indirectly by the information contained in this work.
Downloadable workbooks for this book are available to readers at www.apress.com. You will need to
answer questions pertaining to this book in order to successfully download the workbooks.
ii
Contents at a Glance
■Contents at a Glance............................................................................................iv(cid:2)
■Contents................................................................................................................v(cid:2)
■About the Author..................................................................................................xi(cid:2)
■About the Technical Reviewer.............................................................................xii(cid:2)
■Acknowledgments..............................................................................................xiii(cid:2)
■Introduction .......................................................................................................xiv
■Chapter 1: Introduction to Excel............................................................................1(cid:2)
■Chapter 2: Getting Started and Getting Around the Worksheet...........................17(cid:2)
■Chapter 3: From Data Entry to Data Creation:
Formula Basics and Beyond..............................................................51(cid:2)
■Chapter 4: Keeping Up Appearances—Formatting the Worksheet.....................87(cid:2)
■Chapter 5: The Stuff Of Legend—Charting in Excel...........................................155(cid:2)
■Chapter 6: Setting the Table: Database Features of Excel 2010 .......................201(cid:2)
■Chapter 7: Working With Multiple Sheets.........................................................239(cid:2)
■Chapter 8: PivotTables and Pivot Charts...........................................................265(cid:2)
■Chapter 9: Getting It On Paper—Printing in Excel 2010...................................309(cid:2)
■Chapter 10: Taking it to the Cloud: Sharing and
Collaborating on the Internet........................................................337(cid:2)
■Appendix A: Working With Range Names..........................................................351(cid:2)
■Appendix B: Keyboard Shortcuts......................................................................361(cid:2)
■Appendix C: Error Messages.............................................................................369(cid:2)
■Index.................................................................................................................375(cid:2)

iv
Contents

■Contents at a Glance............................................................................................iv(cid:2)
■Contents................................................................................................................v(cid:2)
■About the Author..................................................................................................xi(cid:2)
■About the Technical Reviewer.............................................................................xii(cid:2)
■Acknowledgments..............................................................................................xiii(cid:2)
■Introduction........................................................................................................xiv
■Chapter 1: Introduction to Excel............................................................................1(cid:2)
Making the Acquaintance...............................................................................................1(cid:2)
Unlocking Your Inner Worksheet.....................................................................................1(cid:2)
The Pep Talk...................................................................................................................4(cid:2)
Interacting with the Interface.........................................................................................5(cid:2)
Tab Talk..........................................................................................................................7(cid:2)
Something New, Something Old.....................................................................................9(cid:2)
Keeping Tabs…on a New One......................................................................................10(cid:2)
Cool QAT........................................................................................................................12(cid:2)
Understood in Context...................................................................................................13(cid:2)
Alt-ered Consciousness................................................................................................14(cid:2)
■Chapter 2: Getting Started and Getting Around the Worksheet...........................17(cid:2)
More Addresses Than the Phone Book—Cells, and How to Get There.........................17(cid:2)
Getting Around..............................................................................................................19(cid:2)
Key Points.....................................................................................................................21
v
■ CONTENTS
Ranges: A Select Tool...................................................................................................24(cid:2)
Data Entry: Getting Started...........................................................................................29(cid:2)
(cid:2)
Entering Text: Trespassing Allowed.....................................................................................................30
Numbers are (a little) Different.....................................................................................36(cid:2)
Relocating the Data: Copying and Moving....................................................................37(cid:2)
Having Your Fill.............................................................................................................41(cid:2)
There’ll Be Some Changes Made—Editing Cells..........................................................46(cid:2)
■Chapter 3: From Data Entry to Data Creation:
Formula Basics and Beyond...............................................................51(cid:2)
(cid:2)
Cell references explained.....................................................................................................................52
Ordering Up Your Results..............................................................................................54(cid:2)
(cid:2)
Not Just Your Average Function...........................................................................................................62
(cid:2)
Making Range Values COUNT..............................................................................................................63
(cid:2)
MAX and MIN—Recording Highs and Lows.........................................................................................64
(cid:2)
Absolute References: Absolutely Important.........................................................................................72
More of the Same.........................................................................................................73(cid:2)
(cid:2)
You Could Look It Up............................................................................................................................79
(cid:2)
If: Worth Knowing—No Ifs, Ands, or Buts............................................................................................83
(cid:2)
In Conclusion…...................................................................................................................................85
■Chapter 4: Keeping Up Appearances—Formatting the Worksheet.....................87(cid:2)
You’ve Got Designs on Your Worksheet........................................................................87(cid:2)
(cid:2)
The Font Button Group: A Closer Look.................................................................................................89
Borderline Command....................................................................................................93(cid:2)
Getting Oriented..........................................................................................................104(cid:2)
Excel Has Got Your Number(s)....................................................................................118(cid:2)
Dates—The Long and the Short of It..........................................................................124(cid:2)
Time Is On Your Side—Yes It Is..................................................................................125(cid:2)
A New Kind of Copy—and Paste.................................................................................131(cid:2)
Style Setter.................................................................................................................137(cid:2)
vi
■ CONTENTS
Formatting—With Conditions.....................................................................................139(cid:2)
Just a Bit More…........................................................................................................152(cid:2)
P. S..............................................................................................................................154(cid:2)
IN CONCLUSION….......................................................................................................154(cid:2)
■Chapter 5: The Stuff Of Legend—Charting in Excel...........................................155(cid:2)
Starting Charting.........................................................................................................155(cid:2)
(cid:2)
Making a Chart of Our Own................................................................................................................158
(cid:2)
Changing The Chart—It’s Your Call...................................................................................................164
The Design Tab—A Closer Look.................................................................................172(cid:2)
(cid:2)
Change Chart Type.............................................................................................................................172
(cid:2)
Save As Template..............................................................................................................................173
(cid:2)
Switch Row/Column...........................................................................................................................174
(cid:2)
Select Data.........................................................................................................................................175
(cid:2)
Chart Layouts.....................................................................................................................................176
(cid:2)
Chart Styles........................................................................................................................................177
(cid:2)
Move Chart.........................................................................................................................................178
Changing the Chart Default—and the 2-Second Chart...............................................178(cid:2)
The Layout Tab............................................................................................................179(cid:2)
Working with Chart Labels..........................................................................................184(cid:2)
Axes to Grind...............................................................................................................187(cid:2)
The Format Tab—Getting Your Objects in Shape.......................................................193(cid:2)
Sparklines: Mini-Charts with Big Impact....................................................................194(cid:2)
You Win Some, You Lose Some..................................................................................198(cid:2)
In Conclusion…..........................................................................................................200(cid:2)
■Chapter 6: Setting the Table: Database Features of Excel 2010 .......................201(cid:2)
Sorting—Sort Of Easy.................................................................................................202(cid:2)
(cid:2)
Using Header Rows............................................................................................................................204
(cid:2)
Sorting by More than One Field.........................................................................................................205
The AutoFilter: Picking and Choosing Your Data.........................................................209(cid:2)
vii
■ CONTENTS
Playing—or Plying—the Numbers.............................................................................213(cid:2)
The Advanced Filter—Setting Your Data Aside..........................................................215(cid:2)
Table Talk...................................................................................................................218(cid:2)
(cid:2)
Creating a Table.................................................................................................................................219
(cid:2)
Using Table Styles..............................................................................................................................220
(cid:2)
Adding a Total Row............................................................................................................................221
(cid:2)
Examining the other Table Style Options...........................................................................................223
At the Risk of Repeating Yourself: The Remove Duplicates Option............................226(cid:2)
Data Validation: Improving Your Entrée to Data Entry.................................................228(cid:2)
(cid:2)
Using Data Validation.........................................................................................................................228
(cid:2)
Adding Data Entry Rules....................................................................................................................231
(cid:2)
Adding an Input Message...................................................................................................................234
(cid:2)
Using the Error Alert Option...............................................................................................................234
(cid:2)
Adding a Validation Rule to Existing Data..........................................................................................236
In Conclusion…..........................................................................................................237(cid:2)
■Chapter 7: Working With Multiple Sheets.........................................................239(cid:2)
Adding To Sheets—Inserting Rows, Columns and Cells............................................240(cid:2)
(cid:2)
Inserting a Column.............................................................................................................................240
(cid:2)
Inserting a Row..................................................................................................................................241
(cid:2)
Deleting Rows and Columns..............................................................................................................241
(cid:2)
Inserting and Deleting Cells...............................................................................................................241
(cid:2)
Hiding Rows and Columns—and Getting them Back.........................................................................242
Multiple Worksheet Basics.........................................................................................243(cid:2)
(cid:2)
Inserting a New Worksheet................................................................................................................244
(cid:2)
Busting a (Sheet) Move......................................................................................................................245
(cid:2)
Hiding Worksheets.............................................................................................................................246
(cid:2)
Grouping Worksheets.........................................................................................................................247
Far-Flung Formulas: Working with Multi-Sheet Cell References................................247(cid:2)
(cid:2)
Doing a Multi-sheet Calculation.........................................................................................................248
(cid:2)
Extending Your Reach: Referring to Cells in Different Workbooks.....................................................250
viii
■ CONTENTS
The Watch Window—Spying On Your Own Data........................................................251(cid:2)
Protect Your Cells From Unwanted Intruders—Even Yourself....................................253(cid:2)
(cid:2)
Protecting a Sheet..............................................................................................................................253
(cid:2)
Protecting a Whole Workbook............................................................................................................258
Consolidating Your Data—Getting It All Together.......................................................259(cid:2)
In Conclusion…..........................................................................................................264(cid:2)
■Chapter 8: PivotTables and Pivot Charts...........................................................265(cid:2)
Starting Out with PivotTables.....................................................................................265(cid:2)
What’s in a Name?......................................................................................................267(cid:2)
Constructing a PivotTable: Let’s Go............................................................................268(cid:2)
(cid:2)
Inserting the Table.............................................................................................................................269
(cid:2)
Setting Up the PivotTable...................................................................................................................271
(cid:2)
Updating the Pivot Report..................................................................................................................274
Filters Again-PivotTable Style.....................................................................................285(cid:2)
Your Very Own Top 10 List..........................................................................................288(cid:2)
The Report Filter—Getting on Top of the PivotTable..................................................289(cid:2)
The Slicer—Filter Deluxe............................................................................................293(cid:2)
Grouping the Data.......................................................................................................296(cid:2)
Formatting the Pivot Report—Values and All.............................................................300(cid:2)
Pivot Charts.................................................................................................................302(cid:2)
In Conclusion…..........................................................................................................307(cid:2)
■Chapter 9: Getting It On Paper— Printing in Excel 2010..................................309(cid:2)
Hard Copy? Pretty Easy...............................................................................................309(cid:2)
The Print Area Option..................................................................................................311(cid:2)
Margin-al Utility..........................................................................................................314(cid:2)
Printing As You See Fit................................................................................................316(cid:2)
Headers and Footers—Getting to the Bottom (and the Top) of Your Printout.............318(cid:2)
Title Search.................................................................................................................323(cid:2)
ix
■ CONTENTS
There’s More Than One Way to View A Worksheet.....................................................327(cid:2)
(cid:2)
Gimme a (Page) Break—Another View..............................................................................................329
Customize Your View, Too...........................................................................................334(cid:2)
In Conclusion…..........................................................................................................336(cid:2)
■Chapter 10: Taking it to the Cloud: Sharing and Collaborating on the Internet 337(cid:2)
Getting There..............................................................................................................338(cid:2)
Permission Granted: Sharing Your Workbooks...........................................................345(cid:2)
Hey—You! Get Onto My Cloud!..................................................................................348(cid:2)
In Conclusion…..........................................................................................................349(cid:2)
■Appendix A: Working With Range Names..........................................................351(cid:2)
What’s in a Name? Plenty, if it’s a Range...................................................................351(cid:2)
Naming Many Ranges – at the Same Time.................................................................357(cid:2)
The Name Manager – Tracking Your Ranges..............................................................359(cid:2)
In Conclusion..............................................................................................................360(cid:2)
■Appendix B:Keyboard Shortcuts.......................................................................361(cid:2)
Control Key Combinations...........................................................................................361(cid:2)
Function Keys.............................................................................................................363
Other shortcut keys.....................................................................................................365(cid:2)
■Appendix C:Error Messages..............................................................................369(cid:2)
Nobody’s Perfect.........................................................................................................369
■Index.................................................................................................................375
(cid:2)
x
■ CONTENTS
About the Author
■ Abbott Katz A native New Yorker, Abbott Katz currently lives in London
and has introduced Excel to numerous corporate and university classes on
both sides of the Atlantic. He has written for a wide range of publications,
including New York Newsday, the (UK) Times Higher Educational, and
insidehighered.com, and holds a doctorate in sociology from SUNY Stony
Brook in New York. Prior to moving to London in 2005 he served as the
Deputy Chair of the Sociology Department at Touro College, while at the
same time teaching the introductory computer course at Queens College. His
interests include jazz and baseball, but like all Americans, he remains utterly
clueless about cricket.

xi