Data Analysis and Reporting Techniques Using Excel

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 Worksheets and Workbooks