Excel® 2010 For Dummies®

Table of Contents


About This Book

How to Use This Book

What You Can Safely Ignore

Foolish Assumptions

How This Book Is Organized

Part I: Getting In on the Ground Floor

Part II: Editing without Tears

Part III: Getting Organized and Staying That Way

Part IV: Digging Data Analysis

Part V: Life beyond the Spreadsheet

Part VI: The Part of Tens

Conventions Used in This Book

Icons Used in This Book

Where to Go from Here

Part I: Getting In on the Ground Floor

Chapter 1: The Excel 2010 User Experience

Excel’s Ribbon User Interface

Going Backstage via File

Bragging about the Ribbon

Customizing the Quick Access toolbar

Having fun with the Formula bar

What to do in the Worksheet area

Showing off the Status bar

Launching and Quitting Excel

Starting Excel from the Start menu

Starting Excel from the Windows XP Start menu

Pinning a Microsoft Excel 2010 option on your Windows Start menu

Adding a Microsoft Excel 2010 shortcut to your Windows desktop

Adding Excel to the Windows Quick Launch toolbar

Pinning an Excel icon to the Windows 7 taskbar

Exiting Excel

Help Is on the Way

Migrating to Excel 2010 from Earlier Versions Using Pull-down Menus

Cutting the Ribbon down to size

Finding the Standard toolbar buttons equivalents

Finding the Formatting toolbar buttons equivalents

Putting the Excel Quick Access toolbar to good use during the transition

Getting good to go with Excel 2010

Chapter 2: Creating a Spreadsheet from Scratch

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

The ins and outs of data entry

You must remember this . . .

Doing the Data-Entry Thing

It Takes All Types

The telltale signs of text

How Excel evaluates its values

Fabricating those fabulous formulas!

If you want it, just point it out

Altering the natural order of operations

Formula flub-ups

Fixing Those Data Entry Flub-Ups

You really AutoCorrect that for me

Cell editing etiquette

Taking the Drudgery out of Data Entry

I’m just not complete without you

Fill ’er up with AutoFill

Inserting special symbols

Entries all around the block

Data entry express

How to Make Your Formulas Function Even Better

Inserting a function into a formula with the Insert Function button

Editing a function with the Insert Function button

I’d be totally lost without AutoSum

Making Sure That the Data Is Safe and Sound

The Save As dialog box in Windows 7 and Windows Vista

The Save As dialog box in Windows XP

Changing the default file location

The difference between the XLSX and XLS file format

Saving the Workbook as a PDF File

Document Recovery to the Rescue

Part II: Editing without Tears

Chapter 3: Making It All Look Pretty

Choosing a Select Group of Cells

Point-and-click cell selections

Keyboard cell selections

Having Fun with the Format as Table Gallery

Cell Formatting from the Home Tab

Formatting Cells Close to the Source with the Mini-Toolbar

Using the Format Cells Dialog Box

Getting comfortable with the number formats

The values behind the formatting

Make it a date!

Ogling some of the other number formats

Calibrating Columns

Rambling rows

Now you see it, now you don’t

Futzing with the Fonts

Altering the Alignment

Intent on indents

From top to bottom

Tampering with how the text wraps

Reorienting cell entries

Shrink to fit

Bring on the borders!

Applying fill colors, patterns, and gradient effects to cells

Do It in Styles

Creating a new style for the gallery

Copying custom styles from one workbook into another

Fooling Around with the Format Painter

Conditional Formatting

Conditionally formatting values with sets of graphic scales and markers

Highlighting cells according to what ranges the values fall into

Chapter 4: Going Through Changes

Opening the Darned Thing Up for Editing

Operating the Open dialog box

Opening more than one workbook at a time

Opening recently edited workbooks

When you don’t know where to find them

Opening files with a twist

Much Ado about Undo

Undo is Redo the second time around

What ya gonna do when you can’t Undo?

Doing the Old Drag-and-Drop Thing

Copies, drag-and-drop style

Insertions courtesy of drag and drop

Formulas on AutoFill

Relatively speaking

Some things are absolutes!

Cut and paste, digital style

Paste it again, Sam . . .

Keeping pace with Paste Options

Paste it from the Clipboard task pane

So what’s so special about Paste Special?

Let’s Be Clear about Deleting Stuff

Sounding the all clear!

Get these cells outta here!

Staying in Step with Insert

Stamping Out Your Spelling Errors

Stamping Out Errors with Text to Speech

Chapter 5: Printing the Masterpiece

Taking a Gander at the Pages in Page Layout View

Checking and Printing a Report from the Print Panel

Printing Just the Current Worksheet

