Microsoft Office Excel 2007 For Dummies

Excel

2007

FOR

DUMmIES

‰

Microsoft

Office

01_037377 ffirs_2.qxp 12/15/06 10:11 AM Page i

01_037377 ffirs_2.qxp 12/15/06 10:11 AM Page ii

by Greg Harvey, PhD

Excel

2007

FOR

DUMmIES

‰

Microsoft

Office

01_037377 ffirs_2.qxp 12/15/06 10:11 AM Page iii

Microsoft

Office Excel

2007 For Dummies

Published by

Wiley Publishing, Inc.

111 River Street

Hoboken, NJ 07030-5774

www.wiley.com

Published by Wiley Publishing, Inc., Indianapolis, Indiana

Published simultaneously in Canada

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or

by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permit-

ted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written

permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the

Requests to the Publisher for permission should be addressed to the Legal Department, Wiley Publishing,

Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317) 572-3447, fax (317) 572-4355, or online at

http://www.wiley.com/go/permissions.

Trademarks: Wiley, the Wiley Publishing logo, For Dummies, the Dummies Man logo, A Reference for the

Rest of Us!, The Dummies Way, Dummies Daily, The Fun and Easy Way, Dummies.com, and related trade

dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates in the United

States and other countries, and may not be used without written permission. Microsoft is a registered

trademark or trademark of Microsoft Corporation. All other trademarks are the property of their respec-

tive owners. Wiley Publishing, Inc., is not associated with any product or vendor mentioned in this book.

LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: WHILE THE PUBLISHER AND AUTHOR HAVE USED

THEIR BEST EFFORTS IN PREPARING THIS BOOK, THEY MAKE NO REPRESENTATIONS OR WAR-

RANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS BOOK

AND SPECIFICALLY DISCLAIM ANY IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A

PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES REPRESENTA-

TIVES OR WRITTEN SALES MATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT

BE SUITABLE FOR YOUR SITUATION. YOU SHOULD CONSULT WITH A PROFESSIONAL WHERE APPRO-

PRIATE. NEITHER THE PUBLISHER NOR AUTHOR SHALL BE LIABLE FOR ANY LOSS OF PROFIT OR

ANY OTHER COMMERCIAL DAMAGES, INCLUDING BUT NOT LIMITED TO SPECIAL, INCIDENTAL, CON-

SEQUENTIAL, OR OTHER DAMAGES.

For general information on our other products and services or to obtain technical support, please contact

our Customer Care Department within the U.S. at 800-762-2974, outside the U.S. at 317-572-3993, or fax

317-572-4002.

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may

not be available in electronic books.

Library of Congress Control Number: 2006934835

ISBN-13: 978-0-470-03737-9

ISBN-10: 0-470-03737-7

1B/QV/RS/QW/IN

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2

01_037377 ffirs_2.qxp 12/15/06 10:11 AM Page iv

About the Author

Greg Harvey has authored tons of computer books, the most recent being

Excel Workbook For Dummies and Roxio Easy Media Creator 8 For Dummies,

and the most popular being Excel 2003 For Dummies and Excel 2003 All-In-One

Desk Reference For Dummies. He started out training business users on how

to use IBM personal computers and their attendant computer software in the

rough and tumble days of DOS, WordStar, and Lotus 1-2-3 in the mid-80s of

the last century. After working for a number of independent training firms,

Greg went on to teach semester-long courses in spreadsheet and database

management software at Golden Gate University in San Francisco.

His love of teaching has translated into an equal love of writing. For Dummies

books are, of course, his all-time favorites to write because they enable him

to write to his favorite audience: the beginner. They also enable him to use

humor (a key element to success in the training room) and, most delightful of

all, to express an opinion or two about the subject matter at hand.

Greg received his doctorate degree in Humanities in Philosophy and Religion

with a concentration in Asian Studies and Comparative Religion last May.

Everyone is glad that Greg was finally able to get out of school before he

retired.

01_037377 ffirs_2.qxp 12/15/06 10:11 AM Page v

01_037377 ffirs_2.qxp 12/15/06 10:11 AM Page vi

Dedication

An Erucolindo melindonya

Author’s Acknowledgments

Let me take this opportunity to thank all the people, both at Wiley Publishing,

Inc., and at Mind over Media, Inc., whose dedication and talent combined to

get this book out and into your hands in such great shape.

At Wiley Publishing, Inc., I want to thank Andy Cummings and Katie Feltman

for their encouragement and help in getting this project underway and their

ongoing support every step of the way, and project editor Christine Berman.

These people made sure that the project stayed on course and made it into

production so that all the talented folks on the production team could create

this great final product.

At Mind over Media, I want to thank Christopher Aiken for his review of the

updated manuscript and invaluable input and suggestions on how best to

restructure the book to accommodate all the new features and, most impor-

tantly, present the new user interface.

01_037377 ffirs_2.qxp 12/15/06 10:11 AM Page vii

Publisher’s Acknowledgments

We’re proud of this book; please send us your comments through our online registration form

located at www.dummies.com/register/.

Some of the people who helped bring this book to market include the following:

Acquisitions, Editorial, and

Media Development

Project Editor: Christine Berman

Senior Acquisitions Editor: Katie Feltman

Copy Editor: Christine Berman

Technical Editor: Gabrielle Sempf

Editorial Manager: Jodi Jensen

Media Development Manager:

Laura Carpenter VanWinkle

Editorial Assistant: Amanda Foxworth

Cartoons: Rich Tennant (

www.the5thwave.com)

Production

Project Coordinator: Adrienne Martinez

Layout and Graphics: Stephanie D. Jumper,

Barbara Moore, Barry Offringa,

Heather Ryan

Proofreaders: John Greenough,

Jessica Kramer, Techbooks

Indexer: Techbooks

Anniversary Logo Design: Richard Pacifico

Publishing and Editorial for Technology Dummies

Richard Swadley, Vice President and Executive Group Publisher

Andy Cummings, Vice President and Publisher

Mary C. Corder, Editorial Director

Publishing for Consumer Dummies

Diane Graves Steele, Vice President and Publisher

Joyce Pepple, Acquisitions Director

Composition Services

Gerry Fahey, Vice President of Production Services

Debbie Stailey, Director of Composition Services

01_037377 ffirs_2.qxp 12/15/06 10:11 AM Page viii

Contents at a Glance

Introduction .................................................................1

Part I: Getting In on the Ground Floor ............................9

Chapter 1: The Excel 2007 User Experience .................................................................11

Chapter 2: Creating a Spreadsheet from Scratch .........................................................51

Part II: Editing Without Tears......................................97

Chapter 3: Making It All Look Pretty..............................................................................99

Chapter 4: Going through Changes..............................................................................141

Chapter 5: Printing the Masterpiece............................................................................173

Part III: Getting Organized and Staying That Way ......201

Chapter 6: Maintaining the Worksheet ........................................................................203

Chapter 7: Maintaining Multiple Worksheets .............................................................231

Part IV: Digging Data Analysis..................................253

Chapter 8: Doing What-If Analysis................................................................................255

Chapter 9: Playing with Pivot Tables ...........................................................................269

Part V: Life Beyond the Spreadsheet ..........................285

Chapter 10: Charming Charts and Gorgeous Graphics .............................................287

Chapter 11: Getting on the Data List............................................................................319

Chapter 12: Hyperlinks and Macros.............................................................................343

Part VI: The Part of Tens ...........................................355

Chapter 13: Top Ten New Features in Excel 2007.......................................................357

Chapter 14: Top Ten Beginner Basics ..........................................................................361

Chapter 15: The Ten Commandments of Excel 2007..................................................363

Index .......................................................................365

02_037377 ftoc.qxp 11/16/06 9:20 AM Page ix

02_037377 ftoc.qxp 11/16/06 9:20 AM Page x

Table of Contents

Introduction..................................................................1

About This Book...............................................................................................1

How to Use This Book .....................................................................................2

What You Can Safely Ignore ............................................................................2

Foolish Assumptions .......................................................................................3

How This Book Is Organized...........................................................................3

Part I: Getting In on the Ground Floor .................................................3

Part II: Editing Without Tears................................................................4

Part III: Getting Organized and Staying That Way ..............................4

Part IV: Digging Data Analysis...............................................................4

Part V: Life Beyond the Spreadsheet ...................................................4

Part VI: The Part of Tens .......................................................................5

Conventions Used in This Book .....................................................................5

Keyboard and mouse.............................................................................5

Special icons ...........................................................................................7

Where to Go from Here....................................................................................8

Part I: Getting In on the Ground Floor.............................9

Chapter 1: The Excel 2007 User Experience . . . . . . . . . . . . . . . . . . . . . .11

Excel’s Ribbon User Interface.......................................................................12

Manipulating the Office Button ..........................................................12

Bragging about the Ribbon .................................................................14

Adapting the Quick Access toolbar ...................................................18

Having fun with the Formula bar........................................................21

What to do in the Worksheet area......................................................22

Showing off the Status bar ..................................................................27

Starting and Exiting Excel .............................................................................29

Starting Excel from the Windows Vista Start menu .........................29

Starting Excel from the Windows XP Start menu .............................29

Pinning Excel to the Start menu .........................................................30

Creating an Excel desktop shortcut for Windows Vista..................30

Creating an Excel desktop shortcut for Windows XP ......................31

Adding the Excel desktop shortcut

to the Quick Launch toolbar ...........................................................32

Exiting Excel..........................................................................................32

Help Is on the Way .........................................................................................33

Migrating to Excel 2007 from Earlier Versions ...........................................34

Cutting the Ribbon down to size ........................................................35

Finding the Standard Toolbar buttons equivalents .........................41

02_037377 ftoc.qxp 11/16/06 9:20 AM Page xi

Finding the Formatting Toolbar buttons equivalents......................43

Putting the Quick Access toolbar to excellent use ..........................45

Getting good to go with Excel 2007....................................................49

Chapter 2: Creating a Spreadsheet from Scratch . . . . . . . . . . . . . . . . .51

So What Ya Gonna Put in That New Workbook of Yours? .........................52

The ins and outs of data entry............................................................52

You must remember this . . . ...............................................................53

Doing the Data-Entry Thing ..........................................................................53

It Takes All Types ...........................................................................................56

The telltale signs of text ......................................................................56

How Excel evaluates its values...........................................................58

Fabricating those fabulous formulas! ................................................64

If you want it, just point it out ............................................................67

Altering the natural order of operations...........................................67

Formula flub-ups...................................................................................68

Fixing Up Those Data Entry Flub-Ups..........................................................70

You really AutoCorrect that for me....................................................70

Cell editing etiquette............................................................................71

Taking the Drudgery out of Data Entry .......................................................73

I’m just not complete without you .....................................................73

Fill ’er up with AutoFill ........................................................................75

Inserting special symbols....................................................................80

Entries all around the block................................................................81

Data entry express ...............................................................................82

How to Make Your Formulas Function Even Better...................................83

Inserting a function into a formula with the

Function Wizard button ...................................................................84

Editing a function with the Function Wizard button........................87

I’d be totally lost without AutoSum ...................................................87

Making Sure That the Data Is Safe and Sound............................................90

The Save As dialog box in Windows Vista.........................................91

The Save As dialog box in Windows XP.............................................92

Changing the default file location ......................................................93

The difference between the XLSX and XLS file format ....................94

Saving the Workbook as a PDF File ..............................................................95

Document Recovery to the Rescue .............................................................96

Part II: Editing Without Tears ......................................97

Chapter 3: Making It All Look Pretty . . . . . . . . . . . . . . . . . . . . . . . . . . . .99

Choosing a Select Group of Cells ...............................................................100

Point-and-click cell selections ..........................................................100

Keyboard cell selections ...................................................................104

Having Fun with the Format as Table Gallery ..........................................107

Microsoft Office Excel 2007 For Dummies

xii

02_037377 ftoc.qxp 11/16/06 9:20 AM Page xii

Cell Formatting from the Home Tab ..........................................................109

Formatting Cells Close to the Source with the Mini Toolbar..................113

Using the Format Cells Dialog Box.............................................................114

Getting comfortable with the number formats ..............................114

The values behind the formatting....................................................119

Make it a date!.....................................................................................121

Ogling some of the other number formats......................................122

Calibrating Columns ....................................................................................123

Rambling rows ....................................................................................124

Now you see it, now you don’t .........................................................125

Futzing with the Fonts .................................................................................126

Altering the Alignment ................................................................................128

Intent on indents ................................................................................130

From top to bottom............................................................................130

Tampering with how the text wraps ................................................131

Reorienting cell entries......................................................................133

Shrink to fit..........................................................................................134

Bring on the borders!.........................................................................135

Applying fill colors, patterns, and gradient effects to cells ..........136

Do It in Styles................................................................................................138

Creating a new style for the gallery .................................................138

Copying custom styles from one workbook into another.............138

Fooling Around with the Format Painter ..................................................139

Chapter 4: Going through Changes . . . . . . . . . . . . . . . . . . . . . . . . . . . . .141

Opening the Darned Thing Up for Editing ................................................142

The Open dialog box in Excel 2007 running

on Windows Vista ...........................................................................142

The Open dialog box in Excel 2007 running on Windows XP .......144

Opening more than one workbook at a time ..................................146

Opening recently edited workbooks ...............................................146

When you don’t know where to find them......................................147

Opening files with a twist ..................................................................149

Much Ado about Undo ................................................................................150

Undo is Redo the second time around ............................................150

What ya gonna do when you can’t Undo?.......................................151

Doing the Old Drag-and-Drop Thing ..........................................................151

Copies, drag-and-drop style..............................................................153

Insertions courtesy of drag and drop..............................................154

Formulas on AutoFill....................................................................................155

Relatively speaking ............................................................................156

Some things are absolutes! ...............................................................157

Cut and paste, digital style................................................................159

Paste it again, Sam . . .........................................................................160

Keeping pace with the Paste Options..............................................160

Paste it from the Clipboard task pane .............................................161

So what’s so special about Paste Special? ......................................162

xiii

Table of Contents

02_037377 ftoc.qxp 11/16/06 9:20 AM Page xiii

Let’s Be Clear about Deleting Stuff.............................................................164

Sounding the all clear! .......................................................................164

Get these cells outta here!.................................................................165

Staying in Step with Insert ..........................................................................166

Stamping Out Your Spelling Errors ............................................................167

Stamping Out Errors with Text to Speech.................................................169

Chapter 5: Printing the Masterpiece . . . . . . . . . . . . . . . . . . . . . . . . . . .173

Taking a Gander at the Pages in Page Layout View .................................174

Checking the Printout with Print Preview ................................................175

Printing the Worksheet................................................................................177

Printing the Worksheet from the Print Dialog Box ..................................178

Printing particular parts of the workbook ......................................179

Setting and clearing the Print Area ..................................................181

My Page Was Set Up! ....................................................................................181

Using the buttons in the Page Setup group.....................................182

Using the buttons in the Scale to Fit group.....................................188

Using the Print buttons in the Sheet Options group......................188

From Header to Footer ................................................................................189

Adding an Auto Header or Auto Footer...........................................190

Creating a custom header or footer.................................................192

Solving Page Break Problems .....................................................................196

Letting Your Formulas All Hang Out ..........................................................198

Part III: Getting Organized and Staying That Way.......201

Chapter 6: Maintaining the Worksheet . . . . . . . . . . . . . . . . . . . . . . . . .203

Zeroing In with Zoom...................................................................................204

Splitting the Difference................................................................................206

Fixed Headings Courtesy of Freeze Panes ................................................209

Electronic Sticky Notes ...............................................................................212

Adding a comment to a cell ..............................................................212

Comments in review...........................................................................214

Editing the comments in a worksheet .............................................215

Getting your comments in print .......................................................216

The Cell Name Game....................................................................................216

If I only had a name . . . ......................................................................216

Name that formula!.............................................................................217

Naming constants...............................................................................218

Seek and Ye Shall Find . . . ...........................................................................220

You Can Be Replaced! ..................................................................................223

Do Your Research.........................................................................................224

You Can Be So Calculating ..........................................................................226

Putting on the Protection............................................................................227

Microsoft Office Excel 2007 For Dummies

xiv

02_037377 ftoc.qxp 11/16/06 9:20 AM Page xiv

Chapter 7: Maintaining Multiple Worksheets . . . . . . . . . . . . . . . . . . .231

Juggling Worksheets ....................................................................................232

Sliding between the sheets ...............................................................232

Editing en masse.................................................................................235

Don’t Short-Sheet Me! ..................................................................................236

A worksheet by any other name . . ..................................................237

A sheet tab by any other color . . . ...................................................238

Getting your sheets in order.............................................................239

Opening Windows on Your Worksheets ....................................................240

Comparing Two Worksheets Side by Side.................................................245

Moving and Copying Sheets to Other Workbooks ...................................246

To Sum Up . . . ...............................................................................................249

Part IV: Digging Data Analysis ..................................253

Chapter 8: Doing What-If Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . .255

Playing what-if with Data Tables ................................................................256

Creating a one-variable data table ...................................................256

Creating a two-variable data table ...................................................259

Playing What-If with Goal Seeking..............................................................261

Examining Different Cases with Scenario Manager .................................264

Setting up the various scenarios......................................................264

Producing a summary report............................................................266

Chapter 9: Playing with Pivot Tables . . . . . . . . . . . . . . . . . . . . . . . . . . .269

Pivot Tables: The Ultimate Data Summary ...............................................269

Producing a Pivot Table ..............................................................................270

Formatting a Pivot Table .............................................................................273

Refining the Pivot Table style ...........................................................274

Formatting the values in the pivot table .........................................275

Sorting and Filtering the Pivot Table Data ................................................275

Filtering the report.............................................................................276

Filtering individual Column and Row fields ....................................276

Sorting the pivot table .......................................................................278

Modifying a Pivot Table...............................................................................278

Modifying the pivot table fields........................................................278

Pivoting the table’s fields ..................................................................279

Modifying the table’s summary function ........................................280

Get Smart with a Pivot Chart......................................................................281

Moving a pivot chart to its own sheet .............................................282

Filtering a pivot chart ........................................................................283

Formatting a pivot chart....................................................................283

Table of Contents

02_037377 ftoc.qxp 11/16/06 9:20 AM Page xv

Part V: Life Beyond the Spreadsheet...........................285

Chapter 10: Charming Charts and Gorgeous Graphics . . . . . . . . . . . .287

Making Professional-Looking Charts .........................................................287

Creating a new chart..........................................................................288

Moving and resizing an embedded chart in a worksheet .............290

Moving an embedded chart onto its own chart sheet ..................290

Customizing the chart type and style from the Design tab ..........291

Customizing chart elements from the Layout tab..........................292

Editing the titles in a chart................................................................295

Formatting chart elements from the Format tab............................296

Adding Great Looking Graphics .................................................................299

Telling all with a text box ..................................................................300

The wonderful world of Clip Art.......................................................302

Inserting pictures from graphics files..............................................305

Editing Clip Art and imported pictures ...........................................305

Formatting Clip Art and imported pictures ....................................305

Adding preset graphic shapes..........................................................307

Working with WordArt .......................................................................308

Make mine SmartArt ..........................................................................310

Theme for a day..................................................................................313

Controlling How Graphic Objects Overlap ...............................................314

Reordering the layering of graphic objects ....................................314

Grouping graphic objects..................................................................315

Hiding graphic objects.......................................................................315

Printing Just the Charts...............................................................................317

Chapter 11: Getting on the Data List . . . . . . . . . . . . . . . . . . . . . . . . . . . .319

Creating a Data List......................................................................................319

Adding records to a data list.............................................................321

Sorting Records in a Data List ....................................................................329

Sorting records on a single field.......................................................330

Sorting records on multiple fields....................................................331

Filtering the Records in a Data List............................................................333

Using readymade number filters......................................................334

Using readymade date filters............................................................335

Getting creative with custom filtering .............................................336

Importing External Data ..............................................................................339

Querying an Access database table.................................................339

Performing a New Web query ...........................................................341

Microsoft Office Excel 2007 For Dummies

xvi

02_037377 ftoc.qxp 11/16/06 9:20 AM Page xvi

Chapter 12: Hyperlinks and Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . .343

Using Add-Ins in Excel 2007 ........................................................................343

Adding Hyperlinks to a Worksheet ............................................................345

Automating Commands with Macros ........................................................348

Recording new macros ......................................................................348

Running macros..................................................................................352

Assigning macros to the Quick Access toolbar..............................353

Part VI: The Part of Tens............................................355

Chapter 13: Top Ten New Features in Excel 2007 . . . . . . . . . . . . . . . .357

Chapter 14: Top Ten Beginner Basics . . . . . . . . . . . . . . . . . . . . . . . . . .361

Chapter 15: The Ten Commandments of Excel 2007 . . . . . . . . . . . . . .363

Index........................................................................365

xvii

Table of Contents

02_037377 ftoc.qxp 11/16/06 9:20 AM Page xvii

Microsoft Office Excel 2007 For Dummies

xviii

02_037377 ftoc.qxp 11/16/06 9:20 AM Page xviii

Introduction

’m very proud to present you with the completely revamped and almost

totally brand new

Excel 2007 For Dummies, the latest version of everybody’s

favorite book on Microsoft Office Excel for readers with no intention whatso-

ever of becoming spreadsheet gurus. The dramatic changes evident in this

version of the book reflect the striking, dare I say, revolutionary changes that

Microsoft has brought to its ever-popular spreadsheet program. One look at

the new Ribbon command structure and all those rich style galleries in Excel

2007 and you know you’re not in Kansas anymore ‘cause this is

definitely not

your mother’s Excel!

In keeping with Excel’s more graphical and colorful look and feel,

Excel 2007

For Dummies

has taken on some color of its own (just take a gander at those

color plates in the mid-section of the book) and now starts off with a defini-

tive introduction to the new user Ribbon interface. This chapter is written

both for those of you for whom Excel is a completely new experience and

those of you who have had some experience with the old pull-down menu

and multi-toolbar Excel interface who are now faced with the seemingly

daunting task of getting comfortable with a whole new user experience.

Excel 2007 For Dummies covers all the fundamental techniques you need

to know in order to create, edit, format, and print your own worksheets.

In addition to showing you around the worksheet, this book also exposes

you to the basics of charting, creating data lists, and performing data analysis.

Keep in mind, though, that this book just touches on the easiest ways to

get a few things done with these features — I make no attempt to cover

charting, data lists, or data analysis in the same definitive way as spread-

sheets: This book concentrates on spreadsheets because spreadsheets are

what most regular folks create with Excel.

About This Book

This book isn’t meant to be read cover to cover. Although its chapters are

loosely organized in a logical order (progressing as you might when studying

Excel in a classroom situation), each topic covered in a chapter is really meant

to stand on its own.

03_037377 intro.qxp 11/16/06 9:20 AM Page 1

Each discussion of a topic briefly addresses the question of what a particular

feature is good for before launching into how to use it. In Excel, as with most

other sophisticated programs, you usually have more than one way to do a

task. For the sake of your sanity, I have purposely limited the choices by usu-

ally giving you only the most efficient ways to do a particular task. Later on,

if you’re so tempted, you can experiment with alternative ways of doing a

task. For now, just concentrate on performing the task as I describe.

As much as possible, I’ve tried to make it unnecessary for you to remember

anything covered in another section of the book. From time to time, however,

you will come across a cross-reference to another section or chapter in the

book. For the most part, such cross-references are meant to help you get more

complete information on a subject, should you have the time and interest. If

you have neither, no problem; just ignore the cross-references as if they never

existed.

How to Use This Book

This book is like a reference in which you start out by looking up the topic

you need information about (in either the table of contents or the index), and

then you refer directly to the section of interest. I explain most topics conver-

sationally (as though you were sitting in the back of a classroom where you

can safely nap). Sometimes, however, my regiment-commander mentality

takes over, and I list the steps you need to take to accomplish a particular

task in a particular section.

What You Can Safely Ignore

When you come across a section that contains the steps you take to get

something done, you can safely ignore all text accompanying the steps

(the text that isn’t in bold) if you have neither the time nor the inclination to

wade through more material.

Whenever possible, I have also tried to separate background or footnote-type

information from the essential facts by exiling this kind of junk to a sidebar

(look for blocks of text on a gray background). These sections are often flagged

with icons that let you know what type of information you will encounter there.

You can easily disregard text marked this way. (I’ll scoop you on the icons I use

in this book a little later.)

Microsoft Office Excel 2007 For Dummies

03_037377 intro.qxp 11/16/06 9:20 AM Page 2

Foolish Assumptions

I’m going to make only one assumption about you (let’s see how close I get):

You have access to a PC (at least some of the time) that is running either

Windows Vista or Windows XP and on which Microsoft Office Excel 2007 is

installed. However, having said that, I make no assumption that you’ve ever

launched Excel 2007, let alone done anything with it.

This book is intended ONLY for users of Microsoft Office Excel 2007! If you’re

using any previous version of Excel for Windows (from Excel 97 through 2003),

the information in this book will only confuse and confound you as your ver-

sion of Excel works nothing like the 2007 version this book describes.

If you are working on an earlier version of Excel, please put this book down

slowly and instead pick up a copy of Excel 2003 For Dummies, published by

Wiley Publishing.

How This Book Is Organized

This book is organized in six parts (which gives you a chance to see at least

six of those great Rich Tennant cartoons!). Each part contains two or more

chapters (to keep the editors happy) that more or less go together (to keep

you happy). Each chapter is further divided into loosely related sections that

cover the basics of the topic at hand. You should not, however, get too hung

up on following along with the structure of the book; ultimately, it doesn’t

matter at all if you find out how to edit the worksheet before you learn how

to format it, or if you figure out printing before you learn editing. The impor-

tant thing is that you find the information — and understand it when you find

it — when you need to perform a particular task.

In case you’re interested, a synopsis of what you find in each part follows.

Part I: Getting In on the Ground Floor

As the name implies, in this part I cover such fundamentals as how to start the

program, identify the parts of the screen, enter information in the worksheet,

save a document, and so on. If you’re starting with absolutely no background

in using spreadsheets, you definitely want to glance at the information in

Chapter 1 to discover the secrets of the new Ribbon interface before you

move on to how to create new worksheets in Chapter 2.

Introduction

03_037377 intro.qxp 11/16/06 9:20 AM Page 3

Part II: Editing Without Tears

In this part, I show how to edit spreadsheets to make them look good, as well

as how to make major editing changes to them without courting disaster.

Peruse Chapter 3 when you need information on formatting the data to

improve the way it appears in the worksheet. See Chapter 4 for rearranging,

deleting, or inserting new information in the worksheet. And read Chapter 5

for the skinny on printing out your finished product.

Part III: Getting Organized

and Staying That Way

Here I give you all kinds of information on how to stay on top of the data that

you’ve entered into your spreadsheets. Chapter 6 is full of good ideas on how

to keep track of and organize the data in a single worksheet. Chapter 7 gives

you the ins and outs of working with data in different worksheets in the same

workbook and gives you information on transferring data between the sheets

of different workbooks.

Part IV: Digging Data Analysis

This part consists of two chapters. Chapter 8 gives you an introduction to

performing various types of what-if analysis in Excel, including setting up

data tables with one and two inputs, performing goal seeking, and creating

different cases with Scenario Manager. Chapter 9 introduces you to Excel’s

vastly improved pivot table and pivot chart capabilities that enable you to

summarize and filter vast amounts of data in a worksheet table or data list

in a compact tabular or chart format.

Part V: Life Beyond the Spreadsheet

In Part V, I explore some of the other aspects of Excel besides the spreadsheet.

In Chapter 10, you find out just how ridiculously easy it is to create a chart

using the data in a worksheet. In Chapter 11, you discover just how useful

Excel’s data list capabilities can be when you have to track and organize a

large amount of information. In Chapter 12, you find out about using add-in

programs to enhance Excel’s basic features, adding hyperlinks to jump to new

places in a worksheet, to new documents, and even to Web pages, as well as

how to record macros to automate your work.

Microsoft Office Excel 2007 For Dummies

03_037377 intro.qxp 11/16/06 9:20 AM Page 4

Part VI: The Part of Tens

As is the tradition in For Dummies books, the last part contains lists of the

top ten most useful and useless facts, tips, and suggestions. In this part,

you find three chapters. Chapter 13 provides my top ten list of the best

new features in Excel 2007 (and boy was it hard keeping it down to just ten).

Chapter 14 gives you the top ten beginner basics you need to know as you

start using this program. And Chapter 15 gives you the King James Version

of the Ten Commandments of Excel 2007. With this chapter under your belt,

how canst thou goest astray?

Conventions Used in This Book

The following information gives you the lowdown on how things look in this

book — publishers call these items the book’s

conventions (no campaigning,

flag-waving, name-calling, or finger-pointing is involved, however).

Keyboard and mouse

Excel 2007 is a sophisticated program with a whole new and wonderful

user interface, dubbed the Ribbon. In Chapter 1, I explain all about this new

Ribbon interface and how to get comfortable with its new command structure.

Throughout the book, you’ll find Ribbon command sequences using the short-

hand developed by Microsoft whereby the name on the tab on the Ribbon and

the command button you select are separated by vertical bars as in:

Home | Copy

This is shorthand for the Ribbon command that copies whatever cells or

graphics are currently selected to the Windows Clipboard. It means that you

click the Home tab on the Ribbon (if it’s not already displayed) and then click

the Copy button (that sports the traditional side-by-side page icon).

Some of the Ribbon command sequences involve not only selecting a com-

mand button on a tab but then also selecting an item on a drop-down menu.

In this case, the drop-down menu command follows the name of the tab and

command button, all separated by vertical bars, as in:

Formulas | Calculation Options | Manual

Introduction

03_037377 intro.qxp 11/16/06 9:20 AM Page 5

This is shorthand for the Ribbon command sequence that turns on manual

recalculation in Excel. It says that you click the Formulas tab (if it’s not already

displayed) and then click the Calculation Options button followed by the

Manual drop-down menu option.

Although you use the mouse and keyboard shortcut keys to move your way

in, out, and around the Excel worksheet, you do have to take some time to

enter the data so that you can eventually mouse around with it. Therefore,

this book occasionally encourages you to type something specific into a spe-

cific cell in the worksheet. Of course, you can always choose not to follow the

instructions. When I tell you to enter a specific function, the part you should

type generally appears in

bold type. For example, =SUM(A2:B2) means that

you should type exactly what you see: an equal sign, the word

SUM, a left

parenthesis, the text

A2:B2 (complete with a colon between the letter-number

combos), and a right parenthesis. You then, of course, have to press Enter to

make the entry stick.

When Excel isn’t talking to you by popping up message boxes, it displays

highly informative messages in the status bar at the bottom of the screen.

This book renders messages that you see on-screen like this:

Calculate

This is the message that tells you that Excel is in manual recalculation mode

(after using the earlier Ribbon command sequence) and that one or more of

the formulas in your worksheet are not up-to-date and are in sore need of

recalculation.

Occasionally I give you a

hot key combination that you can press in order to

choose a command from the keyboard rather than clicking buttons on the

Ribbon with the mouse. Hot key combinations are written like this: Alt+FS or

Ctrl+S (both of these hot key combos save workbook changes).

With the Alt key combos, you press the Alt key until the hot key letters

appear in little squares all along the Ribbon. At that point, you can release

the Alt key and start typing the hot key letters (by the way, you type all

lowercase hot key letters — I only put them in caps to make them stand

out in the text).

Hot key combos that use the Ctrl key are of an older vintage and work a little

bit differently. You have to hold down the Ctrl key as you type the hot letter

(though again, type only lowercase letters unless you see the Shift key in the

sequence, as in Ctrl+Shift+C).

Microsoft Office Excel 2007 For Dummies

03_037377 intro.qxp 11/16/06 9:20 AM Page 6

Excel 2007 uses only one pull-down menu (the File pull-down menu) and one

toolbar (the Quick Access toolbar). You open the File pull-down menu by

clicking the Office Button (the four-color round button in the upper-left

corner of Excel program window) or pressing Alt+F. The Quick Access toolbar

with its four buttons appears to the immediate right of the Office Button.

All earlier versions of this book use command arrows to lead you from the

initial pull-down menu, to the submenu, and so on, to the command you

ultimately want. For example, if you need to open the File pull-down menu

to get to the Open command, that instruction would look like this: Choose

File➪Open. This is the equivalent of Office Button | Open and Alt+FO.

Commands using the older command arrow notation rather than the vertical

bar notation occur only in the tables in Chapter 1 for people upgrading to

Excel 2007 from older versions of Excel.

Finally, if you’re really observant, you may notice a discrepancy between the

capitalization of the names of dialog box options (such as headings, option

buttons, and check boxes) as they appear in text and how they actually

appear in Excel on your computer screen. I intentionally use the convention

of capitalizing the initial letters of all the main words of a dialog box option to

help you differentiate the name of the option from the rest of the text describ-

ing its use.

Special icons

The following icons are strategically placed in the margins to point out stuff

you may or may not want to read.

This icon alerts you to nerdy discussions that you may well want to skip

(or read when no one else is around).

This icon alerts you to shortcuts or other valuable hints related to the topic

at hand.

This icon alerts you to information to keep in mind if you want to meet with a

modicum of success.

This icon alerts you to information to keep in mind if you want to avert com-

plete disaster.

Introduction

03_037377 intro.qxp 11/16/06 9:20 AM Page 7

Where to Go from Here

If you’ve never worked with a computer spreadsheet, I suggest that, right

after getting your chuckles with the cartoons, you first go to Chapter 1 and

find out what you’re dealing with. And, if you’re someone with some experi-

ence with earlier versions of Excel, I want you to head directly to the section,

“Migrating to Excel 2007 from Earlier Versions” in Chapter 1, where you find

out how to stay calm as you become familiar and, yes, comfortable with the

new Ribbon user interface.

Then, as specific needs arise (such as, “How do I copy a formula?” or

“How do I print just a particular section of my worksheet?”), you can go

to the table of contents or the index to find the appropriate section and

go right to that section for answers.

Microsoft Office Excel 2007 For Dummies

03_037377 intro.qxp 11/16/06 9:20 AM Page 8

Part I

Getting In on the

Ground Floor

04_037377 pt01.qxp 11/16/06 9:22 AM Page 9

In this part . . .

ne look at the Excel 2007 screen with its new

Microsoft Office Button, Quick Access toolbar, and

Ribbon, and you realize how much stuff is going on here.

Well, not to worry: In Chapter 1, I break down the parts

of the Excel 2007 Ribbon user interface and make some

sense out of the rash of tabs and command buttons that

you’re going to be facing day after day after day.

Of course, it’s not enough to just sit back and have some-

one like me explain what’s what on the screen. To get any

good out of Excel, you’ve got to start learning how to use

all these bells and whistles (or buttons and boxes, in this

case). That’s where Chapter 2 comes in, giving you the

lowdown on how to use some of the screen’s more promi-

nent buttons and boxes to get your spreadsheet data

entered. From this humble beginning, it’s a quick trip to

total screen mastery.

04_037377 pt01.qxp 11/16/06 9:22 AM Page 10

Chapter 1

The Excel 2007 User Experience

In This Chapter

 Getting familiar with the new Excel 2007 program window

 Selecting commands from the Ribbon

 Customizing the Quick Access Toolbar

 Methods for starting Excel 2007

 Surfing an Excel 2007 worksheet and workbook

 Getting some help with using this program

 Quick start guide for users migrating to Excel 2007 from earlier versions

he designers and engineers at Microsoft have really gone and done it this

time — cooking up a brand new way to use everybody’s favorite electronic

spreadsheet program. This new Excel 2007 user interface scraps its previous

reliance on a series of pull-down menus, task panes, and multitudinous tool-

bars. Instead, it uses a single strip at the top of the worksheet called the

Ribbon designed to put the bulk of the Excel commands you use at your

fingertips at all times.

Add a single remaining Office pull-down menu and sole Quick Access toolbar

along with a few remaining task panes (Clipboard, Clip Art, and Research) to

the Ribbon and you end up with the easiest to use Excel ever. This version

offers you the handiest way to crunch your numbers, produce and print pol-

ished financial reports, as well as organize and chart your data, in other

words, to do all the wonderful things for which you rely on Excel.

And best of all, this new and improved Excel user interface includes all sorts

of graphical improvements. First and foremost is Live Preview that shows

you how your actual worksheet data would appear in a particular font, table

formatting, and so on before you actually select it. In addition, Excel now

supports an honest to goodness Page Layout View that displays rulers and

margins along with headers and footers for every worksheet and has a zoom

slider at the bottom of the screen that enables you to zoom in and out on the

spreadsheet data instantly. Last but not least, Excel 2007 is full of pop-up gal-

leries that make spreadsheet formatting and charting a real breeze, especially

in tandem with Live Preview.

05_037377 ch01.qxp 11/16/06 9:23 AM Page 11

Excel’s Ribbon User Interface

When you first launch Excel 2007, the program opens up the first of three

new worksheets (named Sheet1) in a new workbook file (named Book1)

inside a program window like the one shown in Figure 1-1 and Color Plate 1.

The Excel program window containing this worksheet of the workbook is

made up of the following components:

 Office Button that when clicked opens the Office pull-down menu con-

taining all the file related commands including Save, Open, Print, and

Exit as well as the Excel Options button that enables you to change

Excel’s default settings

 Quick Access toolbar that contains buttons you can click to perform

common tasks such as saving your work and undoing and redoing edits

and which you can customize by adding command buttons

 Ribbon that contains the bulk of the Excel commands arranged into a

series of tabs ranging from Home through View

 Formula bar that displays the address of the current cell along with the

contents of that cell

 Worksheet area that contains all the cells of the current worksheet iden-

tified by column headings using letters along the top and row headings

using numbers along the left edge with tabs for selecting new worksheets

and a horizontal scroll bar to move left and right through the sheet on

the bottom and a vertical scroll bar to move up and down through the

sheet on the right edge

 Status bar that keeps you informed of the program’s current mode, any

special keys you engage, and enables you to select a new worksheet

view and to zoom in and out on the worksheet

Manipulating the Office Button

At the very top of the Excel 2007 program window, you find the Office Button

(the round one with the Office four-color icon in the very upper-left corner of

the screen) followed immediately by the Quick Access toolbar.

When you click the Office Button, a pull-down menu similar to the one shown

in Figure 1-2 appears. This Office menu contains all the commands you need

for working with Excel workbook files such as saving, opening, and closing

files. In addition, this pull-down menu contains an Excel Options button that

you can select to change the program’s settings and an Exit Excel button

that you can select when you’re ready to shut down the program.

Part I: Getting In on the Ground Floor

05_037377 ch01.qxp 11/16/06 9:23 AM Page 12

Figure 1-2:

Click the

Office

Button to

access the

commands

on its pull-

down menu,

open a

recent

workbook,

or change

the Excel

Options.

Quick Access toolbar Ribbon

Office button

Formula bar

Status bar Worksheet area

Figure 1-1:

The Excel

2007

program

window that

appears

immediately

after

launching

the

program.

Chapter 1: The Excel 2007 User Experience

05_037377 ch01.qxp 11/21/06 2:29 PM Page 13

Bragging about the Ribbon

The Ribbon (shown in Figure 1-3) radically changes the way you work in

Excel 2007. Instead of having to memorize (or guess) on which pull-down

menu or toolbar Microsoft put the particular command you want to use, their

designers and engineers came up with the Ribbon that always shows you all

the most commonly used options needed to perform a particular Excel task.

The Ribbon is made up of the following components:

 Tabs for each of Excel’s main tasks that bring together and display all

the commands commonly needed to perform that core task

 Groups that organize related command buttons into subtasks normally

performed as part of the tab’s larger core task

 Command buttons within each group that you select to perform a par-

ticular action or to open a gallery from which you can click a particular

thumbnail — note that many command buttons on certain tabs of the

Excel Ribbon are organized into mini-toolbars with related settings

 Dialog Box launcher in the lower-right corner of certain groups that

opens a dialog box containing a bunch of additional options you can

select

To get more of the Worksheet area displayed in the program window, you

can minimize the Ribbon so that only its tabs are displayed — simply click

Minimize the Ribbon on the menu opened by clicking the Custom Quick

Access Toolbar button, double-click any one of the Ribbon’s tabs or press

Ctrl+F1. To redisplay the entire Ribbon, and keep all the command buttons

on its tab displayed in the program window, click Minimize the Ribbon

item on the Custom Quick Access Toolbar’s drop-down menu, double-click

one of the tabs or press Ctrl+F1 a second time.

Tabs

Dialog box launcher

Groups

Command buttons

Figure 1-3:

Excel’s

Ribbon

consists

of a series

of tabs

containing

command

buttons

arranged

into different

groups.

Part I: Getting In on the Ground Floor

05_037377 ch01.qxp 11/16/06 9:23 AM Page 14

When you work in Excel with the Ribbon minimized, the Ribbon expands

each time you click one of its tabs to show its command buttons but that tab

stays open only until you select one of the command buttons. The moment

you select a command button, Excel immediately minimizes the Ribbon again

to just the display of its tabs.

Keeping tabs on the Excel Ribbon

The very first time you launch Excel 2007, its Ribbon contains the following

seven tabs, going from left to right:

 Home tab with the command buttons normally used when creating,

formatting, and editing a spreadsheet arranged into the Clipboard, Font,

Alignment, Number, Styles, Cells, and Editing groups (see Color Plate 1)

 Insert tab with the command buttons normally used when adding par-

ticular elements (including graphics, PivotTables, charts, hyperlinks,

and headers and footers) to a spreadsheet arranged into the Shapes,

Tables, Illustrations, Charts, Links, and Text groups (see Color Plate 2)

 Page Layout tab with the command buttons normally used when pre-

paring a spreadsheet for printing or re-ordering graphics on the sheet

arranged into the Themes, Page Setup, Scale to Fit, Sheet Options, and

Arrange groups (see Color Plate 3)

 Formulas tab with the command buttons normally used when adding

formulas and functions to a spreadsheet or checking a worksheet for for-

mula errors arranged into the Function Library, Defined Names, Formula

Auditing, and Calculation groups (see Color Plate 4). Note that this tab

also contains a Solutions group when you activate certain add-in programs

such as Conditional Sum and Euro Currency Tools — see Chapter 12 for

more on using Excel add-in programs.

 Data tab with the command buttons normally used when importing,

querying, outlining, and subtotaling the data placed into a worksheet’s

data list arranged into the Get External Data, Manage Connections, Sort

& Filter, Data Tools, and Outline groups (see Color Plate 5). Note that

this tab also contains an Analysis group if you activate add-ins such as

the Analysis Toolpak and Solver Add-In — see Chapter 12 for more on

Excel add-ins.

 Review tab with the command buttons normally used when proofing,

protecting, and marking up a spreadsheet for review by others arranged

into the Proofing, Comments, and Changes, groups (see Color Plate 6).

Note that this tab also contains an Ink group with a sole Start Inking

button if you’re running Office 2007 on a Tablet PC.

 View tab with the command buttons normally used when changing the

display of the Worksheet area and the data it contains arranged into

the Workbook Views, Show/Hide, Zoom, Window, and Macros groups

(see Color Plate 7).

Chapter 1: The Excel 2007 User Experience

05_037377 ch01.qxp 11/16/06 9:23 AM Page 15

In addition to these seven standard tabs, Excel has an eighth, optional

Developer tab that you can add to the Ribbon if you do a lot of work with

macros and XML files — see Chapter 12 for more on the Developer tab.

Although these standard tabs are the ones you always see on the Ribbon

when it’s displayed in Excel, they aren’t the only things that can appear in

this area. In addition, Excel can display contextual tools when you’re working

with a particular object that you select in the worksheet such as a graphic

image you’ve added or a chart or PivotTable you’ve created. The name of the

contextual tools for the selected object appears immediately above the tab

or tabs associated with the tools.

For example, Figure 1-4 shows a worksheet after you click the embedded

chart to select it. As you can see, doing this causes the contextual tool called

Chart Tools to be added to the very end of the Ribbon. Chart Tools contex-

tual tool has its own three tabs: Design (selected by default), Layout, and

Format. Note too that the command buttons on the Design tab are arranged

into their own groups: Type, Data, Chart Layouts, Chart Styles, and Location.

The moment you deselect the object (usually by clicking somewhere on the

sheet outside of its boundaries), the contextual tool for that object and all of its

tabs immediately disappears from the Ribbon, leaving only the regular tabs —

Home, Insert, Page Layout, Formulas, Data, Review, and View — displayed.

Chart Tools Contextual tab

Figure 1-4:

When you

select

certain

objects in

the

worksheet,

Excel adds

contextual

tools to the

Ribbon with

their own

tabs,

groups, and

command

buttons.

Part I: Getting In on the Ground Floor

05_037377 ch01.qxp 11/16/06 9:23 AM Page 16

Selecting commands from the Ribbon

The most direct method for selecting commands on the Ribbon is to click the

tab that contains the command button you want and then click that button in

its group. For example, to insert a piece of Clip Art into your spreadsheet, you

click the Insert tab and then click the Clip Art button to open the Clip Art task

pane in the Worksheet area.

The easiest method for selecting commands on the Ribbon — if you know your

keyboard at all well — is to press the Alt key and then type the sequence of

letters designated as the hot keys for the desired tab and associated command

buttons.

When you first press and release the Alt key, Excel displays the hot keys for

all the tabs on the Ribbon. When you type one of the Ribbon tab hot keys to

select it, all the command button hot keys appear next to their buttons along

with the hot keys for the Dialog Box launchers in any group on that tab (see

Figure 1-5). To select a command button or Dialog Box launcher, simply type

its hot key letter.

If you know the old Excel shortcut keys from versions Excel 97 through 2003,

you can still use them. For example, instead of going through the rigmarole of

pressing Alt+HC to copy a cell selection to the Windows Clipboard and then

Alt+HV to paste it elsewhere in the sheet, you can still press Ctrl+C to copy

the selection and then press Ctrl+V when you’re ready to paste it. Note, how-

ever, that when using a hot key combination with the Alt key, you don’t need

to keep the Alt key depressed while typing the remaining letter(s) as you do

when using a hot key combo with the Ctrl key.

Figure 1-5:

When you

press Alt

plus a tab

hot key,

Excel

displays the

hot keys for

selecting all

of its

command

buttons and

Dialog Box

launchers.

Chapter 1: The Excel 2007 User Experience

05_037377 ch01.qxp 11/16/06 9:23 AM Page 17

Adapting the Quick Access toolbar

When you first start using Excel 2007, the Quick Access toolbar contains only

the following few buttons:

 Save to save any changes made to the current workbook using the same

filename, file format, and location

 Undo to undo the last editing, formatting, or layout change you made

 Redo to reapply the previous editing, formatting, or layout change that

you just removed with the Undo button

The Quick Access toolbar is very customizable as Excel makes it really easy

to add any Ribbon command to it. Moreover, you’re not restricted to adding

buttons for just the commands on the Ribbon: you can add any Excel com-

mand you want to the toolbar, even the obscure ones that don’t rate an

appearance on any of its tabs.

By default, the Quick Access toolbar appears above the Ribbon tabs immedi-

ately to the right of the Office Button. To display the toolbar beneath the

Ribbon immediately above the Formula bar, click the Customize Quick

Access Toolbar button (the drop-down button to the right of the toolbar

with a horizontal bar above a down-pointing triangle) and then click Show

Below the Ribbon on its drop-down menu. You will definitely want to make

this change if you start adding more buttons to the toolbar so that the grow-

ing Quick Access toolbar doesn’t start crowding out the name of the current

workbook that appears to the toolbar’s right.

Adding command buttons on the Customize

Quick Access Toolbar’s drop-down menu

When you click the Customize Quick Access Toolbar button, a drop-down

menu appears containing the following commands:

 New to open a new workbook

 Open to display the Open dialog box for opening an existing workbook

 Save to save changes to your current workbook

 E-mail to open your mail

 Quick Print to send the current worksheet to your default printer

 Print Preview to open the current worksheet in the Print Preview window

 Spelling to check the current worksheet for spelling errors

 Undo to undo your latest worksheet edit

Part I: Getting In on the Ground Floor

05_037377 ch01.qxp 11/16/06 9:23 AM Page 18

 Redo to reapply the last edit that you removed with Undo

 Sort Ascending to sort the current cell selection or column in A to Z

alphabetical, lowest to highest numerical, or oldest to newest date order

 Sort Descending to sort the current cell selection or column Z to A

alphabetical, highest to lowest numerical, or newest to oldest date order

When you first open this menu, only the Save, Undo, and Redo options are

selected (indicated by the check marks in front of their names) and therefore

theirs are the only buttons to appear on the Quick Access toolbar. To add any

of the other commands on this menu to the toolbar, you simply click the

option on the drop-down menu. Excel then adds a button for that command

to the end of the Quick Access toolbar (and a check mark to its option on the

drop-down menu).

To remove a command button that you add to the Quick Access toolbar in this

manner, click the option a second time on the Customize Quick Access

Toolbar button’s drop-down menu. Excel removes its command button from

the toolbar and the check mark from its option on the drop-down menu.

Adding command buttons on the Ribbon

To add any Ribbon command to the Quick Access toolbar, simply right-click

its command button on the Ribbon and then click Add to Quick Access

Toolbar on its shortcut menu. Excel then immediately adds the command

button to the very end of the Quick Access toolbar, immediately in front of

the Customize Quick Access Toolbar button.

If you want to move the command button to a new location on the Quick

Access toolbar or group with other buttons on the toolbar, you need to click

the Customize Quick Access Toolbar button and then click the More

Commands option near the bottom of its drop-down menu.

Excel then opens the Excel Options dialog box with the Customize tab

selected (similar to the one shown in Figure 1-6). Here, Excel shows all the

buttons currently added to the Quick Access toolbar with the order in which

they appear from left to right on the toolbar corresponding to their top-down

order in the list box on the right-hand side of the dialog box.

To reposition a particular button on the bar, click it in the list box on the right

and then click either the Move Up button (the one with the black triangle

pointing upward) or the Move Down button (the one with the black tri-

angle pointing downward) until the button is promoted or demoted to the

desired position on the toolbar.

Chapter 1: The Excel 2007 User Experience

05_037377 ch01.qxp 11/16/06 9:23 AM Page 19

You can add separators to the toolbar to group related buttons. To do this,

click the <Separator> selection in the list box on the left and then click the

Add button twice to add two. Then, click the Move Up or Move Down buttons

to position one of the two separators at the beginning of the group and the

other at the end.

To remove a button added from the Ribbon, right-click it on the Quick Access

toolbar and then click the Remove from Quick Access Toolbar option on its

shortcut menu.

Adding non-Ribbon commands to the Quick Access toolbar

You can also use the options on the Customize tab of the Excel Options

dialog box (see Figure 1-6) to add a button for any Excel command even if it’s

is not one of those displayed on the tabs of the Ribbon:

1. Click the type of command you want to add to the Quick Access tool-

bar in the Choose Commands From drop-down list box.

The types of commands include the File pull-down menu (the default) as

well as each of the tabs that appear on the Ribbon. To display only the

commands that are not displayed on the Ribbon, click Commands Not in

the Ribbon near the bottom of the drop-down list. To display a complete

list of all the Excel commands, click All Commands at the very bottom of

the drop-down list.

2. Click the command whose button you want to add to the Quick Access

toolbar in the list box on the left.

Figure 1-6:

Use the

buttons

on the

Customize

tab of the

Excel

Options

dialog box

to cus-

tomize the

appearance

of the Quick

Access

toolbar.

Part I: Getting In on the Ground Floor

05_037377 ch01.qxp 11/16/06 9:23 AM Page 20

3. Click the Add button to add the command button to the bottom of the

list box on the right.

4. (Optional) To reposition the newly added command button so that it’s

not the last one on the toolbar, click the Move Up button until it’s in

the desired position.

5. Click the OK button to close Excel Options dialog box.

If you’ve created favorite macros (see Chapter 12) that you routinely use and

want to be able to run directly from the Quick Access toolbar, click Macros in

the Choose Commands From drop-down list box in the Excel Options dialog

box and then click the name of the macro to add followed by the Add button.

Having fun with the Formula bar

The Formula bar displays the cell address and the contents of the current

cell. The address of this cell is determined by its column letter(s) followed

immediately by the row number as in cell A1, the very first cell of each work-

sheet at the intersection of column A and row 1 or cell XFD1048576, the very

last of each Excel 2007 worksheet, at the intersection of column XFD and row

1048576. The contents of the current cell are determined by the type of entry

you make there: text or numbers if you just enter a heading or particular

value and the nuts and bolts of a formula if you enter a calculation there.

The Formula bar is divided into three sections:

 Name box: The left-most section that displays the address of the current

cell address

 Formula bar buttons: The second, middle section that appears as a

rather nondescript button displaying only an indented circle on the left

(used to narrow or widen the Name box) with the Function Wizard

button (labeled fx) on the right until you start making or editing a cell

entry at which time, its Cancel (an X) and its Enter (a check mark) but-

tons appear in between them

 Cell contents: The third, right-most white area to the immediate right of

the Function Wizard button that takes up the rest of the bar and expands

as necessary to display really, really long cell entries that won’t fit the

normal area

The Cell contents section of the Formula bar is really important because it

always shows you the contents of the cell even when the worksheet does not

(when you’re dealing with a formula, Excel displays only the calculated result

in the cell in the worksheet and not the formula by which that result is derived)

and you can edit the contents of the cell in this area at anytime. By the same

token, when the Contents area is blank, you know that the cell is empty as well.

Chapter 1: The Excel 2007 User Experience

05_037377 ch01.qxp 11/16/06 9:23 AM Page 21

What to do in the Worksheet area

The Worksheet area is where most of the Excel spreadsheet action takes

place because it’s the place that displays the cells in different sections of the

current worksheet and it’s right inside the cells that you do all your spread-

sheet data entry and formatting, not to mention a great deal of your editing.

Keep in mind that in order for you to be able to enter or edit data in a cell,

that cell must be current. Excel indicates that a cell is current in three ways:

 The cell cursor — the dark black border surrounding the cell’s entire

perimeter — appears in the cell

 The address of the cell appears in the Name box of the Formula bar

 The cell’s column letter(s) and row number are shaded (in a kind of a

beige color on most monitors) in the column headings and row headings

that appear at the top and left of the Worksheet area, respectively

Moving around the worksheet

An Excel worksheet contains far too many columns and rows for all of a

worksheet’s cells to be displayed at one time regardless of how large your

personal computer monitor screen is or how high the screen resolution.

(After all, we’re talking 17,179,869,184 cells total!) Excel therefore offers many

methods for moving the cell cursor around the worksheet to the cell where

you want to enter new data or edit existing data:

 Click the desired cell — assuming that the cell is displayed within the

section of the sheet currently visible in the Worksheet area

Click the Name box, type the address of the desired cell directly into this

box and then press the Enter key

Part I: Getting In on the Ground Floor

How you assign 26 letters to 16,384 columns

When it comes to labeling the 16,384 columns of

an Excel 2007 worksheet, our alphabet with its

measly 26 letters is simply not up to the task. To

make up the difference, Excel first doubles the

letters in the cell’s column reference so that

column AA follows column Z (after which you

find column AB, AC, and so on) and then triples

them so that column AAA follows column ZZ

(after which you get column AAB, AAC, and the

like). At the end of this letter tripling, the 16,384th

and last column of the worksheet ends up being

XFD so that the last cell in the 1,048,576th row

has the cell address XFD1048576.

05_037377 ch01.qxp 11/16/06 9:23 AM Page 22

 Press F5 to open the Go To dialog box, type the address of the desired

cell into its Reference text box and then click OK

 Use the cursor keys as shown in Table 1-1 to move the cell cursor to the

desired cell

 Use the horizontal and vertical scroll bars at the bottom and right edge

of the Worksheet area to move the part of the worksheet that contains

the desired cell and then click the cell to put the cell cursor in it

Keystroke shortcuts for moving the cell cursor

Excel offers a wide variety of keystrokes for moving the cell cursor to a new

cell. When you use one of these keystrokes, the program automatically

scrolls a new part of the worksheet into view, if this is required to move the

cell pointer. In Table 1-1, I summarize these keystrokes and how far each one

moves the cell pointer from its starting position.

Table 1-1 Keystrokes for Moving the Cell Cursor

Keystroke Where the Cell Cursor Moves

→ or Tab Cell to the immediate right.

← or Shift+Tab Cell to the immediate left.

↑ Cell up one row.

↓ Cell down one row.

Home Cell in Column A of the current row.

Ctrl+Home First cell (A1) of the worksheet.

Ctrl+End or End, Home Cell in the worksheet at the intersection of the last

column that has any data in it and the last row that has

any data in it (that is, the last cell of the so-called active

area of the worksheet).

PgUp Cell one full screen up in the same column.

PgDn Cell one full screen down in the same column.

Ctrl+

→ or End, → First occupied cell to the right in the same row that is

either preceded or followed by a blank cell. If no cell is

occupied, the pointer goes to the cell at the very end of

the row.

Ctrl+

← or End, ← First occupied cell to the left in the same row that is

either preceded or followed by a blank cell. If no cell is

occupied, the pointer goes to the cell at the very begin-

ning of the row.

(continued)

Chapter 1: The Excel 2007 User Experience

05_037377 ch01.qxp 11/16/06 9:23 AM Page 23

Table 1-1

(continued)

Keystroke Where the Cell Cursor Moves

Ctrl+↑ or End, ↑ First occupied cell above in the same column that is

either preceded or followed by a blank cell. If no cell is

occupied, the pointer goes to the cell at the very top of

the column.

Ctrl+↓ or End, ↓ First occupied cell below in the same column that is

either preceded or followed by a blank cell. If no cell is

occupied, the pointer goes to the cell at the very bottom

of the column.

Ctrl+Page Down Last occupied cell in the next worksheet of that workbook.

Ctrl+Page Up Last occupied cell in the previous worksheet of that

workbook.

Note: In the case of those keystrokes that use arrow keys, you must either use the arrows on the

cursor keypad or else have the Num Lock disengaged on the numeric keypad of your keyboard.

The keystrokes that combine the Ctrl or End key with an arrow key listed in

Table 1-1 are among the most helpful for moving quickly from one edge to the

other in large tables of cell entries or in moving from table to table in a sec-

tion of the worksheet that contains many blocks of cells.

When you use Ctrl and an arrow key to move from edge to edge in a table or

between tables in a worksheet, you hold down Ctrl while you press one of the

four arrow keys (indicated by the + symbol in keystrokes, such as Ctrl+→).

When you use End and an arrow-key alternative, you must press and then

release the End key before you press the arrow key (indicated by the comma

in keystrokes, such as End, →). Pressing and releasing the End key causes the

End Mode indicator to appear on the status bar. This is your sign that Excel is

ready for you to press one of the four arrow keys.

Because you can keep the Ctrl key depressed as you press the different arrow

keys that you need to use, the Ctrl-plus-arrow-key method provides a more

fluid method for navigating blocks of cells than the End-then-arrow-key

method.

You can use the Scroll Lock key to “freeze” the position of the cell pointer in

the worksheet so that you can scroll new areas of the worksheet in view with

keystrokes such as PgUp (Page Up) and PgDn (Page Down) without changing

the cell pointer’s original position (in essence, making these keystrokes work

in the same manner as the scroll bars).

Part I: Getting In on the Ground Floor

05_037377 ch01.qxp 11/16/06 9:23 AM Page 24

After engaging Scroll Lock, when you scroll the worksheet with the keyboard,

Excel does not select a new cell while it brings a new section of the work-

sheet into view. To “unfreeze” the cell pointer when scrolling the worksheet

via the keyboard, you just press the Scroll Lock key again.

Tips on using the scroll bars

To understand how scrolling works in Excel, imagine its humongous work-

sheet as a papyrus scroll attached to rollers on the left and right. To bring

into view a new section of a papyrus worksheet that is hidden on the right,

you crank the left roller until the section with the cells that you want to see

appears. Likewise, to scroll into view a new section of the worksheet that is

hidden on the left, you would crank the right roller until that section of cells

appears.

You can use the horizontal scroll bar at the bottom of the Worksheet area to

scroll back and forth through the columns of a worksheet and the vertical

scroll bar to scroll up and down through its rows. To scroll a column or a

row at a time in a particular direction, click the appropriate scroll arrow

at the ends of the scroll bar. To jump immediately back to the originally

displayed area of the worksheet after scrolling through single columns or

rows in this fashion, simply click the black area in the scroll bar that now

appears in front of or after the scroll bar.

Keep in mind that you can resize the horizontal scroll bar making it wider or

narrower by dragging the button that appears to the immediate left of its left

scroll arrow. Just keep in mind when working in a workbook that contains a

whole bunch of worksheets that in widening the horizontal scroll bar you can

end up hiding the display of the workbook’s later sheet tabs.

To scroll very quickly through columns or rows of the worksheet, hold down

the Shift key and then drag the mouse pointer in the appropriate direction

within the scroll bar until the columns or rows that you want to see appear

on the screen in the Worksheet area. When you hold down the Shift key as

you scroll, the scroll button within the scroll bar becomes real skinny and

a ScreenTip appears next to the scroll bar, keeping you informed of the

letter(s) of the columns or the numbers of the rows that you’re currently

whizzing through.

If your mouse has a wheel, you can use it to scroll directly through the columns

and rows of the worksheet without using the horizontal or verticals scroll bars.

Simply position the white-cross mouse pointer in the center of the Worksheet

area and then hold down the wheel button of the mouse. When the mouse

pointer changes to a four-pointed arrow, drag the mouse pointer in the appro-

priate direction (left and right to scroll through columns or up and down to

scroll through rows) until the desired column or row comes into view in the

Worksheet area.

Chapter 1: The Excel 2007 User Experience

05_037377 ch01.qxp 11/16/06 9:23 AM Page 25

The only disadvantage to using the scroll bars to move around is that the

scroll bars bring only new sections of the worksheet into view — they don’t

actually change the position of the cell cursor. If you want to start making

entries in the cells in a new area of the worksheet, you still have to remember

to select the cell (by clicking it) or the group of cells (by dragging through

them) where you want the data to appear before you begin entering the data.

Surfing the sheets in a workbook

Each new workbook you open in Excel 2007 contains three blank worksheets,

each with its own 16,384 columns and 1,048,576 rows (giving you a truly stag-

gering total of 51,539,607,552 blank cells!). But that’s not all, if ever you need

more worksheets in your workbook; you can add them simply by clicking the

Insert Worksheet button that appears to the immediate right of the last sheet

tab (see Figure 1-7).

First sheet

Previous sheet

Next sheet

Last sheet

Sheet Tab scroll buttons

Insert Worksheet

Figure 1-7:

The Sheet

Tab scroll

buttons,

sheet tabs,

and Insert

Worksheet

button

enable you

to activate

your

worksheets

and add

to them.

Part I: Getting In on the Ground Floor

05_037377 ch01.qxp 11/16/06 9:23 AM Page 26

On the left side of the bottom of the Worksheet area, the Sheet Tab scroll but-

tons appear followed by the actual tabs for the worksheets in your workbook

and the Insert Worksheet button. To activate a worksheet for editing, you

select it by clicking its sheet tab. Excel lets you know what sheet is active by

displaying the sheet name in boldface type and making its tab appear on top

of the others.

Don’t forget the Ctrl+Page Down and Ctrl+Page Up shortcut keys for selecting

the next and previous sheet, respectively, in your workbook.

If your workbook contains too many sheets for all their tabs to be displayed

at the bottom of the Worksheet area, use the Sheet Tab scroll buttons to

bring new tabs into view (so that you can then click them to activate them).

You click the Next Sheet button to scroll the next hidden sheet tab into view

or the Last Sheet button to scroll the last group of completely or partially

hidden tabs into view.

Showing off the Status bar

The Status bar is the last component at the very bottom of the Excel program

window (see Figure 1-8). The Status bar contains the following areas:

 Mode button that indicates the current state of the Excel program

(Ready, Edit, and so on) as well as any special keys that are engaged

(Caps Lock, Num Lock, and Scroll Lock)

 Macro Recording button (the red dot) that opens the Record Macro

dialog box where you can set the parameters for a new macro and begin

recording it (see Chapter 12)

Chapter 1: The Excel 2007 User Experience

One reason for adding extra sheets to a workbook

You may wonder why on earth anyone would

ever need more than three worksheets given just

how many cells each individual sheet contains.

The simple truth is that it’s all about how you

choose to structure a particular spreadsheet

rather than running out of places to put the data.

For example, suppose that you need to create a

workbook that contains budgets for all the vari-

ous departments in your corporation, you may

decide to devote an individual worksheet to each

department (with the actual budget spreadsheet

tables laid out in the same manner on each

sheet) rather than placing all the tables in differ-

ent sections of the same sheet. Using this kind of

one-sheet-per-budget layout makes it much

easier for you to find each budget, print each one

as a separate page of a report, and, if ever nec-

essary, to consolidate their data in a separate

summary worksheet.

05_037377 ch01.qxp 11/16/06 9:23 AM Page 27

 AutoCalculate indicator that displays the Average and Sum of all the

numerical entries in the current cell selection along with the Count of

every cell in selection

 Layout selector that enables you to select between three layouts for the

Worksheet area: Normal, the default view that shows only the worksheet

cells with the column and row headings; Page Layout View that adds

rulers, page margins, and shows page breaks for the worksheet; and

Page Break Preview that enables you to adjust the paging of a report

(see Chapter 5 for details)

 Zoom slider that enables you to zoom in and out on the cells in the

Worksheet area by dragging the slider to the right or left, respectively

The Num Lock indicator tells you that you can use the numbers on the

numeric keypad for entering values in the worksheet. This keypad will most

often be separate from the regular keyboard on the right side if you’re using a

separate keyboard and embedded in keys on the right side of the regular key-

board on almost all laptop computers where the keyboard is built right into

the computer.

Mode Indicator

Auto Calculate Indicator Layout Selector

Zoom slider

Record Macro

Figure 1-8:

The Status

bar displays

the

program’s

current

standing

and enables

you to

select new

worksheet

views.

Part I: Getting In on the Ground Floor

05_037377 ch01.qxp 11/21/06 2:29 PM Page 28

Starting and Exiting Excel

Excel 2007 runs under both the older Windows XP operating system and the

brand new Windows Vista operating system. Because of changes made to

the Start menu in Windows Vista, the procedure for starting Excel from this

version of Windows is a bit different from Windows XP.

Starting Excel from the Windows

Vista Start menu

You can use the Start Search box at the bottom of the Windows Vista Start

menu to locate Excel on your computer and launch the program in no time

at all:

1. Click the Start button on the Windows taskbar to open the Windows

Start menu.

2. Click the Start Search text box and type the two letters

ex to have

Vista locate Microsoft Office Excel 2007 on your computer.

3. Click the Microsoft Office Excel 2007 option that now appears in the

left Programs column on the Start menu.

If you have more time on your hands, you can also launch Excel from

the Vista Start menu by going through the rigmarole of clicking Start

➪

All Programs➪Microsoft Office➪Microsoft Office Excel 2007.

Starting Excel from the Windows

XP Start menu

When starting Excel 2007 from the Windows XP Start menu, you follow these

simple steps:

1. Click the Start button on the Windows taskbar to open the Windows

Start menu.

2. With the mouse, highlight All Programs on the Start menu and then

Microsoft Office on the Start continuation menu before clicking the

Microsoft Office Excel 2007 option on the Microsoft Office continua-

tion menu.

Chapter 1: The Excel 2007 User Experience

05_037377 ch01.qxp 11/16/06 9:23 AM Page 29

Pinning Excel to the Start menu

If you use Excel all the time, you may want to make its program option a per-

manent part of the Windows Start menu. To do this, you pin the program

option to the Start menu (and the steps for doing this are the same in

Windows XP as they are in Windows Vista):

1. Start Excel from the Windows Start menu.

In launching Excel, use the appropriate method for your version of

Windows as outlined in the “Starting Excel from the Windows Vista Start

menu” or the “Starting Excel from the Windows XP Start menu” section

earlier in this chapter.

After launching Excel, Windows adds Microsoft Office 2007 to the

recently used portion on the left side of the Windows Start menu.

2. Click the Start menu and then right-click Microsoft Excel 2007 on the

Start menu to open its shortcut menu.

3. Click Pin to Start menu on the shortcut menu.

After pinning Excel in this manner, the Microsoft Office Excel 2007 option

always appears in the upper section of the left-hand column of the Start

menu and you can then launch Excel simply by clicking the Start button and

then click this option.

Creating an Excel desktop shortcut

for Windows Vista

Some people prefer having the Excel Program icon appear on the Windows

desktop so that they can launch the program from the desktop by double-

clicking this program icon. To create an Excel program shortcut for Windows

Vista, you follow these steps:

1. Click the Start button on the Windows taskbar.

The Start menu opens where you click the Start Search text box.

2. Click the Start Search text box and type excel.exe.

Excel.exe is the name of the executable program file that runs Excel.

After finding this file on your hard disk, you can create a desktop short-

cut from it that launches the program.

Part I: Getting In on the Ground Floor

05_037377 ch01.qxp 11/16/06 9:23 AM Page 30

3. Right-click the file icon for the excel.exe file at the top of the Start

menu and then highlight Send To on the pop-up menu and click

Desktop (Create Shortcut) on its continuation menu.

A shortcut named EXCEL -Shortcut appears to your desktop. You should

probably rename the shortcut to something a little more friendly, such

as Excel 2007.

4. Right-click the EXCEL - Shortcut icon on the Vista desktop and then

click Rename on the pop-up menu.

5. Replace the current name by typing a new shortcut name, such as

Excel 2007 and then click anywhere on the desktop.

Creating an Excel desktop

shortcut for Windows XP

If you’re running Excel 2007 on Windows XP, you use the following steps to

create a program shortcut for your Windows XP desktop:

1. Click the Start button on the Windows taskbar.

The Start menu opens the Search item.

2. Click Search in the lower-right corner of the Start menu.

The Search Results dialog box appears.

3. Click the All Files and Folders link in the panel on the left side of the

Search Results dialog box.

The Search Companion pane appears on the left side of the Search

Results dialog box.

4. Type excel.exe in the All or Part of the File Name text box.

Excel.exe is the name of the executable program file that runs Excel.

After finding this file on your hard disk, you can create a desktop short-

cut from it that launches the program.

5. Click the Search button.

Windows now searches your hard disk for the Excel program file. After

locating this file, its name appears on the right side of the Search Results

dialog box. When this filename appears, you can click the Stop button in

the left panel to halt the search.

6. Right-click the file icon for the excel.exe file and then highlight

Send To on the pop-up menu and click Desktop (Create Shortcut) on

its continuation menu.

A shortcut named Shortcut to excel.exe appears on your desktop.

Chapter 1: The Excel 2007 User Experience

05_037377 ch01.qxp 11/16/06 9:23 AM Page 31

7. Click the Close button in the upper-right corner of the Search Results

dialog box.

After closing the Search Results dialog box, you should see the icon

named Shortcut to excel.exe on the desktop. You should probably rename

the shortcut to something a little more friendly, such as Excel 2007.

8. Right-click the Shortcut to excel.exe icon and then click Rename on

the pop-up menu.

9. Replace the current name by typing a new shortcut name, such as

Excel 2007 and then click anywhere on the desktop.

After you create an Excel desktop shortcut on the Windows XP desktop you

can launch Excel by double-clicking the shortcut icon.

Adding the Excel desktop shortcut

to the Quick Launch toolbar

If you want to be able to launch Excel 2007 by clicking a single button, drag

the icon for your Excel Windows Vista or XP desktop shortcut to the Quick

Launch toolbar to the immediate right of the Start button at the beginning of

the Windows taskbar. When you position the icon on this toolbar, Windows

indicates where the new Excel button will appear by drawing a black, vertical

I-beam in front of or between the existing buttons on this bar. As soon as you

release the mouse button, Windows adds an Excel 2007 button to the Quick

Launch toolbar that enables you to launch the program by a single-click of

its icon.

Exiting Excel

When you’re ready to call it a day and quit Excel, you have several choices

for shutting down the program:

 Click the Office Button followed by the Exit Excel button

 Press Alt+FX or Alt+F4

 Click the Close button in the upper-right corner of the Excel program

window (the X)

If you try to exit Excel after working on a workbook and you haven’t saved

your latest changes, the program beeps at you and displays an alert box

querying whether you want to save your changes. To save your changes

Part I: Getting In on the Ground Floor

05_037377 ch01.qxp 11/16/06 9:23 AM Page 32

before exiting, click the Yes command button. (For detailed information on

saving documents, see Chapter 2.) If you’ve just been playing around in the

worksheet and don’t want to save your changes, you can abandon the docu-

ment by clicking the No button.

Help Is on the Way

You can get online help with Excel 2007 anytime that you need it while using

the program. Simply click the Help button with the question mark icon on the

right side of the program window opposite the tabs on the Ribbon or press

F1 to open a separate Excel Help window (see Figure 1-9).

When the Help window first opens, Excel attempts to use your Internet con-

nection to update its topics. The opening Help window contains a bunch of

links that you can click to get information on what’s new in the program to

quizzes that test your knowledge of Excel.

To get help with a particular command or function, use the Search text box

at the top of the Excel Help window. Type keywords or a phrase describing

your topic (such as “print preview” or “printing worksheets”) in this text

box and then press Enter or click the Search button. The Excel Help window

then presents a list of links to related help topics that you can click to display

their information.

Figure 1-9:

The

Microsoft

Office Excel

Help window

automati-

cally

connects

you to the

Internet

when you

open it.

Chapter 1: The Excel 2007 User Experience

05_037377 ch01.qxp 11/16/06 9:23 AM Page 33

To print the help topic currently displayed in the Excel Help window, click

the Print button (with the printer icon) on its toolbar. Excel then opens a

Print dialog box where you can select the printer and options to use in print-

ing the information.

To display a table of contents with all the main categories and subtopics

arranged hierarchically, click the Show Table of Contents button (with the

book icon) on the toolbar.

Migrating to Excel 2007

from Earlier Versions

If you’re a brand new Excel user, you’re going to take to the program’s new

Ribbon User Interface like a duck to water. However, if you’re coming to Excel

2007 as a dedicated user of any of the earlier Excel versions (from Excel 97 all

the way through Excel 2003), the first time you launch Excel 2007 and take a

gander at the Ribbon, you’re probably going to feel more like someone just

threw you into the deep end of the pool without a life preserver.

Don’t panic! Simply use this section of the chapter as your Excel 2007 life pre-

server. It’s intended to get you oriented, keep your head above water, and

have you swimming with the new interface in no time at all. Just give me five

minutes of your precious time and I promise I’ll have you up and running with

Excel 2007 and, maybe even smiling again. Now, take a deep breath, and here

we go. . . .

First, the bad news: there is no Classic mode in Excel 2007 that will magically

turn that fat, screen-real estate stealing Ribbon back into those sleek and tried

and true pull-down menus (thanks Microsoft, I needed that)! After the wonder-

ful designers and engineers at Microsoft got through dumping all the pull-down

menus and toolbars that you worked so diligently to master and on which you

relied every Excel workday of your life, there was just nothing left for them to

hang a Classic mode onto.

Now, for the good news: you really don’t need a Classic mode — you just

need to find out where those scoundrel engineers went and put all the stuff

you used to do so effortlessly in versions of Excel before the Ribbon User

Interface. After all, you already know what most of those pull-down menu

items and toolbar buttons do; all you have to do is locate them.

Part I: Getting In on the Ground Floor

05_037377 ch01.qxp 11/16/06 9:23 AM Page 34

Cutting the Ribbon down to size

First thing to do is to get that busy Ribbon out of your face. At this point, it’s

just taking up valuable work space and probably making you crazy. So, please

double-click any one of the tabs or press Ctrl+F1 right now to cut the Ribbon

display down to only its tabs. Single-clicking a tab then temporarily redisplays

the Ribbon until you select one of its command buttons, whereas pressing

Ctrl+F1 a second time redisplays the Ribbon and keeps it open in all its glory.

When only the tabs — Home through View — are showing at the top of the

Excel program window, you should feel a whole lot more comfortable with the

screen. The Excel 2007 screen is then as clean and uncluttered, if not more, as

the earlier version of Excel that you were using with only the Quick Access

toolbar, Ribbon tabs, and Formula bar displayed above the Worksheet area.

Now, you’re probably wondering where those Microsoft engineers moved the

most important and commonly-used pull-down menu commands. Table 1-2

shows the Excel 2007 equivalents for the menu commands you probably used

most often in doing your work in the earlier version of Excel.

When a particular command is assigned to one of the tabs on the Ribbon,

Table 1-2 lists only the tab and command button name without naming the

group since the group name plays no part in selecting the command. So, for

example, the table lists the tab+command button equivalent of the

View

➪Header and Footer command as Insert | Header & Footer without

regard to the fact that the Header & Footer button is part of the Text group

on the Insert tab.

Table 1-2: Excel 2007 Equivalents for Common Pull-Down Menu

Commands in Excel 2003

Excel 2003 Excel 2007 Common Excel 2007

Command Equivalent Shortcut Keys Shortcut Keys

File Menu

File➪New Office Button | New Ctrl+N Alt+FN

File➪Open Office Button | Open Ctrl+O Alt+FO

File

➪Save Office Button | Save or Ctrl+S Alt+FS

Save button on the

Quick Access Toolbar

(continued)

Chapter 1: The Excel 2007 User Experience

05_037377 ch01.qxp 11/16/06 9:23 AM Page 35

Table 1-2

(continued)

Excel 2003 Excel 2007 Common Excel 2007

Command Equivalent Shortcut Keys Shortcut Keys

File➪Save As Office Button | Save As F12 Alt+FA

File➪Print Office Button | Print Ctrl+P Alt+FP

File

➪Send To➪ Office Button| Alt+FDE

Mail Recipient Send|Email

File

➪Send To➪ Office Button| Alt+FDX

Recipient Using Send

| Internet Fax

Internet Fax

Service

File➪Close Office Button | Close Ctrl+W Alt+FC

Edit Menu

Edit➪Office Home | Dialog Box Alt+HFO

Clipboard launcher in the

Clipboard group

Edit

➪Clear➪All Home | Clear (eraser Alt+HEA

icon) | Clear All

Edit

➪Clear➪ Home | Clear (eraser Alt+HEF

Formats icon) | Clear Formats

Edit

➪Clear➪ Home | Clear (eraser Delete key Alt+HEC

Contents icon) | Clear Contents

Edit

➪Clear➪ Home | Clear (eraser Alt+HEM

Comments icon) | Clear Comments

Edit➪Delete Home | Delete Alt+HD

Edit

➪Move or Home | Format | Move Alt+HOM

Copy Sheet or Copy Sheet

Edit

➪Find Home | Find & Select | Ctrl+F Alt+HFDF

Find

Edit

➪Replace Home | Find & Select | Ctrl+H Alt+HFDR

Replace

View Menu

View➪Header Insert | Header

and Footer & Footer

View

➪Full View | Full Screen

Screen

Part I: Getting In on the Ground Floor

05_037377 ch01.qxp 11/16/06 9:23 AM Page 36

Excel 2003 Excel 2007 Common Excel 2007

Command Equivalent Shortcut Keys Shortcut Keys

Insert Menu

Insert➪Cells Home | Insert | Alt+HII

Insert Cells

Insert

➪Rows Home | Insert | Alt+HIR

Insert Sheet Rows

Insert

➪Columns Home | Insert | Alt+HIC

Insert Sheet Columns

Insert➪Worksheets Home | Insert | Insert Sheet Alt+HIS

Insert➪Symbol Insert | Symbol Alt+NU

Insert

➪Page Page Layout | Page Alt+PBI

Break Breaks | Insert Page Break

Insert

➪Name➪ Formulas | Define Name | Alt+MMD

Define Define Name

Insert

➪Name➪ Formulas | Use Alt+MS

Paste in Formula

Insert

➪Name➪ Formulas | Create Alt+MC

Create from Selection

Insert

➪Name➪ Formulas | Name Alt+MN

Label Manager

Insert

➪Comment Review | New Alt+RC

Comment

Insert➪Picture Insert | Picture Alt+NP

Insert➪Hyperlink Insert | Hyperlink Ctrl+K Alt+NI

Format Menu

Format➪Cells Home | Format | Cells Ctrl+1 Alt+HOE

Format

➪Row➪ Home | Format | Alt+HOH

Height Height

Format

➪Row➪ Home | Format | Alt+HOA

AutoFit AutoFit Row Height

Format

➪Row➪ Home | Format | Alt+HOUR/

Hide/Unhide Hide & Unhide | Hide Alt+HOUO

Rows/Unhide Rows

(continued)

Chapter 1: The Excel 2007 User Experience

05_037377 ch01.qxp 11/16/06 9:23 AM Page 37

Table 1-2

(continued)

Excel 2003 Excel 2007 Common Excel 2007

Command Equivalent Shortcut Keys Shortcut Keys

Format➪ Home | Format | Alt+HOW

Column➪Width Column Width

Format

➪ Home | Format | Hide & Alt+HOUC/

Column

➪ Unhide | Hide Columns/ Alt+HOUL

Hide/Unhide Unhide Columns

Format

➪ Home | Format | Alt+HOD

Column

➪ Default Width

Standard Width

Format

➪Sheet➪ Home | Format | Alt+HOR

Rename Rename Sheet

Format

➪Sheet➪ Home | Format | Hide & Alt+HOUS/

Hide/Unhide Unhide | Hide Sheet/ Alt+HOUH

Unhide Sheet

Format

➪Sheet➪ Page Layout | Alt+PG

Background Background

Format

➪Sheet➪ Home | Format | Alt+HOT

Tab Color Tab Color

Format

➪ Home | Format Alt+HT

AutoFormat as Table

Format

➪ Home | Conditional Alt+HL

Conditional Formatting

Formatting

Format➪Style Home | Cell Styles Alt+HJ

Tools Menu

Tools➪Spelling Review | Spelling F7 Alt+RS

Tools➪Research Review | Research Alt+RR

Tools

➪Error Formulas | Error Alt+MK

Checking Checking

Tools

➪Speech➪

Available only as custom

Show Text to

Speak Cells, Speak Cells -

Speech Toolbar

Stop Speak Cells, Speak Cells

by Columns, Speak Cells by

Rows and Speak Cells on

Enter buttons added to

Quick Access toolbar

Part I: Getting In on the Ground Floor

05_037377 ch01.qxp 11/16/06 9:23 AM Page 38

Excel 2003 Excel 2007 Common Excel 2007

Command Equivalent Shortcut Keys Shortcut Keys

Tools➪Track Review | Track Alt+RG

Changes Changes

Tools

➪ Review | Protect Alt+RPS

Protection

➪ Sheet

Protect Sheet

Tools

➪ Review | Allow Users Alt+RU

Protection

➪ to Edit Ranges

Allow Users

to Edit Ranges

Tools

➪ Review | Protect Alt+RPW

Protection

➪ Workbook

Protect

Workbook

Tools

➪ Review | Protect Alt+RO

Protection

➪ Sharing

Protect and

Share Workbook

Tools➪Macro View | Macros Alt+F8 Alt+WM

Tools

➪Add-Ins Office Button | Excel Alt+FIAA and

Options | Add-Ins Alt+G

Tools

➪ Office Button | Excel Alt+FIPP and

AutoCorrect Options

| Proofing | Alt+A

Options AutoCorrect Options

Tools

➪Options Office Button | Excel Alt+FI

Options

Data Menu

Data➪Sort Data | Sort or Home | Sort Alt+AS or

& Filter | Custom Sort Alt+HSU

Data

➪Filter➪ Data | Filter Alt+AT

AutoFilter

Data

➪Filter➪ Data | Advanced Alt+AQ

Advanced Filter

Data

➪Form

Available only as a

custom Form button

added to Quick Access

toolbar

(continued)

Chapter 1: The Excel 2007 User Experience

05_037377 ch01.qxp 11/16/06 9:23 AM Page 39

Table 1-2

(continued)

Excel 2003 Excel 2007 Common Excel 2007

Command Equivalent Shortcut Keys Shortcut Keys

Data➪Subtotals Data | Subtotal Alt+AB

Data

➪Validation Data | Data Validation | Alt+AVV

Data Validation

Data

➪Table Data | What-If Analysis | Alt+AWT

Data Table

Data

➪Text Data | Convert Text Alt+AE

to Columns to Table

Data

➪ Data | Consolidate Alt+AN

Consolidate

Data

➪Group Data | Group/Ungroup Alt+AG/

and Outline Alt+AU

Data

➪PivotTable Insert | PivotTable | Alt+NVT/

and PivotChart PivotTable/PivotChart Alt+NVC

Report

Data

➪Import Data | From Other Alt+AFO

External Data Sources

Window Menu

Window➪ View | New Window Alt+WN

New Window

Window

➪ View | Arrange Alt+WA

Arrange

Window

➪ View | View Side by Alt+WB

Compare Side Side (two-page icon

by Side in Window group)

Window

➪ View | Hide/Unhide Alt+WU

Hide, Unhide Alt+WH/

Window➪Split View | Split Alt+WS

Window

➪ View | Freeze Panes Alt+WF

Freeze Panes

Part I: Getting In on the Ground Floor

05_037377 ch01.qxp 11/16/06 9:23 AM Page 40

For the most part, the pull-down menu commands listed in Table 1-2 are logi-

cally located. The ones that take the most getting used to are the Header and

Footer and PivotTable/Chart commands that are located on the Insert tab

rather than the View tab and Data tab as might be expected given they inhab-

ited, respectively, the View and Data pull-down menus in earlier Excel versions.

In addition, the worksheet background command ended up all by its lonesome

on the Page Layout tab rather than going to the Home tab with all its fellow

formatting commands.

Finding the Standard Toolbar

buttons equivalents

If you’re like me, you came to rely heavily on the buttons of the Standard tool-

bar in doing all sorts of everyday tasks in earlier versions of Excel. Table 1-3

shows you the Excel 2007 equivalents for the buttons on the Standard toolbar

in Excel 2003. As you can see from this table, most of these Standard toolbar

buttons are regulated to one of the places in Excel 2007:

 Office pull-down menu activated by clicking the Office Button or press-

ing Alt+F (New, Open, Save, Print Preview)

 Quick Access toolbar (Save, Undo, and Redo)

 Home tab in the Clipboard group (Cut, Copy, Paste, and Format Painter)

and Editing group (AutoSum, Sort Ascending, and Sort Descending)

Table 1-3: Excel 2007 Equivalents for the Standard Toolbar

Buttons in Excel 2003

Toolbar Excel 2007 Common Excel 2007

button Equivalent Shortcut Keys Shortcut Keys

New Office Button | New Ctrl+N Alt+FN

Open Office Button | Open Ctrl+O Alt+FO

Save Office Button

| Save or Ctrl+S Alt+FS

Save button on Quick

Access toolbar

Permission

Available only as a

custom Permission

button added to

Quick Access toolbar

(continued)

Chapter 1: The Excel 2007 User Experience

05_037377 ch01.qxp 11/16/06 9:23 AM Page 41

Table 1-3

(continued)

Toolbar Excel 2007 Common Excel 2007

button Equivalent Shortcut Keys Shortcut Keys

E-mail Office Button | Send | Alt+FDE

Print Quick Print button on

Quick Access toolbar

Print Preview Office Button

| Print | Alt+FWV

Print Preview

Spelling Review | Spelling F7 Alt+RS

Research Review | Research Alt+RR

Cut Home | Cut (scissors Ctrl+X Alt+HX

icon in Clipboard group)

Copy Home | Copy (double- Ctrl+C Alt+HC

sheet icon in Clipboard

group)

Paste Home | Paste Ctrl+V Alt+HV

Format Painter Home | Format Painter Alt+HFP

(brush icon in Clipboard

group)

Undo Undo button on Quick Ctrl+Z

Access toolbar

Redo Redo button on Quick Ctrl+Y

Access toolbar

Toolbar Excel 2007 Common Excel 2007

button Equivalent Shortcut Keys Shortcut Keys

Insert Ink Review | Start Inking Alt+RK

Annotations

Insert Hyperlink Insert | Hyperlink Ctrl+K Alt+NI

AutoSum Home | Sum Alt+HU

(( - Sigma icon)

Sort Ascending Home | Sort & Filter | Alt+HSS

Sort A to Z

Part I: Getting In on the Ground Floor

05_037377 ch01.qxp 11/16/06 9:23 AM Page 42

Toolbar Excel 2007 Common Excel 2007

button Equivalent Shortcut Keys Shortcut Keys

Sort Descending Home | Sort & Filter | Alt+HSO

Sort Z to A

ChartWizard

Not available except

as specific chart type

command buttons in

the Charts group

on the Insert tab

Drawing

Not available except

as command buttons

in the Shapes,

Illustrations, and Text

groups on the Insert tab

and as custom buttons

added to Quick Access

toolbar

Zoom View | Zoom Alt+WQ

Microsoft Excel Microsoft Office Excel F1

Help Help button to the right

of the Ribbon tabs

Because Excel 2007 supports only a single toolbar, the Quick Access toolbar,

the Drawing toolbar disappears completely from Excel 2007 and thus the

Drawing button on the Standard toolbar has no equivalent. Most of its main

features, including Clip Art, inserting graphics files, and creating diagrams

and WordArt are now found on the Insert tab. Also, keep in mind that Excel

2007 doesn’t have an equivalent to the ChartWizard button on the Standard

toolbar because you can create a chart in a split-second by clicking the

Column, Line, Pie, Bar, Area, XY (Scatter), or Other Charts command buttons

on the Insert tab (see Chapter 8).

Finding the Formatting Toolbar

buttons equivalents

Finding the Excel 2007 equivalents for the buttons on the Formatting toolbar

in earlier versions of Excel couldn’t be easier: Every one of the buttons on the

Formatting toolbar is prominently displayed on the Home tab of the Excel 2007

Chapter 1: The Excel 2007 User Experience

05_037377 ch01.qxp 11/16/06 9:23 AM Page 43

Ribbon. They’re all easy to identify as they use the same icons as before and

are located in the Font, Alignment, or Number group on the Home tab (refer to

Figure 1-3).

In addition to the Font, Font Size, Bold, Italic, Underline, Borders, Fill Color,

and Font Color buttons from the Formatting toolbar, the Font group also con-

tains the following two buttons:

 Increase Font button that bumps up the current font size a point

 Decrease Font button that reduces the current font size by a point

In addition to the Left Align, Center, Right Align, Decrease Indent, Increase

Indent, and Merge and Center buttons, the Alignment group also contains

the following buttons:

 Top Align button that vertically aligns the data entered into the current

cell selection with the top edge of the cell

 Middle Align button that vertically centers the data entered into the

current cell selection

 Bottom Align button that aligns the data entered in the current cell

selection with the bottom edge of the cell

 Orientation button that opens a pop-menu of orientation options that

enable you to change the direction of the text entered into the current

cell selection by angling it up or down, converting it to vertical text,

rotating it up or down as well as opening the Alignment tab of the

Format Cells dialog box

 Wrap Text button that applies the wrap text function to the current cell

selection so that Excel expands the row heights as needed to fit all of its

text within the current column widths

In addition to the Percent Style, Comma, Increase Decimal, and Decrease

Decimal buttons from the Formatting toolbar, the Numbers group contains

the following buttons:

 Accounting Number Format button that enables you to select among

several different currency formats from U.S. dollars to Swiss Francs as

well as to open the Number tab of the Format Cells dialog box with the

Accounting number format selected

 Number Format button that opens a pop-up menu of different number

options from General through Text as well as opens the Number tab in

the Format Cells dialog box when you select its More Number Formats

option

Part I: Getting In on the Ground Floor

05_037377 ch01.qxp 11/16/06 9:23 AM Page 44

Putting the Quick Access

toolbar to excellent use

Figure 1-10 shows you the Excel 2007 program window with the Ribbon mini-

mized and a completely customized Quick Access toolbar that’s moved down

so that it appears under the tabs and immediately above the Formula bar.

This completely custom version of Quick Access toolbar should seem very

familiar to you: It contains every button from the Standard and Formatting

toolbar in Excel 2003 with the exception of the Permission, Zoom and Help

buttons in the original order in which they appear on their respective toolbars.

The Permission button is so esoteric and seldom used that I didn’t bother to

add it and neither the Zoom button nor the Help button is really needed as

the Zoom slider that enables you to quickly select a new screen magnification

percentage is always displayed in the lower-right corner of the Excel 2007

Status bar and the Help button is always displayed on the right side of the

bar containing the Ribbon tabs.

Figure 1-10:

Excel 2007

window

after

minimizing

the Ribbon

and adding

all but two

of the

buttons

from the

Standard

and

Formatting

toolbars to

the Quick

Access

toolbar.

Chapter 1: The Excel 2007 User Experience

05_037377 ch01.qxp 11/16/06 9:23 AM Page 45

To customize your Quick Access toolbar so that it matches the one shown in

Figure 1-10 with every button from the Standard and Formatting toolbars

except the Permission, Zoom, and Help buttons, follow these steps:

1. Click the Customize Quick Access Toolbar button at the end of the

Quick Access toolbar and then click the Show Below the Ribbon option.

When filling the Quick Access toolbar with buttons, you need to place

the bar beneath the Ribbon so that it won’t crowd out name of the cur-

rent workbook file.

2. Click the Customize Quick Access Toolbar button again and this time

click the More Commands option.

Excel opens the Excel Options dialog box with the Customize tab

selected. The Customize Quick Access Toolbar list box on the right side

of this dialog box shows all three of the default buttons in the order in

which they now appear on the toolbar.

3. Click the New option in the Popular Commands list followed by the

Add button.

Excel adds the New command button at the end of the toolbar indicated

by the appearance of the New button at the bottom of the list in the

Customize Quick Access Toolbar list box on the right.

4. Click the Move Up button (with the triangle pointing upward) three

times to move the New button to the top of the Customize Quick Access

Toolbar list box and the first position on the Quick Access toolbar.

Note that the New button is now in front of the Save button on the toolbar.

5. Click the Open option in the Popular Commands list box on the left

and then click the Add button.

Excel inserts the Open button in the Customize Quick Access Toolbar

list box in between the New and Save button, which is exactly where it

appears on the Standard toolbar.

6. Click the Save button in the Customize Quick Access Toolbar list box

on the right to select this button. Then, click the Quick Print option in

the Popular Commands list box on the left and click the Add button.

Excel inserts the Quick Print button after the Save button.

7. Click the Print Preview button near the bottom of the Popular

Commands list box and then click the Add button.

Excel inserts the Print Preview button after the Quick Print button in the

Customize Quick Access Toolbar list box.

Now, you need to add the Spelling and Research buttons. They are located

on the Review tab in Excel 2007. Before you can add their buttons to the

Quick Access toolbar, you need to replace Popular Commands with

Review Tab by selecting this option on the Choose Commands From

drop-down list.

Part I: Getting In on the Ground Floor

05_037377 ch01.qxp 11/16/06 9:23 AM Page 46

8. Click the Choose Commands From drop-down button and then click

Review Tab in the drop-down list.

Excel now displays all the command buttons on the Review tab of the

Ribbon in the list box below.

9. Add the Spelling and Research buttons from the Review Tab list box

to the Customize Quick Access Toolbar list box and position them so

that they appear one after the other following the Print Preview

button.

Next you need to add the Cut, Copy, Paste, and Format Painter buttons to

the Quick Access toolbar. These command buttons are on the Home tab.

10. Click the Home Tab option in the Choose Commands From drop-down

list and then add the Cut, Copy, Paste, and Format Painter buttons to

the Customize Quick Access Toolbar in this order in front of the Undo

button.

Note when adding the Paste button that Choose Commands From dis-

plays two Paste buttons. The first is the regular Paste button that was on

the Standard toolbar. The second is a Paste button with a drop-down

button that, when clicked, opens a drop-down menu with all the special

Paste options. You can add either one, although the second Paste button

with the drop-down menu is much more versatile.

11. Click the Format Painter option in the Insert Tab list box and then

click the Add button.

Excel adds the Format Painter button after the Paste button in the

Customize Quick Access Toolbar list box on the right.

12. Click the Redo button in the Customize Quick Access Toolbar list box

to select its icon and then click the Insert Tab on the Choose

Commands From drop-down list and add the Insert Hyperlink button

from to the Quick Access toolbar.

13. Add the remaining Standard toolbar buttons, AutoSum, Sort Ascending,

Sort Descending, and Create Chart, to the Quick Access toolbar.

The AutoSum, Sort Ascending, and Sort Descending buttons are avail-

able in the Home Tab and the Create Chart button (the closest thing to

the Chart Wizard in Excel 2007) is on the Insert Tab list box.

14. Add the buttons on the Formatting toolbar to the Quick Access toolbar

in the order in which they appear.

The Formatting toolbar contains these tools all found on the Home tab:

• Font

• Font Size

• Bold

• Italic

Chapter 1: The Excel 2007 User Experience

05_037377 ch01.qxp 11/16/06 9:23 AM Page 47

• Underline

• Align Text Left

• Center

• Align Text Right

• Merge and Center

• Accounting Number Format (corresponding to the Currency Style

button)

• Percent Style

• Comma Style

• Increase Decimal

• Decrease Decimal

• Decrease Indent

• Increase Indent

• Borders

• Fill Color

• Font Color.

15. Click the OK button to close the Excel Options dialog box and return

to the Excel program window.

Your Quick Access toolbar should now have the same buttons as the

one shown in Figure 1-10.

After adding all the buttons on the Standard and the Formatting toolbar (with

the exception of the Permission button that almost nobody uses, the Drawing

button that has no equivalent in Excel 2007, and the Zoom and Help buttons

that are always available in the Excel 2007 program window), the Quick

Access toolbar fills the entire width of the screen on many monitors. Keep in

mind that if you need to add extra buttons that can no longer be displayed on

the single row above the Formula bar, Excel automatically adds a More

Controls button to the end of the Quick Access toolbar. You then click this

More Controls button to display a pop-up menu containing all the buttons

that can no longer be displayed on the toolbar.

To add vertical bar separators to divide the buttons into groups as you see in

the original Standard and Formatting toolbars and shown in Figure 1-8, click

the <Separator> option located at the top of each Choose Commands From

list box followed by the Add button.

Part I: Getting In on the Ground Floor

05_037377 ch01.qxp 11/16/06 9:23 AM Page 48

Getting good to go with Excel 2007

The version of the Excel 2007 program window shown in Figure 1-10 with the

Ribbon minimized to just tabs and the Quick Access toolbar displayed above

the Formula bar with all but a few of the buttons from the Standard and

Formatting toolbars is as close as I can get you to any sort of Excel 2003

Classic mode.

Combine this simplified screen layout with the common shortcut keys (see

Table 1-2) that you already know and you should be pretty much good to go

with Excel 2007. Of course, you need to keep in mind that in the course of

using the program, the Ribbon can’t always stay reduced to just its tabs. As

you find out as you explore the features covered in the remaining chapters of

this book, there’ll be times when you need the tools (especially in the form

of those fantastic galleries) that a particular tab has to offer.

The only other issues that should be of any concern to you right now are the

new Excel 2007 file formats and running all those Excel macros on which

you’ve come to rely.

Dealing with the new Excel file formats

Yes, it’s true that Excel 2007 introduces yet another new native file format in

which to save its workbook files (although Microsoft insists that this one is

a truly “open” XML file format and not at all proprietary like all the previous

ones).

Fortunately, Excel 2007 has no trouble opening any workbook files saved in

the good old .XLS file format used by versions 97 through 2003. More impor-

tantly, the program automatically saves all editing changes you make to these

files in this original file format.

This means you don’t have a worry in the world when it comes to making

simple edits to existing spreadsheets with Excel 2007. Simply, open the work-

book file and then make all the necessary changes. When you finish, click the

Save button on the Quick Access toolbar to save your changes in the good

old .XLS file format that everybody in the office who is still using a previous

version of Excel can still open, edit, and print. Excel also warns you should you

ever add a new 2007 element to the existing workbook that’s not supported by

its earlier versions.

The challenge comes when you need to use Excel 2007 to create a brand new

spreadsheet. The program automatically wants to save all new workbooks in

its fancy new .XLSX file format (see Chapter 2 for a complete rundown on this

new workbook file format and the pros and cons of using it). If you don’t want

to save your workbook in this format, you need to remember to click the Save

as Type drop-down button and then click the Excel 97-2003 Workbook (*.xls)

option on its drop-down menu before you click Save.

Chapter 1: The Excel 2007 User Experience

05_037377 ch01.qxp 11/16/06 9:23 AM Page 49

If you’re working an office environment where all the workbooks you produce

with Excel 2007 must be saved in the old 97-2003 file format for compatibility

sake, you can change the program’s default Save setting so that the program

always saves all new workbooks in the old file format. To do this, open the

Save tab of the Excel Options dialog box (Office Button | Excel Options or

Alt+FIS) and then click Excel 97-2003 Workbook in the Save Files in This

Format drop-down list box before you click OK.

Using your macros

The good news is that Excel 2007 supports the creating and running of macros,

using the same Microsoft Visual Basic for Applications of earlier versions. It

even enables you to edit these macros in a version of VBA Editor, if you’re

sufficiently skilled to do so.

The biggest problem with macros comes about if you have a tendency, like

I do, to map your global macros (the ones you save in the PERSONAL.XLSB

workbook so that they’re available when working in any Excel workbook)

onto custom pull-down menus and toolbars. Because Excel 2007 retains only

the single pull-down File menu and Quick Access toolbar, none of the custom

menus and toolbars to which you’ve assigned macros comes over to Excel

2007. This means, that although the macros are still a part of their respective

workbooks and continue to run, you must now run all macros either using

keyboard shortcuts you assigned to them or via the Macro dialog box (click

View | Macros | View Macros or press Alt+WMV or Alt+F8).

You can assign macros to buttons on the Quick Access toolbar and then

run them by clicking their buttons. The only problem is that all macros you

assign to this toolbar use the same generic macro button icon so that the

only way to differentiate the macros is through the ToolTip that appears

when you position the mouse over the macro button.

To assign a macro to a generic macro on the Quick Access toolbar, open the

Customize tab of the Excel Options dialog box (Office Button | Excel Options

or Alt+FIC) and then select Macros in the Choose Commands From drop-down

list box. Excel then displays the names of all the macros in the current work-

book (including all global macros saved in the PERSONAL.XLSB workbook) in

the Choose Commands From list box. To assign a macro to a macro button,

click its name in this list box and then click the Add button. You can then

move the macro button to the desired position on the Quick Access toolbar

with the Move Up and Move Down buttons and, if you so desire, make it part

of a separate section on the toolbar by adding a <Separator> before and after

its button.

Part I: Getting In on the Ground Floor

05_037377 ch01.qxp 11/16/06 9:23 AM Page 50

Chapter 2

Creating a Spreadsheet

from Scratch

In This Chapter

 Starting a new workbook

 Entering the three different types of data in a worksheet

 Creating simple formulas by hand

 Fixing your data-entry boo-boos

 Using the AutoCorrect feature

 Using the AutoFill feature to complete a series of entries

 Entering and editing formulas containing built-in functions

 Totaling columns and rows of numbers with the AutoSum button

 Saving your precious work and recovering workbooks after a computer crash

fter you know how to launch Excel 2007, it’s time to find out how not to

get yourself into trouble when actually using it! In this chapter, you find

out how to put all kinds of information into all those little, blank worksheet

cells I describe in Chapter 1. Here you find out about the Excel AutoCorrect

and AutoComplete features and how they can help cut down on errors and

speed up your work. You also get some basic pointers on other smart ways to

minimize the drudgery of data entry, such as filling out a series of entries

with the AutoFill feature and entering the same thing in a bunch of cells all at

the same time.

And after discovering how to fill up a worksheet with all this raw data, you

find out what has to be the most important lesson of all — how to save all

that information on disk so that you don’t ever have to enter the stuff again!

06_037377 ch02.qxp 11/16/06 9:24 AM Page 51

So What Ya Gonna Put in That

New Workbook of Yours?

When you start Excel without specifying a document to open — which is

what happens when you start the program from the Windows Vista or XP

Start menu (refer to Chapter 1) — you get a blank workbook in a new work-

book window. This workbook, temporarily named Book1, contains three

blank worksheets (Sheet1, Sheet2, and Sheet3). To begin to work on a new

spreadsheet, you simply start entering information in the first sheet of the

Book1 workbook window.

The ins and outs of data entry

Here are a few simple guidelines (a kind of data-entry etiquette, if you will)

that you should keep in mind when you start creating a spreadsheet in

Sheet1 of your new workbook:

 Whenever you can, organize your information in tables of data that use

adjacent (neighboring) columns and rows. Start the tables in the upper-

left corner of the worksheet and work your way down the sheet, rather

than across the sheet, whenever possible. When it’s practical, separate

each table by no more than a single column or row.

 When you set up these tables, don’t skip columns and rows just to “space

out” the information. In Chapter 3, you see how to place as much white

space as you want between information in adjacent columns and rows

by widening columns, heightening rows, and changing the alignment.

 Reserve a single column at the left edge of the table for the table’s row

headings.

 Reserve a single row at the top of the table for the table’s column

headings.

 If your table requires a title, put the title in the row above the column

headings. Put the title in the same column as the row headings. You can

get information on how to center this title across the columns of the

entire table in Chapter 3.

In Chapter 1, I make a really big deal about how big each of the worksheets in

a workbook is. You may wonder why I’m now on your case about not using

that space to spread out the data that you enter into it. After all, given all the

real estate that comes with each and every Excel worksheet, you’d think con-

serving space would be one of the last things you’d have to worry about.

Part I: Getting In on the Ground Floor

06_037377 ch02.qxp 11/16/06 9:24 AM Page 52

And you’d be 100 percent correct . . . except for one little, itty-bitty thing:

Space conservation in the worksheet equals memory conservation. You see,

as a table of data grows and expands into columns and rows in new areas of

the worksheet, Excel decides that it had better reserve a certain amount of

computer memory and hold it open just in case you might go crazy and fill

that area full of cell entries. This means that if you skip columns and rows

that you really don’t need to skip (just to cut down on all that cluttered data),

you end up wasting computer memory that could otherwise be used to store

more information in the worksheet.

You must remember this . . .

So now you know: It’s the amount of computer memory available to Excel

that determines the ultimate size of the spreadsheet you can build, not the

total number of cells in the worksheets of your workbook. When you run

out of memory, you’ve effectively run out of space — no matter how many

columns and rows are still left to fill. To maximize the information you can get

into a single worksheet, always adopt the “covered wagon” approach to

worksheet design by keeping your data close together.

Doing the Data-Entry Thing

Begin by reciting (in unison) the basic rule of worksheet data entry. All

together now:

To enter data in a worksheet, position the cell pointer in the cell where

you want the data, and then begin typing the entry.

Note that before you can position the cell pointer in the cell where you want

the entry, Excel must be in Ready mode (look for Ready as the Program indi-

cator at the beginning of the Status bar). When you start typing the entry,

however, Excel goes through a mode change from Ready to Enter mode (and

Enter replaces Ready as the Program indicator).

If you’re not in Ready mode, try pressing Esc.

As soon as you begin typing in Enter mode, the characters that you type in a

cell in the worksheet area simultaneously appear on the formula bar near the

top of the screen. Starting to type something that’s ultimately destined to go

into the current cell also triggers a change to the Formula bar because two

new boxes, Cancel and Enter, appear in between the Name Box drop-down

button and the Function Wizard button.

Chapter 2: Creating a Spreadsheet from Scratch

06_037377 ch02.qxp 11/16/06 9:24 AM Page 53

As you continue to type, Excel displays your progress both on the formula

bar and in the active cell in the worksheet (see Figure 2-1). However, the

insertion point (the flashing vertical bar that acts as your cursor) is dis-

played only at the end of the characters displayed in the cell.

After you finish typing your cell entry, you still have to get it into the cell so

that it stays put. When you do this, you also change the program from Enter

mode back to Ready mode so that you can move the cell pointer to another

cell and, perhaps, enter or edit the data there.

Figure 2-1:

What you

type

appears

both in the

current cell

and on the

Formula bar.

Part I: Getting In on the Ground Floor

Getting the Enter key to put the cell pointer

where you want it

Excel automatically advances the cell pointer to

the next cell down in the column every time you

press Enter to complete the cell entry. If you

want to customize Excel so that pressing Enter

doesn’t move the cell pointer as the program

enters your data, or to have it move the cell

pointer to the next cell up, left, or right, open the

Advanced tab of the Excel Options dialog box

(Alt+FIA).

To prevent the cell pointer from moving at all,

select the After Pressing Enter, Move Selection

Direction check box to remove its check mark.

To have the cell pointer move in another direc-

tion, click the Direction pop-up list box right

below and then select the new direction you

want to use (Right, Up, or Left). When you’re fin-

ished changing the settings, click OK or press

Enter.

06_037377 ch02.qxp 11/16/06 9:24 AM Page 54

To complete your cell entry and, at the same time, get Excel out of Enter

mode and back into Ready mode, you can click the Enter box on the Formula

bar, press the Enter key, or press one of the arrow keys (

↓, ↑, →, or ←) to

move to another cell. You can also press the Tab or Shift+Tab keys to com-

plete a cell entry.

Now, even though each of these alternatives gets your text into the cell, each

does something a little different afterward, so please take note:

 If you click the Enter box (the one with the check mark) on the Formula

bar, the text goes into the cell, and the cell pointer just stays in the cell

containing the brand-new entry.

 If you press the Enter key on your keyboard, the text goes into the cell,

and the cell pointer moves down to the cell below in the next row.

 If you press one of the arrow keys, the text goes into the cell, and the

cell pointer moves to the next cell in the direction of the arrow. Press

↓,

and the cell pointer moves below in the next row just as when you finish

off a cell entry with the Enter key. Press

→ to move the cell pointer right

to the cell in the next column; press

← to move the cell pointer left to

the cell in the previous column; andor press

↑ to move the cell pointer

up to the cell in the next row above.

 If you press Tab, the text goes into the cell, and the cell pointer moves to

the adjacent cell in the column on the immediate right (the same as

pressing the

→ key). If you press Shift+Tab, the cell pointer moves to the

adjacent cell in the column on the immediate left (the same as pressing

the

← key) after putting in the text.

No matter which of the methods you choose when putting an entry in its

place, as soon as you complete your entry in the current cell, Excel deacti-

vates the Formula bar by removing the Cancel and Enter boxes. Thereafter,

the data you entered continues to appear in the cell in the worksheet (with

certain exceptions that I discuss later in this chapter), and every time you

put the cell pointer into that cell, the data will reappear on the Formula bar

as well.

If, while still typing an entry or after finishing typing but prior to completing

the entry, you realize that you’re just about to stick it in the wrong cell, you

can clear and deactivate the Formula bar by clicking the Cancel box (the one

with the X in it) or by pressing Esc. If, however, you don’t realize that you had

the wrong cell current until after you’ve entered your data there, you have to

either move the entry to the correct cell (something you find out how to do

in Chapter 4) or delete the entry (see Chapter 4) and then reenter the data in

the correct cell.

Chapter 2: Creating a Spreadsheet from Scratch

06_037377 ch02.qxp 11/16/06 9:24 AM Page 55

It Takes All Types

Unbeknownst to you as you go about happily entering data in your spread-

sheet, Excel constantly analyzes the stuff you type and classifies it into one of

three possible data types: a piece of text, a value, or a formula.

If Excel finds that the entry is a formula, the program automatically calculates

the formula and displays the computed result in the worksheet cell (you con-

tinue to see the formula itself, however, on the Formula bar). If Excel is satis-

fied that the entry does not qualify as a formula (I give you the qualifications

for an honest-to-goodness formula a little later in this chapter), the program

then determines whether the entry should be classified as text or as a value.

Excel makes this distinction between text and values so that it knows how to

align the entry in the worksheet. It aligns text entries with the left edge of the

cell and values with the right edge. Also, because most formulas work prop-

erly only when they are fed values, by differentiating text from values, the

program knows which will and will not work in the formulas that you build.

Suffice to say that you can foul up your formulas but good if they refer to any

cells containing text where Excel expects values to be.

The telltale signs of text

A text entry is simply an entry that Excel can’t pigeonhole as either a formula

or value. This makes text the catchall category of Excel data types. As a prac-

tical rule, most text entries (also known as labels) are a combination of let-

ters and punctuation or letters and numbers. Text is used mostly for titles,

headings, and notes in the worksheet.

You can tell right away whether Excel has accepted a cell entry as text because

text entries are automatically aligned at the left edge of their cells. If the text

entry is wider than the cell can display, the data spills over into the neighbor-

ing cell or cells on the right, as long as those cells remain blank (see Figure 2-2).

Part I: Getting In on the Ground Floor

To Excel, text is nothing but a big zero

Use the AutoCalculate indicator to prove to

yourself that Excel gives all text entries the

value of 0 (zero). As an example, enter the

number 10 in one cell and then some stupid

piece of text, such as Excel is like a box of

chocolates, in the cell directly below. Then drag

up so that both cells (the one with 10 and the

one with the text) are highlighted. Take a gander

at the AutoCalculate indicator on the Status bar,

and you see that it reads Average: 10, Count: 2,

and Sum: 10, proving that the text adds nothing

to the total value of these two cells.

06_037377 ch02.qxp 11/16/06 9:24 AM Page 56

If, sometime later, you enter information in a cell that contains spillover text

from a cell to its left, Excel cuts off the spillover of the long text entry (see

Figure 2-3). Not to worry: Excel doesn’t actually lop these characters off the

cell entry — it simply shaves the display to make room for the new entry. To

redisplay the seemingly missing portion of the long text entry, you have to

widen the column that contains the cell where the text is entered. (To find

out how to do this, skip ahead to Chapter 3.)

Figure 2-3:

Entries in

cells to the

right cut off

the spillover

text in cells

on the left.

Figure 2-2:

Long text

entries spill

over into

neighboring

blank cells.

Chapter 2: Creating a Spreadsheet from Scratch

06_037377 ch02.qxp 11/16/06 9:24 AM Page 57

How Excel evaluates its values

Values are the building blocks of most of the formulas that you create in

Excel. As such, values come in two flavors: numbers that represent quantities

(

14 stores or $140,000 dollars) and numbers that represent dates (July 30, 1995)

or times (2 pm).

You can tell whether Excel has accepted your entry as a value because values

are aligned at the right edge of their cells. If the value that you enter is wider

than the column containing the cell can display, Excel automatically converts

the value to (of all things)

scientific notation. For example, 6E+08 indicates

that the 6 is followed by eight zeros for a grand total of 600 million! To restore

a value that’s been converted into that weird scientific notation stuff back to

a regular number, simply widen the column for that cell. (Read how in

Chapter 3.)

Making sure that Excel’s got your number

When building a new worksheet, you’ll probably spend a lot of your time

entering numbers, representing all types of quantities from money that you

made (or lost) to the percentage of the office budget that went to coffee and

donuts. (You mean you don’t get donuts?)

To enter a numeric value that represents a positive quantity, like the amount of

money you made last year, just select a cell, type the numbers — for example,

459600 — and complete the entry in the cell by clicking the Enter box, pressing

the Enter key, and so on. To enter a numeric value that represents a negative

quantity, like the amount of money the office spent on coffee and donuts last

year, begin the entry with the minus sign or hyphen (–) before typing the

numbers — for example,

–175 (that’s not too much to spend on coffee and

donuts when you just made $459,600) — and then complete the entry.

If you’re trained in accounting, you can enclose the negative number (that’s

expense to you) in parentheses. You’d enter it like this: (175). Just note that if

you go to all the trouble to use parentheses for your negatives (expenses),

Excel goes ahead and automatically converts the number so that it begins

with a minus sign; if you enter

(175) in the Coffee and Donut expense cell,

Excel spits back

–175. (Relax, you can find out how to get your beloved

parentheses back for the expenses in your spreadsheet in Chapter 3.)

With numeric values that represent dollar amounts, like the amount of money

you made last year, you can include dollar signs ($) and commas (,) just as

they appear in the printed or handwritten numbers you’re working from.

Just be aware that when you enter a number with commas, Excel assigns a

number format to the value that matches your use of commas. (For more

information on number formats and how they are used, see Chapter 3.)

Likewise, when you preface a financial figure with a dollar sign, Excel assigns

an appropriate dollar-number format to the value (one that automatically

inserts commas between the thousands).

Part I: Getting In on the Ground Floor

06_037377 ch02.qxp 11/16/06 9:24 AM Page 58

When entering numeric values with decimal places, use the period as the

decimal point. When you enter decimal values, the program automatically

adds a zero before the decimal point (Excel inserts

0.34 in a cell when you

enter

.34) and drops trailing zeros entered after the decimal point (Excel

inserts

12.5 in a cell when you enter 12.50).

If you don’t know the decimal equivalent for a value that contains a fraction,

you can just go ahead and enter the value with its fraction. For example, if

you don’t know that 2.1875 is the decimal equivalent for 2

⁄16, just type 2

⁄16

(making sure to add a space between the 2 and 3) in the cell. After complet-

ing the entry, when you put the cell pointer in that cell, you see

⁄16 in the cell

of the worksheet, but

2.1875 appears on the formula bar. As you see in

Chapter 3, it’s then a simple trick to format the display of

2@@bf3⁄16 in the

cell so that it matches the

2.1875 on the Formula bar.

If you need to enter simple fractions, such as

⁄4 or

⁄8, you must enter them

as a mixed number preceded by zero; for example, enter

⁄4 or 0

⁄8 (be sure

to include a space between the zero and the fraction). Otherwise, Excel

gets mixed up and thinks that you’re entering the dates March 4 (3/4) and

May 8 (5/8).

When entering in a cell a numeric value that represents a percentage (so

much out of a hundred), you have this choice:

 You can either divide the number by 100 and enter the decimal equiva-

lent (by moving the decimal point two places to the left like your teacher

taught you; for example, enter

.12 for 12 percent).

 You can enter the number with the percent sign (for example, enter 12%).

Either way, Excel stores the decimal value in the cell (

0.12 in this example).

If you use the percent sign, Excel assigns a percentage-number format to the

value in the worksheet so that it appears as

12%.

How to fix your decimal places (when

you don’t even know they’re broken)

If you find that you need to enter a whole slew of numbers that use the same

number of decimal places, you can turn on Excel’s Fixed Decimal setting and

have the program enter the decimals for you. This feature really comes in

handy when you have to enter hundreds of financial figures that all use two

decimal places (for example, for the number of cents).

fix the number of decimal places in a numeric entry, follow these steps:

1. Click Office Button | Excel Options | Advanced or press Alt+FIA.

The Advanced tab of the Excel Options dialog box opens.

Chapter 2: Creating a Spreadsheet from Scratch

06_037377 ch02.qxp 11/16/06 9:24 AM Page 59

2. Click the Automatically Insert a Decimal Point check box in the

Editing Options section to fill it with a check mark.

By default, Excel fixes the decimal place two places to the left of the last

number you type. To change the default Places setting, go to Step 3; oth-

erwise move to Step 4.

3. (Optional) Type a new number in the Places text box or use the spin-

ner buttons to change the value.

For example, you could change the Places setting to 3 to enter numbers

with the following decimal placement: 00.000.

4. Click OK or press Enter.

Excel displays the Fixed Decimal status indicator on the Status bar to let

you know that the Fixed Decimal feature is now active.

After fixing the decimal place in numeric values, Excel automatically adds a

decimal point to any numeric value that you enter using the number of places

you selected — all you do is type the digits and complete the entry in the

cell. For example, to enter the numeric value 100.99 in a cell after fixing the

decimal point to two places, type the digits 10099 without adding any period

for a decimal point. When you complete the cell entry, Excel automatically

inserts a decimal point two places from the right in the number you typed,

leaving 100.99 in the cell.

When you’re ready to return to normal data entry for numerical values

(where you enter any decimal points yourself), open the Advanced tab of the

Excel Options dialog box (Alt+FIA) and then click the Fixed Decimal check

box again, this time to clear it, and then click OK or press Enter. Excel

removes the Fixed Decimal indicator from the Status bar.

Part I: Getting In on the Ground Floor

Don’t get in a fix over your decimal places!

While the Fixed Decimal setting is turned on,

Excel adds a decimal point to all the numeric

values that you enter. However, if you want to

enter a number without a decimal point, or one

with a decimal point in a position different from

the one called for by this feature, you have to

remember to type the decimal point (period)

yourself. For example, to enter the number 1099

instead of 10.99 when the decimal point is fixed

at two places, type 1099 followed immediately

by a period (.) in the cell.

And, for heaven’s sake, please don’t forget to

turn off the Fixed Decimal feature before you

start work on another worksheet or exit Excel.

Otherwise, when you intend to enter values,

such as 20, you’ll end up with 0.2 instead, and

you won’t have a clue what’s going on!

06_037377 ch02.qxp 11/16/06 9:24 AM Page 60

Tapping on the old ten-key

You can make the Fixed Decimal feature work even better by selecting the

block of the cells where you want to enter the numbers (see “Entries all

around the block,” later in this chapter) and then pressing Num Lock so that

you can do all the data entry for this cell selection from the numeric keypad

(à la ten-key adding machine).

Using this approach, all you have to do to enter the range of values in each

cell is type the number’s digits and press Enter on the numeric keypad —

Excel inserts the decimal point in the proper place as it moves the cell

pointer down to the next cell. Even better, when you finish entering the last

value in a column, pressing Enter automatically moves the cell pointer to the

cell at the top of the next column in the selection.

Look at Figures 2-4 and 2-5 to see how you can make the ten-key method work

for you. In Figure 2-4, the Fixed Decimal feature is turned on (using the default

of two decimal places), and the block of cells from B3 through D9 are

selected. You also see that six entries have already been made in cells B3

through B8 and a seventh, 30834.63, is about to be completed in cell B9. To

make this entry when the Fixed Decimal feature is turned on, you simply type

3083463 from the numeric keypad.

In Figure 2-5, check out what happens when you press Enter (either on the

regular keyboard or the numeric keypad). Not only does Excel automatically

add the decimal point to the value in cell B9, but it also moves the cell

pointer up and over to cell C3 where you can continue entering the values for

this column.

Figure 2-4:

To enter the

value

30834.63 in

cell B9, type

3083463 and

press Enter.

Chapter 2: Creating a Spreadsheet from Scratch

06_037377 ch02.qxp 11/16/06 9:24 AM Page 61

Entering dates with no debate

At first look, it may strike you as a bit odd that dates and times are entered as

values in the cells of a worksheet rather than as text. The reason for this is

simple, really: Dates and times entered as values can be used in formula cal-

culations, whereas dates and times entered as text cannot. For example, if

you enter two dates as values, you can then set up a formula that subtracts

the more recent date from the older date and returns the number of days

between them. This kind of thing just couldn’t happen if you were to enter

the two dates as text entries.

Excel determines whether the date or time that you type is entered as a value

or as text by the format you follow. If you follow one of Excel’s built-in date

and time formats, the program recognizes the date or time as a value. If you

don’t follow one of the built-in formats, the program enters the date or time

as a text entry — it’s as simple as that.

Excel recognizes the following time formats:

3 AM or 3 PM

3 A

or 3 P (upper- or lowercase a or p — Excel inserts 3:00 AM or 3:00 PM)

3:21 AM or 3: 21 PM (upper- or lowercase am or pm)

3:21:04 AM or 3:21:04 PM (upper- or lowercase am or pm)

15:21

15:21:04

Figure 2-5:

Press Enter

to complete

the 30834.63

entry in cell

B9; Excel

automati-

cally moves

the cell

pointer up

and over to

cell C3.

Part I: Getting In on the Ground Floor

06_037377 ch02.qxp 11/16/06 9:24 AM Page 62

Excel isn’t fussy so that you can enter the AM or PM designation in the date

in any case — all upper- or lowercase letters or even a mix of the two.

Excel knows the following date formats. (Note that month abbreviations always

use the first three letters of the name of the month: Jan, Feb, Mar, and so forth.)

November 6, 2008 or November 6, 08 (appear in cell as 6-Nov-08)

11/6/08 or 11-6-08 (appear in cell as 11/6/2008)

6-Nov-08 or 6/Nov/08 or even 6Nov08 (all appear in cell as 6-Nov-08)

11/6 or 6-Nov or 6/Nov or 6Nov (all appear in cell as 6-Nov)

Nov-06 or Nov/06 or Nov06 (all appear in cell as 6-Nov)

Make it a date in the 21st Century

Contrary to what you might think, when entering dates in the 21

Century,

you need to enter only the last two digits of the year. So, for example, to enter

the date January 6, 2008, in a worksheet, I enter 1/6/08 in the target cell.

Likewise, to put the date February 15, 2010, in a worksheet, I enter 2/15/10 in

the target cell.

Note that this system of having to put in only the last two digits of dates in

the 21

Century works only for dates in the first three decades of the new

century (2000 through 2029). To enter dates for the years 2030 on, you need

to input all four digits of the year.

This also means, however, that to put in dates in the first three decades of the

Century (1900 through 1929), you must enter all four digits of the year. For

example, to put in the date July 21, 1925, you have to enter 7/21/1925 in the

target cell. Otherwise, if you enter just the last two digits (25) for the year part

of the date, Excel enters a date for the year 2025 and not 1925!

Chapter 2: Creating a Spreadsheet from Scratch

The dating game

Dates are stored as serial numbers that indicate

how many days have elapsed from a particular

starting date; times are stored as decimal frac-

tions indicating the elapsed part of the 24-hour

period. Excel supports two date systems: the

1900 date system used by Excel in Windows,

where January 1, 1900 is the starting date (serial

number 1) and the 1904 system used by Excel for

the Macintosh, where January 2, 1904 is the

starting date.

If you ever get a hold of a workbook created

with Excel for the Macintosh that contains

dates that seem all screwed up when you open

the file, you can rectify this problem by opening

the Advanced tab of the Excel Options dialog

box (Office Button | Excel Options | Advanced or

Alt+FIA), and then clicking the Use 1904 Date

System check box in the When Calculating This

Workbook section before you click OK.

06_037377 ch02.qxp 11/16/06 9:24 AM Page 63

Excel 2007 always displays all four digits of the year in the cell and on the

Formula bar even when you only enter the last two. So, for example, if you

enter 11/06/08 in a cell, Excel automatically displays 11/06/2008 in the

worksheet cell (and on the Formula bar when that cell is current).

Therefore, by looking at the Formula bar, you can always tell when you’ve

entered a 20

rather than a 21

Century date in a cell even if you can’t keep

straight the rules for when to enter just the last two digits rather than all four.

(Read through Chapter 3 for information on how to format your date entries

so that only the last digits are displayed in the worksheet.)

For information on how to perform simple arithmetic operations between the

dates and time you enter in a worksheet and have the results make sense, see

the information about dates in Chapter 3.

Fabricating those fabulous formulas!

As entries go in Excel, formulas are the real workhorses of the worksheet. If

you set up a formula properly, it computes the right answer when you first

enter it into a cell. From then on, it keeps itself up to date, recalculating the

results whenever you change any of the values that the formula uses.

You let Excel know that you’re about to enter a formula (rather than some

text or a value), in the current cell by starting the formula with the equal sign

(=). Most simple formulas follow the equal sign with a built-in function such

as SUM or AVERAGE. (See the section “Inserting a function into a formula

with the Function Wizard button,” later in this chapter, for more information

on using functions in formulas.) Other simple formulas use a series of values

or cell references that contain values separated by one or more of the follow-

ing mathematical operators:

+ (plus sign) for addition

– (minus sign or hyphen) for subtraction

* (asterisk) for multiplication

/ (slash) for division

^ (caret) for raising a number to an exponential power

For example, to create a formula in cell C2 that multiplies a value entered in

cell A2 by a value in cell B2, enter the following formula in cell C2:

=A2*B2.

Part I: Getting In on the Ground Floor

06_037377 ch02.qxp 11/16/06 9:24 AM Page 64

To enter this formula in cell C2, follow these steps:

1. Select cell C2.

2. Type the entire formula

=A2*B2 in the cell.

3. Press Enter.

1. Select Cell C2.

2. Type

= (equal sign).

3. Select cell A2 in the worksheet by using the mouse or the keyboard.

This action places the cell reference A2 in the formula in the cell (as

shown in Figure 2-6).

4. Type * (Shift+8 on the top row of the keyboard).

The asterisk is used for multiplication rather than the x symbol you

used in school.

5. Select cell B2 in the worksheet by using the mouse or the keyboard.

This action places the cell reference B2 in the formula (as shown in

Figure 2-7).

6. Click the Enter box to complete the formula entry, while at the same

time keeping the cell pointer in cell C2.

Excel displays the calculated answer in cell C2 and the formula =A2*B2

in the Formula bar (as shown in Figure 2-8).

Figure 2-6:

To start the

formula,

type = and

then select

cell A2.

Chapter 2: Creating a Spreadsheet from Scratch

06_037377 ch02.qxp 11/16/06 9:24 AM Page 65

When you finish entering the formula =A2*B2 in cell C2 of the worksheet,

Excel displays the calculated result, depending on the values currently

entered in cells A2 and B2. The major strength of the electronic spreadsheet

is the capability of formulas to automatically change their calculated results

to match changes in the cells referenced by the formulas.

Figure 2-8:

Click the

Enter box,

and Excel

displays the

answer in

cell C2 while

the formula

appears in

the Formula

bar above.

Figure 2-7:

To complete

the second

part of the

formula,

type * and

select

cell B2.

Part I: Getting In on the Ground Floor

06_037377 ch02.qxp 11/16/06 9:24 AM Page 66

Now comes the fun part: After creating a formula like the preceding one that

refers to the values in certain cells (rather than containing those values

itself), you can change the values in those cells, and Excel automatically

recalculates the formula, using these new values and displaying the updated

answer in the worksheet! Using the example shown in Figure 2-8, suppose

that you change the value in cell B2 from 100 to 50. The moment that you

complete this change in cell B2, Excel recalculates the formula and displays

the new answer, 1000, in cell C2.

If you want it, just point it out

The method of selecting the cells you use in a formula, rather than typing their

cell references, is known as

pointing. Pointing is not only quicker than typing cell

references, it also reduces the risk that you might type the wrong cell reference.

When you type a cell reference, you can easily type the wrong column letter

or row number and not realize your mistake just by looking at the calculated

result returned in the cell.

If you select the cell you want to use in a formula, either by clicking it or

moving the cell pointer to it, you have less chance of entering the wrong cell

reference.

Altering the natural order of operations

Many formulas that you create perform more than one mathematical opera-

tion. Excel performs each operation, moving from left to right, according to a

strict pecking order (the natural order of arithmetic operations). In this

order, multiplication and division pull more weight than addition and sub-

traction and, therefore, are performed first, even if these operations don’t

come first in the formula (when reading from left to right).

Consider the series of operations in the following formula:

=A2+B2*C2

If cell A2 contains the number 5, B2 contains the number 10, and C2 contains

the number 2, Excel evaluates the following formula:

=5+10*2

In this formula, Excel multiplies 10 times 2 to equal 20 and then adds this

result to 5 to produce the result 25.

Chapter 2: Creating a Spreadsheet from Scratch

06_037377 ch02.qxp 11/16/06 9:24 AM Page 67

If you want Excel to perform the addition between the values in cells A2 and

B2 before the program multiplies the result by the value in cell C2, enclose

the addition operation in parentheses as follows:

=(A2+B2)*C2

The parentheses around the addition tell Excel that you want this operation

performed before the multiplication. If cell A2 contains the number 5, B2 con-

tains the number 10, and C2 contains the number 2, Excel adds 5 and 10 to

equal 15 and then multiplies this result by 2 to produce the result 30.

In fancier formulas, you may need to add more than one set of parentheses,

one within another (like the wooden Russian dolls that nest within each

other) to indicate the order in which you want the calculations to take place.

When nesting parentheses, Excel first performs the calculation contained

in the most inside pair of parentheses and then uses that result in further

calculations as the program works its way outward. For example, consider

the following formula:

=(A4+(B4–C4))*D4

Excel first subtracts the value in cell C4 from the value in cell B4, adds the dif-

ference to the value in cell A4, and then finally multiplies that sum by the

value in D4.

Without the additions of the two sets of nested parentheses, left to its own

devices, Excel would first multiply the value in cell C4 by that in D4, add the

value in A4 to that in B4, and then perform the subtraction.

Don’t worry too much when nesting parentheses in a formula if you don’t pair

them properly so that you have a right parenthesis for every left parenthesis in

the formula. If you do not include a right parenthesis for every left one, Excel

displays an alert dialog box that suggests the correction that needs to be made

to balance the pairs. If you agree with the program’s suggested correction, you

simply click the Yes button. However, be sure that you only use parentheses:

( ). Excel balks at the use of brackets — [ ] — or braces — { } — in a formula by

giving you an Error alert box.

Formula flub-ups

Under certain circumstances, even the best formulas can appear to have

freaked out after you get them in your worksheet. You can tell right away that

a formula’s gone haywire because instead of the nice calculated value you

expected to see in the cell, you get a strange, incomprehensible message in

all uppercase letters beginning with the number sign (#) and ending with an

exclamation point (!) or, in one case, a question mark (?). This weirdness is

Part I: Getting In on the Ground Floor

06_037377 ch02.qxp 11/16/06 9:24 AM Page 68

known, in the parlance of spreadsheets, as an error value. Its purpose is

to let you know that some element — either in the formula itself or in a

cell referred to by the formula — is preventing Excel from returning the

anticipated calculated value.

When one of your formulas returns one of these error values, an alert indica-

tor (in the form of an exclamation point in a diamond) appears to the left of

the cell when it contains the cell pointer, and the upper-left corner of cell

contains a tiny green triangle. When you position the mouse pointer on this

alert indicator, Excel displays a brief description of the formula error and

adds a drop-down button to the immediate right of its box. When you click

this button, a pop-up menu appears with a number of related options. To

access online help on this formula error, including suggestions on how to get

rid of the error, click the Help on This Error item on this pop-up menu.

The worst thing about error values is that they can contaminate other formu-

las in the worksheet. If a formula returns an error value to a cell and a second

formula in another cell refers to the value calculated by the first formula, the

second formula returns the same error value, and so on down the line.

After an error value shows up in a cell, you have to discover what caused the

error and edit the formula in the worksheet. In Table 2-1, I list some error

values that you might run into in a worksheet and then explain the most

common causes.

Table 2-1 Error Values That You Can Encounter

from Faulty Formulas

What Shows Up in the Cell What’s Going On Here?

#DIV/0! Appears when the formula calls for division by a

cell that either contains the value 0 or, as is more

often the case, is empty. Division by zero is a no-no

in mathematics.

#NAME? Appears when the formula refers to a

range name

(see Chapter 6 for info on naming ranges) that

doesn’t exist in the worksheet. This error value

appears when you type the wrong range name or

fail to enclose in quotation marks some text used in

the formula, causing Excel to think that the text

refers to a range name.

#NULL! Appears most often when you insert a space

(where you should have used a comma) to separate

cell references used as arguments for functions.

(continued)

Chapter 2: Creating a Spreadsheet from Scratch

06_037377 ch02.qxp 11/16/06 9:24 AM Page 69

Table 2-1

(continued)

What Shows Up in the Cell What’s Going On Here?

#NUM! Appears when Excel encounters a problem with a

number in the formula, such as the wrong type of

argument in an Excel function or a calculation that

produces a number too large or too small to be rep-

resented in the worksheet.

#REF! Appears when Excel encounters an invalid cell ref-

erence, such as when you delete a cell referred to

in a formula or paste cells over the cells referred

to in a formula.

#VALUE! Appears when you use the wrong type of argument

or operator in a function, or when you call for a

mathematical operation that refers to cells that

contain text entries.

Fixing Up Those Data Entry Flub-Ups

We all wish we were perfect, but alas, because so few of us are, we are best

off preparing for those inevitable times when we mess up. When entering vast

quantities of data, it’s really easy for those nasty little typos to creep into

your work. In your pursuit of the perfect spreadsheet, here are things you

can do. First, get Excel to automatically correct certain data entry typos right

as they happen with its AutoCorrect feature. Second, manually correct any

disgusting little errors that get through, either while you’re still in the

process of making the entry in the cell or after the entry has gone in.

You really AutoCorrect that for me

The AutoCorrect feature is a godsend for those of us who tend to make the

same stupid typos over and over again. With AutoCorrect, you can alert Excel

2007 to your own particular typing gaffes and tell the program how it should

automatically fix them for you.

When you first install Excel, the AutoCorrect feature already knows to auto-

matically correct two initial capital letters in an entry (by lowercasing the

second capital letter), to capitalize the name of the days of the week, and to

replace a set number of text entries and typos with particular substitute text.

Part I: Getting In on the Ground Floor

06_037377 ch02.qxp 11/16/06 9:24 AM Page 70

You can add to the list of text replacements at any time when using Excel.

These text replacements can be of two types: typos that you routinely make

along with the correct spellings, and abbreviations or acronyms that you

type all the time along with their full forms.

To add to the replacements:

1. Click Office Button | Excel Options | Proofing or press Alt+FIP and

then click the AutoCorrect Options button or press Alt+A to open the

AutoCorrect dialog box.

2. On the AutoCorrect tab in this dialog box, enter the typo or abbrevia-

tion in the Replace text box.

3. Enter the correction or full form in the With text box.

4. Click the Add button or press Enter to add the new typo or abbrevia-

tion to the AutoCorrect list.

5. Click the OK button to close the AutoCorrect dialog box.

Cell editing etiquette

Despite the help of AutoCorrect, some mistakes are bound to get you. How

you correct them really depends upon whether you notice before or after you

complete the cell entry.

 If you catch the mistake before you complete an entry, you can delete it

by pressing your Backspace key until you remove all the incorrect char-

acters from the cell. Then you can retype the rest of the entry or the for-

mula before you complete the entry in the cell.

 If you don’t discover the mistake until after you’ve completed the cell

entry, you have a choice of replacing the whole thing or editing just the

mistakes.

 When dealing with short entries, you’ll probably want to take the

replacement route. To replace a cell entry, you have only to position the

cell pointer in that cell, type your replacement entry, and then complete

the replacement entry by clicking the Enter box or pressing Enter or one

of the arrow keys.

 When the error in an entry is relatively easy to fix and the entry is on the

long side, you’ll probably want to edit the cell entry rather than replace

it. To edit the entry in the cell, simply double-click the cell or select the

cell and then press F2.

Chapter 2: Creating a Spreadsheet from Scratch

06_037377 ch02.qxp 11/16/06 9:24 AM Page 71

 Doing either one reactivates the Formula bar by once again displaying

the Enter and Cancel boxes, while at the same time placing the insert-

ion point in the cell entry in the worksheet. (If you double-click, the

insertion point positions itself wherever you click; press F2, and

the insertion point positions itself after the last character in the entry.)

 Notice also that the mode indicator changes to Edit. While in this mode,

you can use the mouse or the arrow keys to position the insertion point

at the place in the cell entry that needs fixing.

In Table 2-2, I list the keystrokes that you can use to reposition the insertion

point in the cell entry and delete unwanted characters. If you want to insert

new characters at the insertion point, simply start typing. If you want to

delete existing characters at the insertion point as you type new ones, press

the Insert key on your keyboard to switch from the normal insert mode to

overtype mode. To return to normal insert mode, press Insert a second time.

When you finish making corrections to the cell entry, you must complete the

edits by pressing Enter before Excel updates the contents of the cell.

While Excel is in Edit mode, you must reenter the edited cell contents by

either clicking the Enter box or pressing Enter. You can use the arrow keys as

a way to complete an entry only when the program is in Enter mode. When

the program is in Edit mode, the arrow keys move the insertion point only

through the entry that you’re editing, not to a new cell.

Table 2-2 Keystrokes for Fixing Those Cell Entry Flub-Ups

Keystroke What the Keystroke Does

Delete Deletes the character to the right of the insertion point

Backspace Deletes the character to the left of the insertion point

→ Positions the insertion point one character to the right

← Positions the insertion point one character to the left

↑ Positions the insertion point, when it is at the end of the cell entry,

to its preceding position to the left

End or ↓ Moves the insertion point after the last character in the cell entry

Home Moves the insertion point in front of the first character of the cell

entry

Ctrl+

→ Positions the insertion point in front of the next word in the cell

entry

Ctrl+

← Positions the insertion point in front of the preceding word in the

cell entry

Insert Switches between insert and overtype mode

Part I: Getting In on the Ground Floor

06_037377 ch02.qxp 11/16/06 9:24 AM Page 72

Taking the Drudgery out of Data Entry

Before leaving the topic of data entry, I feel duty-bound to cover some of the

shortcuts that really help to cut down on the drudgery of this task. These

data-entry tips include AutoComplete and AutoFill features as well as doing

data entry in a preselected block of cells and making the same entry in a

bunch of cells all at the same time.

I’m just not complete without you

The AutoComplete feature in Excel 2007 is not something you can do any-

thing about, just something to be aware of as you enter your data. In an

attempt to cut down on your typing load, our friendly software engineers at

Microsoft came up with the AutoComplete feature.

AutoComplete is kinda like a moronic mind reader who anticipates what you

might want to enter next based upon what you just entered. This feature

comes into play only when you’re entering a column of text entries. (It does

not come into play when entering values or formulas or when entering a row

of text entries.) When entering a column of text entries, AutoComplete looks

at the kinds of entries that you make in that column and automatically dupli-

cates them in subsequent rows whenever you start a new entry that begins

with the same letter as an existing entry.

Chapter 2: Creating a Spreadsheet from Scratch

The Tale of Two Edits:

Cell versus Formula bar editing

Excel gives you a choice between editing a cell’s

contents either in the cell or on the Formula bar.

Whereas most of the time, editing right in the cell

is just fine, when dealing with really, really long

entries (like humongous formulas that seem to go

on forever or text entries that take up paragraphs

and paragraphs), you may prefer to do your edit-

ing on the Formula bar. This is because Excel

2007 automatically adds up and down scroll

arrow buttons to the end of the Formula bar when

a cell entry is too long to be completely displayed

on a single row. These scroll arrow buttons

enable you to display each line of the cell’s long

entry without expanding the Formula bar (as in

earlier versions of Excel) and thereby obscuring

the top part of the Worksheet area.

To edit the contents in the Formula bar rather

than in the cell itself, click the appropriate scroll

arrow button to display the line with the con-

tents that needs editing and then click the I-

beam mouse pointer at the place in the text or

numbers that requires modification to set the

insertion cursor.

06_037377 ch02.qxp 11/16/06 9:24 AM Page 73

For example, suppose that I enter Jack Sprat Diet Centers (one of the compa-

nies owned and operated by Mother Goose Enterprises) in cell A3 and then

move the cell pointer down to cell A4 in the row below and press J (lower-

case or uppercase, it doesn’t matter). AutoComplete immediately inserts the

remainder of the familiar entry —

ack Sprat Diet Centers — in this cell after

the J, as shown in Figure 2-9.

Now this is great if I happen to need Jack Sprat Diet Centers as the row head-

ing in both cells A3 and A4. Anticipating that I might be typing a different

entry that just happens to start with the same letter as the one above,

AutoComplete automatically selects everything after the first letter in the

duplicated entry it inserted (from

ack on, in this example). This enables me

to replace the duplicate supplied by AutoComplete just by continuing to type.

This is what I did after capturing the Excel screen that you see in Figure 2-9

because I needed to enter Jack and Jill Trauma Centers — another of Mother’s

companies — in cell A4.

If you override a duplicate supplied by AutoComplete in a column by typing

one of your own (as in my example with changing Jack Sprat Diet Centers to

Jack and Jill Trauma Centers in cell A4), you effectively shut down its ability

to supply any more duplicates for that particular letter. So, for instance, in

my example, after changing Jack Sprat Diet Centers to Jack and Jill Trauma

Centers in cell A4, AutoComplete doesn’t do anything if I then type

J in cell

A5. In other words, you’re on your own if you don’t continue to accept

AutoComplete’s typing suggestions.

Figure 2-9:

Auto-

Complete

duplicates a

entry if you

start a new

entry in the

same

column that

begins with

the same

letter.

Part I: Getting In on the Ground Floor

06_037377 ch02.qxp 11/16/06 9:24 AM Page 74

If you find that the AutoComplete feature is really making it hard for you to

enter a series of cell entries that all start with the same letter but are other-

wise not alike, you can turn off the AutoComplete feature. Click Office Button |

Excel Options | Advanced or press Alt+FIA to select the Advanced tab of the

Excel Options dialog box. Then, select the Enable AutoComplete for Cell

Values check box in the Editing Options section to remove its check mark

before clicking OK.

Fill ‘er up with AutoFill

Many of the worksheets that you create with Excel require the entry of a

series of sequential dates or numbers. For example, a worksheet may require

you to title the columns with the 12 months, from January through December

or to number the rows from 1 to 100.

Excel’s AutoFill feature makes short work of this kind of repetitive task. All

you have to enter is the starting value for the series. In most cases, AutoFill is

smart enough to figure out how to fill out the series for you when you drag

the fill handle to the right (to take the series across columns to the right) or

down (to extend the series to the rows below).

Remember that the AutoFill handle looks like this — + — and appears only

when you position the mouse pointer on the lower-right corner of the cell

(or the last cell, when you’ve selected a block of cells). Keep in mind that if

you drag a cell selection with the white-cross mouse pointer rather than the

AutoFill handle, Excel simply extends the cell selection to those cells you

drag through (see Chapter 3). If you drag a cell selection with the arrowhead

pointer, Excel moves the cell selection (see Chapter 4).

When creating a series with the fill handle, you can drag in only one direction

at a time. For example, you can fill the series or copy the entry to the range to

the left or right of the cell that contains the initial values, or you can fill the

series or copy to the range above or below the cell that contains the initial

values. You can’t, however, fill or copy the series to two directions at the same

time (such as down and to the right by dragging the fill handle diagonally).

As you drag the mouse, the program keeps you informed of whatever entry

would be entered into the last cell selected in the range by displaying that

entry next to the mouse pointer (as a kind of AutoFill tips, if you will). When

you release the mouse button after extending the range with the fill handle,

Excel either creates a series in all of the cells that you select or copies the

entire range with the initial value. To the right of the last entry in the filled

or copied series, Excel also displays a drop-down button that contains a

shortcut menu of options. You can use this shortcut menu to override

Chapter 2: Creating a Spreadsheet from Scratch

06_037377 ch02.qxp 11/16/06 9:24 AM Page 75

Excel’s default filling or copying. For example, when you use the fill handle,

Excel copies an initial value into a range of cells. But, if you want a sequential

series, you could do this by selecting the Fill Series command on the AutoFill

Options shortcut menu.

In Figures 2-10 and 2-11, I illustrate how to use AutoFill to enter a row of

months, starting with January in cell B2 and ending with June in cell G2.

To do this, you simply enter

January in cell B2 and then position the mouse

pointer on the fill handle in the lower-right corner of this cell before you drag

through to cell G2 on the right (as shown in Figure 2-10). When you release

the mouse button, Excel fills in the names of the rest of the months (February

through June) in the selected cells (as shown in Figure 2-11). Note that Excel

keeps the cells with the series of months selected, giving you another chance

to modify the series. (If you went too far, you can drag the fill handle to the

left to cut back on the list of months; if you didn’t go far enough, you can

drag it to the right to extend the list of months further.)

Figure 2-11:

Release the

mouse

button, and

Excel fills

the cell

selection

with the

missing

months.

Figure 2-10:

To enter a

series of

months,

enter the

first month

and then

drag the Fill

handle in a

direction to

sequential

months.

Part I: Getting In on the Ground Floor

06_037377 ch02.qxp 11/16/06 9:24 AM Page 76

Also, you can use the options on the AutoFill Options drop-down menu

(opened by clicking the drop-down button that appears on the fill handle to

the right of June) to override the series created by default. To have Excel

copy January into each of the selected cells, choose Copy Cells on this menu.

To have the program fill the selected cells with the formatting used in cell B2

(in this case, the cell has had bold and italics applied to it — see Chapter 3

for details on formatting cells), you select Fill Formatting Only on this menu.

To have Excel fill in the series of months in the selected cells without copying

the formatting used in cell B2, you select the Fill Without Formatting com-

mand from this shortcut menu.

See Table 2-3 in the following section to see some of the different initial values

that AutoFill can use and the types of series that Excel can create from them.

Working with a spaced series

AutoFill uses the initial value that you select (date, time, day, year, and so on)

to design the series. All the sample series I show in Table 2-3 change by a

factor of one (one day, one month, or one number). You can tell AutoFill to

create a series that changes by some other value: Enter two sample values in

neighboring cells that describe the amount of change you want between each

value in the series. Make these two values the initial selection that you

extend with the fill handle.

For example, to start a series with Saturday and enter every other day across

a row, enter

Saturday in the first cell and Monday in the cell next door. After

selecting both cells, drag the fill handle across the cells to the right as far you

need to fill out a series based on these two initial values. When you release

the mouse button, Excel follows the example set in the first two cells by

entering every other day (Wednesday to the right of Monday, Friday to the

right of Wednesday, and so on).

Table 2-3 Samples of Series You Can Create with AutoFill

Value Entered in First Cell Extended Series Created by AutoFill

in the Next Three Cells

June July, August, September

Jun Jul, Aug, Sep

Tuesday Wednesday, Thursday, Friday

Tue Wed, Thu, Fri

4/1/99 4/2/99, 4/3/99, 4/4/99

(continued)

Chapter 2: Creating a Spreadsheet from Scratch

06_037377 ch02.qxp 11/16/06 9:24 AM Page 77

Table 2-3

(continued)

Value Entered in First Cell Extended Series Created by AutoFill

in the Next Three Cells

Jan-00 Feb-00, Mar-00, Apr-00

15-Feb 16-Feb, 17-Feb, 18-Feb

10:00 PM 11:00 PM, 12:00 AM, 1:00 AM

8:01 9:01, 10:01, 11:01

Quarter 1 Quarter 2, Quarter 3, Quarter 4

Qtr2 Qtr3, Qtr4, Qtr1

Q3 Q4, Q1, Q2

Product 1 Product 2, Product 3, Product 4

1st Product 2nd Product, 3rd Product, 4th Product

Copying with AutoFill

You can use AutoFill to copy a text entry throughout a cell range (rather than

fill in a series of related entries). To copy a text entry to a cell range, hold

down the Ctrl key as you click and drag the Fill handle. When you hold down

the Ctrl key as you click the fill handle, a plus sign appears to the right of the

Fill handle — your sign that AutoFill will copy the entry in the active cell

instead of creating a series using it. You can also tell because the entry that

appears as the AutoFill tip next to the mouse pointer as you drag contains

the same text as the original cell. If you decide, after copying an initial label

or value to a range, that you should have used it to fill in a series, all you

have to do is click the drop-down button that appears on the fill handle at

cell with the last copied entry and select the Fill Series command on the

AutoFill Options shortcut menu that then appears.

Although holding down Ctrl as you drag the fill handle copies a text entry,

just the opposite is true when it comes to values! Suppose that you enter the

number

17 in a cell and then drag the fill handle across the row — Excel just

copies the number 17 in all the cells that you select. If, however, you hold

down Ctrl as you drag the fill handle, Excel then fills out the series (17, 18, 19,

and so on). If you forget and create a series of numbers when you only need

the value copied, rectify this situation by selecting the Copy Cells command

on the AutoFill Options shortcut menu.

Part I: Getting In on the Ground Floor

06_037377 ch02.qxp 11/16/06 9:24 AM Page 78

Creating custom lists for AutoFill

In addition to varying the increment in a series created with AutoFill, you can

also create your own custom series. For example, say your company has

offices in the following locations and you get tired of typing out the sequence

in each new spreadsheet that requires them:

 New York

 Chicago

 Atlanta

 Seattle

 San Francisco

 San Diego

After creating a custom list with these locations, you can enter the entire

sequence of cities simply by entering New York in the first cell and then

dragging the Fill handle to the blank cells where the rest of the companies

should appear.

To create this kind of custom series, follow these steps:

1. Click Office Button | Excel Options | Popular or press Alt+FI and

then click the Edit Custom Lists button in the Top Options for Working

with Excel section to open the Custom Lists dialog box (as shown in

Figure 2-12).

If you’ve already gone to the time and trouble of typing the custom list in

a range of cells, go to Step 2. If you haven’t yet typed the series in an

open worksheet, go to Step 5 instead.

Figure 2-12:

Creating a

custom

company list

from a

range of

existing cell

entries.

Chapter 2: Creating a Spreadsheet from Scratch

06_037377 ch02.qxp 11/16/06 9:24 AM Page 79

2. Click inside the Import List from Cells text box and click the Minimize

Dialog Box button (the one with the picture of the tiny grid to the

right of the Import List from Cells text box) so that you can see your

list and drag through the range of cells to select them (see Chapter 3

for details).

3. After selecting the cells in the worksheet, click the Maximize Dialog

box button.

This button automatically replaces the Minimize Dialog box button to

the right of the Import List from Cells text box.

4. Then click the Import button to copy this list into the List Entries

list box.

Skip to Step 7.

5. Click inside the List Entries list box and then type each entry (in the

desired order), being sure to press Enter after typing each one.

When all the entries in the custom list appear in the List Entries list box

in the order you want them, proceed to Step 7.

6. Click the Add button to add the list of entries to the Custom lists box.

Finish creating all the custom lists you need, using the preceding steps.

When you are done, move on to Step 7.

7. Click OK twice, the first time to close the Custom Lists dialog box and

the second to close the Excel Options dialog box and return to the cur-

rent worksheet in the active workbook.

After adding a custom list to Excel, from then on you need only enter the first

entry in a cell and then use the fill handle to extend it to the cells below or to

the right.

If you don’t even want to bother with typing the first entry, use the AutoCorrect

feature — refer to the section “You really AutoCorrect that for me,” earlier in

this chapter — to create an entry that will fill in as soon as you type your

favorite acronym for it (such as ny for New York).

Inserting special symbols

Excel makes it easy to enter special symbols, such as foreign currency indi-

cators, as well as special characters, such as the trademark and copyright

symbols, into your cell entries. To add a special symbol or character to a cell

entry you’re making or editing, click Insert | Symbol on the Ribbon or press

Alt+NU to open the Symbol dialog box (similar to the one shown in Figure 2-13).

Part I: Getting In on the Ground Floor

06_037377 ch02.qxp 11/16/06 9:24 AM Page 80

The Symbol dialog box contains two tabs: Symbols and Special Characters.

To insert a mathematical or foreign currency symbol on the Symbols tab,

click its symbol in the list box and then click the Insert button. (You can also

do this by double-clicking the symbol.) To insert characters, such as foreign

language or accented characters from other character sets, click the Subset

drop-down button, then click the name of the set in the drop-down list, and

then click the desired characters in the list box below. You can also insert

commonly used currency and mathematical symbols, such as the Pound or

plus-or-minus symbol, by clicking them in the Recently Used Symbols section

at the bottom of this tab of the Symbol dialog box.

To insert special characters, such as the registered trademark, paragraph

symbol, and ellipsis, click the Special Characters tab of the Symbol dialog box,

locate the symbol in the scrolling list, click it, and then click the Insert button.

(You can also insert one of these special characters by double-clicking as well.)

When you finish inserting special symbols and characters, close the Symbol

dialog box by clicking its Close button in its upper-right corner.

Entries all around the block

When you want to enter a table of information in a new worksheet, you can

simplify the job of entering the data if you select all the empty cells in which

you want to make entries before you begin entering any information. Just

position the cell pointer in the first cell of what is to become the data table

and then select all the cells in the subsequent columns and rows. (For infor-

mation on the ways to select a range of cells, see Chapter 3.) After you select

the block of cells, you can begin entering the first entry.

Figure 2-13:

Use the

Symbol

dialog box

to insert

special

symbols and

characters

into your

cell entries.

Chapter 2: Creating a Spreadsheet from Scratch

06_037377 ch02.qxp 11/16/06 9:24 AM Page 81

When you select a block of cells (also known as a range) before you start

entering information, Excel restricts data entry to that range as follows:

 The program automatically advances the cell pointer to the next cell in

the range when you click the Enter box or press Enter to complete each

cell entry.

 In a cell range that contains several different rows and columns, Excel

advances the cell pointer down each row of the column as you make

your entries. When the cell pointer reaches the cell in the last row of the

column, the cell pointer advances to the first selected row in the next

column to the right. If the cell range uses only one row, Excel advances

the cell pointer from left to right across the row.

 When you finish entering information in the last cell in the selected

range, Excel positions the cell pointer in the first cell of the now-completed

data table. To deselect the cell range, click the mouse pointer on one of

the cells in the worksheet (inside or outside the selected range — it

doesn’t matter) or press one of the arrow keys.

Be sure that you don’t press one of the arrow keys to complete a cell entry

within a preselected cell range instead of clicking the Enter box or pressing

Enter. Pressing an arrow key deselects the range of cells when Excel moves

the cell pointer. To move the cell pointer around a cell range without dese-

lecting the range, try these methods:

 Press Enter to advance to the next cell down each row and then

across each column in the range. Press Shift+Enter to move up to

the previous cell.

 Press Tab to advance to the next cell in the column on the right and

then down each row of the range. Press Shift+Tab to move left to the

previous cell.

 Press Ctrl+. (period) to move from one corner of the range to another.

Data entry express

You can save a lot of time and energy when you want the same entry (text,

value, or formula) to appear in many cells of the worksheet; you can enter

the information in all the cells in one operation. You first select the cell

ranges to hold the information. (Excel lets you select more than one cell

range for this kind of thing — see Chapter 3 for details.) Then you construct

the entry on the formula bar and press Ctrl+Enter to put the entry into all the

selected ranges.

Part I: Getting In on the Ground Floor

06_037377 ch02.qxp 11/16/06 9:24 AM Page 82

The key to making this operation a success is to hold the Ctrl key as you

press Enter so that Excel inserts the entry on the formula bar into all the

selected cells. If you forget to hold Ctrl and you just press Enter, Excel places

the entry in the first cell only of the selected cell range.

You can also speed up data entry in a list that includes formulas by making

sure that the Extend Data Range Formats and Formulas check box is selected

in the Editing Options section of the Advanced tab in the Excel Options dialog

box. (click Microsoft Office | Excel Options | Advanced or press Alt+FIA.)

When this check box is selected, Excel automatically formats new data that

you type in the last row of a list to match that of like data in earlier rows and

copies down formulas that appear in the preceding rows. Note, however, that

for this new feature to kick in, you must manually enter the formulas and

format the data entries in at least three rows preceding the new row.

How to Make Your Formulas

Function Even Better

Earlier in this chapter, I show you how to create formulas that perform a

series of simple mathematical operations, such as addition, subtraction,

multiplication, and division. (See the section “Fabricating those fabulous

formulas!”) Instead of creating more complex formulas from scratch out of an

intricate combination of these operations, you can find an Excel function to

get the job done.

function is a predefined formula that performs a particular type of com-

putation. All you have to do to use a function is supply the values that the

function uses when performing its calculations. (In the parlance of the

Spreadsheet Guru, such values are known as the

arguments of the function.)

As with simple formulas, you can enter the arguments for most functions

either as a numerical value (for example,

22 or –4.56) or, as is more common,

as a cell reference (

B10) or as a cell range (C3:F3).

Just as with a formula you build yourself, each function you use must start

with an equal sign (=) so that Excel knows to enter the function as a formula

rather than as text. Following the equal sign, you enter the name of the function

(in uppercase or lowercase — it doesn’t matter, as long as you don’t misspell

the name). Following the name of the function, you enter the arguments

required to perform the calculations. All function arguments are enclosed

in a pair of parentheses.

Chapter 2: Creating a Spreadsheet from Scratch

06_037377 ch02.qxp 11/16/06 9:24 AM Page 83

If you type the function directly in a cell, remember not to insert spaces

between the equal sign, function name, and the arguments enclosed in paren-

theses. Some functions use more than one value when performing their desig-

nated calculations. When this is the case, you separate each function with a

comma (not a space).

After you type the equal sign and begin typing the first few letters of the name

of the function you want to use, a drop-down list showing all the functions

that begin with the letters you’ve typed appears immediately beneath the

cell When you see the name of the function you want to use on this list,

double-click it and Excel will finish entering the function name in the cell

and on the Formula bar as well as add the left parenthesis (

() that marks the

beginning of the arguments for the function.

Excel then displays all the arguments that the function takes beneath the cell

and you can indicate any cell or cell range that you want to use as the first

argument either by pointing to it or typing its cell or range references. When

the function uses more than one argument, you can point to the cells or cell

ranges or enter the addresses for the second argument right after you enter

a comma (

,) to complete the first argument.

After you finish entering the last argument, you need to close off the function

by typing a right parenthesis (

)) to mark the end of the argument list. The

display of the function name along with its arguments that appeared beneath

the cell when you first selected the function from the drop-down list then dis-

appears. Click the Enter box or press Enter or the appropriate arrow key to

then insert the function into the cell and have Excel calculate the answer.

Inserting a function into a formula

with the Function Wizard button

Although you can enter a function by typing it directly in a cell, Excel provides

a Function Wizard button on the Formula bar you can use to select any of

Excel’s functions. When you click this button, Excel opens the Insert Function

dialog box (shown in Figure 2-14) where you can select the function you want

to use. After you select your function, Excel opens the Function Arguments

dialog box. In this dialog box, you can specify the function arguments. The real

boon comes when you’re fooling with an unfamiliar function or one that’s kind

of complex (some of these puppies can be really hairy). You can get loads of

help in completing the argument text boxes in the Function Arguments dialog

box by clicking the Help on this Function

hyperlink in the lower-left corner of

this dialog box.

Part I: Getting In on the Ground Floor

06_037377 ch02.qxp 11/16/06 9:24 AM Page 84

The Insert Function dialog box contains three boxes: a Search for a Function

text box, a Select a Category drop-down list box, and a Select a Function list

box. When you open the Insert Function dialog box, Excel automatically

selects Most Recently Used as the category in the Select a Category drop-

down list box and displays the functions you usually use in the Select a

Function list box.

If your function isn’t among the most recently used, you must then select the

appropriate category of your function in the Select a Category drop-down list

box. If you don’t know the category, you must search for the function by

typing a description of its purpose in the Search for a Function text box and

then press Enter or click the Go button. For example, to locate all the Excel

functions that total values, you enter the word

total in the Search for

Function list box and click the Go button. Excel then displays its list of

Recommended functions for calculating totals in the Select a Function list

box. You can peruse the recommended functions by selecting each one. As

you select each function in this list, the Insert Function dialog box shows you

the required arguments followed by a description, at the bottom of the dialog

box, of what the function does.

After you locate and select the function you want to use, click the OK button

to insert the function into the current cell and open the Function Arguments

dialog box. This dialog box displays the required arguments for the function

along with any that are optional. For example, suppose that you select the

SUM function (the crown jewel of the Most Recently Used function category)

in the Select a Function list box and then click OK. As soon as you do, the

program inserts

SUM()

Figure 2-14:

Select the

function you

want to use

in Insert

Function

dialog box.

Chapter 2: Creating a Spreadsheet from Scratch

06_037377 ch02.qxp 11/16/06 9:24 AM Page 85

in the current cell and on the Formula bar (following the equal sign), and the

Function Arguments dialog box showing the SUM arguments appears on the

screen (as shown in Figure 2-15). This is where you add the arguments for

the Sum function.

As you can read in the Function Arguments dialog box shown in Figure 2-15,

you can select up to 255 numbers to be summed. What’s not obvious, how-

ever (there’s always some trick, huh?), is that these numbers don’t have to

be in single cells. In fact, most of the time you’ll be selecting a whole slew of

numbers in nearby cells (in a multiple cell selection — that range thing) that

you want to total.

To select your first number argument in the dialog box, you click the cell

(or drag through the block of cells) in the worksheet while the insertion point

is in the Number1 text box. Excel then displays the cell address (or range

address) in the Number1 text box while, at the same time, showing the value

in the cell (or values, if you select a bunch of cells) in the box to the right.

Excel displays the total so far near the bottom of the Function Arguments

dialog box after the words

Formula result=.

Keep in mind that when selecting cells, you can minimize this arguments

dialog box down to just the contents of the Number1 text box by clicking the

Minimize Dialog box button on the right of the Number1 text box. After mini-

mizing the arguments dialog box so that you can select the cells to be used as

the first argument, you can expand it again by clicking the Maximize Dialog

box button (the only button displayed on the far right) or by pressing the Esc

key. Instead of minimizing the dialog box, you can also temporarily move it

out of the way by clicking on any part and then dragging the dialog box to its

new destination on the screen.

If you’re adding more than one cell (or bunch of cells) in a worksheet, press the

Tab key or click the Number2 text box to move the insertion point that text

box. (Excel responds by extending the argument list with a Number3 text box.)

Here is where you specify the second cell (or cell range) that is to be added to

the one now showing in the Number1 text box. After you click the cell or drag

Figure 2-15:

Specify the

arguments

to use in the

selected

function in

the Function

Arguments

dialog box.

Part I: Getting In on the Ground Floor

06_037377 ch02.qxp 11/16/06 9:24 AM Page 86

through the second cell range, the program displays the cell address(es), with

the numbers in the cell(s) to the right and the running total near the bottom of

the Function Arguments dialog box after the words

Formula result= (as

shown in Figure 2-15). Note that you can minimize the entire Arguments dialog

box down to just the contents of the argument text box you’re dealing with

(Number2, Number3, and so on) by clicking its particular Minimize Dialog box

button if the dialog box obscures the cells that you need to select.

When you finish pointing out the cells or bunches of cells to be summed,

click the OK button to close the Function Arguments dialog box and put the

SUM function in the current cell.

Editing a function with the

Function Wizard button

Use the Excel Function Wizard button to edit formulas that contain functions

right from the Formula bar. Select the cell with the formula with the function

to be edited before you click the Function Wizard button (the one sporting

the

fx that appears immediately in front of the current cell entry on the

Formula bar).

As soon as you click the Function Wizard button, Excel opens the Function

Arguments dialog box where you can edit its arguments. To edit just the argu-

ments of a function, select the cell references in the appropriate argument’s

text box (marked Number1, Number2, Number3, and so on) and then make

whatever changes are required to the cell addresses or select a new range of

cells. Keep in mind that Excel automatically adds any cell or cell range that

you highlight in the worksheet to the current argument. If you want to

replace the current argument, you need to highlight it and then get rid of its

cell addresses by pressing the Delete key before you highlight the new cell or

cell range to be used as the argument. (Remember that you can always mini-

mize this dialog box or move it to a new location if it obscures the cells you

need to select.)

When you finish editing the function, press Enter or click the OK button in

the Function Arguments dialog box to put it away and update the formula in

the worksheet.

I’d be totally lost without AutoSum

Before leaving this fascinating discussion on entering functions, I want you to

get to the AutoSum tool in the Editing group on the Home tab of the Ribbon.

Look for the Greek sigma (

Σ) symbol. This little tool is worth its weight in

gold. In addition to entering the SUM, AVERAGE, COUNT, MAX, or MIN func-

tions, it also selects the most likely range of cells in the current column or

Chapter 2: Creating a Spreadsheet from Scratch

06_037377 ch02.qxp 11/16/06 9:24 AM Page 87

row that you want to use as the function’s argument and then automatically

enters them as the function’s argument. And nine times out of ten, Excel

selects (with the

marquee or moving dotted line) the correct cell range to be

totaled, averaged, counted, and so forth. For that tenth case, you can manu-

ally correct the range by simply dragging the cell pointer through the block

of cells that need to be summed.

Simply click the AutoSum button on the Home tab when you want to insert

the Sum function into the current cell. If you want to use this button to insert

another function, such as Average, Count, Max, or Min, you need to click its

drop-down button and select the name of the desired function on its pop-up

menu (click Count Numbers on the menu to insert the COUNT function). Note

that if you select the More Functions command on this menu, Excel opens

the Insert Function dialog box as though you had clicked the

fx button on the

Formula bar.

In Figure 2-16, check out how to use the AutoSum tool to total the sales of Jack

Sprat Diet Centers in row 3. Position the cell pointer in cell E3 where the first-

quarter total is to appear and click the AutoSum tool. Excel inserts SUM (equal

sign and all) onto the Formula bar; places a marquee around the cells B3, C3,

and D3; and uses the cell range B3:D3 as the argument of Sum function.

Now look at the worksheet after you insert the function in cell E3 (see

Figure 2-17). The calculated total appears in cell E3 while the following

Sum function formula appears in the Formula bar:

=SUM(B3:D3)

Figure 2-16:

To total

Jack Sprat

Diet Centers

first quarter

sales for

row 3,

click the

AutoSum

button in

cell E3 and

press Enter.

Part I: Getting In on the Ground Floor

06_037377 ch02.qxp 11/16/06 9:24 AM Page 88

After entering the function to total the sales of Jack Sprat Diet Centers, you

can copy this formula to total sales for the rest of the companies by dragging

the fill handle down column E until the cell range E3:E9 is highlighted.

Look at Figure 2-18 to see how you can use the AutoSum tool to total the

January sales for all the Mother Goose Enterprises in column B. Position the

cell pointer in cell B10 where you want the total to appear. Click the AutoSum

tool, and Excel places the marquee around cells B3 through B9 and correctly

enters the cell range B3:B9 as the argument of the SUM function.

In Figure 2-19, you see the worksheet after inserting the function in cell B10

and using the AutoFill feature to copy the formula to cells C10, D10, and E10

to the right. (To use AutoFill, drag the fill handle through the cells to the right

until you reach cell E10. Release the mouse button.)

Figure 2-18:

Click the

AutoSum

button in

cell B10 and

press Enter

to total the

January

sales for all

companies

in column B.

Figure 2-17:

The

worksheet

with the first

quarter

totals

calculated

with

AutoSum.

Chapter 2: Creating a Spreadsheet from Scratch

06_037377 ch02.qxp 11/16/06 9:24 AM Page 89

Making Sure That the Data

Is Safe and Sound

All the work you do in any of the worksheets in your workbook is at risk until

you save the workbook as a disk file normally on your computer’s hard drive.

Should you lose power or should your computer crash for any reason before

you save the workbook, you’re out of luck. You have to re-create each and

every keystroke — a painful task, made all the worse because it’s so unneces-

sary. To avoid this unpleasantness altogether, adopt this motto: Save your

work any time you enter more information than you could possibly bear

to lose.

To encourage frequent saving on your part, Excel even provides you with a

Save button on the Quick Access toolbar (the one with the picture of a 3

⁄4”

floppy disk, the very first on the toolbar). You don’t even have to take the

time and trouble to choose the Save command from the File pull-down menu

(opened by clicking the Office Button) or even press Ctrl+S; you can simply

click this tool whenever you want to save new work on disk.

When you click the Save button, press Ctrl+S, or click Office Button | Save for

the first time you, Excel displays the Save As dialog box. Use this dialog box

to replace the temporary document name (Book1, Book2, and so forth) with

a more descriptive filename in the File Name text box, select a new file format

in the Save As Type drop-down list box, and to select a new drive and folder

before you save the workbook as a disk file.

When you finish making changes in the Save As dialog box, click the Save

button or press Enter to have Excel 2007 save your work. When Excel saves

your workbook file, the program saves all the information in every worksheet

in your workbook (including the last position of the cell cursor) in the desig-

nated folder and drive.

Figure 2-19:

The

worksheet

after

copying the

Sum

function

formulas

using the fill

handle.

Part I: Getting In on the Ground Floor

06_037377 ch02.qxp 11/16/06 9:24 AM Page 90

You don’t have to fool with the Save As dialog box again unless you want to

rename the workbook or save a copy of it in a different directory. If you want

to do either of these things, you must click Office Button | Save AS or press

Alt+FA to choose the Save As command rather than click the Save button on

the Quick Access toolbar or press Ctrl+S.

The Save As dialog box in Windows Vista

Figure 2-20 shows you the Save As dialog box as it appears in Excel 2007

when running the program under Windows Vista. Here, you can replace the

temporary filename (Book1, Book2, and so on) with a more descriptive name

by clicking the File Name text box and typing in the new name (up to 255

characters total including spaces).

To select a new folder in which to save the new workbook file, follow these

steps:

1. Click the Browse Folders button (with the triangle pointing downward)

to expand the Save As dialog box.

When you expand the Save As dialog box, the dialog box displays the

Navigation Pane where you can select folders listed in the Favorite Links

or Folders sections.

2. In the Navigation Pane, click the name of the Favorites link containing

the folder in which you want to save the file or click the Folders button

(with the upward-pointing triangle) and then click the name of this

folder

Figure 2-20:

The Save As

dialog box in

Windows

Vista

enables you

select the

filename,

folder for

the new

workbook

file as well

as add tags

to it.

Chapter 2: Creating a Spreadsheet from Scratch

06_037377 ch02.qxp 11/16/06 9:24 AM Page 91

3. (Optional) If you want to save the workbook file inside a new sub-

folder within the folder currently open in the Save As dialog box,

click the New Folder button on the toolbar and then replace the sug-

gested New Folder name by typing the actual name of the folder and

press Enter.

4. Click the Save button to save the file in the selected folder.

When the Save As dialog box is expanded, you can add a title, subject, and

tags to the new workbook file by clicking the Add a Title, Specify the Subject,

or Add a Tag links. You can then use any or all of these pieces of information

you add to the file when later searching for the workbook (see Chapter 4 for

details on searching).

The Save As dialog box in Windows XP

Figure 2-21 shows you the Save As dialog box as it appears in Excel 2007

when running the program under Windows XP.

The Windows XP version of the Save As dialog box contains a bunch of large

buttons that appear on the left side of the dialog box: My Recent Documents,

Desktop, My Documents, My Computer, and My Network Places. Use these

buttons to select the following folders in which to save your new workbook

file:

 Click the My Recent Documents button to save your workbook in the

Recent folder. The Recent folder resides in this hierarchy: Windows folder

(on your hard drive)\Application Data folder\Microsoft folder\Office

folder\Recent folder.

 Click the Desktop button to save your workbook on your computer’s

desktop.

 Click the My Documents button to save your workbook in the My

Documents folder.

 Click the My Computer button to save your workbook on one of the

disks on your computer or in your own or a shared documents folder on

your hard drive.

 Click the My Network Places button to save your workbook in one of the

folders on your company’s network.

To save your workbook in a new subfolder within the folder open in the Save

As dialog box, click the Create New Folder button on the toolbar (or press

Alt+4) and then type the name for the folder in the New Folder dialog box

before you click OK.

Part I: Getting In on the Ground Floor

06_037377 ch02.qxp 11/16/06 9:24 AM Page 92

Changing the default file location

Whenever you open the Save As dialog box to save a new workbook file,

Excel 2007 automatically selects the folder listed in the Default File Location

text box on the Save tab of the Excel Options dialog box (Office Button |

Excel Options | Save or Alt+FIS).

When you first start using Excel, the default folder is either the My Documents

(Windows XP) or the Documents folder (Windows Vista) under your user

name on your hard drive. So, for example, the directory path of the default

folder where Excel 2007 automatically saves new workbook files on my com-

puter running Windows XP is

C:\Documents and Settings\Greg\My Documents

However, the directory path of the default folder where Excel 2007 automati-

cally saves new workbook files on my other computer running Windows

Vista is

C:\Users\Greg\Documents

The very generic My Documents or Documents folder may not be the places

on your hard drive where you want all the new workbooks you create auto-

matically saved. To change the default file location to another folder on your

computer, follow these steps:

1. Click Office Button | Excel Options | Save or press Alt+FIS to open the

Save tab of the Excel Options dialog box.

The Default File Location text box displays the directory path to the cur-

rent default folder.

Figure 2-21:

The Save As

dialog box in

Windows

XP makes

it easy to

select the

filename

and folder

location for

your new

workbook

file.

Chapter 2: Creating a Spreadsheet from Scratch

06_037377 ch02.qxp 11/16/06 9:24 AM Page 93

2. Click the Default File Location text box to select the current directory

path.

When you click the Default File Location text box, Excel selects the entire

directory path so that if you begin typing, the characters you type replace

the entire path. To edit part of the path (such as the My Documents or

Documents folder name after your user name), click the mouse pointer

at that place in the path to set the Insertion point.

3. Edit the existing path or replace it with the path to another existing

folder in which you want all future workbooks to automatically be

saved.

4. Click OK to close the Excel Options dialog box.

The difference between the

XLSX and XLS file format

Excel 2007 supports the use of a new XML-based file format (which Microsoft

officially calls the Microsoft Office Open XML format), which is touted as

being more efficient in saving data resulting in smaller file size and offering

superior integration with external data sources (especially, when these

resources are Web-based ones supporting XML files). This XML-based file

format carries the filename extension .xlsx and the file format in which Excel

automatically saves any new workbook you create.

The only problem with that this newfangled XML-based file format is that it’s

not one that earlier versions of Excel can open. This means that if everybody

who needs to work with the workbook you’ve just created hasn’t yet upgraded

to Excel 2007, you need to save the new workbook in the earlier file format

used in versions 97 through 2003 with the old .xls filename extension. To do

this, click the File As Type drop-down button and then click Excel 97-2003

Workbook (*.xls) on the drop-down menu. (If you work in an office where

all workbooks must be backwardly compatible with earlier versions, see

“Dealing with the new Excel file formats” in Chapter 1 for a tip on making the

Excel 97-2003 workbook file format the new default for Excel 2007.)

Keep in mind that filename extensions such as .xlsx and .xls do not appear

as part of the filename (even though they are appended) in the File Name

text box in the Save As dialog box unless you’ve removed the check mark

from the Hide Extensions for Known File Types check box found on the

View tab of the Folder Options dialog box (Tools

➪Options) in any Windows

Explorer window such as My Documents in Windows XP or Documents in

Windows Vista.

Part I: Getting In on the Ground Floor

06_037377 ch02.qxp 11/16/06 9:24 AM Page 94

Excel 2007 also supports a new binary file format called Office Excel 2007

Binary or BIFF12 for short that carries the .xlsb filename extension. Select

this binary format for really huge spreadsheets that you create that have to

be backwardly compatible with earlier versions of Excel.

Saving the Workbook as a PDF File

The PDF (Portable Document File) file format developed by Adobe Systems

Incorporated enables people to open up and print documents without access

to the original programs with which the documents were created.

For the first time ever, Excel enables you to save your workbook files directly in

this special PDF file format. This means that you can readily share your Excel

2007 workbooks with users who don’t have Excel installed on their computers

by saving them as PDF files. All they have to have installed in order to open and

print the PDF copy of workbook file is the free Adobe Reader software (which

can be downloaded from the Adobe Web site at www.adobe.com).

Before you can save files in the PDF file format, you must download and

install the Excel Microsoft Save as PDF or XPS Add-in program from the

Microsoft Web site. To do this, open the Excel Help window and then search

for “PDF and XPS” in the Search text box. Then, click the Enable Support for

Other File Formats, Such as PDF and XPS link in the Help window. Locate the

Microsoft Save as PDF or XPS Add-in for 2007 Microsoft Office Programs link

the help screen describing PDF and XPS file formats. Finally, follow the online

instructions for downloading and installing this add-in program on the

Microsoft Downloads Web site.

To save a workbook as a PDF file, click the Office Button and then position

the mouse over the continuation button attached to the Save As button (the

triangle pointing to the right) and click the PDF or XPS option on the continu-

ation menu (or simply press Alt+FFP) to open the Publish as PDF or XPS

dialog box. If you don’t need to edit the filename (Excel automatically appends

.pdf to the current filename) or the folder location, simply click the Publish

button, and Excel will save the workbook in a PDF file and automatically open

it up for your inspection in Adobe Reader.

If you create an Excel 2007 workbook that incorporates new features not sup-

ported in earlier versions of Excel, instead of saving the workbook as an .xls

file, thereby losing all of its 2007 enhancements, consider saving it as a PDF

file so that co-workers still using pre-2007 Excel versions can still have access

to the data in all its glory via the Adobe Reader.

Chapter 2: Creating a Spreadsheet from Scratch

06_037377 ch02.qxp 11/16/06 9:24 AM Page 95

Document Recovery to the Rescue

Excel 2007 offers a document recovery feature that can help you in the event

of a computer crash because of a power failure or some sort of operating

system freeze or shutdown. The AutoRecover feature saves your workbooks at

regular intervals. In the event of a computer crash, Excel displays a Document

Recovery task pane the next time you start Excel after rebooting the computer.

When you first start using Excel 2007, the AutoRecover feature is set to auto-

matically save changes to your workbook (provided that the file has already

been saved) every ten minutes. You can shorten or lengthen this interval as

you see fit. Click Office Button | Excel Options | Save or press Alt+FIS to open

the Excel Options dialog box with the Save tab selected. Use the spinner but-

tons or enter a new automatic save interval into the text box marked Save

AutoRecover Information Every 10 Minutes before clicking OK.

The Document Recovery task pane shows the available versions of the work-

book files that were open at the time of the computer crash. It identifies the

original version of the workbook file and when it was saved along with the

recovered version of the file and when it was saved. To open the recovered

version of a workbook (to see how much of the work it contains that was

unsaved at the time of the crash), position the mouse pointer over the

AutoRecover version. Then click its drop-down menu button and click Open

on its pop-up menu. After you open the recovered version, you can (if you

choose) then save its changes by clicking the Save button on the Quick

Access toolbar or by clicking Office Button | Save.

To save the recovered version of a workbook without bothering to first open

it up, place your mouse over the recovered version, click its drop-down

button, and choose the Save As option on the pop-up menu. To permanently

abandon the recovered version (leaving you with

only the data in the original

version), click the Close button at the bottom of the Document Recovery task

pane. When you click the Close button, an alert dialog box appears, giving

you the chance to retain the recovered versions of the file for later viewing.

To retain the files for later viewing, select the Yes (I want to view these

files later) radio button before clicking OK. To retain only the original

versions of the files shown in the task pane, select the No (remove these

files. I have saved the files I need) radio button instead.

Note that the AutoRecover features only works on Excel workbooks that you

have saved at least one time (as explained in the earlier section “Making Sure

That the Data Is Safe and Sound”). In other words, if you build a new work-

book and don’t bother to save and rename it prior to experiencing a com-

puter crash, the AutoRecover feature will not bring back any part of it. For

this reason, it is very, very important that you get into the habit of saving

new workbooks with the Save button on the Quick Access toolbar very

shortly after beginning to work on one its worksheets. Or you can use the

trusty keyboard shortcut Ctrl+S.

Part I: Getting In on the Ground Floor

06_037377 ch02.qxp 11/16/06 9:24 AM Page 96

Part II

Editing

Without Tears

07_037377 pt02.qxp 11/16/06 9:24 AM Page 97

In this part . . .

he business world wouldn’t be half bad if it weren’t for

the fact that right around the time you master your

job, somebody goes and changes it on you. When your life

must always be flexible, changing gears and “going with

the flow” can really grate on a person! The sad truth is

that a big part of the work you do with Excel 2007 is

changing the stuff you slaved so hard to enter into the

spreadsheet in the first place.

In Part II, I break this editing stuff down into three phases:

formatting the raw data; rearranging the formatting data

or in some cases deleting it; and, finally, spitting out the

final formatted and edited data in printed form. Take it

from me, after you know your way around editing your

spreadsheets (as presented in this part of the book),

you’re more than halfway home with Excel 2007.

07_037377 pt02.qxp 11/16/06 9:24 AM Page 98

Chapter 3

Making It All Look Pretty

In This Chapter

 Selecting the cells to be formatted

 Formatting data tables with the Format as Table command button

 Using various number formats on cells containing values

 Adjusting column width and row height in a worksheet

 Hiding columns and rows in a worksheet

 Formatting cell ranges from the Home tab of the Ribbon

 Formatting cell ranges with Styles

 Formatting cells with the Format Painter

n spreadsheet programs like Excel, you normally don’t worry about how

the stuff looks until after you enter all the data in the worksheets of your

workbook and save it all safe and sound (see Chapters 1 and 2). Only then do

you pretty up the information so that it’s clearer and easy to read.

After you decide on the types of formatting that you want to apply to a por-

tion of the worksheet, select all the cells to be beautified and then click the

appropriate tool or choose the menu command to apply those formats to the

cells. But before you discover all the fabulous formatting features you can

use to dress up cells, you first need to know how to pick out the group of

cells that you want to apply the formatting to —

selecting the cells or, alter-

nately,

making a cell selection.

Be aware, also, that entering data into a cell and formatting that data are two

completely different things in Excel. Because they’re separate, you can

change the entry in a formatted cell, and new entries assume the cell’s for-

matting. This enables you to format blank cells in a worksheet, knowing that

when you get around to making entries in those cells, those entries automati-

cally assume the formatting you assign to those cells.

08_037377 ch03.qxp 11/16/06 9:25 AM Page 99

Choosing a Select Group of Cells

Given the monotonously rectangular nature of the worksheet and its compo-

nents, it shouldn’t come as a surprise to find that all the cell selections

you make in the worksheet have the same kind of cubist feel to them. After all,

worksheets are just blocks of cells of varying numbers of columns and rows.

cell selection (or cell range) is whatever collection of neighboring cells you

choose to format or edit. The smallest possible cell selection in a worksheet is

just one cell: the so-called

active cell. The cell with the cell cursor is really just

a single cell selection. The largest possible cell selection in a worksheet is all

the cells in that worksheet (the whole enchilada, so to speak). Most of the cell

selections you need for formatting a worksheet will probably fall somewhere

in between, consisting of cells in several adjacent columns and rows.

Excel shows a cell selection in the worksheet by highlighting in color the

entire block of cells within the extended cell cursor except for the active cell

that keeps its original color. (Look at Figure 3-1 to see several cell selections

of different sizes and shapes.)

In Excel, you can select more than one cell range at a time (a phenomenon

somewhat ingloriously called a

noncontiguous or nonadjacent selection). In

fact, although Figure 3-1 appears to contain several cell selections, it’s really

just one big, nonadjacent cell selection with cell D12 (the active one) as the

cell that was selected last.

Point-and-click cell selections

The mouse is a natural for selecting a range of cells. Just position the mouse

pointer (in its thick, white-cross form) on the first cell and drag in the direc-

tion that you want to extend the selection.

Figure 3-1:

Several cell

selections

of various

shapes and

sizes.

100

Part II: Editing Without Tears

08_037377 ch03.qxp 11/16/06 9:25 AM Page 100

 To extend the cell selection to columns to the right, drag your mouse to

the right, highlighting neighboring cells as you go.

 To extend the selection to rows to the bottom, drag your mouse down.

 To extend the selection down and to the right at the same time, drag

your mouse diagonally toward the cell in the lower-right corner of the

block you’re highlighting.

Shifty cell selections

To speed up the old cell-selection procedure, you can use the old Shift+click

method, which goes as follows:

1. Click the first cell in the selection.

This selects that cell.

2. Position the mouse pointer in the last cell in the selection.

This is kitty-corner from the first cell in your selected rectangular block.

3. Press the Shift key and hold it down while you click the mouse button

again.

When you click the mouse button the second time, Excel selects all the

cells in the columns and rows between the first cell and last cell.

The Shift key works with the mouse like an

extend key to extend a selection

from the first object you select through to, and including, the second object

you select. See the section “Extend that cell selection,” later in this chapter.

Using the Shift key enables you to select the first and last cells, as well as all

the intervening cells in a worksheet or all the document names in a dialog

list box.

If, when making a cell selection with the mouse, you notice that you include

the wrong cells before you release the mouse button, you can deselect the

cells and resize the selection by moving the pointer in the opposite direction.

If you already released the mouse button, click the first cell in the highlighted

range to select just that cell (and deselect all the others) and then start the

whole selection process again.

Nonadjacent cell selections

To select a nonadjacent cell selection made up of more than one noncontigu-

ous (that is, not touching) block of cells, drag through the first cell range and

then hold down the Ctrl key while you click the first cell of the second range

and drag the pointer through the cells in this range. As long as you hold down

Ctrl while you select the subsequent ranges, Excel doesn’t deselect any of the

previously selected cell ranges.

101

Chapter 3: Making It All Look Pretty

08_037377 ch03.qxp 11/16/06 9:25 AM Page 101

The Ctrl key works with the mouse like an add key to include non-neighboring

objects in Excel. See the section “Nonadjacent cell selections with the key-

board,” later in this chapter. By using the Ctrl key, you can add to the selection

of cells in a worksheet or to the document names in a dialog list box without

having to deselect those already selected.

Going for the “big” cell selections

You can select the cells in entire columns or rows or even all the cells in the

worksheet by applying the following clicking-and-dragging techniques to the

worksheet frame:

 To select every single cell in a particular column, click its column letter

on the frame at the top of the worksheet document window.

 To select every cell in a particular row, click its row number on the

frame at the left edge of the document window.

 To select a range of entire columns or rows, drag through the column

letters or row numbers on the frame surrounding the workbook.

 To select more than entire columns or rows that are not right next to

each other (that old noncontiguous stuff, again), press and hold down

the Ctrl key while you click the column letters or row numbers of the

columns and rows that you want to add to the selection.

 To select each and every cell in the worksheet, press Ctrl+A or click the

Select All button, which is the button with the triangle pointing down-

ward on the diagonal, in the upper-left corner of the workbook frame,

formed by the intersection of the row with the column letters and the

column with the row numbers.

Selecting the cells in a table of data, courtesy of AutoSelect

Excel provides a really quick way (called AutoSelect) to select all the cells in a

table of data entered as a solid block. To use AutoSelect, simply follow these

steps:

1. Click the first cell of the table to select it.

This is the cell in the table’s upper-left corner.

2. Hold down the Shift key while you double-click either the right or

bottom edge of the selected cell with the arrowhead mouse pointer.

(See Figure 3-2.)

Double-clicking the bottom edge of the cell causes the cell selection

to expand to the cell in the last row of the first column (as shown in

Figure 3-3). If you double-click the right edge of the cell, the cell selection

expands to the cell in the last column of the first row.

102

Part II: Editing Without Tears

08_037377 ch03.qxp 11/16/06 9:25 AM Page 102

3a. Double-click somewhere on the right edge of the cell selection (refer

to Figure 3-3) if the cell selection now consists of the first column of

the table.

This selects all the remaining rows of the table of data (as shown in

Figure 3-4).

3b. Double-click somewhere on the bottom edge of the current cell selec-

tion if the cell selection now consists of the first row of the table.

This selects all the remaining rows in the table.

Figure 3-3:

Hold down

Shift while

you double-

click the

bottom edge

of the first

cell to

extend the

selection

down the

column.

Figure 3-2:

Position the

mouse

pointer on

the first

cell’s bottom

edge to

select all

cells of the

first table

column.

103

Chapter 3: Making It All Look Pretty

08_037377 ch03.qxp 11/16/06 9:25 AM Page 103

Although the preceding steps may lead you to believe that you have to select

the first cell of the table when you use AutoSelect, you can actually select any

of the cells in the four corners of the table. Then when expanding the cell

selection in the table with the Shift key depressed, you can choose whatever

direction you like (left, by clicking the left edge; right, by clicking the right

edge; up, by clicking the top edge; or down, by clicking the bottom edge)

to select either the first or last row of the table or the first or last column.

After expanding the cell selection to include either the first or last row or

first or last column, you need to click whichever edge of that current cell

selection that will expand it so that it includes all the remaining table rows

or columns.

Keyboard cell selections

If you’re not really keen on using the mouse, you can use the keyboard to

select the cells you want. Sticking with the Shift+click method of selecting

cells, the easiest way to select cells with the keyboard is to combine the

Shift key with other keystrokes that move the cell cursor. (I list these key-

strokes in Chapter 1.)

Start by positioning the cell cursor in the first cell of the selection and

then holding the Shift key while you press the appropriate cell-pointer

movement keys. When you hold the Shift key as you press direction keys —

such as the arrow keys (↑, →, ↓, ←), PgUp, or PgDn — Excel anchors the

selection on the current cell, moves the cell cursor, and also highlights

cells as it goes.

Figure 3-4:

Hold down

Shift as you

double-click

the right

edge of the

current

selection to

extend it

across the

rows of the

table.

104

Part II: Editing Without Tears

08_037377 ch03.qxp 11/16/06 9:25 AM Page 104

When making a cell selection this way, you can continue to alter the size and

shape of the cell range with the cell-pointer movement keys as long as you

don’t release the Shift key. After you release the Shift key, pressing any of the

cell-pointer movement keys immediately collapses the selection, reducing it

to just the cell with the cell cursor.

Extend that cell selection

If holding the Shift key as you move the cell cursor is too tiring, you can place

Excel in Extend mode by pressing (and promptly releasing) F8 before you

press any cell-pointer movement key. Excel displays the Extend Selection

indicator on the left side of the Status bar — when you see this, the program

will select all the cells that you move the cell cursor through (just as though

you were holding down the Shift key).

After you highlight all the cells you want in the cell range, press F8 again (or

Esc) to turn off Extend mode. The Extend Selection indicator disappears

from the status bar, and then you can once again move the cell cursor with

the keyboard without highlighting everything in your path. In fact, when you

first move the pointer, all previously selected cells are deselected.

AutoSelect keyboard style

For the keyboard equivalent of AutoSelect with the mouse (read this chap-

ter’s section “Selecting the cells in a table of data, courtesy of AutoSelect”),

you combine the use of the F8 (Extend key) or the Shift key with the

Ctrl+arrow keys or End+arrow keys to zip the cell cursor from one end of a

block to the other, merrily selecting all the cells in its path as it goes.

To select an entire table of data with a keyboard version of AutoSelect, follow

these steps:

1. Position the cell cursor in the first cell.

That’s the cell in the upper-left corner of the table.

2. Press F8 (or hold the Shift key) and then press Ctrl+→ to extend the

cell selection to the cells in the columns on the right.

3. Then press Ctrl+

↓ to extend the selection to the cells in the rows below.

Keep in mind that the directions in the preceding steps are somewhat

arbitrary — you can just as well press Ctrl+↓ before you press Ctrl+→.

Just be sure (if you’re using the Shift key instead of F8) that you don’t let

up on the Shift key until after you finish performing these two directional

maneuvers. Also, if you press F8 to get the program into Extend mode,

don’t forget to press this key again to get out of Extend mode after the

table cells are all selected, or you’ll end up selecting cells that you don’t

want included when you next move the cell cursor.

105

Chapter 3: Making It All Look Pretty

08_037377 ch03.qxp 11/16/06 9:25 AM Page 105

Nonadjacent cell selections with the keyboard

Selecting more than one cell range is a little more complicated with the key-

board than it is with the mouse. When using the keyboard, you alternate

between

anchoring the cell cursor and moving the cell cursor to select the

cell range and

unanchoring the cell cursor and repositioning it at the begin-

ning of the next range. To unanchor the cell cursor so that you can move it

into position for selecting another range, press Shift+F8. This puts you in Add

to Selection mode, in which you can move to the first cell of the next range

without selecting any more cells. Excel lets you know that the cell cursor is

unanchored by displaying the

Add to Selection indicator on the left side

of the Status bar.

To select more than one cell range by using the keyboard, follow these gen-

eral steps:

1. Move the cell cursor to the first cell of the first cell range that you

want to select.

2. Press F8 to get into Extend Selection mode.

Move the cell cursor to select all the cells in the first cell range.

Alternatively, hold the Shift key as you move the cell cursor.

3. Press Shift+F8 to switch from Extend Selection mode to Add to

Selection mode.

The Add to Selection indicator appears in the Status bar.

4. Move the cell cursor to the first cell of the next nonadjacent range that

you want to select.

5. Press F8 again to get back into Extend Selection mode and then move

the cell cursor to select all the cells in this new range.

6. If you still have other nonadjacent ranges to select, repeat Steps 3, 4,

and 5 until you select and add all the cell ranges that you want to use.

Cell selections à la Go To

If you want to select a really big cell range that would take a long time to

select by pressing various cell-pointer movement keys, use the Go To feature

to extend the range to a far distant cell. All you gotta do is follow this pair of

steps:

1. Position the cell cursor in the first cell of the range; then press F8 to

anchor the cell cursor and get Excel into Extend Selection mode.

2. Press F5 or Ctrl+G to open the Go To dialog box, type the address of

the last cell in the range (the cell kitty-corner from the first cell), and

then click OK or press Enter.

106

Part II: Editing Without Tears

08_037377 ch03.qxp 11/16/06 9:25 AM Page 106

Because Excel is in Extend Selection mode at the time you use Go To to jump

to another cell, the program not only moves the cell cursor to the designated

cell address but selects all the intervening cells as well. After selecting the

range of cells with the Go To feature, don’t forget to press F8 (the Extend

Selection key) again to prevent the program from messing up your selection

by adding on more cells the next time you move the cell cursor.

Having Fun with the Format

as Table Gallery

Here’s a formatting technique that doesn’t require you to do any prior cell

selecting. (Kinda figures, doesn’t it?) In fact, the Format as Table feature is so

automatic that to use it, the cell cursor just has to be somewhere within the

table of data prior to your clicking the Format as Table command button in

the Styles group on the Home tab. Clicking the Format as Table command

button opens its rather extensive Table gallery with the formatting thumb-

nails divided into three sections — Light, Medium, and Dark — each of which

describes the intensity of the colors used by it various formats (see Color

Plate 11).

As soon as you click one of the table formatting thumbnails in this Table

gallery, Excel makes its best guess as to the cell range of the data table to

apply it to (indicated by the marquee around its perimeter) and the Format

As Table dialog box similar to the one shown in Figure 3-5 appears.

Figure 3-5:

Selecting a

format from

the Table

gallery and

indicating

its range in

the Format

As Table

dialog box.

107

Chapter 3: Making It All Look Pretty

08_037377 ch03.qxp 11/16/06 9:25 AM Page 107

This dialog box contains a Where Is the Data for Your Table text box that

shows the address of the cell range currently selected by the marquee and a

My Table Has Headers check box (selected by default).

If Excel does not correctly guess the range of the data table you want to

format, drag through the cell range to adjust the marquee and the range

address in the Where Is the Data for Your Table text box. If your data table

doesn’t use column headers or, if the table has them, but you still don’t want

Excel to add Filter drop-down buttons to each column heading, click the My

Table Has Headers check box before you click the OK button.

Keep in mind that the table formats in the Table gallery are not available if

you select multiple nonadjacent cells before you click the Format as Table

command button on the Home tab.

After you click the OK button in the Format As Table dialog box, Excel applies

the format of the thumbnail you clicked in the gallery to the data table and

the Design tab under the Table Tools contextual tab shown in Figure 3-6

appears at the end of the Ribbon.

The Design tab enables you to use Live Preview to see how your table would

appear. Simply position the mouse pointer over any of the format thumbnails

in the Table Style group to see the data in your table appear in that table

format. Click the button with the triangle pointing downward to scroll up new

rows of table formats in the Table Styles group and the button with the trian-

gle pointing upward to scroll down rows without opening the Table gallery

and possibly obscuring the actual data table in the Worksheet area. Click the

More button (the one with the horizontal bar above the downward pointing

triangle) to redisplay the Table gallery and then mouse over the thumbnails

in the Light, Medium, and Dark sections to have Live Preview apply them to

the table.

Figure 3-6:

After you

select a

format from

the Table

gallery, the

Design

contextual

tab appears.

108

Part II: Editing Without Tears

08_037377 ch03.qxp 11/16/06 9:25 AM Page 108

In addition to enabling you to select a new format from the Table gallery in

the Table Styles group, the Design tab contains a Table Styles Options group

containing a bunch of check boxes that enable you to customize the look of

the selected table format even further:

 Header Row to add Filter buttons to each of the column headings in the

first row of the table

 Totals Row to have Excel add a Totals row to the bottom of the table

that displays the sums of each column that contains values — to apply

another Statistical function to the values in a particular column, click

the cell in that column’s Totals row to display a drop-down list button

and then the function to use the drop-down menu of functions, Average,

Count, Count Numbers, Max, Min, Sum, StdDev (Standard Deviation), or

Var (Variation) that appears when you click its drop-down button

 First Column to have Excel display the row headings in the first column

of the table in bold

 Last Column to have Excel display the row headings in the last column

of the table in bold

 Banded Rows to have Excel apply shading to every other row in the table

 Banded Columns to have Excel apply shading to every other column in

the table

Keep in mind that whenever you assign a format in the Table gallery to one of

the data tables in your workbook, Excel automatically assigns that table a

generic range name (Table1, Table2, and so on). You can use the Table Name

text box in the Properties group on the Design tab to rename the data table

by giving it a more descriptive range name (see Chapter 6 for all you need to

know about naming cell ranges).

When you finish selecting and/or customizing the formatting of your data

table, click a cell outside of the table to remove the Design contextual tab

from the Ribbon. If you later decide that you want to further experiment with

the table’s formatting, click any of the table’s cells to redisplay the Design

contextual tab at the end of the Ribbon.

Cell Formatting from the Home Tab

Some spreadsheet tables require a lighter touch than the Format as Table

command button offers. For example, you may have a data table where the

only emphasis you want to add is to make the column headings bold at the

top of the table and to underline the row of totals at the bottom (done by

drawing a borderline along the bottom of the cells).

109

Chapter 3: Making It All Look Pretty

08_037377 ch03.qxp 11/16/06 9:25 AM Page 109

The formatting buttons that appear in the Font, Alignment, and Number

groups on the Home tab enable you to accomplish just this kind of targeted

cell formatting. Figure 3-7 shows you the Home tab with all the buttons in

these three groups identified. See Table 3-1 for a complete rundown on how

to use each of these formatting buttons.

Table 3-1 The Formatting Command Buttons in the Font,

Alignment, and Number Groups on the Home Tab

Group Button Name Function

Font

Font Displays a Font drop-down menu from which you

can select a new font for your cell selection

Font Size Displays a Font Size drop-down menu from which

you can select a new font size for your cell selec-

tion — click the Font Size text box and enter the

desired point size if it doesn’t appear on the drop-

down menu

Increase Increases the size of the font in the cell selection

Font Size by one point

Decrease Decreases the size of the font in the cell selection

Font Size by one point

Bold Applies boldface to the entries in the cell selection

Italic Italicizes the entries in the cell selection

Figure 3-7:

The Font,

Alignment,

and Number

groups on

the Home

tab contain

almost all the

formatting

tools you will

ever need.

110

Part II: Editing Without Tears

08_037377 ch03.qxp 11/16/06 9:25 AM Page 110

Group Button Name Function

Underline Underlines the entries in the cell selection

Borders Displays a Borders drop-down menu from which

you can select a border style for the cell selection

Fill Color Displays a Color drop-down palette from which

you can select a new background color for the

cell selection

Font Color Displays a Color drop-down palette from which you

can select a new font color for the cell selection

Alignment

Align Left Aligns all the entries in the cell selection with the

left edge of their cells

Center Centers all the entries in the cell selection within

their cells

Align Right Aligns all the entries in the cell selection with the

right edge of their cells

Decrease Decreases the margin between entries in the cell

Indent selection and their left cell borders by one tab stop

Increase Increases the margin between the entries in the

Indent cell selection and their left cell borders by one

tab stop

Top Align Aligns the entries in the cell selection with the top

border of their cells

Middle Align Vertically centers the entries in the cell selection

between the top and bottom borders of their cells

Bottom Align Aligns the entries in the cell selection with the

bottom border of their cells

Orientation Displays a drop-down menu with options for

changing the angle and direction of the entries in

the cell selection

Wrap Text Wraps the entries in the cell selection that spill

over their right borders onto multiple lines within

the current column width

(continued)

111

Chapter 3: Making It All Look Pretty

08_037377 ch03.qxp 11/16/06 9:25 AM Page 111

Table 3-1

(continued)

Group Button Name Function

Merge and Merges the cell selection into a single cell and the

Center centers the entry in the first cell between its new

left and right border — click the Merge and

Center drop-down button to display a menu of

options that enable to merge the cell selection

into a single cell without centering the entries as

well as to split up a merged cell back into its origi-

nal individual cells

Number

Number Format Displays the number format applied to active cell

in the cell selection — click its drop-down button

to display a menu showing the active cell in cell

selection formatted with all of Excel’s major

Number formats

Accounting Formats the cell selection using Accounting

Number Format Number format that adds a dollar sign, uses

commas to separate thousands, displays two dec-

imal places, and encloses negative values in a

closed pair of parentheses — click the

Accounting Number Format drop-down button to

display a menu of other major currency number

formats from which you can choose

Percent Style Formats the cell selection using the Percent Style

Number format that multiplies the values by 100

and adds a percent sign with no decimal places

Comma Style Formats the cell selection with the Comma Style

Number format that uses commas to separate

thousands, displays two decimal places, and

encloses negative values in a closed pair of

parentheses

Increase Adds a decimal place to the values in the cell

Decimal selection

Decrease Removes a decimal place from the values in the

Decimal cell selection

Don’t forget about the shortcut keys: Ctrl+B for toggling on and off bold in the

cell selection, Ctrl+I for toggling on and off italics, and Ctrl+U for toggling on

and off underlining for quickly adding or removing these attributes from the

entries in the cell selection.

112

Part II: Editing Without Tears

08_037377 ch03.qxp 11/16/06 9:25 AM Page 112

Formatting Cells Close to the Source

with the Mini Toolbar

Excel 2007 makes it easy to apply common formatting changes to a cell selec-

tion right within the Worksheet area thanks to its new mini toolbar feature

(nicknamed the mini-bar — makes me thirsty just thinking about it!).

To display the mini-bar, select the cells that need formatting and then

right-click somewhere in the cell selection. The mini toolbar then appears

immediately above the cell selection’s shortcut menu (see Figure 3-8).

As you can see in this figure, the mini-bar contains most of the buttons from

the Font group of the Home tab (with the exception of the Underline button).

It also contains the Center and Merge and Center buttons from the Alignment

group (see “Altering the Alignment” later in this chapter) and the Accounting

Number Format, Percent Style, Comma Style, Increase Decimal and Decrease

Decimal buttons from the Number group (see “Getting comfortable with the

number formats” later in this chapter). Simply click these buttons to apply

their formatting to the current cell selection.

In addition, the mini-bar contains the Format Painter button from the

Clipboard group of the Home tab that you can use to copy the formatting in

the active cell to a cell selection you make (see “Fooling Around with the

Format Painter” later in this chapter for details).

Figure 3-8:

Use the

buttons on

the mini

toolbar to

apply

common

formatting

changes to

the cell

selection

within the

Worksheet

area.

113

Chapter 3: Making It All Look Pretty

08_037377 ch03.qxp 11/16/06 9:25 AM Page 113

Using the Format Cells Dialog Box

Although the command buttons in the Font, Alignment, and Number groups

on the Home tab give you immediate access the most commonly used format-

ting commands, they do not represent all of Excel’s formatting commands by

any stretch of the imagination.

To have access to all the formatting commands, you need to open the Format

Cells dialog box by doing any of the following:

 Click the More option at the very bottom of the drop-down menu

attached to the Number Format button

 Click the Dialog Box launcher button in the lower-right of the Number

group

 Press Ctrl+1

The Format Cells dialog box that this command calls up contains six tabs:

Number, Alignment, Font, Border, Fill, and Protection. In this chapter, I show

you how to use them all except the Protection tab; for information on that

tab, see Chapter 6.

The keystroke shortcut that opens the Format Cells dialog box — Ctrl+1 — is

one worth knowing. Just keep in mind that the keyboard shortcut is pressing

the Ctrl key plus the number 1 key, and not the function key F1.

Getting comfortable with

the number formats

As I explain in Chapter 2, how you enter values into a worksheet determines

the type of number format that they get. Here are some examples:

 If you enter a financial value complete with the dollar sign and two deci-

mal places, Excel assigns a Currency number format to the cell along

with the entry.

 If you enter a value representing a percentage as a whole number fol-

lowed by the percent sign without any decimal places, Excel assigns to

the cell the Percentage number format that follows this pattern along

with the entry.

 If you enter a date (dates are values, too) that follows one of the built-in

Excel number formats, such as 11/06/02 or 06-Nov-02, the program

assigns a Date number format that follows the pattern of the date along

with a special value representing the date.

114

Part II: Editing Without Tears

08_037377 ch03.qxp 11/16/06 9:25 AM Page 114

Although you can format values in this manner as you go along (which is nec-

essary in the case of dates), you don’t have to do it this way. You can always

assign a number format to a group of values before or after you enter them.

And, in fact, formatting numbers after you enter them is often the most effi-

cient way to go because it’s just a two-step procedure:

1. Select all the cells containing the values that need dressing up.

2. Select the number format that you want to use either from the format-

ting command buttons on the Home tab or the options available on

the Number tab in the Format Cells dialog box.

Even if you’re a really, really good typist and prefer to enter each value

exactly as you want it to appear in the worksheet, you still have to resort to

using number formats to make the values that are calculated by formulas

match the others you enter. This is because Excel applies a General number

format (which the Format Cells dialog box defines: “General format cells have

no specific number format.”) to all the values it calculates as well as any you

enter that don’t exactly follow one of the other Excel number formats. The

biggest problem with the General format is that it has the nasty habit of drop-

ping all leading and trailing zeros from the entries. This makes it very hard to

line up numbers in a column on their decimal points.

You can view this sad state of affairs in Figure 3-9, which is a sample work-

sheet with the first-quarter 2008 sales figures for Mother Goose Enterprises

before any of the values have been formatted. Notice how the numbers in the

monthly sales figures columns zig and zag because they don’t align according

to decimal place. This is the fault of Excel’s General number format; the only

cure is to format the values with another more uniform number format.

Figure 3-9:

Numbers

with

decimals

don’t align

when you

choose

General

formatting.

115

Chapter 3: Making It All Look Pretty

08_037377 ch03.qxp 11/16/06 9:25 AM Page 115

Accenting your cells with the Accounting Number format

Given the financial nature of most worksheets, you probably use the

Accounting Number format more than any other. This is a really easy format

to apply because you can assign it to the cell selection simply by clicking the

Accounting Number Format button on the Home tab.

Remember that the Accounting Number format adds a dollar sign, commas

between thousands of dollars, and two decimal places to any values in a

selected range. If any of the values in the cell selection are negative, this

number format displays them in parentheses (the way accountants like them).

If you want a minus sign in front of your negative financial values rather

enclose them in parentheses, select the Currency format on the Number

Format drop-down menu or on the Number tab of the Format Cells dialog box.

You can see in Figure 3-10 only the cells containing totals are selected (cell

ranges E3:E10 and B10:D10). This cell selection was then formatted with the

Accounting Number Format by simply clicking its command button (the one

with the $ icon, naturally) in the Number group on the Ribbon’s Home tab.

Although you could put all the figures in the table into the Accounting

Number Format to line up the decimal points, this would result in a super-

abundance of dollar signs in a fairly small table. In this example, I only for-

matted the monthly and quarterly totals à la Accounting Number format.

Figure 3-10:

The totals in

the Mother

Goose sales

table after

clicking the

Accounting

Number

Format

button on

the Home

tab.

116

Part II: Editing Without Tears

08_037377 ch03.qxp 11/16/06 9:25 AM Page 116

“Look, Ma, no more format overflow!”

When I apply the Accounting Number format to the selection in the cell

ranges of E3:E10 and B10:D10 in the sales table shown in Figure 3-10, Excel

not only adds dollar signs, commas between the thousands, a decimal point,

and two decimal places to the highlighted values, but also, at the same time,

automatically widens columns B, C, D, and E just enough to display all this

new formatting. In versions of Excel earlier than Excel 2003, you’d have had

to widen these columns yourself, and instead of the perfectly aligned num-

bers, you would have been confronted with columns of #######s in cell

ranges E3:E10 and B10:D10. Such pound signs (where nicely formatted dollar

totals should be) serve as overflow indicators, declaring that whatever for-

matting you added to the value in that cell has added so much to the value’s

display that Excel can no longer display it within the current column width.

Fortunately, Excel eliminates the format overflow indicators when you’re for-

matting the values in your cells by automatically widening their columns. The

only time you’ll ever run across these dreaded #######s in your cells is when

you take it upon yourself to manually narrow a worksheet column (see the sec-

tion “Calibrating Columns,” later in this chapter) to such an extent that Excel

can no longer display all the characters in its cells with formatted values.

Currying your cells with the Comma Style

The Comma Style format offers a good alternative to the Currency Style format.

Like Currency, the Comma format inserts commas in larger numbers to sepa-

rate thousands, hundred thousands, millions, and . . . well, you get the idea.

This format also displays two decimal places and puts negative values in

parentheses. What it doesn’t display is dollar signs. This makes it perfect for

formatting tables where it’s obvious that you’re dealing with dollars and

cents or for larger values that have nothing to do with money.

The Comma Style format also works well for the bulk of the values in the

sample first-quarter sales worksheet. Check out Figure 3-11 to see this table

after I format the cells containing the monthly sales for each Mother Goose

company with the Comma Style format. To do this, select the cell range B3:D9

and click the Comma Style button — the one with the comma icon (

,) — in

the Number group on the Home tab.

Note how, in Figure 3-11, that the Comma Style format takes care of the ear-

lier decimal alignment problem in the quarterly sales figures. Moreover,

Comma Style-formatted monthly sales figures align perfectly with the

Currency style-formatted monthly totals in row 10. If you look really closely

(you may need a magnifying glass for this one), you see that these formatted

117

Chapter 3: Making It All Look Pretty

08_037377 ch03.qxp 11/16/06 9:25 AM Page 117

values no longer abut the right edges of their cells; they’ve moved slightly

to the left. The gap on the right between the last digit and the cell border

accommodates the right parenthesis in negative values, ensuring that they,

too, align precisely on the decimal point.

Playing around with the Percent Style

Many worksheets use percentages in the form of interest rates, growth rates,

inflation rates, and so on. To insert a percentage in a cell, type the percent

sign (%) after the number. To indicate an interest rate of 12 percent, for exam-

ple, you enter

12% in the cell. When you do this, Excel assigns a Percent Style

number format and, at the same time, divides the value by 100 (that’s what

makes it a percentage) and places the result in the cell (0.12 in this example).

Not all percentages in a worksheet are entered by hand in this manner. Some

may be calculated by a formula and returned to their cells as raw decimal

values. In such cases, you should add a Percent format to convert the calcu-

lated decimal values to percentages (done by multiplying the decimal value

by 100 and adding a percent sign).

The sample first-quarter-sales worksheet just happens to have some percent-

ages calculated by formulas in row 12 that need formatting (these formulas

indicate what percentage each monthly total is of the first-quarter total in cell

E10). In Figure 3-12, these values reflect Percent Style formatting. To accom-

plish this feat, you simply select the cells and click the Percent Style button

in the Number group on the Home tab. (Need I point out that it’s the button

with the % symbol?)

Figure 3-11:

Monthly

sales

figures after

formatting

their cells

with the

Comma

Style

number

format.

118

Part II: Editing Without Tears

08_037377 ch03.qxp 11/16/06 9:25 AM Page 118

Deciding how many decimal places

You can increase or decrease the number of decimal places used in a number

entered with the Accounting Number Format, Comma Style, or Percent Style

button in the Number group of the Home tab simply by clicking the Increase

Decimal tool or the Decrease Decimal button in this group.

Each time you click the Increase Decimal button (the one with the arrow

pointing left), Excel adds another decimal place to the number format you

apply. Percentages appear in the cell range B12:D12 (see Figure 3-13) after I

increase the number of decimal places in the Percent format from none to

two. (Note that the Percent Style doesn’t use any decimal places.) I accom-

plish this by clicking the Increase Decimal button twice.

The values behind the formatting

Make no mistake about it — all that these fancy number formats do is spiff up

the presentation of the values in the worksheet. Like a good illusionist, a par-

ticular number format sometimes appears to magically transform some

entries; but in reality, the entries are the same old numbers you started with.

For example, suppose that a formula returns the following value:

25.6456

Figure 3-12:

Monthly-to-

quarterly

sales

percentages

with Percent

Style

number

formatting.

119

Chapter 3: Making It All Look Pretty

08_037377 ch03.qxp 11/16/06 9:25 AM Page 119

Now suppose that you format the cell containing this value with the

Accounting Number Format button on the Home tab. The value now appears

as follows:

$25.65

This change may lead you to believe that Excel rounded the value up to two

decimal places. In fact, the program has rounded up only the display of the

calculated value — the cell still contains the same old value of 25.6456. If you

use this cell in another worksheet formula, keep in mind that Excel uses the

behind-the-scenes value in its calculation, not the spiffed-up one shown in

the cell.

But what if you want the values to match their formatted appearance in the

worksheet? Well, Excel can do that in a single step. Be forewarned, however,

that this is a one-way trip. You can convert all underlying values to the way

they are displayed by selecting a single check box, but you can’t return them

to their previous state by deselecting this check box.

Well, because you insist on knowing this little trick anyway, here goes (just

don’t write and try to tell me that you weren’t warned):

1. Make sure that you format all the values in your worksheet with the

right number of decimal places.

You must do this step before you convert the precision of all values in

the worksheet to their displayed form.

Figure 3-13:

Monthly-to-

quarterly

sales

percentages

after adding

two decimal

places to

the Percent

Style

number

format.

120

Part II: Editing Without Tears

08_037377 ch03.qxp 11/16/06 9:25 AM Page 120

2. Click Microsoft Office Button | Excel Options | Advanced or press

Alt+FIA to open the Advanced tab of Excel Options dialog box.

3. In the When Calculating This Workbook section, click the Set

Precision as Displayed check box (to fill it with a check mark) and

click OK.

Excel displays the Data Will Permanently Lose Accuracy alert dialog box.

4. Go ahead (live dangerously) and click the OK button or press Enter to

convert all values to match their display.

Save the workbook with the calculated values. Then, after converting all

the values in a worksheet by selecting the Set Precision as Displayed check

box, open the Save As dialog box (click Office Button | Save As or press

Alt+FA) and edit the filename in the File Name text box (maybe by appending

as Displayed to the current filename) before you click the Save button or

press Enter. That way, you’ll have two copies: the original workbook file

with the values as entered and calculated by Excel and the new as Displayed

version.

Make it a date!

In Chapter 2, I mention that you can easily create formulas that calculate the

differences between the dates and times that you enter in your worksheets.

The only problem is that when Excel subtracts one date from another date or

one time from another time, the program automatically formats the calcu-

lated result in a corresponding date or time number format as well. So, for

example, if you enter 8-15-08 in cell B4 and 4/15/08 in cell C4 and in cell E4

enter the following formula for finding the number of elapsed days between

the two dates:

=B4-C4

Excel returns the result of 122 disguised as 5/1/1900 in cell E4. To reformat the

result, you need to assign the General number format to the cell — you can do

this quickly by selecting the cell and then pressing Ctrl+Shift+~ (tilde) or not so

quickly by clicking General on the Number Format drop-down list on the Home

tab. When you assign the General format to this cell, the value 122 replaces

5/1/1900, indicating that 122 days have elapsed between the two dates.

Likewise, when dealing with formulas that calculate the difference between

two times in a worksheet, you also have to reformat the result that appears in

a corresponding time format into the General format. For example, suppose

121

Chapter 3: Making It All Look Pretty

08_037377 ch03.qxp 11/16/06 9:25 AM Page 121

that you enter 8:00 AM in cell C8 and 4:00 PM in cell D8 and then create in cell

E8 the following formula for calculating the difference in hours between the

two times:

=D8-C8

You still have to convert the result in cell E8 — that automatically appears as

8:00 AM — to the General format. When you do this, the fraction 0.333333 —

representing its fraction of the total 24-hour period — replaces 8:00 AM in

cell E8. You can then convert this fraction of a total day into the correspond-

ing number of hours by multiplying this cell by 24.

Ogling some of the other number formats

Excel supports many more number formats than just the Accounting Number,

Comma Style, and Percent Style formats. To use them, select the cell range

(or ranges) you want to format and select Format Cells on the cell shortcut

menu (right-click somewhere in the cell selection to activate this menu) or

just press Ctrl+1 to open the Format Cells dialog box.

After the Format Cells dialog box opens, click the Number tab and select the

desired format from the Category list box. Some Number format categories —

such as Date, Time, Fraction, and Special — give you further formatting

choices in a Type list box. Other number formats, such as Number and

Currency, have their own particular boxes that give you options for refining

their formats. When you click the different formats in these list boxes, Excel

shows what effect this would have on the first of the values in the current cell

selection in the Sample area above. When the sample has the format that you

want to apply to the current cell selection, you just click OK or press Enter to

apply the new number format.

Excel contains a nifty category of number formats called Special. The Special

category contains the following four number formats that may interest you:

 Zip Code: Retains any leading zeros in the value (important for zip

codes and of absolutely no importance in arithmetic computations).

Example: 00123.

 Zip Code + 4: Automatically separates the last four digits from the first

five digits and retains any leading zeros. Example: 00123-5555.

 Phone Number: Automatically encloses the first three digits of the

number in parentheses and separates the last four digits from the previ-

ous three with a dash. Example: (999) 555-1111.

122

Part II: Editing Without Tears

08_037377 ch03.qxp 11/16/06 9:25 AM Page 122

 Social Security Number: Automatically puts dashes in the value to

separate its digits into groups of three, two, and four. Example:

666-00-9999.

These Special number formats really come in handy when creating databases

in Excel, which often deal with stuff like zip codes, telephone numbers, and

sometimes even Social Security Numbers (see Chapter 9 for more on creating

databases).

Calibrating Columns

For those times when Excel 2007 doesn’t automatically adjust the width of your

columns to your complete satisfaction, the program makes your changing the

column widths a breeze. The easiest way to adjust a column is to do a best-fit,

using the AutoFit feature. With this method, Excel automatically determines

how much to widen or narrow the column to fit the longest entry currently in

the column.

Here’s how to use AutoFit to get the best-fit for a column:

1. Position the mouse pointer on the right border of the worksheet frame

with the column letter at the top of the worksheet.

The mouse pointer changes to a double-headed arrow pointing left and

right.

2. Double-click the mouse button.

Excel widens or narrows the column width to suit the longest entry.

You can apply a best-fit to more than one column at a time. Simply select all

the columns that need adjusting (if the columns neighbor one another, drag

through their column letters on the frame; if they don’t, hold down the Ctrl key

while you click the individual column letters). After you select the columns,

double-click any of the right borders on the frame.

Best-fit à la AutoFit doesn’t always produce the expected results. A long title

that spills into several columns to the right produces an awfully wide column

when you use best-fit.

When AutoFit’s best-fit won’t do, drag the right border of the column (on the

frame) until it’s the size you need instead of double-clicking it. This manual

technique for calibrating the column width also works when more than one

column is selected. Just be aware that all selected columns assume whatever

size you make the one that you’re actually dragging.

123

Chapter 3: Making It All Look Pretty

08_037377 ch03.qxp 11/16/06 9:25 AM Page 123

You can also set the widths of columns from the Format button’s drop-down

list in the Cells group on the Home tab. When you click this drop-down

button, the Cell Size section of this drop-down menu contains the following

width options:

 Column Width to open the Column Width dialog box you enter the

number of characters that you want for the column width before you

click OK

 AutoFit Column Width to have Excel apply best-fit to the columns based

on the widest entries in the current cell selection

 Default Width to open the Standard Width dialog box containing the

standard column width of 8.43 characters that you can apply to the

columns in the cell selection

Rambling rows

The story with adjusting the heights of rows is pretty much the same as that

with adjusting columns except that you do a lot less row adjusting than you

do column adjusting. That’s because Excel automatically changes the height

of the rows to accommodate changes to their entries, such as selecting a

larger font size or wrapping text in a cell. I discuss both of these techniques

in the upcoming section “Altering the Alignment.” Most row-height adjustments

come about when you want to increase the amount of space between a table

title and the table or between a row of column headings and the table of infor-

mation without actually adding a blank row. (See the section “From top to

bottom,” later in this chapter, for details.)

To increase the height of a row, drag the bottom border of the row frame

down until the row is high enough and then release the mouse button. To

shorten a row, reverse this process and drag the bottom row-frame border

up. To use AutoFit to create a best-fit for the entries in a row, you double-click

the bottom row frame border.

As with columns, you can also adjust the height of selected rows using row

options in the Cells section on the Format button’s drop-down menu on the

Home tab:

 Row Height to open the Row Height dialog box where you enter the

number of characters in the Row Height text box and click OK

 AutoFit Row Height to return the height of selected rows to the best fit

124

Part II: Editing Without Tears

08_037377 ch03.qxp 11/16/06 9:25 AM Page 124

Now you see it, now you don’t

A funny thing about narrowing columns and rows: You can get too carried away

and make a column so narrow or a row so short that it actually disappears from

the worksheet! This can come in handy for those times when you don’t want

part of the worksheet visible. For example, suppose you have a worksheet that

contains a column listing employee salaries — you need these figures to calcu-

late the departmental budget figures, but you would prefer to leave sensitive

info off most printed reports. Rather than waste time moving the column of

salary figures outside the area to be printed, you can just hide the column until

after you print the report.

Hiding worksheet columns

Although you can hide worksheet columns and rows by just adjusting them

out of existence, Excel does offer an easier method of hiding them, via the

Hide & Unhide option on the Format button’s drop-down menu (located in the

Cells group of the Home tab). Suppose that you need to hide column B in the

worksheet because it contains some irrelevant or sensitive information that

you don’t want printed. To hide this column, you could follow these steps:

1. Click anywhere in column B to select the column.

2. Click the drop-down button attached to the Format button in the Cells

group on the Home tab.

Excel opens the Format button’s drop-down menu.

3. Click Hide & Unhide | Hide Columns on the drop-down menu.

That’s all there is to it — column B goes poof! All the information in the

column disappears from the worksheet. When you hide column B, notice that

the row of column letters in the frame now reads A, C, D, E, F, and so forth.

You could just as well have hidden column B by clicking its column letter on

the frame with the secondary mouse button and then clicking the Hide com-

mand on the column’s shortcut menu.

So now, suppose that you’ve printed the worksheet and need to make a change

to one of the entries in column B. To unhide the column, follow these steps:

1. Position the mouse pointer on column letter A in the frame and drag

the pointer right to select both columns A and C.

You must drag from A to C to include hidden column B as part of the

column selection — don’t click while holding down the Ctrl key or you

won’t get B.

125

Chapter 3: Making It All Look Pretty

08_037377 ch03.qxp 11/16/06 9:25 AM Page 125

2. Click the drop-down button attached to the Format button in the Cells

group on the Home tab.

3. Click Hide & Unhide | Unhide Columns on the drop-down menu.

Excel brings back the hidden B column, and all three columns (A, B, and C)

are selected. You can then click the mouse pointer on any cell in the work-

sheet to deselect the columns.

You could also unhide column B by selecting columns A and C, clicking either

one of them with the secondary mouse button, and then clicking the Unhide

command on the column shortcut menu.

Hiding worksheet rows

The procedure for hiding and unhiding rows of the worksheet is essentially

the same as for hiding and unhiding columns. The only difference is that

after selecting the rows to hide, you click Hide & Unhide | Hide Rows on the

Format button’s drop-down menu and Hide & Unhide | Unhide Rows to bring

them back.

Don’t forget that you can use the Hide and Unhide options on the rows’

shortcut menu to make selected rows disappear and then reappear in the

worksheet.

Futzing with the Fonts

When you start a new worksheet, Excel 2007 assigns a uniform font and type

size to all the cell entries you make. The default font varies according to the

version of Windows under which you’re running Excel. When you run Excel

on Windows Vista, Excel uses its new Calibri font (the so-called Body Font) in

11-point size, and when running Excel under Windows XP, it uses its standard

Arial font in a 10-point size. Although these two fonts may be fine for normal

entries, you may want to use something with a little more zing for titles and

headings in the worksheet.

If you don’t especially care for the standard font that Excel uses on your ver-

sion of Windows, modify it by from the Personalize tab of the Excel Options

dialog box (click Office Button | Excel Options | Popular or press Alt+FIP).

Look for the Use This Font drop-down list box in the When Creating New

Workbooks section and then click the new standard font you want from its

drop-down list. If you want a different type size, choose the Font Size drop-

down list box and click a new point size on its drop-down menu or enter the

new point size for the standard font directly into the Font Size text box.

Using the buttons in the Font group on the Home tab, you can make most

font changes (including selecting a new font style or new font size) without

126

Part II: Editing Without Tears

08_037377 ch03.qxp 11/16/06 9:25 AM Page 126

having to resort to changing the settings on the Font tab in the Format Cells

dialog box (Ctrl+1):

 To select a new font for a cell selection, click the drop-down button next

to the Font combo box and then select the name of the font you want to

use from the list box — note that Excel displays the name of each font

that appears in this list box in the actual font named (so that the font

name becomes an example of what the font looks like — on-screen

anyway)

 To change the font size, click the drop-down button next to the Font Size

combo box; then select the new font size or click the Font Size text box,

type the new size, and press Enter

You can also add the attributes of

bold, italics, underlining, or strikethrough

to the font you use. The Font group of the Home tab contains the Bold, Italic,

and Underline buttons, which not only add these attributes to a cell selection

but remove them as well. After you click any of these attribute tools, notice

that the tool becomes shaded whenever you position the cell cursor in the cell

or cells that contain that attribute. When you click an outlined format button to

remove an attribute, Excel no longer shades the attribute button when you

select the cell.

Although you’ll probably make most font changes with the toolbars, on rare

occasions you may find it more convenient to make these changes from the

Font tab in the Format Cells dialog box (Ctrl+1).

As you can see in Figure 3-14, this Font tab in the Format Cells dialog box brings

together under one roof fonts, font styles (bold and italics), effects (underlining

and strikethrough), and color changes. When you want to make a lot of font-

related changes to a cell selection, working in the Font tab may be your best

bet. One of the nice things about using this tab is that it contains a Preview box

that shows you how your font changes appear (on-screen at least).

Figure 3-14:

Use the Font

tab on the

Format Cells

dialog box

to make lots

of font

changes at

one time.

127

Chapter 3: Making It All Look Pretty

08_037377 ch03.qxp 11/16/06 9:25 AM Page 127

To change the color of the entries in a cell selection, click the Font Color

button’s drop-down menu in the Font group on the Home tab and then select

the color you want the text to appear in the drop-down palette. Keep in mind

that you can use Live Preview to see what the entries in the cell selection

look like in a particular font color by moving the mouse pointer over the

color swatches in the palette before you select one by clicking it (assuming,

of course, that the palette doesn’t cover the cells).

If you change font colors and then print the worksheet with a black-and-white

printer, Excel renders the colors as shades of gray. The Automatic option at

the top of the Font button’s drop-down menu picks up the color assigned in

Windows as the window text color. This color is black unless you change it on

the Advanced Appearance tab of the Display Properties dialog box in Windows

Vista and XP. (For help on this subject, please see

Microsoft Windows XP For

Dummies

or Microsoft Windows Vista For Dummies both by Andy Rathbone,

from Wiley Publishing, Inc. — and be sure to tell Andy that Greg sent ya!)

Altering the Alignment

The horizontal alignment assigned to cell entries when you first make them is

simply a function of the type of entry it is: All text entries are left-aligned, and

all values are right-aligned with the borders of their cells. You can, however,

alter this standard arrangement anytime it suits you.

The Alignment group of the Home tab contains three normal horizontal align-

ment tools: the Align Left, Center, and Align Right buttons. These buttons

align the current cell selection exactly as you expect them to. On the right

side of the Alignment group, you usually find the special alignment button

called Merge and Center.

Despite its rather strange name, you’ll want to get to know this button. You can

use it to center a worksheet title across the entire width of a table in seconds

(or faster, depending upon your machine). I show you in Figures 3-15 and 3-16

how you can use this tool. In Figure 3-15, notice that the worksheet title

Mother Goose Enterprises – 2008 Sales is in cell A1. Because it’s a long text

entry, it spills over to the empty cell to the right (B1). To center this title over

the table (which extends from column A through E), select the cell range

A1:E1 (the width of the table) and then click the Merge and Center button in

the Alignment group on the Ribbon’s Home tab.

128

Part II: Editing Without Tears

08_037377 ch03.qxp 11/16/06 9:25 AM Page 128

Look at Figure 3-16 to see the result: The cells in row 1 of columns A through

E are merged into one cell, and now the title is properly centered in this

supercell and consequently over the entire table.

Figure 3-16:

A work-

sheet title

after

centering

it across

columns

A through E.

Figure 3-15:

A work-

sheet title

before

merging and

centering.

129

Chapter 3: Making It All Look Pretty

08_037377 ch03.qxp 11/16/06 9:25 AM Page 129

If you ever need to split up a supercell that you’ve merged with the Merge

and Center back into its original, individual cells, select the cell, and then

simply click the Merge and Center button in the Alignment group on the

Home tab again. You can also do this by clicking the drop-down button

attached to the Merge and Center button on the Home tab and then clicking

Unmerge Cells on this drop-down menu (a few more steps, I’d say!).

Intent on indents

In Excel 2007, you can indent the entries in a cell selection by clicking the

Increase Indent button on the Formatting toolbar. The Increase Indent button

in the Alignment group of the Home tab sports a picture of an arrow pushing

the lines of text to the right. Each time you click this button, Excel indents

the entries in the current cell selection to the right by one character width

of the standard font. (See the section “Futzing with the Fonts,” earlier in this

chapter, if you don’t know what a standard font is or how to change it.)

You can remove an indent by clicking the Decrease Indent button (to the

immediate left of the Increase Indent button) on the Home tab with the pic-

ture of the arrow pushing the lines of text to the left. Also, you can change

how many characters an entry is indented with the Increase Indent button or

outdented with the Decrease Indent button. Open the Format Cells dialog box

(Ctrl+1). Select the Alignment tab, and then alter the value in the Indent text

box (by typing a new value in this text box or by dialing up a new value with

its spinner buttons).

From top to bottom

Left, right, and center alignment all refer to the horizontal positioning of a text

entry in relation to the left and right cell borders (that is, horizontally). You

can also align entries in relation to the top and bottom borders of their cells

(that is, vertically). Normally, all entries are vertically aligned with the bottom

of the cells (as though they were resting on the very bottom of the cell). You

can also vertically center an entry in its cell or align it with the top of its cell.

To change the vertical alignment of a cell range that you’ve selected, click the

appropriate button (Top Align, Middle Align, or Bottom Align) in the

Alignment group on the Home tab.

130

Part II: Editing Without Tears

08_037377 ch03.qxp 11/16/06 9:25 AM Page 130

Figure 3-17 shows the title for the 2008 Mother Goose Enterprises sales work-

sheet after centering it vertically in its cell by clicking the Middle Align

button on the Home tab. (This text entry was previously centered across the

cell range A1:E1; the height of row 1 is increased from the normal 12.75 char-

acters to 36 characters.)

Tampering with how the text wraps

Traditionally, column headings in worksheet tables have been a problem —

you either had to keep them really short or abbreviate them if you wanted to

avoid widening all the columns more than the data warranted. You can avoid

this problem in Excel by using the Wrap Text button in the Alignment group

on the Home tab (the one to the immediate right of the Increase Indent

button). In Figure 3-18, I show a new worksheet in which the column headings

containing the various Mother Goose companies use the Wrap Text feature to

avoid widening the columns as much as these long company names would

otherwise require.

To create the effect shown in Figure 3-18, select the cells with the column

headings (the cell range B2:H2) and then click the Wrap Text button in the

Alignment group on the Home tab.

Figure 3-17:

The

worksheet

title after

centering it

vertically

between the

top and

bottom

edges of

row 1.

131

Chapter 3: Making It All Look Pretty

08_037377 ch03.qxp 11/16/06 9:25 AM Page 131

Selecting Wrap Text breaks up the long text entries in the selection (that

either spill over or are cut off) into separate lines. To accommodate more

than one line in a cell, the program automatically expands the row height so

that the entire wrapped-text entry is visible.

When you select Wrap Text, Excel continues to use the horizontal and vertical

alignment you specify for the cell. Note that you can use any of the Horizontal

alignment options found on the Alignment tab of the Format Cells dialog box

(Ctrl+1), including Left (Indent), Center, Right (Indent), Justify, or Center Across

Selection. You can’t, however, use the Fill option or Distributed (Indent) option.

Select the Fill option on the Horizontal drop-down list box only when you want

Excel to repeat the entry across the entire width of the cell.

If you want to wrap a text entry in its cell and have Excel justify the text

with both the left and right borders of the cell, select the Justify option

from the Horizontal pop-up menu in the Alignment tab in the Format Cells

dialog box.

You can break a long text entry into separate lines by positioning the inser-

tion point in the cell entry (or on the Formula bar) at the place where you

want the new line to start and pressing Alt+Enter. Excel expands the row

containing the cell (and the Formula bar above) when it starts a new line.

When you press Enter to complete the entry or edit, Excel automatically

wraps the text in the cell, according to the cell’s column width and the posi-

tion of the line break.

Figure 3-18:

A new

worksheet

with the

column

headings

formatted

with the

Wrap Text

option.

132

Part II: Editing Without Tears

08_037377 ch03.qxp 11/16/06 9:25 AM Page 132

Reorienting cell entries

Instead of wrapping text entries in cells, you may find it more beneficial to

change the orientation of the text by rotating the text up (in a counterclock-

wise direction) or down (in a clockwise direction). Peruse Figure 3-19 for a

situation where changing the orientation of the wrapped column headings

works much better than just wrapping them in their normal orientation in the

cells.

This example shows the same column headings for the sample order form

I introduce in Figure 3-19 after rotating them 90 degrees counterclockwise.

Notice that switching to this orientation allows their columns to be narrower

than when displayed in the normal orientation.

To make this switch, first select the cell range B2:H2. Next, click the drop-

down button attached to the Orientation button in the Alignment group on

the Home tab and then click the Rotate Text Up option on this drop-down

menu.

Figure 3-20 shows the same headings rotated up at a 45-degree angle. To

create what you see in this figure, you click the Angle Counterclockwise

option on the Orientation button’s drop-down menu after making the same

cell selection, B2:H2.

If you need to set the rotation of the entries in a spreadsheet at angles

other than 45 and 90 degrees (up or down), you need to click the Format

Cell Selection option on the Orientation button’s drop-down menu to open

the Alignment tab of the Format Cells dialog box (or press Ctrl+1 and click the

Alignment tab) and then use the controls in the Orientation section to set

the angle and number of degrees.

Figure 3-19:

Column

headings

rotated 90°

counter-

clockwise.

133

Chapter 3: Making It All Look Pretty

08_037377 ch03.qxp 11/16/06 9:25 AM Page 133

To set a new angle, enter the number of degrees in the Degrees text box, click

the appropriate place on the semicircular diagram, or drag the line extending

from the word Text in the diagram to the desired angle.

To angle text up using the Degrees text box, enter a positive number between

1 and 45 in the text box. To angle the text down, enter a negative number

between -1 and -45.

To set the text vertically so that each letter is above the other in a single

column, click the Vertical Text option on the Orientation button’s drop-down

menu on the Home tab.

Shrink to fit

For those times when you need to prevent Excel from widening the column

to fit its cell entries (as might be the case when you need to display an

entire table of data on a single screen or printed page), use the Shrink to Fit

text control.

Click the Alignment tab of the Format Cells dialog box (Ctrl+1) and then click

the Shrink to Fit check box in Text Control section. Excel reduces the font

size of the entries to the selected cells so that they don’t require changing

the current column width. Just be aware when using this Text Control

option that, depending the length of the entries and width of the column,

you can end up with some text entries so small that they’re completely

illegible!

Figure 3-20:

Column

headings

rotated 45°

counter-

clockwise.

134

Part II: Editing Without Tears

08_037377 ch03.qxp 11/16/06 9:25 AM Page 134

Bring on the borders!

The gridlines you normally see in the worksheet to separate the columns and

rows are just guidelines to help you keep your place as you build your spread-

sheet. You can choose to print them with your data or not (by checking or

clearing the Print check box in the Gridlines section on the Page Layout tab).

To emphasize sections of the worksheet or parts of a particular table, you

can add borderlines or shading to certain cells. Don’t confuse the borderlines

that you add to accent a particular cell selection with the gridlines normally

used to define cell borders in the worksheet — borders that you add are

printed whether or not you print the worksheet gridlines.

To better see the borders that you add to the cells in a worksheet, remove

the gridlines normally displayed in the worksheet by clearing the View check

box in the Gridlines section on the Page Layout tab.

To add borders to a cell selection, click the drop-down button attached to the

Borders button in the Font group on the Home tab. Doing this displays a

drop-down menu with all the border options you can apply to the cell selec-

tion (see Figure 3-21) where you click the type of line you want to apply to all

its cells.

Figure 3-21:

Select

borders for

a cell

selection

with the

Border tab

on the

Format Cells

dialog box.

135

Chapter 3: Making It All Look Pretty

08_037377 ch03.qxp 11/16/06 9:25 AM Page 135

When selecting options on this drop-down menu determining where you want

the borderlines drawn, keep these things in mind:

 To have Excel draw borders only around the outside edges of the entire

cell selection (in other words, following the path of the expanded cell

cursor), click the Outside Borders or the Thick Box Border option

button on this menu

 If you want borderlines to appear around all four edges of each cell in

the cell selection (like a paned window), select the All Borders option on

this drop-down menu

To change the type of line or line thickness or color of the borders you apply

to a cell selection, you must open the Format Cells dialog box and use the

options on its Border tab (click More Borders at the bottom of the Borders

button’s drop-down menu or press Ctrl+1 and then click the Border tab).

To select a new line thickness or line style for a border you’re applying, click

its example in the Style section. To change the color of the border you want

to apply, click the color sample on the Color drop-down palette. After you

select a new line style and/or color, apply the border to the cell selection by

clicking the appropriate line in either the Presets or Border section of the

Border tab before you click OK.

To get rid of existing borders in a worksheet, you must select the cell or cells

that presently contain them and then click the No Border option at the top of

the second section on the Borders button’s drop-down menu.

Applying fill colors, patterns,

and gradient effects to cells

You can also add emphasis to particular sections of the worksheet or one of

its tables by changing the fill color of the cell selection and/or applying a pat-

tern or gradient to it.

If you’re using a black-and-white printer, you will want to restrict your color

choices to light gray in the color palette. Also, you will want to restrict your

use of pattern styles to the very open ones with few dots when enhancing a

cell selection that contains any kind of entries (otherwise, the entries will be

almost impossible to read when printed).

136

Part II: Editing Without Tears

08_037377 ch03.qxp 11/16/06 9:25 AM Page 136

To just choose a new fill color for the background of a cell selection, you can

click the Fill Color button’s drop-down menu in the Font group on the Home

tab and then select the color you want to use in the drop-down palette.

Remember that you can use Live Preview to see what the cell selection looks

like in a particular fill color by moving the mouse pointer over the color

swatches. Click one to select it.

To choose a new pattern for a cell selection, you must open the Format Cells

dialog box (Ctrl+1), and then click the Fill tab (see Figure 3-22). To change the

pattern of the cell selection, click a pattern swatch from the Pattern Style

button’s pattern palette. To add a fill color to the pattern you select, click its

color swatch in the Background Color section of the Fill tab.

If you want to add a gradient effect to the cell selection that goes from one

color to another in a certain direction, click the Fill Effects button on the

Fill tab to open the Fill Effects dialog box (see Figure 3-22). This dialog box

contains a Gradient tab with controls that enable you to determine the

two colors to use as well as shading style and variant.

After you select the colors and styles of the gradient, check the Sample

swatch in the Fill Effects dialog box. When you’ve got it the way you want it,

click OK to close the Fill Effects dialog box and return to the Format Cells

dialog box. The selected gradient effect then appears in its Sample area on

the Fill tab in the Format Cells dialog box Unfortunately, this is one area

where Live Preview doesn’t work, so you’re just going to have to click its OK

button to apply the gradient to the cell selection to see how it actually looks

in the worksheet.

Figure 3-22:

Select new

gradient

for a cell

selection in

the Fill

Effects

dialog box.

137

Chapter 3: Making It All Look Pretty

08_037377 ch03.qxp 11/16/06 9:25 AM Page 137

Although you can’t select new patterns or gradients (only colors) with the Fill

Color button on the Home tab, you can remove fill colors, patterns, and gradi-

ents assigned to a cell selection by clicking the No Fill option on the Fill Color

button’s drop-down menu.

Do It in Styles

In Excel 2007, cell styles really come alive in the form of the new Cell Styles

gallery (see Color Plate12) that you open by clicking the Cells Styles button

in the Style group on the Home tab.

The Cell Styles gallery contains loads of readymade styles you can immedi-

ately apply to the current cell selection. Simply click the desired style sample

in the gallery after using the Live Preview feature to determine which style

looks best on your data.

Creating a new style for the gallery

To create a new style for the gallery by example, manually format a single cell

with all the attributes you want (font, font size, font color, bold, italic, underlin-

ing, fill color, pattern, borders, orientation, and so on) and then click Cell Styles

button on the Home tab followed by the New Cell Style at the bottom of the

gallery. Excel then opens a Style dialog box where you replace the generic style

name (Style 1, Style 2, and so on) with your own descriptive name. Click OK.

Excel then adds a sample of your new style — the style name formatted, with

the new style’s attributes — to a Custom section at the top of the Cell Style

gallery. To then apply this custom style to a cell selection, you only have to

click its sample in the Custom section of the Cells Styles gallery.

The custom cell styles you create don’t become part the current workbook

until the next time you save the workbook so you need to remember to click

the Save button on the Quick Access toolbar or press Ctrl+S to save your

changes after creating a new cell style if want that style to remain part of the

workbook’s Cell Styles gallery the next time you open the workbook in Excel.

Copying custom styles from

one workbook into another

Excel makes it easy to copy custom cell styles that you’ve saved as part one

workbook into the workbook you’re currently working on. To copy custom

styles from workbook to another, follow these steps:

138

Part II: Editing Without Tears

08_037377 ch03.qxp 11/16/06 9:25 AM Page 138

1. Open the workbook that needs the custom styles added to it from

another existing workbook.

This can be a brand new workbook or one that you’ve opened for editing

(see Chapter 4).

2. Open the workbook which has the custom styles you want to copy

saved as part of it.

See the previous section, “Creating a new style for the gallery” for tips

on how to create and save cell styles.

3. Switch back to the workbook into which you want to copy the saved

custom styles.

You can do this by clicking the workbook’s button on the Windows Vista

or XP taskbar or using the Flip feature by pressing Alt+Tab until you

select the workbook’s thumbnail in the center of the desktop.

4. Click the Cell Styles button on the Home tab followed by Merge Styles

in the Cell Styles gallery or press Alt+HY2M to open the Merge Styles

dialog box.

5. Click the name of the open workbook file that contains the custom

styles to copy in the Merge Styles From list box and then click OK.

After you close the Merge Styles dialog box, Excel adds all the custom styles

from the designated workbook into the current workbook adding it to the

Custom section of its Cell Styles gallery. To retain the custom styles you just

imported, save the current workbook (Save button on the Quick Access tool-

bar or Ctrl+S). Then, you can switch back to the workbook containing the

original custom styles you just copied and close its file (Alt+FC).

Fooling Around with the Format Painter

Using cell styles to format ranges of worksheet cells is certainly the way to go

when you have to apply the same formatting over and over again in the work-

books you create. However, there may be times when you simply want to

reuse a particular cell format and apply it to particular groups of cells in a

single workbook without ever bothering to open the Cells Styles Gallery.

For those occasions when you feel the urge to format on the fly (so to speak),

use the Format Painter button (the paintbrush icon) in the Clipboard group

on the Home tab. This wonderful little tool enables you to take the formatting

from a particular cell that you fancy up and apply its formatting to other cells

in the worksheet simply by selecting those cells.

139

Chapter 3: Making It All Look Pretty

08_037377 ch03.qxp 11/16/06 9:25 AM Page 139

To use the Format Painter to copy a cell’s formatting to other worksheet

cells, just follow these easy steps:

1. Format an example cell or cell range in your workbook, selecting

whatever fonts, alignment, borders, patterns, and color you want it to

have.

2. With the cell cursor in one of the cells you just fancied up, click the

Format Painter button in the Clipboard group on the Home tab.

The mouse pointer changes from the standard thick, white cross to a

thick, white cross with an animated paintbrush by its side, and you see a

marquee around the selected cell with the formatting to be used by the

Format Painter.

3. Drag the white-cross-plus-animated-paintbrush pointer (the Format

Painter pointer) through all the cells you want to format in the same

manner as the example cell you first selected.

As soon as you release the mouse button, Excel applies all the format-

ting used in the example cell to all the cells you just selected!

To keep the Format Painter selected so that you can format a bunch of different

cell ranges with the Format Painter pointer, double-click the Format Painter

button on the Home tab after you select the sample cell with the desired for-

matting. To stop formatting cells with the Format Painter pointer, you simply

click the Format Painter button on the Home tab (it remains selected when you

double-click it) again to restore the button to its unselected state and return

the mouse pointer to its normal thick, white-cross shape.

Note that you can use the Format Painter to restore a cell range that you

gussied all up back to its boring default (General) cell format. To do this, click

an empty, previously unformatted cell in the worksheet before you click the

Format Painter button and then use the Format Painter pointer to drag

through the cells you want returned to the default General format.

140

Part II: Editing Without Tears

08_037377 ch03.qxp 11/16/06 9:25 AM Page 140

Chapter 4

Going through Changes

In This Chapter

 Opening workbook files for editing

 Undoing your boo-boos

 Moving and copying with drag and drop

 Copying formulas

 Moving and copying with Cut, Copy, and Paste

 Deleting cell entries

 Deleting and inserting columns and rows

 Spell-checking the worksheet

 Corroborating cell entries in a worksheet with the Text to Speech feature

icture this: You just finished creating, formatting, and printing a major

project with Excel — a workbook with your department’s budget for

the next fiscal year. Because you finally understand a little bit about how the

Excel thing works, you finish the job in crack time. You’re actually ahead of

schedule.

You turn the workbook over to your boss so that she can check the numbers.

With plenty of time for making those inevitable last-minute corrections,

you’re feeling on top of this situation.

Then comes the reality check — your boss brings the document back, and

she’s plainly agitated. “We forgot to include the estimates for the temps and

our overtime hours. They’ve got to go right here. While you’re adding them,

can you move these rows of figures up and those columns over?”

As she continues to suggest improvements, your heart begins to sink. These

modifications are in a different league than, “Let’s change these column head-

ings from bold to italics and add shading to that row of totals.” Clearly, you’re

looking at a lot more work on this baby than you had contemplated. Even

worse, you’re looking at making structural changes that threaten to unravel

the very fabric of your beautiful worksheet.

09_037377 ch04.qxp 11/16/06 9:39 AM Page 141

As the preceding fable points out, editing a worksheet in a workbook can

occur on different levels:

 You can make changes that affect the contents of the cells, such as copy-

ing a row of column headings or moving a table to a new area in a partic-

ular worksheet.

 You can make changes that affect the structure of a worksheet itself,

such as inserting new columns or rows (so that you can enter new data

originally left out) or deleting unnecessary columns or rows from an

existing table so that you don’t leave any gaps.

 You can even make changes to the number of worksheets in a workbook

(either by adding or deleting sheets).

In this chapter, you discover how to safely make all these types of changes to

a workbook. As you see, the mechanics of copying and moving data or insert-

ing and deleting rows are simple to master. It’s the impact that such actions

have on the worksheet that takes a little more effort to understand. Not to

worry! You always have the Undo feature to fall back on for those (hopefully

rare) times when you make a little tiny change that throws an entire work-

sheet into complete and utter chaos.

In the final section of this chapter (“Stamping Out Errors with Text to Speech”),

find out how to use the Text to Speech feature to check out and confirm the

accuracy of the data entries you make in your worksheets. With Text to

Speech, you can listen as you computer reads back a series of cell entries as

you visually corroborate their accuracy from the original source document.

Text to Speech can make this sort of routine and otherwise labor-intensive edit-

ing much easier and greatly increase the accuracy of your spreadsheets.

Opening the Darned Thing Up for Editing

Before you can do any damage — I mean, make any changes — in a work-

book, you have to open it up in Excel. To open a workbook, you can click

Office Button | Open or press Alt+FO or use the old standby keyboard short-

cuts Ctrl+O or Ctrl+F12.

The Open dialog box in Excel 2007

running on Windows Vista

If you’re running Excel 2007 under Windows Vista, an Open dialog box very

much like the one in Figure 4-1 appears. This dialog box is divided into panes:

142

Part II: Editing Without Tears

09_037377 ch04.qxp 11/16/06 9:39 AM Page 142

the Navigation Pane on the left where you can select a new folder to open and

the main pane on the right showing the icons for all the subfolders in the cur-

rent folder as well as the documents that Excel can open.

This current folder whose contents is displayed in the Open dialog box is

either the one designated as the Default File Location on the Save tab of the

Excel Options dialog box or the folder you last opened during your current

Excel work session.

To open a workbook in another folder, click its link in the Favorite Links

section of the Navigation pane or click the Expand Folders button (the one

with the triangle pointing upward) and click its folder in this list.

If you open a new folder and it appears empty of all files (and you know that

it’s not an empty folder), this just means the folder doesn’t contain any of the

types of files that Excel can open directly (such as workbooks, template files,

and macro sheets). To display all the files whether or not Excel can open them

directly (meaning without some sort of conversion), click the drop-down

button that appears next to the drop-down list box that currently contains

Microsoft Office Excel Files and click All Files on its drop-down menu.

When the icon for the workbook file you want to work with appears in the

Open dialog box, you can then open it either by clicking its file icon and

then clicking the Open button or, if you’re handy with the mouse, by just

double-clicking the file icon.

You can use the slider attached to the Views drop-down list button in the

Open dialog box to change the way folder and file icons appear in the dialog

box. When you select Large Icons or Extra Large Icons on this slider (or any-

where in between), the Excel workbook icons actually show a preview of the

Figure 4-1:

Use the

Open dialog

box to find

and open a

workbook

for editing.

143

Chapter 4: Going through Changes

09_037377 ch04.qxp 11/16/06 9:39 AM Page 143

data in the upper-left corner of the first worksheet when the file is saved with

the preview picture option turned on:

 To enable the preview feature when saving workbooks in Excel 2007,

select the Save Thumbnails check box in the Save As dialog box before

saving the file for the first time.

 To enable the preview feature when saving workbooks in Excel 97

through 2003, click the Save Preview Picture check box on the Summary

tab of the workbook’s Properties dialog box selected (File➪Properties)

before saving the file for the first time.

This preview of part of the first sheet can help you quickly identify the work-

book you want to open for editing or printing.

The Open dialog box in Excel 2007

running on Windows XP

Figure 4-2 shows you the Open dialog box that appears when you run Excel

2007 under Windows XP. This dialog box is divided into two sections: a My

Places panel on the left and a folder and file list box on the right.

Back Views

Up One Level New Folder

Delete

Figure 4-2:

Use the

Open dialog

box to find

and open a

workbook

for editing.

144

Part II: Editing Without Tears

09_037377 ch04.qxp 11/16/06 9:39 AM Page 144

When you can’t find the filename you’re looking for in the list box, the first

thing you need to do is check to make sure that you’re looking in the right

folder — because if you’re not, you’re never going to find the missing file. To

tell which folder is currently open, check the Look In drop-down list box at

the top of the Open dialog box (refer to Figure 4-2).

If the folder that is currently open is not the one that has the workbook file

you need to use, you then need to open the folder that does contain the file.

In Excel, you can use the Up One Level button (refer to Figure 4-2) in the

Open dialog box to change levels until you see the folder you want to open in

the list box. To open the new folder, click its icon in the list box and then

click the Open button or press Enter (or you can just double-click its icon).

If the workbook file you want is on another drive, click the Up One Level

button until the C: drive icon appears in the Look In drop-down list box. You

can then switch drives by clicking the drive icon in the list box and then

choosing the Open button or pressing Enter (or you can just double-click

the drive icon).

When you locate the file you want to use in the list box in the Open dialog

box, open it by clicking its file icon and then clicking the Open button or

pressing Enter (or by double-clicking the file icon).

Use the buttons displayed in the My Places panel on the left side of the Open

dialog box (My Recent Documents, Desktop, My Documents, My Computer,

and My Network Places) to easily open any folders associated with these

buttons that contain workbook files:

 My Recent Documents: Click this button to open workbook files you

save in the Recent folder (located inside the Office folder within the

Microsoft folder).

 Desktop: Click this folder to open workbook files you save directly on

the desktop of your computer.

 My Documents: Click this button to open workbook files you save in the

Personal folder inside the Windows folder. (In fact, on some computers,

the My Documents button in the Excel 200X Open dialog box appears

as the Personal button.)

 My Computer: Click this button to open workbook files you save in fold-

ers on the local disks on your computer.

 My Network Places: Click this button to open workbook files you save in

folders on the disks attached to your company’s network.

145

Chapter 4: Going through Changes

09_037377 ch04.qxp 11/16/06 9:39 AM Page 145

You can select Preview on the Views button’s drop-down menu to display a

preview pane on the right side of the Open dialog box. This pane shows data

in the upper-left corner of the first worksheet for all workbooks saved when

the preview picture option is enabled as described in the Remember tip in

the previous section.

Opening more than one workbook at a time

If you know that you’re going to edit more than one of the workbook files’

sheets shown in the list box of the Open dialog box, you can select multiple

files in the list box and Excel will then open all of them (in the order they’re

listed) when you click the Open button or press Enter.

Remember that in order to select multiple files that appear sequentially in the

Open dialog box, you click the first filename and then hold down the Shift key

while you click the last filename. To select files that are not listed sequentially,

you need to hold down the Ctrl key while you click the various filenames.

After the workbook files are open in Excel, you can then switch documents

by selecting their filename buttons on the Window taskbar or by using the

Flip feature (Alt+Tab) to select the workbook’s thumbnail. (See Chapter 7 for

detailed information on working on more than one worksheet at a time.)

Opening recently edited workbooks

If you know that the workbook you now need to edit is one of those that you

had open recently, you don’t even have to fool around with the Open dialog

box. Just click Office Button to open the File pull-down menu and then click

the link to the workbook file in the Recent Documents list displayed in the

column on the right side.

Excel 2007 keeps a running list of the last nine files you opened in the Recent

Documents list on the Office pull-down menu. If the workbook you want to

work with is one of those shown on this list, you can open its file by clicking

its filename in the list or typing its number (1, 2, 3, and so on).

If you want, you can have Excel list more or fewer files in the Recent

Documents list on the Office pull-down menu. To change the number of

recently opened files listed, follow these simple steps:

1. Click Office Button | Excel Options | Advanced or press Alt+FIA to

open the Advanced tab of the Excel Options dialog box.

146

Part II: Editing Without Tears

09_037377 ch04.qxp 11/16/06 9:39 AM Page 146

2. Type a new entry (between 1 and 50) in the Show This Number of

Recent Documents or use the spinner buttons to increase or decrease

this number.

3. Click OK or press Enter to close the Options dialog box.

Note that if you don’t want any files displayed in the Recent Documents list

on the Office pull-down menu, enter 0 in the Show This Number of Recent

Documents text box or select it with the spinner buttons.

When you don’t know where to find them

The only problem you can encounter in opening a document from the Open

dialog box is locating the filename. Everything’s hunky-dory as long as you

can see the workbook filename listed in Open dialog box or know which

folder to open in order to display it. But what about those times when a file

seems to have mysteriously migrated and is now nowhere to be found on

your computer?

Searching for workbooks when running Excel on Windows Vista

When you run Excel 2007 under Windows Vista, the new operating system

adds a Search text box to the Open dialog box (see Figure 4-3) that enables

you to search for missing notebooks right from within the dialog box.

To use Vista’s Search feature to find a workbook, click the Search text box in

the upper-right corner of the Open dialog box and then begin typing search

characters used in the workbook’s filename or contained in the workbook

itself.

Figure 4-3:

Use the

Search text

box in the

Open dialog

box to

quickly

search for

any Excel

workbook

on your

computer.

147

Chapter 4: Going through Changes

09_037377 ch04.qxp 11/16/06 9:39 AM Page 147

As Vista finds any matches for the characters you type, the names of the

workbook files (and other Excel files such as templates and macros sheets as

well) appear in the Open dialog box. As soon as the workbook you want to

open is listed, you can open it by clicking its icon and filename followed by

the Open button or by double-clicking it.

Searching for workbooks when running Excel on Windows XP

Unfortunately, the Open dialog box in Excel 2007 when running under

Windows XP does not have a search feature built into it. This means that to

search for missing workbooks, you have to do it outside of Excel by using the

Windows XP’s search feature.

To use the Windows search feature to find an Excel workbook, follow these

steps:

1. Click the Start button on the Windows XP taskbar and then click

Search in the right column of the Search menu.

Windows opens a Search Results dialog box.

2. Click the Documents (Word Processing, Spreadsheets, Etc.) link in the

left panel of the Search Results dialog box.

3. (Optional) If you know the last time the workbook file was modified,

click the appropriate option button (Within Last Week, Past Month, or

Within the Past Year).

If you haven’t the slightest idea the last time the workbook was edited,

leave the Don’t Remember option button selected.

4. Click the All or Part of the Document Name text box and then type the

filename or the part of the name that you’re sure of (see Figure 4-4).

Type an asterisk (*) for multiple missing characters and a question mark

(?) for single missing characters that you can’t supply in the workbook

filename such as

Budget*.xls?, for which Budget 1-2005.xlsx and Budget

2-2005.xls are both matches.

5. (Optional) To search for the workbook on a particular hard drive or

folder or by a phrase or name entered in the spreadsheets in the file,

click the Use Advanced Search Options link and then enter contents

to search for in the workbook in the Word or Phrase in the Workbook

text, change the location to search Look In as well as any other of the

advanced options that help narrow the search.

Note that if you don’t know the workbook filename but you do know

the size, a key phrase or name it contains, just enter the appropriate

advanced search criteria, leaving the All or Part of the Document Name

text box blank.

6. After you finish specifying all your search criteria, click the Search

button to have Windows XP begin searching for the workbook file.

148

Part II: Editing Without Tears

09_037377 ch04.qxp 11/16/06 9:39 AM Page 148

After you click Search, Windows displays all the workbook files that match

your search criteria in the list box on the right side of the Search Results

dialog box. When you locate the workbook file you want to edit in Excel,

right-click its file icon and filename and then click Open on the shortcut menu

or simply double-click it.

Opening files with a twist

The pop-up menu attached to the Open button in the Open dialog box

enables you to open the selected workbook file(s) in special ways. These

ways include

 Open Read-Only: This command opens the files you select in the Open

dialog box’s list box in a read-only state, which means that you can look

but you can’t touch. (Actually, you can touch; you just can’t save your

changes.) To save changes in a read-only file, you must use the File

➪

Save As command from the Excel menu bar and give the workbook file a

new filename. (Refer to Chapter 2.)

 Open as Copy: This command opens a copy of the files you select in the

Open dialog box. Use this method of file-opening as a safety net: If you

mess up the copies, you always have the originals to fall back on.

 Open in Browser: This command opens workbook files you save as Web

pages (as I describe in Chapter 10) in your favorite Web browser (which

would normally be the Microsoft Internet Explorer). Note that this com-

mand is not available unless the program identifies that the selected file

or files were saved as Web pages rather than plain old Excel worksheet

files.

Figure 4-4:

Use the

Windows

XP Search

Results

dialog box

to search

for a

missing

Excel

workbook

you want to

open.

149

Chapter 4: Going through Changes

09_037377 ch04.qxp 11/16/06 9:39 AM Page 149

 Open and Repair: This command attempts to repair corrupted work-

book files before opening them in Excel. When you select this command,

a dialog box appears giving you a choice between attempting to repair

the corrupted file or opening the recovered version, extracting data out

of the corrupted file, and placing it in a new workbook (which you can

save with the Save command). Click the Repair button to attempt to

recover and open the file. Click the Extract Data button if you previously

tried unsuccessfully to have Excel repair the file.

Much Ado about Undo

Before you start tearing into the workbook that you just opened, get to know

the Undo feature and how it can put right many of the things that you could

inadvertently mess up. The Undo command button on the Quick Access tool-

bar is a regular chameleon button. When you delete the cell selection by

pressing the Delete key, the Undo button’s ToolTip says Undo Clear (Ctrl+Z).

If you move some entries to a new part of the worksheet by dragging it, the

Undo command button ToolTip changes to Undo Drag and Drop.

In addition to clicking the Undo command button (in whatever guise it

appears), you can also choose this command by pressing Ctrl+Z (perhaps

for

unZap).

The Undo command button on Quick Access toolbar changes in response to

whatever action you just took; it keeps changing after each action. If you

forget to strike when the iron is hot, so to speak — by using the Undo feature

to restore the worksheet to its previous state before you choose another

command — you then need to consult the drop-down menu on the Undo

button by clicking its drop-down button that appears to the right of the Undo

icon (the curved arrow pointing to the left). After the Undo drop-down menu

is open, click the action on this menu that you want undone. Excel will then

undo this action and all actions that precede it in the list (which are automat-

ically selected).

Undo is Redo the second time around

After using the Undo command button on the Quick Access toolbar, Excel

2007 activates the Redo command button to its immediate right. If you delete

an entry from a cell by pressing the Delete key and then click the Undo com-

mand button or press Ctrl+Z, the ToolTip that appears when you position the

mouse over the Redo command button appears as:

Redo Clear Ctrl+Y

150

Part II: Editing Without Tears

09_037377 ch04.qxp 11/16/06 9:39 AM Page 150

When you click the Redo command button or press Ctrl+Y, Excel redoes the

thing you just undid. Actually, this sounds more complicated than it is. It

simply means that you use Undo to switch back and forth between the result

of an action and the state of the worksheet just before that action until you

decide how you want the worksheet (or until the cleaning crew turns off the

lights and locks up the building).

What ya gonna do when you can’t Undo?

Just when you think it is safe to begin gutting the company’s most important

workbook, I really feel that I’ve got to tell you that (yikes!) Undo doesn’t work

all the time! Although you can undo your latest erroneous cell deletion, bad

move, or unwise copy, you can’t undo your latest imprudent save. (You know,

like when you meant to choose Save As from the File pull-down menu to save

the edited worksheet under a different document name but instead chose

Save and ended up saving the changes as part of the current document.)

Unfortunately, Excel doesn’t let you know when you are about to take a step

from which there is no return — until it’s too late. After you’ve gone and done

the un-undoable and you click the Undo button where you expect its ToolTip

to say Undo blah, blah, it now reads Can’t Undo.

One exception to this rule is when the program gives you advance warning

(which you should heed). When you choose a command that is normally

undoable but currently — because you’re low on memory, or the change will

affect so much of the worksheet, or both — Excel knows that it can’t undo

the change if it goes through with it, the program displays an alert box telling

you that there isn’t enough memory to undo this action and asking whether

you want to go ahead anyway. If you click the Yes button and complete the

edit, just realize that you do so without any possibility of pardon. If you find

out, too late, that you deleted a row of essential formulas (that you forgot

about because you couldn’t see them), you can’t bring them back with Undo.

In such a case, you would have to close the file (Office Button |Close) and

NOT save your changes.

Doing the Old Drag-and-Drop Thing

The first editing technique you need to learn is called drag and drop. As the

name implies, it’s a mouse technique that you can use to pick up a cell selec-

tion and drop it into a new place on the worksheet. Although drag and drop is

primarily a technique for moving cell entries around a worksheet, you can

adapt it to copy a cell selection, as well.

151

Chapter 4: Going through Changes

09_037377 ch04.qxp 11/16/06 9:39 AM Page 151

To use drag and drop to move a range of cell entries (you can only move one

cell range at a time), follow these steps:

1. Select a cell range.

2. Position the mouse pointer on one edge of the extended cell cursor

that now surrounds the entire cell range.

Your signal that you can start dragging the cell range to its new position

in the worksheet is when the pointer changes to the arrowhead.

3. Drag your selection to its destination.

Drag your selection by depressing and holding down the primary mouse

button — usually the left one — while moving the mouse.

While you drag your selection, you actually move only the outline of the

cell range, and Excel keeps you informed of what the new cell range

address would be (as a kind of drag-and-drop tool tip) if you were to

release the mouse button at that location.

Drag the outline until it’s positioned on the new cells in the worksheet

where you want the entries to appear (as evidenced by the cell range in

the drag-and-drop tool tip).

4. Release the mouse button.

The cell entries within that range reappear in the new location as soon

as you release the mouse button.

In Figures 4-5 and 4-6, I show how you can drag and drop to move a cell

range. In Figure 4-5, I select the cell range A10:E10 (containing the quarterly

totals) to move it to row 12 to make room for sales figures for two new com-

panies (Simple Simon Pie Shoppes and Jack Be Nimble Candlesticks, which

hadn’t been acquired when this workbook was first created). In Figure 4-6,

you see the Mother Goose Enterprises 2008 sales worksheet right after com-

pleting this move.

Figure 4-5:

Dragging

the cell

selection to

its new

position in a

worksheet.

152

Part II: Editing Without Tears

09_037377 ch04.qxp 11/16/06 9:39 AM Page 152

The arguments for the SUM functions in cell range B12: E12 do not keep pace

with the change in row — it continues to sum only the values in rows 3

through 9 right after the move. When, however, you enter the sales figures for

these new enterprises in columns B through C in rows 10 and 11, Excel shows

off its smarts by automatically updating the formulas in row 12 to include the

new entries. So, for example, the SUM(B3:B9) formula in B12 magically

becomes SUM(B3:B11).

Copies, drag-and-drop style

What if you want to copy a cell range instead of dragging and dropping one?

Suppose that you need to start a new table in rows further down the work-

sheet, and you want to copy the cell range with the formatted title and

column headings for the new table. To copy the formatted title range in the

sample worksheet, follow these steps:

1. Select the cell range.

In the case of Figures 4-5 and 4-6, that’s cell range B2:E2.

2. Hold the Ctrl key down while you position the mouse pointer on an

edge of the selection (that is, the expanded cell cursor).

The pointer changes from a thick, shaded cross to an arrowhead with a

+ (plus sign) to the right of it with the drag-and-drop screen tips right

beside it. Keep in mind that the plus sign next to the pointer is your

signal that drag and drop will

copy the selection rather than move it.

3. Drag the cell-selection outline to the place where you want the copy to

appear and release the mouse button.

Figure 4-6:

A work-

sheet after

dropping the

cell

selection

into its new

place.

153

Chapter 4: Going through Changes

09_037377 ch04.qxp 11/16/06 9:39 AM Page 153

If, when using drag and drop to move or copy cells, you position the outline

of the selection so that it overlaps any part of cells that already contain

entries, Excel displays an alert box with the following question: Do you

want to replace contents of the destination cells?

To avoid replacing existing entries and to abort the entire drag-and-drop

mission, click the Cancel button in this alert box. To go ahead and extermi-

nate the little darlings, click OK or press Enter.

Insertions courtesy of drag and drop

Like the Klingons of Star Trek fame, spreadsheets, such as Excel, never take

prisoners. When you place or move a new entry into an occupied cell, the

new entry completely replaces the old as though the old entry never existed

in that cell.

To insert the cell range you’re moving or copying within a populated region

of the worksheet without wiping out existing entries, hold down the Shift key

while you drag the selection. (If you’re copying, you have to get really ambi-

tious and hold down both the Shift and Ctrl keys at the same time!).

With the Shift key depressed as you drag, instead of a rectangular outline of

the cell range, you get an I-beam shape that shows where the selection will be

inserted along with the address of the cell range (as a kind of Insertion

screen tip) indicating where it would be inserted if you release the mouse

button. As you move the I-beam shape, notice that it wants to attach itself to

the column and row borders as you move it. When you position the I-beam

shape at the column or row border where you want the cell range to be

inserted, release the mouse button. Excel inserts the cell range, moving the

existing entries to neighboring blank cells (out of harm’s way).

154

Part II: Editing Without Tears

But I held down the Shift key just like you said . . .

Drag and drop in Insert mode is one of Excel’s

most finicky features. Sometimes you can do

everything just right and still get the alert box

warning you that Excel is about to replace exist-

ing entries instead of pushing them aside. When

you see this alert box, always click the Cancel

button! Fortunately, you can insert things with

the Insert commands without worrying about

which way the I-beam selection goes (see “In

Step with Insert” later in this chapter).

09_037377 ch04.qxp 11/16/06 9:39 AM Page 154

When inserting cells with drag and drop, it may be helpful to think of the

I-beam shape as a pry bar that pulls apart the columns or rows along the

axis of the I. Also, keep in mind that sometimes after moving a range to a new

place in the worksheet, instead of the data appearing, you see only #######s

in the cells. (Excel 2007 doesn’t automatically widen the new columns for the

incoming data as it does when formatting the data.) Remember that the way

to get rid of the #######s in the cells is by widening those troublesome

columns enough to display all the data-plus-formatting, and the easiest way

to do this kind of widening is by double-clicking the right border of the

column.

Formulas on AutoFill

Copying with drag and drop (by holding down the Ctrl key) is useful when

you need to copy a bunch of neighboring cells to a new part of the work-

sheet. Frequently, however, you just need to copy a single formula that you

just created to a bunch of neighboring cells that need to perform the same

type of calculation (such as totaling columns of figures). This type of formula

copy, although quite common, can’t be done with drag and drop. Instead, use

the AutoFill feature (read about this in Chapter 2) or the Copy and Paste com-

mands. (See the section “Cut and paste, digital style” later in this chapter.)

Here’s how you can use AutoFill to copy one formula to a range of cells. In

Figure 4-7, you can see the Mother Goose Enterprises – 2008 Sales worksheet

with all the companies but this time without the monthly totals in row 12.

Figure 4-7:

Copying a

formula to a

cell range

with

AutoFill.

155

Chapter 4: Going through Changes

09_037377 ch04.qxp 11/16/06 9:39 AM Page 155

Figure 4-7 shows the worksheet after dragging the fill handle in cell B12 and

dragging it to select the cell range C12:E12 (where this formula should be

copied).

Relatively speaking

Figure 4-8 shows the worksheet after the formula in a cell is copied to the cell

range C12:E12 and cell C12 is active. Notice how Excel handles the copying of

formulas. The original formula in cell B12 is as follows:

=SUM(B3:B11)

When the original formula is copied next door to cell C12, Excel changes the

formula slightly so that it looks like this:

=SUM(C3:C11)

Excel adjusts the column reference, changing it from B to C, because I copied

from left to right across the rows.

When you copy a formula to a cell range that extends down the rows, Excel

adjusts the row numbers in the copied formulas rather than the column let-

ters to suit the position of each copy. For example, cell E3 in the Mother

Goose Enterprises – 2008 Sales worksheet contains the following formula:

=SUM(B3:D3)

When you copy this formula down to cell E4, Excel changes the copy of the

formula to the following:

=SUM(B4:D4)

Figure 4-8:

The

worksheet

after

copying the

formula

totaling the

monthly

sales.

156

Part II: Editing Without Tears

09_037377 ch04.qxp 11/16/06 9:39 AM Page 156

Excel adjusts the row reference to keep current with the new row 4 position.

Because Excel adjusts the cell references in copies of a formula relative to

the direction of the copying, the cell references are known as

relative cell

references

Some things are absolutes!

All new formulas you create naturally contain relative cell references unless

you say otherwise. Because most copies you make of formulas require adjust-

ments of their cell references, you rarely have to give this arrangement a

second thought. Then, every once in a while, you come across an exception

that calls for limiting when and how cell references are adjusted in copies.

One of the most common of these exceptions is when you want to compare a

range of different values with a single value. This happens most often when you

want to compute what percentage each part is to the total. For example, in the

Mother Goose Enterprises – 2008 Sales worksheet, you encounter this situation

in creating and copying a formula that calculates what percentage each

monthly total (in the cell range B14:D14) is of the quarterly total in cell E12.

Suppose that you want to enter these formulas in row 14 of the Mother Goose

Enterprises – 2008 Sales worksheet, starting in cell B14. The formula in cell

B14 for calculating the percentage of the January-sales-to-first-quarter-total is

very straightforward:

=B12/E12

This formula divides the January sales total in cell B12 by the quarterly total

in E12 (what could be easier?). Look, however, at what would happen if you

dragged the fill handle one cell to the right to copy this formula to cell C14:

=C12/F12

The adjustment of the first cell reference from B12 to C12 is just what the

doctor ordered. However, the adjustment of the second cell reference from

E12 to F12 is a disaster. Not only do you not calculate what percentage the

February sales in cell C12 are of the first quarter sales in E12, but you also

end up with one of those horrible

#DIV/0! error things in cell C14.

To stop Excel from adjusting a cell reference in a formula in any copies you

make, convert the cell reference from relative to absolute. You do this by

pressing the function key F4. Excel indicates that you make the cell reference

absolute by placing dollar signs in front of the column letter and row number.

For example, look at Figure 4-9. Cell B14 in this figure contains the correct for-

mula to copy to the cell range C14:D14:

=B12/$E$12

157

Chapter 4: Going through Changes

09_037377 ch04.qxp 11/16/06 9:39 AM Page 157

Look at the worksheet after this formula is copied to the range C14:D14 with

the fill handle and cell C14 selected (see Figure 4-10). Notice that the formula

bar shows that this cell contains the following formula:

=C12/$E$12

Because E12 was changed to $E$12 in the original formula, all the copies have

this same absolute (nonchanging) reference.

Figure 4-10:

The

worksheet

after

copying the

formula with

the absolute

cell

reference.

Figure 4-9:

Copying the

formula for

computing

the ratio of

monthly to

quarterly

sales with

an absolute

cell

reference.

158

Part II: Editing Without Tears

09_037377 ch04.qxp 11/16/06 9:39 AM Page 158

If you goof up and copy a formula where one or more of the cell references

should have been absolute but you left them all relative, edit the original for-

mula as follows:

1. Double-click the cell with the formula or press F2 to edit it.

2. Position the insertion point somewhere on the reference you want to

convert to absolute.

3. Press F4.

4. When you finish editing, click the Enter button on the Formula bar

and then copy the formula to the messed-up cell range with the fill

handle.

Be sure to press F4 only to change a cell reference to completely absolute as

I describe earlier. If you press the F4 function key a second time, you end up

with a so-called mixed reference, where only the row part is absolute and the

column part is relative (as in E$12). If you then press F4 again, Excel comes

up with another type of mixed reference, where the column part is absolute

and the row part is relative (as in $E12). If you go on and press F4 yet again,

Excel changes the cell reference back to completely relative (as in E12). After

you’re back where you started, you can continue to use F4 to cycle through

this same set of cell reference changes all over again.

Cut and paste, digital style

Instead of using drag and drop or AutoFill, you can use the old standby Cut,

Copy, and Paste commands to move or copy information in a worksheet. These

commands use the Office Clipboard as a kind of electronic halfway house

where the information you cut or copy remains until you decide to paste it

somewhere. Because of this Clipboard arrangement, you can use these com-

mands to move or copy information to any other worksheet open in Excel or

even to other programs running in Windows (such as a Word document).

To move a cell selection with Cut and Paste, follow these steps:

1. Select the cells you want to move.

2. Click the Cut command button in the Clipboard group on the Home

tab (the button with the scissors icon).

Or, if you prefer, you can choose Cut by pressing Ctrl+X.

Whenever you choose the Cut command in Excel, the program surrounds

the cell selection with a

marquee (a dotted line that travels around the

cells’ outline) and displays the following message on the Status bar:

Select destination and press ENTER or choose Paste

159

Chapter 4: Going through Changes

09_037377 ch04.qxp 11/16/06 9:39 AM Page 159

3. Move the cell cursor to, or click the cell in the upper-left corner of the

new range to which you want the information moved.

4. Press Enter to complete the move operation.

Or, if you’re feeling really ambitious, click the Paste command button in

the Clipboard group on the Home tab or press Ctrl+V.

Notice that when you indicate the destination range, you don’t have to select a

range of blank cells that matches the shape and size of the cell selection you’re

moving. Excel only needs to know the location of the cell in the upper-left

corner of the destination range to figure out where to put the rest of the cells.

Copying a cell selection with the Copy and Paste commands follows an identi-

cal procedure to the one you use with the Cut and Paste commands. After

selecting the range to copy, you have even more choices about how to get

the information into the Clipboard. Instead of clicking the Copy button on

the Home tab on the Ribbon or choosing Copy from the cell shortcut menu

after right-clicking it, you can press Ctrl+C.

Paste it again, Sam . . .

An advantage to copying a selection with the Copy and Paste commands and

the Clipboard is that you can paste the information multiple times. Just make

sure that, instead of pressing Enter to complete the first copy operation, you

click the Paste button on the Home tab in the Ribbon or press Ctrl+V.

When you use the Paste command to complete a copy operation, Excel

copies the selection to the range you designate without removing the mar-

quee from the original selection. This is your signal that you can select

another destination range (either in the same or a different document).

After you select the first cell of the next range where you want the selection

copied, choose the Paste command again. You can continue in this manner,

pasting the same selection to your heart’s content. When you make the last

copy, press Enter instead of choosing the Paste command button or pressing

Ctrl+V. If you forget and choose Paste, get rid of the marquee around the orig-

inal cell range by pressing the Esc key.

Keeping pace with the Paste Options

Right after you click the Paste button on the Home tab of the Ribbon or press

Ctrl+V to paste cell entries that you copy (not cut) to the Clipboard, Excel

displays a Paste Options button with its own drop-down button at the end of

the pasted range. You can use the options available when you click the drop-

down button to modify the paste operation in the following ways:

160

Part II: Editing Without Tears

09_037377 ch04.qxp 11/16/06 9:39 AM Page 160

 Keep Source Formatting: Excel copies the formatting from the original

cells and pastes this into the destination cells (along with the copied

entries).

 Use Destination Theme: Excel formats the copied entries according the

cell style applied to the destination cell range.

 Match Destination Formatting: Excel formats the copied entries accord-

ing to the formatting assigned to the destination cell range.

 Values Only: Excel copies only the calculated results of any formulas in

the source cell range.

 Values and Number Formatting: Excel copies the calculated results of

any formulas along with all the formatting assigned to the labels, values,

and formulas in the source cell range to the destination range. This

means that all the labels and values in the destination range appear for-

matted just like the source range even though all the original formulas

are lost and only the calculated values are retained.

 Values and Source Formatting: Excel copies the calculated results of

any formulas along with all formatting assigned to source cell range.

 Keep Source Column Widths: Excel makes the width of the columns in

the destination range the same as those in the source range when it

copies their cell entries.

 Formatting Only: Excel copies only the formatting (and not the entries)

from the source cell range to the destination range.

 Link Cells: Excel creates linking formulas in the destination range so

that any changes that you make to the entries in cells in the source

range are immediately brought forward and reflected in the correspond-

ing cells of the destination range.

Paste it from the Clipboard task pane

The Office Clipboard can store multiple cuts and copies from any program

running under Windows not just Excel. In Excel, this means that you can

continue to paste stuff from the Office Clipboard into a workbook even after

finishing a move or copy operation (even when you do so by pressing the

Enter key rather than using the Paste command).

To open the Office Clipboard in its own task pane to the immediate left of the

Worksheet area (see Figure 4-11), click the Dialog Box launcher button in the

right-hand corner of the Clipboard group on the Ribbon’s Home tab.

To paste an item from the Office Clipboard into a worksheet other than the

one with the data last cut or copied onto it, click the item in the Clipboard

task pane to paste it into the worksheet starting at the current position of the

cell cursor.

161

Chapter 4: Going through Changes

09_037377 ch04.qxp 11/16/06 9:39 AM Page 161

Note that you can paste all the items stored in the Office Clipboard into the

current worksheet by clicking the Paste All button at the top of the Clipboard

task pane. To clear the Office Clipboard of all the current items, click the

Clear All button. To delete only a particular item from the Office Clipboard,

position the mouse pointer over the item in the Clipboard task pane until its

drop-down button appears. Click this drop-down button, and then choose

Delete from the pop-up menu.

To have the Office Clipboard task pane appear automatically after making

two cuts or copies to the Clipboard in an Excel workbook, click the Show

Office Clipboard Automatically option on the Options button pop-up menu.

To be able to open the Office Clipboard task pane in the Excel program

window by pressing Ctrl+CC, click Show Office Clipboard When Ctrl+C

Pressed Twice on the Options button’s pop-up menu. Note that this option

only opens the task pane by pressing Ctrl+CC: you still have to click the Close

button on the Office Clipboard to close the task pane.

So what’s so special about Paste Special?

Normally, unless you fool around with the Paste Options (see the section

“Keeping pace with the Paste Options” earlier in this chapter), Excel copies

all the information in the range of cells you selected: formatting, as well the

formulas, text, and other values you enter. If you want, use the Paste Special

command to specify that only the entries be copied (without the formatting)

or that just the formatting be copied (without the entries).

Figure 4-11:

The Office

Clipboard

task pane

appears on

the left side

of the Excel

Worksheet

area.

162

Part II: Editing Without Tears

09_037377 ch04.qxp 11/16/06 9:39 AM Page 162

To paste particular parts of a cell selection while discarding others, click the

drop-down button that appears at the bottom of the Paste command button

on the Ribbon’s Home tab. Then, click Paste Special on its drop-down menu

to open the Paste Special dialog box. Here you can specify which parts of the

current cell selection to use by selecting the appropriate Paste Special radio

button or check box as follows:

 All to paste all the stuff in the cell selection (formulas, formatting, you

name it).

 Formulas to paste all the text, numbers, and formulas in the current cell

selection without their formatting.

 Values to convert formulas in the current cell selection to their calcu-

lated values.

 Formats to paste only the formatting from the current cell selection,

leaving the cell entries in the dust.

 Comments to paste only the notes that you attach to their cells (kinda

like electronic self-stick notes — see Chapter 6 for details).

 Validation to paste only the data validation rules into the cell range that

you set up with the Data Validation command (which enables you to set

what value or range of values is allowed in a particular cell or cell range).

 All Using Source Theme to paste all the information plus the cell styles

applied to the cells.

 All Except Borders to paste all the stuff in the cell selection without

copying any borders you use there.

 Column Widths to apply the column widths of the cells copied to the

Clipboard to the columns where the cells are pasted.

 Formulas and Number Formats to include the number formats assigned

to the pasted values and formulas.

 Values and Number Formats to convert formulas to their calculated

values and include the number formats you assign to all the pasted

values.

 None to have Excel perform no operation between the data entries you

cut or copy to the Clipboard and the data entries in the cell range where

you paste.

 Add to add the data you cut or copy to the Clipboard and the data

entries in the cell range where you paste.

 Subtract to subtract the data you cut or copy to the Clipboard from the

data entries in the cell range where you paste.

 Multiply to multiply the data you cut or copy to the Clipboard by the

data entries in the cell range where you paste.

163

Chapter 4: Going through Changes

09_037377 ch04.qxp 11/16/06 9:39 AM Page 163

 Divide to divide the data you cut or copy to the Clipboard by the data

entries in the cell range where you paste.

 Skip Blanks check box when you want Excel to paste everywhere except

for any empty cells in the incoming range. In other words, a blank cell

cannot overwrite your current cell entries.

 Transpose check box when you want Excel to change the orientation of

the pasted entries. For example, if the original cells’ entries run down

the rows of a single column of the worksheet, the transposed pasted

entries will run across the columns of a single row.

 Paste Link button when you’re copying cell entries and you want to

establish a link between copies you’re pasting and the original entries.

That way, changes to the original cells automatically update in the

pasted copies.

You can alternatively select the Formulas, Paste Values, No Borders,

Transpose, and Paste Link paste options directly from the drop-down

menu attached to the Paste button in the Clipboard group on the Home

tab of the Ribbon without having to open the Paste Special dialog box.

(Note that the No Borders option on the Paste button’s drop-down menu

is the same as the All Except Borders option in the Paste Special dialog box.)

Let’s Be Clear about Deleting Stuff

No discussion about editing in Excel would be complete without a section

on getting rid of the stuff you put into cells. You can perform two kinds of

deletions in a worksheet:

 Clearing a cell: Clearing just deletes or empties the cell’s contents

without removing the cell from the worksheet, which would alter the

layout of the surrounding cells.

 Deleting a cell: Deleting gets rid of the whole kit and caboodle — cell

structure along with all its contents and formatting. When you delete a

cell, Excel has to shuffle the position of entries in the surrounding cells

to plug up any gaps made by the demise.

Sounding the all clear!

To get rid of just the contents of a cell selection rather than delete the cells

along with their contents, select the range of cells to be cleared, simply press

the Delete key.

164

Part II: Editing Without Tears

09_037377 ch04.qxp 11/16/06 9:39 AM Page 164

If you want to get rid of more than just the contents of a cell selection, click

the Clear button (the one with the eraser) in the Editing group on the

Ribbon’s Home tab and then click one of the following options on its drop-

down menu:

 Clear All: Gets rid of all formatting and notes, as well as entries in the

cell selection (Alt+HEA).

 Clear Formats: Deletes only the formatting from the cell selection with-

out touching anything else (Alt+HEF).

 Clear Contents: Deletes only the entries in the cell selection just like

pressing the Delete key (Alt+HEC).

 Clear Comments: Removes the notes in the cell selection but leaves

everything else behind (Alt+HEM).

Get these cells outta here!

To delete the cell selection rather than just clear out its contents, select the

cell range, and then click the drop-down button attached to the Delete com-

mand button in the Cells group of the Home tab and click Delete Cells on the

drop-down menu (or press Alt+HDD). The Delete dialog box opens, showing

options for filling in the gaps created when the cells currently selected are

blotted out of existence with those left behind:

 Shift Cells Left: This default option moves entries from neighboring

columns on the right to the left to fill in gaps created when you delete

the cell selection by clicking OK or pressing Enter.

 Shift Cells Up: Select this to move entries up from neighboring rows

below.

 Entire Row: Select this to remove all the rows in the current cell selection.

 Entire Columns: Select this to delete all the columns in the current cell

selection.

If you know that you want to shift the remaining cells to the right after deleting

the cells in the current selection, you can simply click the Delete command

button on the Home tab of the Ribbon (this is the same thing as opening the

Delete dialog box and then clicking OK when the default Shift Cells Left

option button is selected).

To delete an entire column or row from the worksheet, you can select the

column or row on the workbook window frame, then right-click the selection

and click Delete from the column’s or row’s shortcut menu.

165

Chapter 4: Going through Changes

09_037377 ch04.qxp 11/16/06 9:39 AM Page 165

You can also delete entire columns and rows selected in the worksheet by

clicking the drop-down button attached to the Delete command button on the

Ribbon’s Home tab and then clicking the Delete Sheet Columns (Alt+HDC) or

Delete Sheet Rows option (Alt+HDR) on the drop-down menu.

Deleting entire columns and rows from a worksheet is risky business unless

you are sure that the columns and rows in question contain nothing of value.

Remember, when you delete an entire row from the worksheet, you delete all

information from column A through XFD in that row (and you can see only a

very few columns in this row). Likewise, when you delete an entire column

from the worksheet, you delete all information from row 1 through 1,048,576 in

that column.

Staying in Step with Insert

For those inevitable times when you need to squeeze new entries into an

already populated region of the worksheet, you can insert new cells in the

area rather than go through all the trouble of moving and rearranging several

individual cell ranges. To insert a new cell range, select the cells (many of

which are already occupied) where you want the new cells to appear and then

click the drop-down attached to the Insert command button in the Cells group

of the Home tab and then click Insert Cells on the drop-down menu (or press

Alt+HII). The Insert dialog box opens with the following options buttons:

 Shift Cells Right: Select this to shift existing cells to the right to make

room for the ones you want to add before clicking OK or pressing Enter.

 Shift Cells Down: Use this default to instruct the program to shift exist-

ing entries down instead before clicking OK or pressing Enter.

 Entire Row or Entire Column: As when you delete cells, when you

insert cells with the Insert dialog box, you can insert complete rows or

columns in the cell range by selecting either of these radio buttons. You

can also select the row number or column letter on the frame before you

choose the Insert command.

If you know that you want to shift the existing cells to the right to make

room for the newly inserted cells, you can simply click the Insert command

button on the Ribbon’s Home tab (this is the same thing as opening the

Insert dialog box and then clicking OK when the Shift Cells Right option

button is selected).

166

Part II: Editing Without Tears

09_037377 ch04.qxp 11/16/06 9:39 AM Page 166

Remember that you can also insert entire columns and rows in a worksheet

by right-clicking the selection and then clicking Insert on the Column’s or

Row’s shortcut menu.

Keep in mind that just like when you delete whole columns and rows, insert-

ing entire columns and rows affects the entire worksheet, not just the part

you see. If you don’t know what’s out in the hinterlands of the worksheet, you

can’t be sure how the insertion will impact — perhaps even sabotage — stuff

(especially formulas) in the other unseen areas. I suggest that you scroll all

the way out in both directions to make sure that nothing’s out there.

Stamping Out Your Spelling Errors

If you’re as good a speller as I am, you’ll be really relieved to know that Excel

2007 has a built-in spell checker that can catch and get rid of all those embar-

rassing little spelling errors. With this in mind, you no longer have any excuse

for putting out worksheets with typos in the titles or headings.

To check the spelling in a worksheet, you have the following options:

 Click the Spelling command button on the Ribbon’s Review tab

 Press Alt+RS

 Press F7

Any way you do it, Excel begins checking the spelling of all text entries in the

worksheet. When the program comes across an unknown word, it displays

the Spelling dialog box, similar to the one shown in Figure 4-12.

Figure 4-12:

Check your

spelling

from the

Spelling

dialog box.

167

Chapter 4: Going through Changes

09_037377 ch04.qxp 11/16/06 9:39 AM Page 167

Excel suggests replacements for the unknown word shown in the Not in

Dictionary text box with a likely replacement in the Suggestions list box of

the Spelling dialog box. If that replacement is incorrect, you can scroll

through the Suggestions list and click the correct replacement. Use the

Spelling dialog box options as follows:

 Ignore Once and Ignore All: When Excel’s spell check comes across a

word its dictionary finds suspicious but you know is viable, click the

Ignore Once button. If you don’t want the spell checker to bother query-

ing you about this word again, click the Ignore All button.

 Add to Dictionary: Click this button to add the unknown (to Excel)

word — such as your name — to a custom dictionary so that Excel

won’t flag it again when you check the spelling in the worksheet

later on.

 Change: Click this button to replace the word listed in the Not in

Dictionary text box with the word Excel offers in the Suggestions

list box.

 Change All: Click this button to change all occurrences of this misspelled

word in the worksheet to the word Excel displays in the Suggestions

list box.

 AutoCorrect: Click this button to have Excel automatically correct this

spelling error with the suggestion displayed in the Suggestions list box

(by adding the misspelling and suggestion to the AutoCorrect dialog

box; for more, read through Chapter 2).

 Dictionary Language: To switch to another dictionary (such as a

United Kingdom English dictionary, or a French dictionary when

checking French terms in a multilingual worksheet), click this drop-

down button and then select the name of the desired language in the

pop-up list.

Notice that the Excel spell checker not only flags words not found in its built-

in or custom dictionary but also flags occurrences of double words in a cell

entry (such as

total total) and words with unusual capitalization (such as NEw

York

instead of New York). By default, the spell checker ignores all words with

numbers and all Internet addresses. If you want it to ignore all words in upper-

case letters as well, click the Options button at the bottom of the Spelling

dialog box, and then select the Ignore Words in UPPERCASE check box before

clicking OK.

168

Part II: Editing Without Tears

09_037377 ch04.qxp 11/16/06 9:39 AM Page 168

Keep in mind that you can check the spelling of just a particular group of

entries by selecting the cells before you click the Spelling command button

on the Review tab of the Ribbon or press F7.

Stamping Out Errors with Text to Speech

The good news is that Excel 2007 still supports the Text to Speech feature

first introduced in Excel 2003. This feature enables your computer to read

aloud any series of cell entries in the worksheet. By using Text to Speech, you

can check your printed source as the computer reads out loud the values and

labels that you’ve actually entered — a real nifty way to catch and correct

errors that may otherwise escape unnoticed.

The Text to Speech translation feature requires no prior training or special

microphones: All that’s required is a pair of speakers or headphones con-

nected to your computer.

Now for the bad news: Text to Speech is not available from any of the tabs on

the Ribbon. The only way to access Text to Speech is by adding its various

Speak Cells command buttons as custom button on the Quick Access toolbar

as follows:

1. Click Office Button | Excel Options | Customize or press Alt+FIC.

The Customize tab of the Excel Options dialog box opens.

2. Click Commands Not in the Ribbon on the Choose Commands From

drop-down menu.

The Text to Speech command buttons include Speak Cells, Speak Cells –

Stop Speaking Cells, Speak Cells by Column, Speak Cells by Row, and

Speak Cells on Enter.

3. Click the Speak Cells button in Choose Commands From list box on

the left and then click the Add button to add it to the bottom of the

Customize Quick Access Toolbar list box on the right.

4. Repeat Step 3 until you’ve added the remaining Text to Speech but-

tons to the Quick Access toolbar: Speak Cells – Stop Speaking Cells,

Speak Cells by Column, Speak Cells by Row, and Speak Cells on Enter.

If you want to reposition the Text to Speech buttons on the Quick Access

toolbar, select each button in the Customize Quick Access Toolbar and

then move it left on the bar by clicking the Move Up button or right by

169

Chapter 4: Going through Changes

09_037377 ch04.qxp 11/16/06 9:39 AM Page 169

clicking Move Down. If you want to set off the Text to Speech buttons as

a separate group on the Quick Access toolbar, add a <Separator> icon

ahead of the Speak Cells command button (and following the Speak Cells

on Enter button if you have buttons not related to the Text to Speech

function that follow on the Quick Access toolbar).

5. Click the OK button to close the Excel Options dialog box.

Figure 4-13 shows the Quick Access toolbar in my Excel 2007 program

window after I moved the toolbar down so that it appears below the Ribbon

and after I added all the Speak Cells command buttons to it and grouped

them together with a <Separator> icon.

After adding the Text to Speech commands as custom Speak Cells buttons on

the Quick Access toolbar, you can use them to corroborate spreadsheet

entries and catch those hard-to-spot errors as follows:

1. Select the cells in the worksheet whose contents you want read aloud

by Text to Speech.

2. Click the Speak Cells custom button on the Quick Access toolbar to

have the computer begin reading back the entries in the selected cells.

By default, the Text to Speech feature reads the contents of each cell in

the cell selection by first reading down each column and then across the

rows. If you want Text to Speech to read across the rows and then down

the columns, click the Speak Cells by Rows button on the Quick Access

toolbar (the button with the two opposing horizontal arrows).

Speak Cells

Speak Cells by Columns

Speak Cells by Rows

Speak Cells on Enter

Speak Cells - Stop Speaking Cells

Figure 4-13:

After adding

the Speak

Cells

command

buttons to

the Quick

Access tool-

bar, you can

use them to

audibly

check cell

entries.

170

Part II: Editing Without Tears

09_037377 ch04.qxp 11/21/06 12:33 PM Page 170

3. To have the Text to Speech feature read back each cell entry as you

press the Enter key (at which point the cell cursor moves down to the

next cell in the selection), click the Speak Cells on Enter custom

button (the button with the curved arrow Enter symbol) on the Quick

Access toolbar.

As soon as you click the Speak Cells on Enter button, the computer tells

you, “Cells will now be spoken on Enter.” After selecting this option, you

need to press Enter each time that you want to hear an entry read back

to you.

4. To pause the Text to Speech feature when you’re not using the Speak

Cells on Enter option (Step 3) and you locate a discrepancy between

what you’re reading and what you’re hearing, click the Speak Cells -

Stop Speaking Cells button (the Speak Cells button with the x).

Keep in mind that after you click the Speak Cells on Enter button on the

Quick Access toolbar, the computer

only speaks each new cell entry that you

complete by pressing the Enter key (which moves the cell cursor down one

row) and not by some other method, such as clicking the Enter button on the

Formula bar or pressing the

↓ key in order to have the Text to Speech feature

read what you just entered.

171

Chapter 4: Going through Changes

09_037377 ch04.qxp 11/16/06 9:39 AM Page 171

172

Part II: Editing Without Tears

09_037377 ch04.qxp 11/16/06 9:39 AM Page 172

Chapter 5

Printing the Masterpiece

In This Chapter

 Previewing pages in Page Layout view and Print Preview

 Quick Printing from the Quick Access toolbar

 Printing all the worksheets in a workbook

 Printing just some of the cells in a worksheet

 Changing page orientation

 Printing the whole worksheet on a single page

 Changing margins for a report

 Adding a header and footer to a report

 Printing column and row headings as print titles on every page of a report

 Inserting page breaks in a report

 Printing the formulas in your worksheet

or most people, getting the data down on paper is really what spread-

sheets are all about (all the talk about a so-called paperless office to the

contrary). Everything — all the data entry, all the formatting, all the formula

checking, all the things you do to get a spreadsheet ready — is really just

preparation for printing its information.

In this chapter, you find out just how easy it is to print reports with Excel

2007. Thanks to the program’s new Page Layout worksheet view and handy

Page Layout tab on the Ribbon, you discover how to produce top-notch

reports the first time you send the document to the printer (instead of the

second or even the third time around).

The only trick to printing a worksheet is getting used to the paging scheme and

learning how to control it. Many of the worksheets you create with Excel are

not only longer than one printed page but also wider. Word processors, such

as Word 2007, page the document only vertically; they won’t let you create a

document wider than the page size you’re using. Spreadsheet programs like

Excel 2007, however, often have to break up pages both vertically and horizon-

tally to print a worksheet document (a kind of tiling of the print job, if you will).

10_037377 ch05.qxp 11/16/06 9:39 AM Page 173

When breaking a worksheet into pages, Excel first pages the document verti-

cally down the rows in the first columns of the print area (just like a word

processor). After paging the first columns, the program pages down the rows

of the second set of columns in the print area. Excel pages down and then

over until the entire document included in the current print area (which can

include the entire worksheet or just sections) is paged.

When paging the worksheet, keep in mind that Excel does not break up the

information within a row or column. If all the information in a row won’t fit at

the bottom of the page, the program moves the entire row to the following page.

If all the information in a column won’t fit at the right edge of the page, the pro-

gram moves the entire column to a new page. (Because Excel pages down and

then over, the column may not appear on the next page of the report.)

You can deal with such paging problems in several ways — and, in this chap-

ter, you see all of them! After you have these page problems under control,

printing is a proverbial piece of cake.

Taking a Gander at the Pages

in Page Layout View

Excel 2007’s new Page Layout view gives you instant access to the paging of

the current worksheet. Activate this feature by clicking the Page Layout View

button (the center one) to the immediate left of the Zoom slider on the Status

bar or the Page Layout View command button on the Ribbon’s View tab

(Alt+WP). As you can see in Figure 5-1, when you switch to Page Layout view,

Excel adds horizontal and vertical rulers to the column letter and row

number headings. In the Worksheet area, this view shows the margins for

each printed page with any headers and footers defined for the report along

with the breaks between each (often you have to use the Zoom slider to

reduce the screen magnification to display the page breaks on the screen).

To see all the pages required to print the active worksheet, drag the slider

button in the Zoom slider on the Status bar to the left until you decrease the

screen magnification sufficiently to display all the pages of data.

Excel displays rulers using the default units for computer (inches on a U.S.

computer and centimeters on a European machine). To change the units,

open the Advanced tab of the Excel Options dialog box (Office Button | Excel

Options | Advanced or Alt+FIA) and then select the appropriate unit on the

Ruler Units drop-down menu in the Display section (Inches, Centimeters, or

Millimeters) in the Display section.

Note that the Ruler command button acts as a toggle switch so that the first

time you click this button, Excel removes the rulers from the Page Layout

view and the second time the program adds them back again.

174

Part II: Editing Without Tears

10_037377 ch05.qxp 11/16/06 9:39 AM Page 174

Checking the Printout with Print Preview

Save wasted paper and your sanity by using the Print Preview feature before

you print any worksheet, section of worksheet, or entire workbook. Because

of the peculiarities in paging worksheet data, check the page breaks for any

report that requires more than one page. Print Preview shows you exactly

how the worksheet data will be paged when printed and you can make last

minute changes to the page settings before sending the report on to the

printer when everything looks okay.

To switch to Print Preview, click the Office Button and then highlight the £

button to the right of the Print option (and not before you click by Print

Preview on the submenu that appears) or press Alt+FWV. Excel displays the

first page of the report in a separate window with its own Print Preview con-

textual tab. When positioned over the spreadsheet, the mouse pointer

becomes a magnifying glass. Look at Figure 5-2 to see the first page of a four-

page report in Print Preview.

When Excel displays a full page in the Print Preview window, you can barely

read its contents; increase the view to actual size if you need to verify some

of the information. Zoom up to 100 percent by clicking the previewed page

with the magnifying-glass mouse pointer or by clicking the Zoom button in

the Print Preview tab on the Ribbon. Check out the difference in Figure 5-3 —

here you can see what the first page of the four-page report looks like after I

zoom in by clicking the Zoom pointer (with the magnifying-glass icon) on the

top central portion of the page.

Figure 5-1:

Viewing a

spreadsheet

in Page

Layout view.

175

Chapter 5: Printing the Masterpiece

10_037377 ch05.qxp 11/21/06 12:40 PM Page 175

After you enlarge a page to actual size, use the scroll bars to bring new parts

of the page into view in the Print Preview window. If you prefer to use the key-

board, press the

↑ and ↓ keys or PgUp and PgDn to scroll up or down the

page, respectively; press

← and → or Ctrl+PgUp and Ctrl+PgDn to scroll left

and right, respectively.

Figure 5-3:

Page 1 of a

four-page

report after

clicking the

top of the

page with

the Zoom

tool.

Figure 5-2:

Page 1 of a

four-page

report in

Preview.

176

Part II: Editing Without Tears

10_037377 ch05.qxp 11/16/06 9:39 AM Page 176

To return to the full-page view, click the mouse pointer (in its arrowhead

form) anywhere on the page or click the Zoom command button on the Print

Preview tab on the Ribbon.

Excel indicates the number of pages in a report on the Status bar of the Print

Preview window (at the far-left bottom of your Excel screen). If your report

has more than one page, view pages that follow by clicking the Next Page

button in the Preview group of the Print Preview tab on the Ribbon. To review

a page you’ve already seen, back up a page by clicking the Previous Page

button immediately below it. (The Previous button is grayed out if you’re on

the first page.) You can also advance to the next page by pressing the PgDn

↓ key or move back to the previous page by pressing the PgUp or ↑ key

when the page view is full-page rather than actual size.

When you finish previewing the report, the Print Preview tab offers you fol-

lowing options:

 Print command button in the Print group to display the Print dialog box

and print the report from the normal worksheet view. (See the section

“Printing the Worksheet,” later in this chapter.)

 Page Setup command button in the Print group to open the Page Setup

dialog box where you can take care of paging problems by choosing a

new paper size, page order, orientation, or margins, or if you notice a

problem with the header or footer (the text you enter in the top or

bottom margin of the pages). For more on what printing parameters you

can set here, see the section “My Page Was Set Up!” later in this chapter.

 Show Margins check box in the Preview group to display markers for

the current top, bottom, left, and right margins that you can then adjust

by dragging them. (See “Massaging the margins,” later in this chapter,

for details.)

 Close Print Preview command button in the Preview group to close the

Print Preview window and return to the previous worksheet view.

Printing the Worksheet

As long as you want to use Excel’s default print settings to print all the cells

in the current worksheet, printing in Excel 2007 is a breeze. Simply add the

Quick Print button to the Quick Access toolbar (by clicking Customize Quick

Access Toolbar button and then clicking Quick Print on its drop-down menu).

After adding the Quick Print button to the Quick Access toolbar, you can use

this button to print a single copy of all the information in the current work-

sheet, including any charts and graphics — but not including comments you

177

Chapter 5: Printing the Masterpiece

10_037377 ch05.qxp 11/16/06 9:39 AM Page 177

add to cells. (See Chapter 6 for details about adding comments to your work-

sheet and Chapter 8 for details about charts and graphics.)

When you click the Quick Print button, Excel routes the print job to the

Windows print queue, which acts like a middleman to send the job to the

printer. While Excel sends the print job to the print queue, Excel displays a

Printing dialog box to inform of its progress (displaying such updates as

Printing Page 2 of 3). After this dialog box disappears, you are free to go back to

work in Excel. To stop the printing while the job is still in the process of being

sent to the print queue, click the Cancel button in the Printing dialog box.

If you don’t realize that you want to cancel the print job until after Excel fin-

ishes shipping it to the print queue (that is, while the Printing dialog box

appears on-screen), you must open the dialog box for your printer by clicking

and cancel printing from there:

1. Click the printer icon in the Notification area at the far right of the

Windows Vista or XP taskbar (to the immediate left of the current

time) with the secondary mouse button to open its shortcut menu.

This printer icon displays the screen tip 1 document(s) pending

for

so-and-so. For example, when I’m printing, this message reads 1

document(s) pending for Greg

when I position the mouse pointer

over the printer icon.

2. Right-click the printer icon and then select the Open Active Printers

command from its shortcut menu.

This opens the dialog box for the printer with the Excel print job in its

queue (as described under the Document heading in the list box).

3. Select the Excel print job that you want to cancel in the list box of

your printer’s dialog box.

4. Choose Document

➪Cancel from the menu bar.

5. Wait for the print job to disappear from the queue in the printer’s

dialog box and then click the Close button to get rid of it and return

to Excel.

Printing the Worksheet from

the Print Dialog Box

Printing with the Quick Print button on the Quick Access toolbar is fine pro-

vided that all you want is a single copy of all the information in the current

worksheet. If you want more copies, more or less data (such as all the work-

sheets in the workbook or just a cell selection within a particular worksheet),

then you need to print from the Print dialog box (shown in Figure 5-4).

178

Part II: Editing Without Tears

10_037377 ch05.qxp 11/16/06 9:39 AM Page 178

Excel provides a number of ways to open the Print dialog box:

 Press Ctrl+P.

 Click Office Button | Print.

 Press Alt+FP

 Press Ctrl+Shift+F12.

Printing particular parts of the workbook

Within the Print dialog box are the Print Range and Print What sections (from

which you can select how much of the information is printed), and the

Copies section, from which you can change the number of copies printed.

Here’s what’s in these areas and how you use their options:

 All: When the All option button is selected, all the pages in your docu-

ment will print. Because this is the default choice, you would only need

to select it if you previously printed a portion of the document by select-

ing the Page(s) option button.

 Page(s): Normally, Excel prints all the pages required to produce the

information in the areas of the workbook that you want printed.

Sometimes, however, you may need to reprint only a page or range of

pages that you’ve modified within this section. To reprint a single page,

enter its page number in both the From and To text boxes here or select

these page numbers with the spinner buttons. To reprint a range of

pages, put the first page number in the From text box and the last page

number in the To text box. Excel automatically deselects the All option

button and selects the Page(s) option button in the Page Range section

as soon as you start typing in the From or To text boxes.

Figure 5-4:

Selecting

the print

options to

use in the

Print dialog

box.

179

Chapter 5: Printing the Masterpiece

10_037377 ch05.qxp 11/16/06 9:39 AM Page 179

 Selection: Select this option button to have Excel print just the cells that

are currently selected in your workbook. (Yes, you must remember to

select these cells before opening the Print dialog box and choosing this

printing option).

 Active Sheet(s): Excel automatically displays and selects this option

button and prints all the information in whatever worksheets are

selected in your workbook. Normally, this means printing just the data in

the current worksheet. To print other worksheets in the workbook when

this option button is selected, hold down Ctrl while you click the sheet’s

tab. To include all the sheets between two sheet tabs, click the first one

and then hold Shift while you click the second tab (Excel selects all the

tabs in between).

 Entire Workbook: Select this option button to have Excel print all the

data in each of the worksheets in your workbook.

 Table: Select this option button to have Excel print only the data range

that you formatted as a table (see Chapter 3 for details). Note this

option button remains grayed out and unavailable if your worksheet

doesn’t contain any tables.

 Ignore Print Areas: Click this check box to put a check mark in it when

you want one of the other Print What options (Selection, Active Sheet(s),

or Entire Workbook) that you selected to be used in the printing rather

than the Print Area you defined (see “Setting and clearing the Print Area”

section later in this chapter for details).

 Number of Copies: To print more than one copy of the report, enter the

number of copies you want to print in the Number of Copies text box in

the Copies section — or use the spinner buttons to select the required

number.

 Collate: When you collate pages, you simply make separate stacks of

each complete report, rather than print all copies of page one, and then

all copies of page two, and so on. To have Excel collate each copy of the

report for you, select the Collate check box in the Copies section to put

a check mark in it.

After you finish choosing new print options, you can send the job to the

printer by clicking OK or pressing Enter. To use another printer that’s been

installed for Windows (Excel lists the current printer in the Name text box

and all printers installed for Windows on the Name pop-up list), select the

new printer on the Name drop-down menu in the Printer section at the top of

the dialog box before you start printing.

To open the report in the Print Preview window for last-minute checking

before sending it to the printer, click the Preview button (see “Checking the

Printout with Print Preview” earlier in this chapter).

180

Part II: Editing Without Tears

10_037377 ch05.qxp 11/16/06 9:39 AM Page 180

Setting and clearing the Print Area

Excel includes a special printing feature called the Print Area. You click Print

Area | Set Print Area on the Ribbon’s Page Layout tab or press Alt+PRS to

define any cell selection on a worksheet as the Print Area. After you define the

Print Area, Excel then prints this cell selection anytime you print the worksheet

(either with the Quick Print button, if you’ve added it to the Quick Access tool-

bar or by using the Office Button| Print command or one of its shortcuts).

Whenever you fool with the Print Area, you need to keep in mind that after

you define it, its cell range is the only one you can print (regardless of what

other Print What options you select in the Print dialog box) unless you click

the Ignore Print Areas check box and until you clear the Print Area.

To clear the Print Area (and therefore go back to the printing defaults Excel

establishes in the Print dialog box — see the preceding section, “Printing par-

ticular parts of the workbook” ) you just have to click Print Area | Clear Print

File on the Page Layout tab or simply press Alt+PRC.

You can also define and clear the Print Area from the Sheet tab of the Page

Setup dialog box opened by clicking the Dialog Box launcher in the Page

Setup group on in the Ribbon’s Page Layout tab. To define the Print Area from

this dialog box, click the Print Area text box on the Sheet tab to insert the

cursor and then select the cell range or ranges in the worksheet (remember-

ing that you can reduce the Page Setup dialog box to just this text box by

clicking its minimize box). To clear the Print Area from this dialog box, select

the cell addresses in the Print Area text box and press the Delete key.

My Page Was Set Up!

About the only thing the slightest bit complex in printing a worksheet is figur-

ing out how to get the pages right. Fortunately, the command buttons in the

Page Setup group on the Page Layout tab of the Ribbon give you a great deal

of control over what goes on which page.

Three groups of buttons on the Page Layout tab help you get your page set-

tings exactly as you want them: the Page Setup group, the Scale to Fit group,

and the Sheet Options group, all described in the following sections.

To see the effect of changes you make to the page setup settings in the

Worksheet area, put the worksheet into Page Layout view by clicking the

Page Layout button on the Status bar as you work with the command buttons

in Page Setup, Scale to Fit, and Sheet Options groups on the Page Layout tab

of the Ribbon.

181

Chapter 5: Printing the Masterpiece

10_037377 ch05.qxp 11/16/06 9:39 AM Page 181

Using the buttons in the Page Setup group

The Page Setup group of the Page Layout tab contains the following impor-

tant command buttons:

 Margins button to select one of three preset margins for the report or to

set custom margins on the Margins tab of the Page Setup dialog box.

(See “Massaging the margins” that follows in this chapter.)

 Orientation button to switch between Portrait and Landscape mode for

the printing (see “Getting the lay of the landscape” later in this chapter).

 Size button to select one of the preset paper sizes or to set a custom

size or to change the printing resolution or page number on the Page tab

of the Page Setup dialog box.

 Print Area button to set and clear the Print Area. (See “Setting and clear-

ing the Print Area” earlier in this chapter.)

 Breaks button to insert or remove page breaks. (See “Solving Page Break

Problems” later in this chapter.)

 Background button to open the Sheet Background dialog box where you

can select a new graphic image or photo to be used as a background for

all the worksheets in the workbook. (Note that this button changes to

Delete Background as soon as you select a background image.)

 Print Titles button to open up the Sheet tab of the Page Setup dialog box

where you can define rows of the worksheet to repeat at the top and

columns at the left as print titles for the report. (See “Putting out the

print titles” later in this chapter.)

Massaging the margins

The Normal margin settings that Excel applies to a new report uses standard

top, bottom, left, and right margins of

⁄4 inch with just over a

⁄4 inch separat-

ing the header and footer from the top and bottom margin, respectively.

In addition to the Normal margin settings, the program enables you to select

two other standard margins from the Margins button’s drop-down menu:

 Wide margins with 1-inch top, bottom, left, and right margins and

⁄2 inch

separating the header and footer from the top and bottom margin,

respectively.

 Narrow margins with a top and bottom margin of

⁄4 inch and a left and

right margin of

⁄4 inch, and

⁄2 inch separating the header and footer from

the top and bottom margin, respectively.

Frequently, you find yourself with a report that takes up a full printed page

and then just enough also to spill over onto a second, mostly empty, page.

182

Part II: Editing Without Tears

10_037377 ch05.qxp 11/16/06 9:39 AM Page 182

To squeeze the last column or the last few rows of the worksheet data onto

page 1, try selecting Narrow on the Margins button’s drop-down menu.

If that doesn’t do it, you can try manually adjusting the margins for the report

either from the Margins tab of the Page Setup dialog box or by dragging the

margin markers in the Print Preview window. To get more columns on a page,

try reducing the left and right margins. To get more rows on a page, try

reducing the top and bottom margins.

To open the Margins tab of the Page Setup dialog box (shown in Figure 5-5),

click Custom Margins on the Margins button’s drop-down menu. There, enter

the new settings in the Top, Bottom, Left, and Right text boxes — or select

the new margin settings with their respective spinner buttons.

Select one or both Center on Page options in the Margins tab of the Page

Setup dialog box (refer to Figure 5-5) to center a selection of data (that takes

up less than a full page) between the current margin settings. In the Center

on the Page section, select the Horizontally check box to center the data

between the left and right margins. Select the Vertically check box to center

the data between the top and bottom margins.

When you select the Show Margins check box on the Print Preview tab in the

Print Preview window (Alt+FWV) to change the margin settings, you can modify

the column widths as well as the margins. (See Figure 5-6.) To change one of

the margins, position the mouse pointer on the desired margin marker (the

pointer shape changes to a double-headed arrow) and drag the marker with

your mouse in the appropriate direction. When you release the mouse button,

Excel redraws the page, using the new margin setting. You may gain or lose

columns or rows, depending on what kind of adjustment you make. Changing

the column widths is the same story: Drag the column marker to the left or

right to decrease or increase the width of a particular column.

Figure 5-5:

Adjust your

report

margins

from the

Margins tab

in the Page

Setup dialog

box.

183

Chapter 5: Printing the Masterpiece

10_037377 ch05.qxp 11/16/06 9:39 AM Page 183

* * * Free Preview End * * *

Purchase Required To Gain Total Access

Visit www.propmgmtforms.com To Purchase Property Management Forms Package