Outline for this course
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
- 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
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
Summarizing Business Information
- 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
Formulating Decisions from Database Information
- Distilling data sets for data analysis
- Managing multiple data sets on a single worksheet with the Table feature
- Defining an Excel data set to ensure appropriate use of built-in features
- 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
- Filtering details with Report Filters and Slicers
- Visualizing and exploring PivotTable reports
- Presenting PivotTable reports effectively with PivotCharts
- Examining data patterns with Sparklines
- Analyzing multiple tables of data with Power Pivots
- Discovering and presenting information with Power View
Enhancing Excel Usage with Macros
- Automating repetitive tasks
- Simplifying complex tasks and reducing errors
- Bulletproofing routine editing and formatting
- Invoking macros with Form controls
