Home > Store

Microsoft Excel 2010 On Demand

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

Microsoft Excel 2010 On Demand


  • Sorry, this book is no longer in print.
Not for Sale

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 PDF reading software, such as Acrobat® Reader®.

    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
  • Dimensions: 7-1/2" x 9-1/8"
  • Edition: 1st
  • Book
  • ISBN-10: 0-7897-4277-2
  • ISBN-13: 978-0-7897-4277-3
  • 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

Online Sample Chapter

Working with Formulas and Functions in Excel 2010

Sample Pages

Download the sample pages (includes Chapter 3 and Index)

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