CONTENTS:

  • Tools and Techniques
    • Consolidating Data from Separate Files and Sheets
    • Advanced Data Validation Using Lists, Dates and Custom Validation
    • Array Functions
    • Cell Management Tools: Left, Right, Mid, Concatenate, Value
    • Naming Cells and Ranges
    • Subtotal, Sumif, Sumifs, Sumproduct
    • Looking-Up Data, Texts, and Values Using Vlookup
    • The Incredible Table Tools Techniques
    • Slicing Dates into Day Names, Month Names, Years and Quarters
    • Text to Columns and Dynamic Trimming Using Trim, Len
    • Find and Substitute
    • Text Change Functions
  • The 19 Must Learn Pivot Tables Tools
    • Creating Pivot Tables
    • Number Formatting Techniques
    • Designing Report Layout
    • Sorting in Ascending, Descending and More Sort Options
    • Filtering Labels and Values
    • Expanding and Collapsing Reports
    • Summarize Data By Sum, Average, Minimum, Maximum, Count
    • Show Values as % of Total & % of …
    • Pivot Table Options
    • Inserting Formulas
    • Date Analysis
    • Copying Pivot Tables
    • Creating Pivot Charts
    • Dynamic Chart Labeling
    • Mastering the Slicer
    • Showing Report Filter Pages
    • Linking Pivot Tables and Pivot Graphs with PowerPoint
    • Conditional Formatting with Pivot Tables
    • Designing Reports Using the GetPivotData
  • Report Design and Modeling Techniques
    • Spinner
    • Check Box Data Modeling with IF function
    • Option Box Data Modeling with IF function
    • List Box Data Modeling with CHOOSE function
    • Scenario Manager
  • Tips and Tricks in Excel
    • Data Entry Form
    • Custom List
    • Text to Speech
    • Protecting Worksheet