beginning microsoft excel 2010

this print for content only—size & color not accurate 7.5 x 9.25 spine = 0.75" 408 page count Katz Microsoft Excel 2010

THE EXPERT’S VOICE® IN MICROSOFT OFFICE

Beginning

Microsoft Excel

2010

CYAN

MAGENTA

YELLOW

BLACK

PANTONE 123 C

Abbott Katz

Companion

eBook

Available

All you needed to get started with

Microsoft Excel 2010

BOOKS FOR PROFESSIONALS BY PROFESSIONALS®

Beginning Microsoft Excel 2010

Dear Reader,

Tell me if you identify with this question:

“What’s a spreadsheet?”

Truth be told, this question is a very good one, and because spreadsheets are

so vast, and their capabilities so broad, the answer doesn’t come easily.

People apply Excel spreadsheets to countless tasks, but merely knowing how

to do carry out those tasks isn’t always good enough. If the user isn’t quite sure

about what’s going on in the spreadsheet – and why – it makes it that much

harder to carry out the tasks a second time, let alone the first – particularly if

there’s no one around to ask.

I’ve written Beginning Microsoft Excel 2010 with that agenda in mind. All too

many introductory books slip into the click-here-click-there approach, even as

they omit the answers to the “what’s going on?” questions which often lurk in

the background. I’ve tried here to cover many of Excel’s capabilities but at the

same time tried to anticipate the kinds of questions about them you might ask

of an instructor, since you probably won’t pay for house calls.

The book covers all aspects of using Excel 2010 from the day-to-day moving

around a spreadsheet and performing calculations on your data, to the more

complex areas of charting and using pivot tables. All of those areas are covered

in an easy to understand manner so you can quickly get up to speed with the

core features of Excel 2010, whether you’re arriving from a previous version of

Excel or you’re tackling spreadsheets for the first time.

Abbott Katz, Ph.D.

Abbott Katz

US $34.99

Shelve in:

Applications / MS Excel

User level:

Beginner

www.apress.com

Companion eBook

See last page for details

on $10 eBook version

ISBN 978-1-4302-2955-1

9 781430 229551

5 34 9 9

Beginning

RELATED TITLES

i

Beginning Microsoft

Excel 2010

■ ■ ■

Abbott Katz

ii

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 (201) 348-4505, 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.

iii

iv

Contents at a Glance

■Contents at a Glance ............................................................................................iv

■Contents ................................................................................................................v

■About the Author..................................................................................................xi

■About the Technical Reviewer.............................................................................xii

■Acknowledgments..............................................................................................xiii

■Introduction ....................................................................................................... xiv

■Chapter 1: Introduction to Excel............................................................................1

■Chapter 2: Getting Started and Getting Around the Worksheet...........................17

■Chapter 3: From Data Entry to Data Creation:

Formula Basics and Beyond ..............................................................51

■Chapter 4: Keeping Up Appearances—Formatting the Worksheet .....................87

■Chapter 5: The Stuff Of Legend—Charting in Excel...........................................155

■Chapter 6: Setting the Table: Database Features of Excel 2010 .......................201

■Chapter 7: Working With Multiple Sheets .........................................................239

■Chapter 8: PivotTables and Pivot Charts...........................................................265

■Chapter 9: Getting It On Paper—Printing in Excel 2010 ...................................309

■Chapter 10: Taking it to the Cloud: Sharing and

Collaborating on the Internet ........................................................337

■Appendix A: Working With Range Names..........................................................351

■Appendix B: Keyboard Shortcuts ......................................................................361

■Appendix C: Error Messages .............................................................................369

■Index .................................................................................................................375

v

Contents

■Contents at a Glance ............................................................................................iv

■Contents ................................................................................................................v

■About the Author ..................................................................................................xi

■About the Technical Reviewer.............................................................................xii

■Acknowledgments..............................................................................................xiii

■Introduction........................................................................................................ xiv

■Chapter 1: Introduction to Excel............................................................................1

Making the Acquaintance ............................................................................................... 1

Unlocking Your Inner Worksheet..................................................................................... 1

The Pep Talk ................................................................................................................... 4

Interacting with the Interface ......................................................................................... 5

Tab Talk .......................................................................................................................... 7

Something New, Something Old ..................................................................................... 9

Keeping Tabs…on a New One...................................................................................... 10

Cool QAT........................................................................................................................ 12

Understood in Context................................................................................................... 13

Alt-ered Consciousness ................................................................................................ 14

■Chapter 2: Getting Started and Getting Around the Worksheet...........................17

More Addresses Than the Phone Book—Cells, and How to Get There......................... 17

Getting Around .............................................................................................................. 19

Key Points ..................................................................................................................... 21

■ CONTENTS

vi

Ranges: A Select Tool ................................................................................................... 24

Data Entry: Getting Started ........................................................................................... 29

Entering Text: Trespassing Allowed.....................................................................................................30

Numbers are (a little) Different ..................................................................................... 36

