Home > Store

Microsoft Excel 2010 On Demand, Portable Documents

Register your product to gain access to bonus material or receive a coupon.

Microsoft Excel 2010 On Demand, Portable Documents

eBook (Watermarked)

  • Your Price: $19.19
  • List Price: $23.99
  • About Watermarked eBooks
  • This PDF will be accessible from your Account page after purchase and requires the free Adobe® Reader® software to read it.

    The eBook requires no passwords or activation to read. We customize your eBook by discreetly watermarking it with your name, making it uniquely yours.

    Watermarked eBook FAQ


  • Copyright 2011
  • Edition: 1st
  • eBook (Watermarked)
  • ISBN-10: 0-13-249153-2
  • ISBN-13: 978-0-13-249153-2

What you need, when you need it!

Need answers quickly? Microsoft Excel 2010 on Demand provides those answers in a visual step-by-step format. We will show you exactly what to do through lots of full color illustrations and easy-to-follow instructions.



MCAS Exam Prep

More than 500 Essential Excel Tasks

Inside the Book

• Create workbooks more efficiently using the improved Ribbon interface

• Use data-analysis tools and techniques for better decision making

• Use organizing, processing, and presenting tools to create and analyze data

• Integrate data from external sources and add hyperlinks

• Use conditional formatting and Sparklines to visualize results

• Add impact to your data with PivotTable and PivotChart reports

• Organize information and add impact with clip art, SmartArt diagrams, tables, and charts

• Use SharePoint to collaborate and share documents

• Use the Excel Web App to view and edit documents in a browser

• Prepare for the Microsoft Certified Application Specialist (MCAS) exam

Bonus Online Content

Register your book at queondemand.com to gain access to:

• Workshops and related files

• Keyboard shortcuts

Sample Content

Table of Contents

Introduction xvii

Chapter 1 Getting Started with Excel 1

Starting Excel 2 New!

Viewing the Excel Window 3

Using the Ribbon 4 New!

Choosing Commands 5 New!

Working with Toolbars 6 New!

Choosing Dialog Box Options 8

Using the Status Bar 9

Creating a Blank Workbook 10

Creating a Workbook Using a Template 11

Opening an Existing Workbook 12

Converting an Existing Workbook 14

Using Task and Window Panes 15

Moving Around the Workbook 16

Arranging Windows 18

Getting Help While You Work 20

Saving a Workbook 22

Saving a Workbook with Different Formats 24

Checking Compatibility 26

Checking Accessibility 27 New!

Documenting Workbook Properties 28

Switching Views 29

Recovering a Workbook 30 New!

Maintaining and Repairing Office 32

Getting Updates on the Web 33

Closing a Workbook and Quitting Excel 34

Chapter 2 Basic Workbook Skills 35

Making Label Entries 36

Selecting Cells 37

Selecting Rows, Columns, and Special Ranges 38

Entering Labels on a Worksheet 40

Entering Values on a Worksheet 42

Entering Values Quickly with AutoFill 44

Editing Cell Contents 46

Clearing Cell Contents 47

Understanding How Excel Pastes Data 48

Storing Cell Contents 49

Copying Cell Contents 50 New!

Moving Cell Contents 52 New!

Inserting and Deleting Cell Contents 54

Finding and Replacing Cell Contents 56

Correcting Cell Contents with AutoCorrect 58 New!

Inserting Information the Smart Way 60 New!

Checking Spelling 62

Changing Proofing Options 63

Using Custom Dictionaries 64

Inserting Symbols 66

Finding the Right Words 67

Inserting Research Material 68

Translating Text to Another Language 69

Undoing and Redoing an Action 70

Chapter 3 Working with Formulas and Functions 71

Understanding Formulas 72

Creating a Simple Formula 74

Creating a Formula Using Formula AutoComplete 76

Editing a Formula 78New!

Understanding Cell Referencing 80

Using Absolute Cell References 81

Using Mixed Cell References 82

Using 3-D Cell References 83

Naming Cells and Ranges 84

Entering Named Cells and Ranges 86

