Excel Data Analysis Course

Summarizing 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 

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 

Organizing workbooks and links  

• Arranging multiple workbooks with Workspaces 
• Managing external links 

Consolidating ranges  

• Building 3D formulas to analyze worksheet data 
• Summarizing multiple sources of Excel information into one worksheet 

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 
• Analyzing data sets with filters and aggregation 

Interpreting and refining data with PivotTables  

• Defining data summaries interactively 
• Summarizing data sets with grouping and aggregation 
• Comparing related totals dynamically 

Visualizing and exploring PivotTable reports  

• Filtering details with Report Filters and Slicers 
• Presenting PivotTable reports effectively with Pivot Charts 
• Examining data patterns with Sparklines 

Automating repetitive tasks  

• Simplifying complex tasks and reducing errors 
• Bulletproofing routine editing and formatting 
• Invoking macros with Form controls