Unit 1: Working with Data lists
• Apply data filters (AutoFilter)
• Perform single and multi-level sorts
• Applying Subtotals in Filtered Lists
Exercise
Unit 2: Working with Advanced Filters
• Extract data with Advanced Filter
• Combining criteria
Exercise
Unit 3: Data validation
• Applying Validation Criteria in cells
• Creating drop down lists for automatic selection of data
• Validating Data
Exercise
Unit 4: Grouping and Outlining
• Creating Subtotals
• Creating Nested Subtotals
• Using Groups and outlines
Consolidation
Exercise
Unit 5: Calculations based on Logical Functions and Nested Functions
• Using IF and nested IF
• Using AND, OR
Exercise
Unit 6: Using Named Ranges and Usage in Functions
• Creating a named range
• Changing a named range
• Selecting fields by using names
• Creating names using labels
• Using names in Functions
Unit 7: Creating Smart sheets with VLOOKUP
• Using VLOOKUP
• Nested functions with IF and VLOOKUP
• Automating Calculations with IF and formulae
Unit 8: Collaborating and Protecting
• Working with notes
• Protecting and Unprotect workbooks, spreadsheets and cells
• Protecting a file using passwords
Unit 9: Creating Templates
• Creating new Templates
• Using the existing Templates
Exercise
Unit 10: Data analysis with PivotTables
• Creating PivotTables
• Summarizing data using various functions
• Applying Filters
• Using Slicers and Timelines
• Creating Pivot Charts
Exercise
Unit 11: Analysis Tools
• Using Goal Seek
Working with Scenarios
Using Solver (Goal Seek)
Unit 12: Auditing
• Using the Auditing tools
Finding and correcting Formulae errors