Advance MS Excel 2013 Course Content

Day 1 (Saturday - 4 Hours)

Protecting and Sharing

• Sharing a file
• Tracking changes
• Accepting or rejecting changes
• Applying Data validation rules
• Inserting comments
• Protecting cells, sheets, files
• Password protecting a file
• Password protecting a cell range

Conditional Formatting/Data Validation

• Update Workbook Properties
• Apply Conditional Formatting
• Add Data Validation Criteria

Functions

• If Statements
• Nested If
• And
• Or
• Not
• Combining If, And, Or, Not
• Sumif
• Countif
• Horizontal Lookup (Hlookup)
• Vertical Lookup (Vlookup)

Auditing Worksheets

• Trace Cells
• Troubleshoot Invalid Data and Formula Errors
• Watch and Evaluate Formulas
• Create a Data List Outline

Lookup and Information Functions

• Match function
• Index Function
• IFERROR
• Vlookup, Hlookup
• Database Functions: Dsum, Dmin, Dmax, Daverage, Dcount

Day 2 (Sunday - 4 Hours)

Summarizing Data with Pivot Tables

• Inserting calculated fields
• Manipulating Fields
• Changing Value Field Settings
• Using Report Filter
• Grouping Data containing Dates and Numbers
• Formatting Pivot Table
• Showing and Hiding the Grand Totals
• Refreshing Data In Pivot Table
• Changing The Scope Of The Data source
• Summarizing Values by Sum, Count, Average, Max, and Product
• Show Values As % of Grand Total, % of Column Total, % of Row Total
• Pivot Table Options
• Using Slicers for Effective Filtering
• Pivot Chart

General Analysis Tools

• Scenarios
• Custom Views

Introduction to Macros

• Displaying the Developer Tab
• Review And Purpose Of Macros
• Where To Save Macros
• Absolute and relative record
• Running macros: Assigning to Quick Access Toolbar, 
• shapes, Pictures and keyboard shortcuts

Analyzing Data

• Create a Trendline
• Create Scenarios
• Perform a What-if Analysis
• Perform a Statistical Analysis with the Analysis ToolPak 

Importing and Exporting Data

• Export Excel Data
• Import a Delimited Text File