Welcome to

 

         Lifeline to Learning®

          The online learning center for life's most important skills

                                                    About Our Courseware
    All Lifeline to Learning courseware has been developed, tested
    and proven effective over many years. Each program has been
    created as a tool to be used either by an instructor in a workshop
    environment, or for individual use.  All of our courses are user
    friendly and easily downloaded.

    System Requirements:   Adobe Reader, level 8 or newer
                                                   Microsoft Office, Version 2007
                                                      Get Adobe Reader

Microsoft Excel Intermediate
                                                                                                Table of Contents of The Instructor's Guide

Section 1: Advanced File Tasks..................................................................................................... 8

Lesson 1.1: Using My Computer within Excel ..................................................................................... 9

Using the My Places Toolbar ............................................................................................................................. 10

Navigating with My Computer ........................................................................................................................... 15

Performing Basic Tasks with My Computer....................................................................................................... 16

Changing Views ............................................................................................................................................... 18

Step-By-Step ..................................................................................................................................................... 22

Skill Sharpener ................................................................................................................................................. 26

Lesson 1.2: Saving your Files ............................................................................................................... 27

File Formats ..................................................................................................................................................... 27

File Properties ................................................................................................................................................. 30

AutoRecover ..................................................................................................................................................... 40

Upgrading a Workbook .................................................................................................................................... 42

Step-By-Step ..................................................................................................................................................... 44

Skill Sharpener ................................................................................................................................................. 50

Lesson 1.3: Using Templates ................................................................................................................ 51

Opening a Template ......................................................................................................................................... 52

Downloading a Template ................................................................................................................................. 55

Using a Template ............................................................................................................................................. 56

Creating a Template ......................................................................................................................................... 57

Step-By-Step ..................................................................................................................................................... 59

Skill Sharpener ................................................................................................................................................. 63

Section 1: Case Study ........................................................................................................................... 64

Section 1: Review Questions ................................................................................................................ 65

Section 2: Working with Functions and Formulas .................................................................... 67

Lesson 2.1: Using Formulas in Excel .................................................................................................. 68

Understanding Relative and Absolute Cell References ...................................................................................... 68

Basic Mathematical Operators .......................................................................................................................... 71

Using Formulas with Multiple Cell References ................................................................................................. 72

The Formula Auditing Buttons ........................................................................................................................... 73

Fixing Formula Errors ..................................................................................................................................... 75

Displaying and Printing Formulas .................................................................................................................... 77

Step-By-Step ..................................................................................................................................................... 79

Skill Sharpener ................................................................................................................................................. 84

Lesson 2.2: Exploring Excel Functions ............................................................................................... 85

What are Functions? ........................................................................................................................................ 85

Finding the Right Function ............................................................................................................................... 93

Some Useful and Simple Functions .................................................................................................................... 96

Step-By-Step ..................................................................................................................................................... 98

Skill Sharpener ............................................................................................................................................... 103

Lesson 2.3: Using Functions in Excel ................................................................................................ 104

Inserting Functions ........................................................................................................................................ 104

Using Functions and AutoFill to Perform Difficult Calculations .................................................................... 107

Using the IF Function .................................................................................................................................... 110

Working with Nested Functions ....................................................................................................................... 110

Step-By-Step ................................................................................................................................................... 112

Skill Sharpener ............................................................................................................................................... 115

Lesson 2.4: Working with Names and Ranges ................................................................................. 116

What Are Range Names? ............................................................................................................................... 116

Defining and Using Range Names ................................................................................................................... 117

Selecting Nonadjacent Ranges ......................................................................................................................... 119

Using AutoCalculate ...................................................................................................................................... 119

Step-By-Step ................................................................................................................................................... 122

Skill Sharpener ............................................................................................................................................... 127

Lesson 2.5: Working with Array Formulas ..................................................................................... 128

What are Array Formulas? .............................................................................................................................. 128

Using Basic Array Formulas ........................................................................................................................... 128

Using Functions with Array Formulas ............................................................................................................. 131

Using the IF function in Array Formulas ......................................................................................................... 133

Step-By-Step ................................................................................................................................................... 137

Skill Sharpener ............................................................................................................................................... 142

Section 2: Case Study ......................................................................................................................... 143

Section 2: Review Questions .............................................................................................................. 144

Section 3: Managing Tables ...................................................................................................... 146

Lesson 3.1: Working with Tables ...................................................................................................... 147

What is a Table? ............................................................................................................................................ 147

Creating Tables .............................................................................................................................................. 148

Modifying Tables............................................................................................................................................ 151

What is the Total Row? .................................................................................................................................. 154

Step-By-Step ................................................................................................................................................... 157

Skill Sharpener ............................................................................................................................................... 160

Lesson 3.2: Working with Records and Fields ................................................................................. 161

What are Records and Fields? ......................................................................................................................... 161

Adding Fields by Inserting Columns ................................................................................................................ 162

Adding Records by Inserting Rows .................................................................................................................. 163

Quickly Adding Records to a Data table .......................................................................................................... 163

Deleting Records or Fields .............................................................................................................................. 165

Step-By-Step ................................................................................................................................................... 166

Skill Sharpener ............................................................................................................................................... 170

Lesson 3.3: Working with Tables and Filters .................................................................................. 171

Sorting Data in a Table .................................................................................................................................. 171

What is an AutoFilter? ................................................................................................................................... 174

Custom AutoFilters ........................................................................................................................................ 177

