Contents

Introduction

1 Getting Started with Excel

Starting Excel

Viewing the Excel Window

Using the Ribbon

Choosing Commands

Working with Toolbars

Choosing Dialog Box Options

Using the Status Bar

Creating a Blank Workbook

Creating a Workbook Using a Template

Opening an Existing Workbook

Converting an Existing Workbook

Using Task and Window Panes

Moving Around the Workbook

Arranging Windows

Getting Help While You Work

Saving a Workbook

Saving a Workbook with Different Formats

Checking Compatibility

Checking Accessibility

Documenting Workbook Properties

Switching Views

Recovering a Workbook

Maintaining and Repairing Office

Getting Updates on the Web

Closing a Workbook and Quitting Excel

2 Basic Workbook Skills

Making Label Entries

Selecting Cells

Selecting Rows, Columns, and Special Ranges

Entering Labels on a Worksheet

Entering Values on a Worksheet

Entering Values Quickly with AutoFill

Editing Cell Contents

Clearing Cell Contents

Understanding How Excel Pastes Data

Storing Cell Contents

Copying Cell Contents

Moving Cell Contents

Inserting and Deleting Cell Contents

Finding and Replacing Cell Contents

Correcting Cell Contents with AutoCorrect

Inserting Information the Smart Way

Checking Spelling

Changing Proofing Options

Using Custom Dictionaries

Inserting Symbols

Finding the Right Words

Inserting Research Material

Translating Text to Another Language

Undoing and Redoing an Action

3 Working with Formulas and Functions

Understanding Formulas

Creating a Simple Formula

Creating a Formula Using Formula AutoComplete

Editing a Formula

Understanding Cell Referencing

Using Absolute Cell References

Using Mixed Cell References

Using 3-D Cell References

Naming Cells and Ranges

Entering Named Cells and Ranges

Managing Names

Simplifying a Formula with Ranges

Displaying Calculations with the Status Bar

Calculating Totals with AutoSum

Performing One Time Calculations

Converting Formulas and Values

Correcting Calculation Errors

Correcting Formulas

Auditing a Worksheet

Locating Circular References

Performing Calculations Using Functions

Creating Functions

Creating Functions Using the Library

Calculating Multiple Results

Using Nested Functions

Using Constants and Functions in Names

4 Modifying Worksheets and Workbooks

Selecting and Naming a Worksheet

Inserting and Deleting a Worksheet

Moving and Copying a Worksheet

Hiding and Unhiding Worksheets and Workbooks

Selecting a Column or Row

Hiding and Unhiding a Column or Row

Inserting a Column or Row

Deleting a Column or Row

Adjusting Column Width and Row Height

Freezing and Unfreezing a Column or Row

Splitting a Worksheet into Panes

Showing and Hiding Workbook Elements

Zooming the View In and Out

Creating Custom Views

Saving a Workspace Layout

Creating a Template

Opening a Template

Changing a Template

5 Formatting a Worksheet

Formatting Numbers

Formatting Text

Applying Conditional Formatting

Applying Specialized Conditional Formatting

Creating Conditional Formatting

Clearing Conditional Formatting

Managing Conditional Formatting

Finding Conditional Formatting

Creating Sparkline Formatting

Changing Data Alignment

Controlling Text Flow

Changing Data Color

Adding Color and Patterns to Cells

Adding Custom Colors

Adding Borders to Cells

Formatting Tabs and Background

Copying Cell Formats

Understanding Color Themes

Viewing and Applying a Theme

Creating Theme Colors

Choosing Theme Fonts

Choosing Theme Effects

Creating a Custom Theme

Choosing a Custom Theme

Applying and Creating Cell Styles

Modifying a Cell Style

Finding and Replacing Cell Formatting

6 Viewing and Printing Worksheets and Workbooks

Setting Up the Page

Adjusting Page Margins

Adding Headers and Footers

Inserting Page Breaks