Managing Names 88

Simplifying a Formula with Ranges 90

Displaying Calculations with the Status Bar 91

Calculating Totals with AutoSum 92

Performing One Time Calculations 94

Converting Formulas and Values 96

Correcting Calculation Errors 97

Correcting Formulas 98

Auditing a Worksheet 100

Locating Circular References 101

Performing Calculations Using Functions 102

Creating Functions 103

Creating Functions Using the Library 104 New!

Calculating Multiple Results 105

Using Nested Functions 106

Using Constants and Functions in Names 108

Chapter 4 Modifying Worksheets and Workbooks 109

Selecting and Naming a Worksheet 110

Inserting and Deleting a Worksheet 111

Moving and Copying a Worksheet 112

Hiding and Unhiding Worksheets and Workbooks 114

Selecting a Column or Row 116

Hiding and Unhiding a Column or Row 117

Inserting a Column or Row 118

Deleting a Column or Row 119

Adjusting Column Width and Row Height 120

Freezing and Unfreezing a Column or Row 122

Splitting a Worksheet into Panes 123

Showing and Hiding Workbook Elements 124

Zooming the View In and Out 125

Creating Custom Views 126

Saving a Workspace Layout 127

Creating a Template 128

Opening a Template 129

Changing a Template 130

Chapter 5 Formatting a Worksheet 131

Formatting Numbers 132

Formatting Text 134

Applying Conditional Formatting 136

Applying Specialized Conditional Formatting 138

Creating Conditional Formatting 140

Clearing Conditional Formatting 141

Managing Conditional Formatting 142

Finding Conditional Formatting 143

Creating Sparkline Formatting 144 New!

Changing Data Alignment 146

Controlling Text Flow 148

Changing Data Color 149

Adding Color and Patterns to Cells 150

Adding Custom Colors 151

Adding Borders to Cells 152

Formatting Tabs and Background 154

Copying Cell Formats 155

Understanding Color Themes 156

Viewing and Applying a Theme 157 New!

Creating Theme Colors 158

Choosing Theme Fonts 160

Choosing Theme Effects 162

Creating a Custom Theme 163

Choosing a Custom Theme 164

Applying and Creating Cell Styles 166

Modifying a Cell Style 168

Finding and Replacing Cell Formatting 170

Chapter 6 Viewing and Printing Worksheets and Workbooks 171

Setting Up the Page 172

Adjusting Page Margins 174

Adding Headers and Footers 176

Inserting Page Breaks 178

Customizing Worksheet Printing 180

Setting the Print Area 182

Previewing a Worksheet 183 New!

Printing a Worksheet and Workbook 184 New!

Creating a PDF Document 185

Creating an XPS Document 186

Chapter 7 Inserting and Modifying Graphics 187

Locating and Inserting Clip Art 188 New!

Inserting Media Clips 189

Accessing Clip Art on the Web 190

Organizing Clips into Categories 192

Adding and Removing Clips 194

Inserting a Picture 195

Inserting a Picture Screen Shot 196 New!

Adding an Artistic Style to a Picture 197 New!

Adding a Quick Style to a Picture 198

Applying a Shape to a Picture 199 New!

Applying a Border to a Picture 200

Applying Picture Effects 201

Modifying Picture Size 202

Compressing a Picture 204 New!

Correcting a Picture 205 New!

Recoloring a Picture 206 New!

Cropping and Rotating a Picture 208 New!

Removing a Picture Background 210 New!

Creating WordArt Text 211

Formatting WordArt Text 212

Applying WordArt Text Effects 214

Modifying WordArt Text Position 215

Creating SmartArt Graphics 216 New!

Using the Text Pane with SmartArt Graphics 218

Modifying a SmartArt Graphic 220

Resizing a SmartArt Graphic 221

Formatting a SmartArt Graphic 222

Formatting a Shape in a SmartArt Graphic 224

Adding Pictures to a SmartArt Graphic 226 New!

Creating an Organization Chart 227

Modifying an Organization Chart 228

Chapter 8 Drawing and Modifying Shapes 229

