Skills for Success with Microsoft Excel 2010, Comprehensive

Kris Townsend is an Information Systems instructor at Spokane Falls Community College in Spokane, Washington. Kris earned a bachelor's degree in both Education and Business, and a master's degre in Education. He has also worked as a public school teacher and as a systems analyst. Kris enjoys working with wood, snowboarding, and camping. He commutes to work by bike and enjoys long rides in the Palouse country south of Spokane.   Philip S. Vavalides is a Professor of Computer Technology at Guilford Technical Community College in North Carolina. He holds a bachelor's degree in Biology, and a Juris Doctorate degree from the University of Tennessee. He has also practiced law. He enjoys golf and traveling.   Catherine Hain is an instructor at Central New Mexico Community College in Albuquerque, New Mexico. She teaches computer applications classes in the Business and Information Technology School, both in the classroom and through the distance learning office. Catherine holds a bachelor's degree in Management and Marketing and a master's degree in Business Administration  

Skills for Success with Excel Comprehensive


Table of Contents
Start! Common Features of Office 2010
Skill 1 Start Word and Navigate the Word Window

Skill 2 Start Excel and PowerPoint and Work with Multiple Windows

Skill 3 Save Files in New Folders

Skill 4 Print and Save Documents

Skill 5 Open Student Data Files and Save Copies Using Save As

Skill 6 Type and Edit Text

Skill 7 Cut, Copy, and Paste Text

Skill 8 Format Text and Paragraphs

Skill 9 Use the Ribbon

Skill 10  Use Shortcut Menus and Dialog Boxes
More Skills
    More Skills 11 Capture Screens with the Snipping Tool  

    More Skills 12 Use Microsoft Office Help               

    More Skills 13 Organize Files               

    More Skills 14 Save Documents to Windows Live SkyDrive

Chapter 1 Create Workbooks with Excel 2010

Skill 1  Create and Save New Workbooks

Skill 2  Enter Worksheet Data and Merge and Center Titles

Skill 3  Construct Addition and Subtraction Formulas

Skill 4  Construct Multiplication and Division Formulas

Skill 5  Adjust Column Widths and Apply Cell Styles

Skill 6  Use the SUM Function

Skill 7  Copy Formulas and Functions Using the Fill Handle

Skill 8  Format, Edit, and Check the Spelling of Data

Skill 9  Create Footers and Change Page Settings

Skill 10  Display and Print Formulas and Scale Worksheets for Printing

MORE SKILLS

    More Skills 11 Create New Workbooks from Templates

    More Skills 12 Use Range Names in Formulas

    More Skills 13 Change Themes

    More Skills 14 Manage Document Properties

 

Chapter 2 Create Charts

Skill 1  Open Existing Workbooks and Align Text

Skill 2  Construct and Copy Formulas Containing Absolute Cell References

Skill 3  Format Numbers

Skill 4  Create Column Charts

Skill 5  Format Column Charts

Skill 6  Create Pie Charts and Chart Sheets

Skill 7  Apply 3-D Effects and Rotate Pie Chart Slices

Skill 8  Explode and Color Pie Slices, and Insert Text Boxes

Skill 9  Update Charts and Insert WordArt

Skill 10  Prepare Chart Sheets for Printing

MORE SKILLS

    More Skills 11 Insert and Edit Comments

    More Skills 12 Change Chart Types

    More Skills 13 Copy Excel Data to Word Documents

    More Skills 14 Fill Series Data into Worksheet Cells

 

Chapter 3 Manage Multiple Worksheets in a Workbook

Skill 1  Work with Sheet Tabs

Skill 2  Enter and Format Dates

Skill 3  Clear Cell Contents and Formats

Skill 4  Move, Copy, Paste and Paste Options

Skill 5  Work with Grouped Worksheets

Skill 6  Use Multiple Math Operators in a Formula

Skill 7  Format Grouped Worksheets

Skill 8  Insert and Move Worksheets

Skill 9  Construct Formulas that Refer to Cells in Other Worksheets

Skill 10  Create Clustered Bar Charts

MORE SKILLS

    More Skills 11 Create Organization Charts

    More Skills 12 Create Line Charts

    More Skills 13 Set and Clear Print Areas

    More Skills 14 Insert Hyperlinks

 

Chapter 4 Using Excel Functions and Tables

Skill 1  Use the SUM and AVERAGE Functions

Skill 2  Use the MIN and MAX Functions

Skill 3  Move Ranges with Functions, Add Borders, and Rotate Text

Skill 4  Use the IF Function

Skill 5  Apply Conditional Formatting with Custom Formats, Data Bars, and Sparklines

Skill 6  Use Find and Replace and Insert the NOW Function

Skill 7  Freeze and Unfreeze Panes

Skill 8  Create and Sort Excel Tables

Skill 9  Use the Search Filter in Excel Tables

Skill 10  Convert Tables to Ranges, Hide Rows and Columns, and Format Large Worksheets

MORE SKILLS

    More Skills 11 Apply Conditional Color Scales with Top and Bottom Rules

    More Skills 12 Use the Payment (PMT) Function

    More Skills 13 Create PivotTable Reports

    More Skills 14 Use Goal Seek

 

