Excel Introduction

• Overview of the screen, navigation, and basic spreadsheet concepts
• Various selection techniques
• Shortcut keys

Customizing Excel

• Customizing the Ribbon
• Using and customizing AutoCorrect
• Changing Excel’s default options

Using Basic Functions

• Using functions: SUM, AVERAGE, MAX, MIN, COUNT, COUNTA
• Absolute, mixed, and relative referencing

Formatting and Proofing

• Currency format
• Format Painter
• Formatting options

Formatting and Proofing (continued)

• Currency Format
• Format Painter
• Formatting Dates
• Custom and Special Formats
• Formatting Cells with Number Formats, Font Formats, Alignment, Borders, etc.
• Basic Conditional Formatting

Mathematical Functions

• SUMIF, SUMIFS, COUNTIF, COUNTIFS
• AVERAGEIF, AVERAGEIFS
• Nested IF, IFERROR Statements
• Logical Functions: AND, OR, NOT

Protecting Excel

• File-Level Protection
• Workbook and Worksheet Protection

Text Functions

• UPPER, LOWER, PROPER
• LEFT, MID, RIGHT
• TRIM, LEN, EXACT
• CONCATENATE
• FIND, SUBSTITUTE

Advanced Paste Special Techniques

• Paste Formulas, Paste Formats
• Paste Validations
• Transpose Tables

New in Excel 2013 / 2016 / 365

• New Charts – Tree Map & Waterfall
• Sunburst, Box and Whisker Charts
• Combo Charts – Secondary Axis
• Adding Slicers Tool in Pivot Tables
• Using Power Map and Power View
• Forecast Sheet
• Sparklines – Line, Column & Win/Loss
• Using 3-D Map
• New Controls in Pivot Table – Field, Items and Sets
• Various Timelines in Pivot Table
• Auto Complete a Data Range and List
• Quick Analysis Tool
• Smart Lookup and Manage Store

Printing Workbooks

• Setting Up Print Area
• Customizing Headers & Footers
• Designing the Structure of a Template
• Print Titles – Repeat Rows / Columns

Advanced Excel

What-If Analysis

• Goal Seek
• Scenario Analysis
• Data Tables (PMT Function)
• Solver Tool

Logical Functions

• IF Function
• How to Fix Errors – IFERROR
• Nested IF
• Complex IF and OR Functions

Data Validation (continued)

• Number, Date & Time Validation
• Text and List Validation
• Custom Validations Based on Formula for a Cell
• Dynamic Dropdown List Creation using Data Validation – Dependency List

Lookup Functions

• VLOOKUP / HLOOKUP
• INDEX and MATCH
• Creating Smooth User Interface using Lookup
• Nested VLOOKUP
• Reverse Lookup using CHOOSE Function
• Worksheet Linking using INDIRECT
• VLOOKUP with Helper Column

Pivot Tables

• Creating Simple Pivot Tables
• Basic and Advanced Value Field Settings
• Classic Pivot Table
• Choosing Fields
• Filtering PivotTables