Microsoft Excel Intermediate

Business Benefit

By completing this course, participants will gain the skills and knowledge necessary to efficiently work with complex data scenarios and optimise their use of Excel for various professional tasks.

Who Should Attend

This course is designed for individuals who:

• Want to display data dynamically in Excel worksheets based on specific conditions or criteria.
• Seek to visually understand and follow the logical flow of formulas within worksheets, including addressing circular references.
• Are responsible for managing and maintaining lists containing a moderate number of items, such as company assets, personnel records, product inventories, or invoices, often numbering up to a few thousand rows.

Learning Outcomes

• Efficiently Query Spreadsheet Data: Learn how to retrieve specific values within a spreadsheet using various techniques and functions.
• Advanced Data Analysis: Gain expertise in analysing data by employing advanced formulas and functions to extract valuable insights.
• Data Management Mastery: Develop the skills to effectively manage and manipulate large volumes of data, making it more organised and accessible.
• Pivot Table and Chart Proficiency: Learn to create, customise and manage pivot tables and charts to visualise data and uncover trends and patterns.

Course Content

• The IF Function: Learn how to use the IF function to perform conditional calculations in Excel, allowing you to make decisions based on specified criteria.
• Nested IF: Dive deeper into conditional logic by mastering nested IF statements, which enable you to create more complex decision trees.
• The Lookup Function: Explore the Lookup functions (such as VLOOKUP and HLOOKUP) to search for and retrieve specific data from tables or ranges.
• Validating Lookups: Discover techniques for validating and enhancing the accuracy of lookup functions by handling errors and inconsistencies.
• IFs With Lookups: Combine IF functions with lookup functions to perform conditional lookups and data manipulation.
• Date Functions: Learn how to work with date and time functions in Excel to perform various date-related calculations and operations.
• The Auditing Toolbar: Utilise Excel’s auditing tools to review and analyse formulas, track changes, and detect errors in your worksheets.
• Checking Errors: Understand methods for identifying and correcting errors in Excel formulas and calculations.
• Precedent & Dependent Tracing: Master the art of tracing cell precedents and dependents to visualise and understand formula dependencies.
• List Management: Explore techniques for efficiently managing and manipulating lists and databases within Excel.
• Subtotals & Outlines: Learn how to create subtotals and outlines to summarize and structure data within large datasets.
• Data Statistical Functions: Discover statistical functions to analyse and interpret data, including functions like AVERAGE, COUNT, and SUMIF.
• Goal Seek: Use the Goal Seek feature to solve for a desired outcome by adjusting input values in your calculations.
• Scenarios: Create and analyse scenarios to evaluate different sets of data inputs and their impact on outcomes.
• Pivot Tables & Pivot Charts: Delve into pivot tables and pivot charts to efficiently summarise, analyse, and visualise large datasets.