WORKING WITH FUNCTIONS
• Writing conditional expressions (using IF)
• Using logical functions (AND, OR, NOT)
• Using lookup and reference functions (LOOKUP, HOOKUP, MATCH, INDEX)
• Vlookup with Exact Match, Approximate Match
• Nested Vlookup with Exact Match
• Vlookup with Tables, Dynamic Ranges
• Nested VLOOKUP with Exact Match
• Using VLookup to consolidate Data from Multiple Sheets
DATA VALIDATIONS
• Specifying a valid range of values for a cell
• Specifying a list of valid values for a cell
• Specifying custom validations based on a formula for a cell
WORKING WITH TEMPLATES
SORTING AND FILTERING DATA
• Sorting tables, Using multiple-level sorting, Using custom sorting
• Filtering data for selected view (AutoFilter), using advanced filter options
WORKING WITH REPORTS
• Creating subtotals, Multiple-level subtotals
• Creating Pivot tables, Formatting, and customizing Pivot tables
• Pivot charts
• Consolidating data from multiple sheets and files using Pivot tables
• Using external data sources
• Using the data consolidation feature to consolidate data
• Show Value As (% of Row, % of Column, Running Total, Compare with Specific Field)
• Viewing Subtotal under Pivot
MORE FUNCTIONS
• Date and time functions
• Text functions
• Database functions
• Power Functions (CountIf, CountIFS, SumIF, SumIfS)
FORMATTING
• Using the auto-formatting option for worksheets
• Using the conditional formatting option for rows, columns, and cells
WHATIF ANALYSIS
• Goal Seek
• Data Tables
• Scenario Manager
CHARTS
• Using Charts, Formatting Charts, Using 3D Graphs
• Using Bar and Line Chart together, Using Secondary Axis in Graphs
• Sharing Charts with PowerPoint / MS Word, Dynamically
• (Data Modified in Excel, Chart would automatically get updated)