Microsoft Excel 2010: Advanced Skills
Preliminary Table of Contents
Microsoft Excel 2010: Advanced Skills
Preliminary Table of Contents
|
Lesson 1: Working with Tables, Lists, and Outlines Why Use a Table? Working with Tables Table Rows Table Columns Creating a Table Converting a Range to a Table Creating a New Blank Table Renaming a Table Header Row Total Row Formatting a Table Adding and Deleting Rows and Columns Selecting Table Rows and Columns Calculated Columns Converting a Table to a Range Printing a Table Deleting a Table Understanding Structured References Formulas with Structured References Using Enhanced Sorting and Filtering in Lists and Tables Sorts Filters Custom Filters Explore Number and Date Criteria Options Using the Outline Feature How Outlines Work Auto Outline Creating Groups Manually Displaying Subtotals Sorting the List The Subtotal Dialog Box
Lesson 2: Adding Graphics to Worksheets Using Illustrations with Excel Design Principles The Illustrations Group on the Ribbon Inserting Pictures and Clip Art Inserting a Picture from a File Inserting Clip Art Moving, Sizing, and Rotating Images Scaling and Cropping Images Adjusting Images and Special Effects Getting into Shapes Inserting Shapes Introducing SmartArt Using SmartArt Adding Text to SmartArt Formatting SmartArt
|
Lesson 3: Using Templates and Protecting Worksheets Using Templates Template Features The New Workbook Dialog Box Template Storage Locations Customizing Templates Creating Your Own Templates Modifying Custom Templates Protecting Workbooks and Worksheets Protecting the Workbook Structure Protecting Worksheet Elements Password Protection Protecting Cells Creating Digital Signatures When to Use a Digital Signature Creating a Digital Certificate Creating a Digital Signature
Lesson 4: Creating PivotTables and Macros Creating PivotTables PivotTable Example 1 PivotTable Example 2 How PivotTables Work Formatting a PivotTable Changing PivotTable Fields Filtering the Display of Data Items Filtering by Categories Filtering a PivotTable Report Editing PivotTable Calculations Refreshing PivotTable Data Creating PivotCharts PivotChart Filter Pane Formatting PivotCharts Changing Macro Security Security Levels Recording Macros Naming a Macro Recording Macro Steps Storing Macros Saving a Workbook Containing Macros Running Macros Assigning Macros Assigning Macros to Shortcut Keys Assigning Macros to Custom Buttons
|
|
Lesson 5: Using Financial Functions and Data Analysis Creating Financial Functions PMT and FV Functions Financial Function Syntax Using Data Analysis Tools Using Goal Seek Using Solver Scenario Manager
Lesson 6: Auditing and Additional Functions Using 3-D Cell References in Formulas Why Use a 3-D Reference? Creating a 3-D Reference Introducing Lookup Functions Lookup Function Syntax How the VLOOKUP Function Works Specifying the Range Lookup Argument Sorting a Table Array Using the SUBTOTAL Function to Calculate Filtered Lists Filtering a List Creating Subtotal Formulas Creating Formulas Using Criteria IF Functions Function Syntax How the SUMIF Function Works How the COUNTIF Function Works Using Other Logical Functions in Formulas AND OR NOT IFERROR Using Formulas to Format Text Change Case of Text Replace Text Convert Text to Columns Tracing Formulas The Formula Auditing Tools Tracing Precedents Tracing Dependents Auditing Formula Errors Auditing Single Cells Error Checking in Multiple Cells Tracing Errors Evaluating Formulas
|
Lesson 7: Using Advanced Formatting and Analysis Tools Working with Grouped Worksheets Grouping Worksheets Ungrouping Worksheets Consolidating Worksheet Data Consolidation Functions Types of Consolidation Creating Links to Source Data Working with Data Validation Creating Drop-Down Lists for Data Entry Restricting Data Entry in Cells Circling Invalid Data Removing Duplicate Records Using Data Tables One-Variable Data Tables Two-Variable Data Tables Creating Trendlines Creating Sparklines in Cells
Lesson 8: Collaborating in Excel Creating Folders in Excel Working with Project Folders Creating Folders Renaming Folders Organizing Workbooks in Folders Inserting and Viewing Comments When to Use a Comment Viewing Comments Navigating Through Comments Setting the Username Inserting and Deleting Comments Adding to Comments Formatting Comment Text Positioning and Sizing a Comment Printing Comments Preparing Workbooks for Distribution Granting a User Permission to Edit Inspecting Workbooks for Personal Information and Hidden Data Marking a Workbook as Final Sharing Workbooks Tracking Changes to Workbooks Example of Tracked Changes at Work Reviewing Tracked Changes The Change History Sharing Workbooks on a Network Characteristics of Shared Workbooks Simultaneous Access to Shared Workbooks Disabled Features in Shared Workbooks Switching Off Sharing Merging Multiple Workbooks Example of a Merge What Happens When Workbooks Are Merged Merged Cells Compared to Merged Workbooks Protecting Elements in a Shared Workbook
|
|
Lesson 9: Integrating Excel with Other Programs Maintaining Compatibility with Previous Versions of Excel About File Formats Excel 2010 Open XML File Formats Earlier Excel File Formats Checking for Excel Version Compatibility Using the Compatibility Pack Converters Converting Workbooks to Other File Formats Text file Formats Limitations of File Formats PDF and XPS File Formats Using Excel Tables with Word Mail Merge Sharing Excel Data with Access Inserting Excel Charts in PowerPoint Linking Compared to Embedding Office Web Apps Collaborating on Office Live Workspace The Excel Web App Importing External Data Using Copy and Paste Importing Data with Drag and Drop Importing a Text File Saving Workbook Elements as a Web Page Saving Selected Elements Republishing the Web Page
Glossary Index |