Customizing Worksheet Printing

Setting the Print Area

Previewing a Worksheet

Printing a Worksheet and Workbook

Creating a PDF Document

Creating an XPS Document

7 Inserting and Modifying Graphics

Locating and Inserting Clip Art

Inserting Media Clips

Accessing Clip Art on the Web

Organizing Clips into Categories

Adding and Removing Clips

Inserting a Picture

Inserting a Picture Screen Shot

Adding an Artistic Style to a Picture

Adding a Quick Style to a Picture

Applying a Shape to a Picture

Applying a Border to a Picture

Applying Picture Effects

Modifying Picture Size

Compressing a Picture

Correcting a Picture

Recoloring a Picture

Cropping and Rotating a Picture

Removing a Picture Background

Creating WordArt Text

Formatting WordArt Text

Applying WordArt Text Effects

Modifying WordArt Text Position

Creating SmartArt Graphics

Using the Text Pane with SmartArt Graphics

Modifying a SmartArt Graphic

Resizing a SmartArt Graphic

Formatting a SmartArt Graphic

Formatting a Shape in a SmartArt Graphic

Adding Pictures to a SmartArt Graphic

Creating an Organization Chart

Modifying an Organization Chart

8 Drawing and Modifying Shapes

Drawing and Resizing Shapes

Inserting Multiple Shapes

Adding Text to a Shape

Drawing Lines and Arrows

Creating and Editing Freeforms

Copying and Moving Objects

Adding a Quick Style to a Shape

Adding a Quick Style to Shape Text

Applying Color Fills

Applying Picture Fills

Applying Texture Fills

Applying Gradient Fills

Applying Shape Effects

Adding 3-D Effects to a Shape

Adding 3-D Rotation Effects to a Shape

Creating Shadows

Aligning and Distributing Objects

Connecting Shapes

Selecting Objects Using the Selection Pane

Changing Stacking Order

Rotating and Flipping a Shape

Grouping and Ungrouping Shapes

Adding a Shape to the Clip Organizer

9 Creating and Modifying Charts

Understanding Chart Terminology

Choosing the Right Type of Chart

Creating a Chart

Editing a Chart

Moving and Resizing a Chart

Selecting Chart Elements

Changing a Chart Type

Changing a Chart Layout and Style

Formatting Chart Elements

Changing Chart Gridlines and Axes

Changing Chart Titles

Changing Chart Labels

Pulling Out a Pie Slice

Formatting Chart Data Series

Formatting Chart Text

Formatting Line and Bar Charts

Changing the Chart Background

Enhancing a Chart

Editing Chart Data

Adding and Deleting a Data Series

Saving a Chart Template

Managing Chart Templates

10 Analyzing Worksheet Data

Understanding Tables

Creating a Table

Formatting a Table

Creating or Modifying a Table Style

Formatting Table Elements

Creating Calculations in a Table

Working with Tables

Removing Table Rows and Columns

Entering Data in a Table Using a Drop-Down List

Sorting Data in a Table

Displaying Parts of a Table with AutoFilter

Creating Custom Searches

Analyzing Data Using a PivotTable or PivotChart

Updating a PivotTable or PivotChart

Modifying a PivotTable

Formatting a PivotTable

Working with PivotTable Data

Using Slicers to Filter a PivotTable

Charting a PivotTable

Creating Groups and Outlines

Converting Text to Columns

Adding Data Validation to a Worksheet

Creating a Drop-Down List

Exporting a Table to a SharePoint List

11 Building More Powerful Worksheets

Using Data Analysis Tools

Using the Euro Conversion

Looking at Alternatives with Data Tables

Asking “What If” with Goal Seek

Creating Scenarios

Using Solver

Using Lookup and Reference Functions

Using Text Functions

Summarizing Data Using Subtotals

Summarizing Data Using Functions

Using Date & Time Functions

Using Logical Functions

Using Financial Functions