My Page Was Set Up!

Using the buttons in the Page Setup group

Using the buttons in the Scale to Fit group

Using the Print buttons in the Sheet Options group

From Header to Footer

Adding an Auto Header or Auto Footer

Creating a custom header or footer

Solving Page Break Problems

Letting Your Formulas All Hang Out

Part III: Getting Organized and Staying That Way

Chapter 6: Maintaining the Worksheet

Zeroing In with Zoom

Splitting the Difference

Fixed Headings Courtesy of Freeze Panes

Electronic Sticky Notes

Adding a comment to a cell

Comments in review

Editing the comments in a worksheet

Getting your comments in print

The Cell Name Game

If I only had a name . . .

Name that formula!

Naming constants

Seek and Ye Shall Find . . .

You Can Be Replaced!

Do Your Research

You Can Be So Calculating

Putting on the Protection

Chapter 7: Maintaining Multiple Worksheets

Juggling Worksheets

Sliding between the sheets

Editing en masse

Don’t Short-Sheet Me!

A worksheet by any other name . . .

A sheet tab by any other color . . .

Getting your sheets in order

Opening Windows on Your Worksheets

Comparing Two Worksheets Side by Side

Moving and Copying Sheets to Other Workbooks

To Sum Up . . .

Part IV: Digging Data Analysis

Chapter 8: Doing What-If Analysis

Playing What-If with Data Tables

Creating a one-variable data table

Creating a two-variable data table

Playing What-If with Goal Seeking

Examining Different Cases with Scenario Manager

Setting up the various scenarios

Producing a summary report

Chapter 9: Playing with Pivot Tables

Pivot Tables: The Ultimate Data Summary

Producing a Pivot Table

Formatting a Pivot Table

Refining the Pivot Table style

Formatting the values in the pivot table

Sorting and Filtering the Pivot Table Data

Filtering the report

Filtering individual column and row fields

Filtering with slicers

Sorting the pivot table

Modifying a Pivot Table

Modifying the pivot table fields

Pivoting the table’s fields

Modifying the table’s summary function

Get Smart with a Pivot Chart

Moving a pivot chart to its own sheet

Filtering a pivot chart

Formatting a pivot chart

Part V: Life beyond the Spreadsheet

Chapter 10: Charming Charts and Gorgeous Graphics

Making Professional-Looking Charts

Creating a new chart

Moving and resizing an embedded chart in a worksheet

Moving an embedded chart onto its own chart sheet

Customizing the chart type and style from the Design tab

Customizing chart elements from the Layout tab

Editing the titles in a chart

Formatting chart elements from the Format tab

Adding Great Looking Graphics

Sparking up the data with sparklines

Telling all with a text box

The wonderful world of clip art

Inserting pictures from graphics files

Editing clip art and imported pictures

Formatting clip art and imported pictures

Adding preset graphic shapes

Working with WordArt

Make mine SmartArt

Screenshots anyone?

Theme for a day

Controlling How Graphic Objects Overlap

Reordering the layering of graphic objects

Grouping graphic objects

Hiding graphic objects

Printing Just the Charts

Chapter 11: Getting on the Data List

Creating a Data List

Adding records to a data list

Sorting Records in a Data List

Sorting records on a single field

Sorting records on multiple fields

Filtering the Records in a Data List

Using ready-made number filters

Using ready-made date filters

Getting creative with custom filtering

Importing External Data

Querying an Access database table

Performing a New Web query

Chapter 12: Linking, Automating, and Sharing Spreadsheets

Using Add-Ins in Excel 2010

Adding Hyperlinks to a Worksheet

Automating Commands with Macros

Recording new macros

Running macros

Assigning macros to the Ribbon and the Quick Access toolbar

Sharing Worksheets

Sending a workbook via e-mail

Sharing a workbook on a SharePoint Web site

Uploading workbooks to your SkyDrive and editing them with the Excel Web App

Part VI: The Part of Tens

Chapter 13: Top Ten Features in Excel 2010

Chapter 14: Top Ten Beginner Basics

Chapter 15: The Ten Commandments of Excel 2010

Excel® 2010 For Dummies®

by Greg Harvey, PhD


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.


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. 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 importantly, present the new user interface.

Publisher’s Acknowledgments

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

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

Acquisitions and Editorial

Project Editor: Nicole Sholly

Senior Acquisitions Editor: Katie Feltman

Copy Editor: Brian Walls

Technical Editors: Mike Talley, Joyce Nielsen

Editorial Manager: Kevin Kirschner

Editorial Assistant: Amanda Graham

Senior Editorial Assistant: Cherie Case

