Module 1: Troubleshooting and Enhancing Professional Workbooks
Deciphering and correcting functions for data integrity
Accurately interpreting calculations
Implementing Names to enhance your workbook model
Monitoring KPIs using conditional formatting
Module 2: Analyzing Data with Functions
Summarising business data with functions
Identifying the correct statistical function to aid analysis
Applying basic financial functions
Differentiating serial dates and date presentations
Calculating the number of working days
Controlling calculations and nested formulas
Interpreting data variations with the IF function
Streamlining calculations with referencing
Developing nested functions for multiple conditions
Capturing information with lookup functions
Applying techniques to implement and troubleshoot nested calculations
Module 3: Optimizing Workbook Models with ``What-If`` Analysis
Planning for contingencies
Managing variables in worksheets with Scenarios
Comparing and contrasting different data sets with Scenarios reports
Quantifying variables in a workbook model
Determining the magnitude of a variable with Goal Seek to achieve an end value
Calculating the optimum variable values in a worksheet model with Solver
Module 4: Summarizing Business Information
Organising workbooks and links
Arranging multiple workbooks with Workspaces
Managing external links
Consolidating ranges
Building 3D formulas to analyze worksheet data
Summarising multiple sources of Excel information into one worksheet
Module 5: Formulating Decisions from Database Information
Distilling data sets for data analysis
Defining the architecture of an Excel data set
Managing multiple data sets on a single worksheet with the Table feature
Extracting unique lists of records from an Excel data set with the Advanced Filter
Analysing data sets with filters and aggregation
Interpreting and refining data with PivotTables
Defining data summaries interactively
Summarising data sets with grouping and aggregation
Comparing related totals dynamically
Visualising and exploring PivotTable reports
Filtering details with Report Filters and Slicers
Presenting PivotTable reports effectively with PivotCharts
Examining data patterns with Sparklines
Module 6: Enhancing Excel Usage with Macros
Automating repetitive tasks
Simplifying complex tasks and reducing errors
Bulletproofing routine editing and formatting
Invoking macros with Form controls