Relocating the Data: Copying and Moving .................................................................... 37

Having Your Fill ............................................................................................................. 41

There’ll Be Some Changes Made—Editing Cells .......................................................... 46

■Chapter 3: From Data Entry to Data Creation:

Formula Basics and Beyond...............................................................51

Cell references explained.....................................................................................................................52

Ordering Up Your Results.............................................................................................. 54

Not Just Your Average Function...........................................................................................................62

Making Range Values COUNT ..............................................................................................................63

MAX and MIN—Recording Highs and Lows.........................................................................................64

Absolute References: Absolutely Important.........................................................................................72

More of the Same ......................................................................................................... 73

You Could Look It Up ............................................................................................................................79

If: Worth Knowing—No Ifs, Ands, or Buts............................................................................................83

In Conclusion… ...................................................................................................................................85

■Chapter 4: Keeping Up Appearances—Formatting the Worksheet .....................87

You’ve Got Designs on Your Worksheet........................................................................ 87

The Font Button Group: A Closer Look .................................................................................................89

Borderline Command .................................................................................................... 93

Getting Oriented.......................................................................................................... 104

Excel Has Got Your Number(s) .................................................................................... 118

Dates—The Long and the Short of It .......................................................................... 124

Time Is On Your Side—Yes It Is .................................................................................. 125

A New Kind of Copy—and Paste................................................................................. 131

Style Setter ................................................................................................................. 137

■ CONTENTS

vii

Formatting—With Conditions ..................................................................................... 139

Just a Bit More…........................................................................................................ 152

P. S.............................................................................................................................. 154

IN CONCLUSION…....................................................................................................... 154

■Chapter 5: The Stuff Of Legend—Charting in Excel...........................................155

Starting Charting......................................................................................................... 155

Making a Chart of Our Own................................................................................................................158

Changing The Chart—It’s Your Call ...................................................................................................164

The Design Tab—A Closer Look ................................................................................. 172

Change Chart Type.............................................................................................................................172

Save As Template ..............................................................................................................................173

Switch Row/Column...........................................................................................................................174

Select Data.........................................................................................................................................175

Chart Layouts .....................................................................................................................................176

Chart Styles........................................................................................................................................177

Move Chart.........................................................................................................................................178

Changing the Chart Default—and the 2-Second Chart............................................... 178

The Layout Tab............................................................................................................ 179

Working with Chart Labels.......................................................................................... 184

Axes to Grind............................................................................................................... 187

The Format Tab—Getting Your Objects in Shape ....................................................... 193

Sparklines: Mini-Charts with Big Impact .................................................................... 194

You Win Some, You Lose Some .................................................................................. 198

In Conclusion… .......................................................................................................... 200

■Chapter 6: Setting the Table: Database Features of Excel 2010 .......................201

Sorting—Sort Of Easy................................................................................................. 202

Using Header Rows............................................................................................................................204

Sorting by More than One Field .........................................................................................................205

The AutoFilter: Picking and Choosing Your Data......................................................... 209

■ CONTENTS

viii

Playing—or Plying—the Numbers ............................................................................. 213

The Advanced Filter—Setting Your Data Aside .......................................................... 215

Table Talk ................................................................................................................... 218

Creating a Table.................................................................................................................................219

Using Table Styles..............................................................................................................................220

Adding a Total Row ............................................................................................................................221

Examining the other Table Style Options ...........................................................................................223

At the Risk of Repeating Yourself: The Remove Duplicates Option ............................ 226

Data Validation: Improving Your Entrée to Data Entry................................................. 228

Using Data Validation.........................................................................................................................228

Adding Data Entry Rules ....................................................................................................................231

Adding an Input Message...................................................................................................................234

Using the Error Alert Option ...............................................................................................................234

Adding a Validation Rule to Existing Data ..........................................................................................236

In Conclusion… .......................................................................................................... 237

■Chapter 7: Working With Multiple Sheets .........................................................239

Adding To Sheets—Inserting Rows, Columns and Cells ............................................ 240

Inserting a Column.............................................................................................................................240

Inserting a Row ..................................................................................................................................241

Deleting Rows and Columns ..............................................................................................................241

Inserting and Deleting Cells ...............................................................................................................241

Hiding Rows and Columns—and Getting them Back.........................................................................242

Multiple Worksheet Basics ......................................................................................... 243

Inserting a New Worksheet................................................................................................................244

Busting a (Sheet) Move......................................................................................................................245

Hiding Worksheets.............................................................................................................................246

Grouping Worksheets.........................................................................................................................247

Far-Flung Formulas: Working with Multi-Sheet Cell References................................ 247

Doing a Multi-sheet Calculation.........................................................................................................248

Extending Your Reach: Referring to Cells in Different Workbooks.....................................................250

■ CONTENTS

ix

The Watch Window—Spying On Your Own Data ........................................................ 251

Protect Your Cells From Unwanted Intruders—Even Yourself.................................... 253

Protecting a Sheet..............................................................................................................................253

Protecting a Whole Workbook............................................................................................................258