Drawing and Resizing Shapes 230

Inserting Multiple Shapes 232

Adding Text to a Shape 233

Drawing Lines and Arrows 234

Creating and Editing Freeforms 236

Copying and Moving Objects 237 New!

Adding a Quick Style to a Shape 238

Adding a Quick Style to Shape Text 239

Applying Color Fills 240

Applying Picture Fills 242

Applying Texture Fills 243

Applying Gradient Fills 244 New!

Applying Shape Effects 246

Adding 3-D Effects to a Shape 248

Adding 3-D Rotation Effects to a Shape 250

Creating Shadows 251

Aligning and Distributing Objects 252

Connecting Shapes 254

Selecting Objects Using the Selection Pane 255

Changing Stacking Order 256

Rotating and Flipping a Shape 257

Grouping and Ungrouping Shapes 258

Adding a Shape to the Clip Organizer 260

Chapter 9 Creating and Modifying Charts 261

Understanding Chart Terminology 262

Choosing the Right Type of Chart 263

Creating a Chart 264

Editing a Chart 266

Moving and Resizing a Chart 268

Selecting Chart Elements 269

Changing a Chart Type 270

Changing a Chart Layout and Style 272 New!

Formatting Chart Elements 274 New!

Changing Chart Gridlines and Axes 276

Changing Chart Titles 278

Changing Chart Labels 280

Pulling Out a Pie Slice 282

Formatting Chart Data Series 283

Formatting Chart Text 284

Formatting Line and Bar Charts 285

Changing the Chart Background 286

Enhancing a Chart 287

Editing Chart Data 288 New!

Adding and Deleting a Data Series 290

Saving a Chart Template 292

Managing Chart Templates 294

Chapter 10 Analyzing Worksheet Data 295

Understanding Tables 296

Creating a Table 297

Formatting a Table 298

Creating or Modifying a Table Style 299

Formatting Table Elements 300

Creating Calculations in a Table 301

Working with Tables 302

Removing Table Rows and Columns 304

Entering Data in a Table Using a Drop-Down List 305

Sorting Data in a Table 306

Displaying Parts of a Table with AutoFilter 308 New!

Creating Custom Searches 309

Analyzing Data Using a PivotTable or PivotChart 310

Updating a PivotTable or PivotChart 311

Modifying a PivotTable 312 New!

Formatting a PivotTable 313

Working with PivotTable Data 314 New!

Using Slicers to Filter a PivotTable 316 New!

Charting a PivotTable 318 New!

Creating Groups and Outlines 320

Converting Text to Columns 321

Adding Data Validation to a Worksheet 322

Creating a Drop-Down List 323

Exporting a Table to a SharePoint List 324

Chapter 11 Building More Powerful Worksheets 325

Using Data Analysis Tools 326

Using the Euro Conversion 327

Looking at Alternatives with Data Tables 328

Asking “What If” with Goal Seek 329

Creating Scenarios 330

Using Solver 332 New!

Using Lookup and Reference Functions 334

Using Text Functions 336

Summarizing Data Using Subtotals 337

Summarizing Data Using Functions 338

Using Date & Time Functions 340

Using Logical Functions 342

Using Financial Functions 343

Using Math Functions 344

Using Statistical Functions 345 New!

Using Information Functions 346

Using Compatibility Functions 347 New!

Chapter 12 Protecting and Securing a Workbook 349

Inspecting Workbooks 350

Protecting a Worksheet 352

Locking and Unlocking Worksheet Cells 354

Adding Security Encryption to a Workbook 355

Adding Password Protection to a Workbook 356

Adding a Digital Signature 358

Adding a Signature Line 360

Avoiding Harmful Attacks 362

Using the Trust Center 364 New!

Selecting Trusted Publishers and Locations 365

Setting Document Related Security Options 366 New!

Setting Add-in Security Options 368

Setting ActiveX Security Options 369

Setting Macro Security Options 370

Changing Message Bar Security Options 371

Setting Privacy Options 372

Setting External Content Security Options 374

Working with Office Safe Modes 376

