Lesson 1: Automating Worksheet Functionality
TOPIC A: Update Workbook Properties
TOPIC B: Create and Edit Macro
- Macros
- The Record Macro Dialog Box
- Naming Macros
- Visual Basic for Applications
- Copying Macros Between Workbooks
- Macro Security Settings
TOPIC C: Apply Conditional Formatting
- Conditional Formatting
- Conditional Formats
- The Conditional Formatting Rules Manager Dialog Box
- The New Formatting Rule Dialog Box
- Clear Rules
TOPIC D: Add Data Validation Criteria
- Data Validation
- The Data Validation Dialog Box
- Summary
- Review Questions
Lesson 2: Auditing Worksheets
TOPIC A: Trace Cells
- The Trace Cells Feature
- Tracer Arrows
TOPIC B: Troubleshoot Invalid Data and Formula Errors
- Invalid Data
- The Error Checking Command
- Error Types
TOPIC C: Watch and Evaluate Formulas
- The Watch Window
- Formula Evaluation
TOPIC D: Create a Data List Outline
- Outlines
- The Outline Group
Lesson 3: Analysing and Presenting Data
TOPIC A: Create Sparklines
- Sparklines
- Types of Sparklines
- The Sparkline Tools – Design Tab
TOPIC B: Create Scenarios
- Scenarios
- The What-If Analysis Tools
- The Scenario Manager Dialog Box
TOPIC C: Perform a What-If Analysis
- Add-In Types
- Goal Seek Feature
- The Solver Tool
TOPIC D: Perform a Statistical Analysis with the Analysis ToolPak
- Analysis ToolPak
- The Data Analysis Dialog Box
TOPIC E: Create Interactive Data with Power View
- The Power View Add-In
- Enabling Power View
- Creating a Power View
Lesson 4: Working with Multiple Workbooks
TOPIC A: Consolidate Data
- Data Consolidation
- The Consolidate Dialog Box
- Consolidation Functions
TOPIC B: Link Cells in Different Workbooks
TOPIC C: Merge Workbooks
- The Compare and Merge Workbooks Feature
Lesson 5: Exporting Excel Data
TOPIC A: Export Excel Data
TOPIC B: Import a Delimited Text File
- The Import Process
- The Get External Data Group
- Delimited Text Files
- Methods of Importing Text Files
TOPIC C: Integrate Excel Data with the Web
- The File Publishing Process
- Publish as Web Page Dialog Box
TOPIC D: Create a Web Query
- Web Queries
- The New Web Query Dialog Box
Lesson 6: Importing and Exporting XML Data
TOPIC A: Import and Export XML Data
- XML
- XML Components
- XML Schemas
- XML Maps
- The XML Source Task Pane
- Import and Export XML Data