Business Solutions and Excel

Developing workbook models

• Planning effective worksheets and workbooks to improve workbook maintenance
• Highlighting KPIs and data anomalies with conditional formatting

Optimising solutions with "what-if" analysis

• Managing variables in worksheets with Scenarios
• Comparing and contrasting different datasets with scenario reports
• 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

Analysing and Deciphering Data

Evaluating data with Excel functions

• Discovering and implementing functions with the Fχ tool
• Interpreting calculations with the Formula Auditing tools
• Identifying the correct statistical function to aid analysis
• Applying basic financial functions
• Differentiating serial dates and date presentations
• Calculating the number of working days using the Analysis Toolpak

Controlling calculations and nested formulas

• Troubleshooting calculations with the Function Arguments tool
• Interpreting data variations with the IF function
• Developing nested functions to cope with multiple conditions

Extracting values with data-centred calculations

• Capturing information with lookup functions
• Handling missing information
• Checking data for uniqueness

Summarising Business Information

Organising workbooks and links

• Arranging multiple workbooks with Workspaces
• Managing external links

Consolidating ranges

• Building 3-D formulas to analyse worksheet data
• Summarising multiple sources of Excel information into one worksheet

Formulating Decisions from Database Information

Distilling lists for data analysis

• Managing multiple datasets on a single worksheet with the Table feature
• Defining an Excel list to ensure appropriate use of built-in list features
• Extracting unique lists of records from an Excel list
• Analysing lists with filters and aggregation
• Calculating subtotals and grand totals

Condensing and refining data with PivotTables and PivotCharts

• Creating interactive PivotTables and PivotCharts for real-time data analysis
• Comparing related totals dynamically
• Extracting and filtering records
• Defining data summaries interactively
• Presenting PivotTable reports effectively

Enhancing Excel Usage with Macros

Automating repetitive tasks

• Recording and executing macros to simplify complex tasks
• Invoking macros with Form controls

Recognising the code behind a macro

• Working with the Visual Basic Editor
• Identifying the composition of a macro
• Troubleshooting and interpreting code in a macro procedure
• Modifying macros in the code window