Marking a Workbook as Read-Only 378

Chapter 13 Reviewing and Sharing Workbook Data 379

Sharing Workbooks 380

Creating and Reading a Cell Comment 382

Editing and Deleting a Cell Comment 383

Tracking Changes 384

Sending a Workbook for Review Using E-Mail 386

Sending a Workbook by Internet Fax 387

Working with XML 388 New!

Creating an XML Data Map 390

Exporting and Saving Data in XML 392

Sharing Information Between Programs 394

Exporting and Importing Data 396 New!

Linking and Embedding Files 398 New!

Consolidating Data 400

Linking Data 402

Getting External Data 403

Managing Connections 404

Getting Query Data from a Database 406

Getting Data from Microsoft Access 408

Getting Text Data 410

Chapter 14 Publishing Data on the Web 411

Opening a Workbook as a Web Page 412

Previewing a Web Page 413

Creating a Hyperlink 414

Formatting a Hyperlink 416

Changing Web Page Options 41714

Saving a Worksheet as a Web Page 418

Publishing a Web Page 420

Copying a Web Table to a Worksheet 421

Creating Refreshable Web Queries 422

Getting Data from Web Queries 423

Saving Web Queries 424

Accessing Office Information on the Web 425

Getting Documents from the Web 426

Chapter 15 Tools for Working More Efficiently 427

Getting General Excel Options 428

Setting New Workbook Options 429

Setting Editing Options 430 New!

Setting Image and Chart Options 432 New!

Setting Display View Options 433 New!

Setting Workbook and Worksheet Display Options 434 New!

Setting Advanced Options 436 New!

Setting Formula Options 437

Changing Advanced Document Properties 438

Changing Research Options 439

Customizing the Way You Create Objects 440

Accessing Commands Not in the Ribbon 441

Managing Pictures 442

Using Multiple Languages 444 New!

Chapter 16 Expanding Excel Functionality 445

Viewing and Managing Add-ins 446

Loading and Unloading Add-ins 448

Enhancing a Workbook with VBA 450

Viewing the Visual Basic Editor 452

Setting Developer Options 453

Understanding How Macros Automate Your Work 454

Recording a Macro 455 New!

Creating a Macro 456

Running a Macro 457

Controlling a Macro 458

Adding a Digital Signature to a Macro Project 460

Assigning a Macro to a Toolbar 461

Saving a Workbook with Macros 462

Opening a Workbook with Macros 463

Inserting ActiveX Controls 464

Using ActiveX Controls 466

Setting ActiveX Control Properties 467

Adding VBA Code to an ActiveX Control 468

Playing a Movie Using an ActiveX Control 469

Changing the Document Information Panel 470

Chapter 17 Working Together on Office Documents 471

Getting Started with Office Web Apps 472 New!

Setting Up to Use Office Web Apps 474 New!

Saving and Opening Documents with Windows Live 476 New!

Comparing the Desktop App to Web App 478 New!

Accessing Documents on Windows Live 480 New!

Working with Folders on Windows Live 482

Setting Folder Permissions on Windows Live 484

Creating Office Documents on Windows Live 485 New!

Working with Documents on Windows Live 486

Downloading Documents from Windows Live 488

Downloading or Saving Documents in Office Web Apps 490 New!

Collaborating with Documents on Windows Live 492 New!

Working with SharePoint Workspaces 494 New!

Sharing Documents in a Groove Workspace 496 New!

Inviting Others to a Groove Workspace 497 New!

Saving a Document to a SharePoint Server 498 New!

Using Office Mobile 2010 500 New!

Workshops: Putting It All Together 501

Project 1: Creating a Drop-Down List 501

Project 2: Adding a Form Control 503

Project 3: Adding Conditional Formatting 504

Project 4: Creating a VBA Script 506

Project 5: Creating a VBA Interface 508

Want More Projects 513

New Features 515 New!

Microsoft Certified Applications Specialist 521

Index 529


Submit Errata

More Information

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.


Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.


If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.


This site is not directed to children under the age of 13.


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020