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