Cartoons: Rich Tennant (

Composition Services

Project Coordinator: Patrick Redmond

Layout and Graphics: Ashley Chamberlain, Joyce Haughey, Christine Williams

Proofreader: Linda Seifert

Indexer: Sharon Shock

Publishing and Editorial for Technology Dummies

Richard Swadley, Vice President and Executive Group Publisher

Andy Cummings, Vice President and Publisher

Mary Bednarek, Executive Acquisitions Director

Mary C. Corder, Editorial Director

Publishing for Consumer Dummies

Diane Graves Steele, Vice President and Publisher

Composition Services

Debbie Stailey, Director of Composition Services


I’m very proud to present you with Excel 2010 For Dummies, the latest version of everybody’s favorite book on Microsoft Office Excel for readers with no intention whatsoever of becoming spreadsheet gurus.

Excel 2010 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 don’t attempt to cover charting, data lists, or data analysis in the same definitive way as spreadsheets: 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.

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 usually giving you only the most efficient ways to do a particular task. Later, 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 similar to a reference book. You can start by looking up the topic you need information about (in either the Table of Contents or the index) and then refer directly to the section of interest. I explain most topics conversationally (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). Often, these sections are 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.)

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 Windows 7, Windows Vista, or Windows XP and on which Microsoft Office Excel 2010 is installed. Having said that, I don’t assume that you’ve ever launched Excel 2010, let alone done anything with it.

warning_bomb.eps This book is intended for users of Microsoft Office Excel 2010. If you’re using Excel for Windows version Excel 97 through 2003, the information in this book will only confuse and confound you because only Excel 2007 works similar to the 2010 version that this book describes.

If you’re working with a version of Excel earlier than Excel 2007, please put this book down slowly and pick up a copy of Excel 2003 For Dummies instead.

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 divided further into loosely related sections that cover the basics of the topic at hand. However, don’t get hung up on following the structure of the book; ultimately, it doesn’t matter whether you find out how to edit the worksheet before you learn how to format it, or whether you figure out printing before you learn editing. The important 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 Ribbon interface before you move on to how to create new worksheets in Chapter 2.

Part II: Editing without Tears

In this part, I show you how to edit spreadsheets to make them look good, including how to make major editing changes 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. Read Chapter 5 for the skinny on printing 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 introduces 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 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.

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 2010 (and boy was it hard keeping it to just ten). Chapter 14 gives you the top ten beginner basics you need to know as you start using this program. Chapter 15 gives you the King James Version of the Ten Commandments of Excel 2010. 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).

Throughout the book, you’ll find Ribbon command sequences (the name on the tab on the Ribbon and the command button you select) separated by a command arrow, as in:


This shorthand is 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 isn’t displayed already) 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 command 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 command arrows, as in:

FormulasCalculation OptionsManual

This shorthand is the Ribbon command sequence that turns on manual recalculation in Excel. It says that you click the Formulas tab (if it isn’t displayed already) 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 specific 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.

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 while you type the hot key letter (though again, type only lowercase letters unless you see the Shift key in the sequence, as in Ctrl+Shift+C).

Excel 2010 uses only one pull-down menu (File) and one toolbar (the Quick Access toolbar). You open the File pull-down menu by clicking the File tab or pressing Alt+F. The Quick Access toolbar with its four buttons appears to the immediate right of the File tab.

Finally, if you’re really observant, you may notice a discrepancy in how the names of dialog box options (such as headings, option buttons, and check boxes) appear in the 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 describing its use.

Icons Used in This Book

The following icons are placed in the margins to point out stuff you may or may not want to read.

technicalstuff.eps This icon alerts you to nerdy discussions that you may well want to skip (or read when no one else is around).

tip.eps This icon alerts you to shortcuts or other valuable hints related to the topic at hand.

remember.eps This icon alerts you to information to keep in mind if you want to meet with a modicum of success.

warning_bomb.eps This icon alerts you to information to keep in mind if you want to avert complete disaster.

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. If you’re someone with some experience with earlier versions of Excel, I want you to head directly to the section, “Migrating to Excel 2010 from Earlier Versions Using Pull-down Menus” in Chapter 1, where you find out how to stay calm as you become familiar and, yes, comfortable with the 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.

Part I

Getting In on the Ground Floor


In this part . . .

In this part, I break down the Excel user interface and make sense of the tabs and command buttons you’re going to face day after day after day. Of course, it does you no good just to know what’s what onscreen; you need to be able to use all these bells and whistles (or buttons and boxes in this case). Therefore, I also show you how to use some of the more prominent buttons and boxes to enter your spreadsheet data. From this humble beginning, it’s a quick trip to total screen mastery.