Consolidating Your Data—Getting It All Together....................................................... 259

In Conclusion… .......................................................................................................... 264

■Chapter 8: PivotTables and Pivot Charts...........................................................265

Starting Out with PivotTables ..................................................................................... 265

What’s in a Name?...................................................................................................... 267

Constructing a PivotTable: Let’s Go ............................................................................ 268

Inserting the Table .............................................................................................................................269

Setting Up the PivotTable...................................................................................................................271

Updating the Pivot Report ..................................................................................................................274

Filters Again-PivotTable Style..................................................................................... 285

Your Very Own Top 10 List.......................................................................................... 288

The Report Filter—Getting on Top of the PivotTable .................................................. 289

The Slicer—Filter Deluxe............................................................................................ 293

Grouping the Data ....................................................................................................... 296

Formatting the Pivot Report—Values and All ............................................................. 300

Pivot Charts................................................................................................................. 302

In Conclusion… .......................................................................................................... 307

■Chapter 9: Getting It On Paper— Printing in Excel 2010 ..................................309

Hard Copy? Pretty Easy............................................................................................... 309

The Print Area Option.................................................................................................. 311

Margin-al Utility .......................................................................................................... 314

Printing As You See Fit................................................................................................ 316

Headers and Footers—Getting to the Bottom (and the Top) of Your Printout............. 318

Title Search................................................................................................................. 323

■ CONTENTS

x

There’s More Than One Way to View A Worksheet..................................................... 327

Gimme a (Page) Break—Another View..............................................................................................329

Customize Your View, Too........................................................................................... 334

In Conclusion… .......................................................................................................... 336

■Chapter 10: Taking it to the Cloud: Sharing and Collaborating on the Internet 337

Getting There .............................................................................................................. 338

Permission Granted: Sharing Your Workbooks ........................................................... 345

Hey—You! Get Onto My Cloud!.................................................................................. 348

In Conclusion… .......................................................................................................... 349

■Appendix A: Working With Range Names..........................................................351

What’s in a Name? Plenty, if it’s a Range................................................................... 351

Naming Many Ranges – at the Same Time................................................................. 357

The Name Manager – Tracking Your Ranges.............................................................. 359

In Conclusion... ........................................................................................................... 360

■Appendix B:Keyboard Shortcuts .......................................................................361

Control Key Combinations........................................................................................... 361

Function Keys ............................................................................................................. 363

Other shortcut keys..................................................................................................... 365

■Appendix C:Error Messages ..............................................................................369

Nobody’s Perfect......................................................................................................... 369

■Index .................................................................................................................375

■ CONTENTS

xi

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.

■ CONTENTS

xii

About the Technical Reviewer

■ Simon Murphy is a freelance software developer specialising in integrating

Excel with other enterprise resources. He has been doing spreadsheets forever,

having started in the 80s, and mainly works in the financial analysis and

reporting arena. He also runs a successful business selling Excel add-ins via the

internet.

Simon spoke at the inaugural Excel User Conference in Dallas Fort Worth

in 2005, and regularly speaks and contributes to the European Spreadsheet Risk

Interest Group (eusprig.org), the worldwide authority on the risks inherent in

commercial spreadsheet use.

XLAnalyst, the spreadsheet risk assessment tool that Simon developed is

one of the most popular spreadsheet audit tools in the market.

Simon is also a member of the Microsoft Office Developer Advisory

Council, and helped steer the developer features of Excel 2010. He has an MSc

in Software development and his main consulting interest is around

performant User Defined Functions.

■ CONTENTS

xiii

Acknowledgments

It’s a truism, but it’s true nonetheless: Writing a book nowadays calls upon a team effort. Blog away in

solitary bliss, but writing – and assembling - a hard-copy book is something different. The name you see

attached to the book’s title identifies but one of its contributors, and it’s only proper that I acknowledge

those unfailingly congenial, if anonymous, teammates whose work would otherwise go unsung.

First, all due thanks to Apress Lead Editor Ben Renow-Clarke, who was there at the book’s inception,

and has lent a continually helpful hand to the project. Coordinating Editor Kelly Moritz has offered her

non-stop assistance, across six time zones no less, and Copy Editor Janet Gokay has delivered spot-on

grammatical and stylistic counsel. Technical Reviewer Simon Murphy asked the right questions, and

often supplied some of the answers, too.

Closer to home – much closer – my devoted wife Marsha granted me the time and space to pursue

this literary chore, even if it meant neglecting my other chores – even more than usual. (I did manage to

mow the lawn today, though.)

And speaking of spaces, a writer’s desk these days is wherever his laptop alights, and so a number of

venues should be cited and thanked as well, including the Bewick Centre in Gateshead (thanks to Sholmi

Issacson), assorted tables on the East Coast and Grand Central train lines, a clutch of Starbuckses (check

that plural, Janet) and the British Library.

On the other hand, of course, blame for any and all of the book’s shortcomings reverts to the guy

with his name attached to the title.