Chapter 5: Format Cells and Worksheets

Skill 1 Insert Symbols and Create Custom Number Formats

Skill 2 Format Cells With the Format Painter

Skill 3 Apply Icon Sets as Conditional Formatting

Skill 4 Use Formulas to Write Multiple Conditional Formatting Rules

Skill 5 Insert and Delete Rows, Columns, and Cells

Skill 6 Change Cell Backgrounds and Modify Borders

Skill 7 Create and Apply Custom Cell Styles

Skill 8 Customize, Save, and Apply Themes

Skill 9 Add Watermarks and Modify Background Colors

Skill 10 Hide Gridlines and Column and Row Headings

MORE SKILLS

    More Skills 11 Copy Styles Between Workbooks

    More Skills 12 Draw Borders

    More Skills 13 Convert Comma Separated Text into Columns

    More Skills 14 Create Screen Clippings

 

Chapter 6: Work with Advanced Functions and Formulas

Skill 1 Modify Text Using Text Functions

Skill 2 Use Paste Options to Change Underlying Values

Skill 3 Write Nested IF Functions

Skill 4 Look Up Data using Lookup Functions

Skill 5 Apply Logical Functions

Skill 6 Use COUNTA and COUNTIF Functions

Skill 7 Use the Scenario Manager to Perform What-If Analysis

Skill 8 Calculate Future Value

Skill 9 Use Goal Seek

Skill 10 Calculate Conditional Sums

MORE SKILLS

    More Skills 11 Query Tables Using Criteria Ranges

    More Skills 12 Use Solver

    More Skills 13 Delete Duplicate Records

    More Skills 14 Use External Links to Update Data

 

Chapter 7: Work with Data and Audit Formulas

Skill 1 Import Data from Text Files

Skill 2 Sort Data and Apply Advanced AutoFilters

Skill 3 Sort Data Based on Conditional Formatting

Skill 4 Use the Subtotal Tool to Summarize Data

Skill 5 Trace and Evaluate Formulas

Skill 6 Audit Formulas Using Cell Watch

Skill 7 Create One-Variable Data Tables

Skill 8 Create Two-Variable Data Tables

Skill 9 Create Custom Table Quick Styles

Skill 10 Convert Tables to Ranges

MORE SKILLS

    More Skills 11 Create Amortization Tables

    More Skills 12 Use the ROUND Function

    More Skills 13 Use Information Functions

    More Skills 14 Sort Tables Based on Multiple Criteria

 

Chapter 8: Insert and Format Graphics and Shapes

Skill 1 Insert Graphics into Headers

Skill 2 Resize, Crop, and Compress Images

Skill 3 Insert, Modify, and Remove Backgrounds from Clip Art

Skill 4 Recolor Graphics and Apply Artistic Effects

Skill 5 Create SmartArt Organization Charts

Skill 6 Apply Quick Styles and Resize SmartArt

Skill 7 Resize and Position SmartArt Shapes

Skill 8 Format Individual SmartArt Shapes

Skill 9 Insert and Format Shapes

Skill 10 Group and Resize Shapes

MORE SKILLS

    More Skills 11 Add Alternate Text to Graphics

    More Skills 12 Add Graphics to SmartArt Shapes

    More Skills 13 Convert Graphics to SmartArt Shapes and Use the Selection and Visibility Pane

    More Skills 14 Insert PowerPoint Slides Into Worksheets

 

Chapter 9: Import Data, Create PivotTable Reports and PivotChart Reports

Skill 1 Import Data from Access Databases

Skill 2 Create PivotTable Reports

Skill 3 Change PivotTable Report Views

Skill 4 Change Calculation Types and Format PivotTable Reports

Skill 5 Create PivotTable Report Calculated Fields

Skill 6 Group and Filter PivotTable Reports

Skill 7 Slice PivotChart Report Views

Skill 8 Create PivotChart Reports from External Data Sources

Skill 9 Create PivotChart Reports from PivotTable Reports

Skill 10 Format PivotChart Reports

MORE SKILLS

    More Skills 11 Import Word 2010 Tables

    More Skills 12 Create Web Queries and Clear Hyperlinks

    More Skills 13 Change PivotChart Report Types and Print PivotChart Reports

    More Skills 14 Use Paste Special

 

Chapter 10: Share Workbooks and Create Macros

Skill 1 Modify Excel Options and Work With Comments

Skill 2 Validate Data with Data Entry Rules

Skill 3 Track Changes

Skill 4 Accept and Reject Tracked Changes

Skill 5 Merge Changes

Skill 6 Use the Document Inspector to Remove Personal Information

Skill 7 Save Workbooks as Web Pages

Skill 8 Unlock Cells and Protect Worksheets

Skill 9 Record Macros

Skill 10 Write Macros Using VBA

MORE SKILLS

    More Skills 11 Hide Cells

    More Skills 12 Modify the Ribbon

    More Skills 13 Save a Workbook as a PDF File

    More Skills 14 Add a Macro Button to the Quick Access Toolbar