Using an Advanced Filter ............................................................................................................................... 180

Copying Filtered Records ............................................................................................................................... 182

Step-By-Step ................................................................................................................................................... 184

Skill Sharpener ............................................................................................................................................... 189

Lesson 3.4: Using Excel as a Database .............................................................................................. 190

Filtering with Wildcard Characters ................................................................................................................. 190

Validating Your Data ..................................................................................................................................... 193

What are Database Functions? ........................................................................................................................ 200

Step-By-Step ................................................................................................................................................... 204

Skill Sharpener ............................................................................................................................................... 208

Section 3: Case Study ......................................................................................................................... 209

Section 3: Review Questions .............................................................................................................. 210

Section 4: Enhancing your Workbook ...................................................................................... 212

Lesson 4.1: Customizing your Workbook ........................................................................................ 213

Inserting a Symbol or Special Character ......................................................................................................... 213

Adding and Editing Shapes .............................................................................................................................. 215

Creating and Altering Diagrams ..................................................................................................................... 223

Changing the Diagram Type ............................................................................................................................ 230

Incorporating Text ......................................................................................................................................... 232

Adding a Signature Line................................................................................................................................. 234

Step-By-Step ................................................................................................................................................... 238

Skill Sharpener ............................................................................................................................................... 245

Lesson 4.2: Working with Text Boxes............................................................................................... 246

Adding a Text Box .......................................................................................................................................... 246

Selecting a Text Box ....................................................................................................................................... 248

Manipulating a Text Box ................................................................................................................................ 249

Formatting a Text Box ................................................................................................................................... 251

Step-By-Step ................................................................................................................................................... 257

Skill Sharpener ............................................................................................................................................... 263

Lesson 4.3: WordArt .......................................................................................................................... 264

Adding WordArt to your Spreadsheet .............................................................................................................. 264

Changing the Font Color ............................................................................................................................... 269

Changing the Outline Color ............................................................................................................................. 271

Adding Effects ................................................................................................................................................ 272

Step-By-Step ................................................................................................................................................... 274

Skill Sharpener ............................................................................................................................................... 280

Lesson 4.4: ClipArt ............................................................................................................................. 281

Finding ClipArt .............................................................................................................................................. 281

Inserting ClipArt ............................................................................................................................................ 285

Manipulating ClipArt ..................................................................................................................................... 286

Inserting a Photographic Image ...................................................................................................................... 287

Step-By-Step ................................................................................................................................................... 291

Skill Sharpener ............................................................................................................................................... 295

Lesson 4.5: Using Objects .................................................................................................................. 296

Creating New Embedded Objects .................................................................................................................... 296

Embedding Existing Files ............................................................................................................................... 300

Editing Embedded Objects ............................................................................................................................... 302

Embedding Parts of Files ............................................................................................................................... 306

Step-By-Step ................................................................................................................................................... 309

Skill Sharpener ............................................................................................................................................... 314

Section 4: Case Study ......................................................................................................................... 315

Section 4: Review Questions .............................................................................................................. 316

Section 5: Finalizing Your Workbook....................................................................................... 318

Lesson 5.1: Protecting your Workbook ............................................................................................ 319

Protecting your Workbook ............................................................................................................................... 319

Protecting your Worksheets ............................................................................................................................. 321

Unlocking Cells .............................................................................................................................................. 323

Protecting your Excel Files .............................................................................................................................. 326

Step-By-Step ................................................................................................................................................... 329

Skill Sharpener ............................................................................................................................................... 333

Lesson 5.2: Finishing Your Workbook ............................................................................................. 334

Using the Document Inspector ......................................................................................................................... 334

Using the Compatibility Checker ..................................................................................................................... 340

Marking a Workbook as Final ......................................................................................................................... 344

Step-By-Step ................................................................................................................................................... 347

Skill Sharpener ............................................................................................................................................... 352

Lesson 5.3: Using Excel in Word ....................................................................................................... 353

Inserting Excel Data in Word .......................................................................................................................... 353

Linking Excel Data in a Word Document ........................................................................................................ 358

Modifying Excel Data after Insertion ............................................................................................................... 361

Insert an Excel Chart into a Word Document .................................................................................................. 362

Step-By-Step ................................................................................................................................................... 366

Skill Sharpener ............................................................................................................................................... 369

Lesson 5.4: Using Excel and Access .................................................................................................. 370

Linking an Excel Workbook to an Access Database ........................................................................................ 370

Importing Table, PivotTable, and PivotChart Data from Access .................................................................... 374

Transforming an Excel Workbook into an Access Database ........................................................................... 379

Step-By-Step ................................................................................................................................................... 386

Skill Sharpener ............................................................................................................................................... 391

Lesson 5.5: Using Excel With other Programs and Files ................................................................ 392

Using Outlook to Send Excel Data ................................................................................................................... 392

Opening an Excel File in a Different Format .................................................................................................. 394

Importing Data from a Text File ...................................................................................................................... 401

Importing Data from External Data Sources ................................................................................................... 405

Publishing a Workbook as a PDF File ............................................................................................................ 410

Faxing a Workbook ........................................................................................................................................ 417

Step-By-Step ................................................................................................................................................... 419

Skill Sharpener ............................................................................................................................................... 428

Section 5: Case Study ......................................................................................................................... 429

Section 5: Review Questions .............................................................................................................. 430

Index ......................................................................................................................................... 432