Using Math Functions

Using Statistical Functions

Using Information Functions

Using Compatibility Functions

12 Protecting and Securing a Workbook

Inspecting Workbooks

Protecting a Worksheet

Locking and Unlocking Worksheet Cells

Adding Security Encryption to a Workbook

Adding Password Protection to a Workbook

Adding a Digital Signature

Adding a Signature Line

Avoiding Harmful Attacks

Using the Trust Center

Selecting Trusted Publishers and Locations

Setting Document Related Security Options

Setting Add-in Security Options

Setting ActiveX Security Options

Setting Macro Security Options

Changing Message Bar Security Options

Setting Privacy Options

Setting External Content Security Options

Working with Office Safe Modes

Marking a Workbook as Read-Only

13 Reviewing and Sharing Workbook Data

Sharing Workbooks

Creating and Reading a Cell Comment

Editing and Deleting a Cell Comment

Tracking Changes

Sending a Workbook for Review Using E-Mail

Sending a Workbook by Internet Fax

Working with XML

Creating an XML Data Map

Exporting and Saving Data in XML

Sharing Information Between Programs

Exporting and Importing Data

Linking and Embedding Files

Consolidating Data

Linking Data

Getting External Data

Managing Connections

Getting Query Data from a Database

Getting Data from Microsoft Access

Getting Text Data

14 Publishing Data on the Web

Opening a Workbook as a Web Page

Previewing a Web Page

Creating a Hyperlink

Formatting a Hyperlink

Changing Web Page Options

Saving a Worksheet as a Web Page

Publishing a Web Page

Copying a Web Table to a Worksheet

Creating Refreshable Web Queries

Getting Data from Web Queries

Saving Web Queries

Accessing Office Information on the Web

Getting Documents from the Web

15 Tools for Working More Efficiently

Getting General Excel Options

Setting New Workbook Options

Setting Editing Options

Setting Image and Chart Options

Setting Display View Options

Setting Workbook and Worksheet Display Options

Setting Advanced Options

Setting Formula Options

Changing Advanced Document Properties

Changing Research Options

Customizing the Way You Create Objects

Accessing Commands Not in the Ribbon

Managing Pictures

Using Multiple Languages

16 Expanding Excel Functionality

Viewing and Managing Add-ins

Loading and Unloading Add-ins

Enhancing a Workbook with VBA

Viewing the Visual Basic Editor

Setting Developer Options

Understanding How Macros Automate Your Work

Recording a Macro

Creating a Macro

Running a Macro

Controlling a Macro

Adding a Digital Signature to a Macro Project

Assigning a Macro to a Toolbar

Saving a Workbook with Macros

Opening a Workbook with Macros

Inserting ActiveX Controls

Using ActiveX Controls

Setting ActiveX Control Properties

Adding VBA Code to an ActiveX Control

Playing a Movie Using an ActiveX Control

Changing the Document Information Panel

17 Working Together on Office Documents

Getting Started with Office Web Apps

Setting Up to Use Office Web Apps

Saving and Opening Documents with Windows Live

Comparing the Desktop App to Web App

Accessing Documents on Windows Live

Working with Folders on Windows Live

Setting Folder Permissions on Windows Live

Creating Office Documents on Windows Live

Working with Documents on Windows Live

Downloading Documents from Windows Live

Downloading or Saving Documents in Office Web Apps

Collaborating with Documents on Windows Live

Working with SharePoint Workspaces

Sharing Documents in a Groove Workspace

Inviting Others to a Groove Workspace

Saving a Document to a SharePoint Server

Using Office Mobile 2010

W Workshops: Putting It All Together

Project 1: Creating a Drop-Down List

Project 2: Adding a Form Control

Project 3: Adding Conditional Formatting

Project 4: Creating a VBA Script

Project 5: Creating a VBA Interface

Want More Projects

New Features

Microsoft Certified Applications Specialist

Index