Level 3 – 2 Days
If you have attended our Level 1 and Level 2 courses you have a good foundation of how to navigate Excel as well as having a good grasp of the tools to build worthwile spreadsheets. In this course we will get under the hood of Excel and explore all of the great features which will help you extract meaningful results from your raw data. Prepare to be amazed!
What We Will Cover:
DATA CONSOLIDATION
- Consolidate Multiple Worksheets into One
- Consolidate by Position
- Consolidate by Category
- Consolidate from Other Workbooks
CUSTOMIZING EXCEL
- Changing Default Options
- Custom Lists
SCENARIOS
- Creating and Saving the Scenario
- Displaying Scenarios
- Other Scenario Options-Edit, Merge, Summary, etc.
CONSOLIDATE
- Creating and Saving Consolidation
- Other Consolidation Options
DATA TABLES
- Creating
- Substitute Consecutive Values in a Formula
A BRIEF INTRODUCTION TO MACROS
- What is a Macro?
- Recording a Macro
- Playing a Macro
- Relative Referencing
- Rerecording a Macro
PIVOT TABLES
- Changing the Layout of a Pivot Table
- Basic Pivot Table Formatting
- Calculations
- Pivot Table Summaries
- Pivot Table Slicers
- Creating Pivot Tables from External Data
- Creating Pivot Charts
ADVANCED FILTER
- Setting Up the Ranges
- Setting Up the Criteria Range
- Performing the Filter
- Copy Unique Records to Another Location
GROUP & OUTLINE, & SUBTOTALS
- Grouping Rows/Columns on a Sheet
- Showing/Hiding Details in an Outline
- Display/Hide Outline Symbols
USING SUBTOTALS
- Adding Automatic Subtotals
- Adding Other Automatic Analysis (Average, Count, etc)
- Removing Subtotals
CHARTS
- Understanding Charts
- Adding Data
- Pie, Column, Bar and Line
DATA SET CLEANUP
- How to clean a dataset
- What not to do?
- How to fix datasets to work with PivotTables