+49 (0) 30 5201 4923
info@orb-experts.com
A practical intermediate course for participants who already have fundamental Excel skills and want to develop their ability to perform calculations, analyse data and connect information across worksheets. Participants build structured calculation models, use more advanced formulas and functions, evaluate larger datasets and present results through clear and informative charts.
You will strengthen your understanding of formulas, functions and cell references and learn how to build clear, reliable calculation worksheets. You will organise, sort, filter and analyse data more efficiently and connect information across worksheets and workbooks.
You will also create and edit charts to communicate trends, comparisons and relationships effectively. By the end of the course, you will be able to develop more sophisticated calculation models and analyse larger datasets systematically.
• Apply more advanced formulas and functions confidently
• Build structured and easy-to-understand calculation worksheets
• Analyse data using logical, statistical and conditional functions
• Sort, filter and examine datasets efficiently
• Link information across worksheets and workbooks
• Create and professionally format informative charts
• Review formulas and resolve common calculation errors
• Knowledge equivalent to the Microsoft Excel Fundamentals course
• Confidence working with workbooks, worksheets and cell ranges
• Experience creating simple formulas and using basic functions
• Basic understanding of relative and absolute cell references
*We customize the course outline and content to your specific needs and relevant use cases.
Module 1: Reviewing and extending calculation fundamentals
• Reviewing the structure and operation of formulas
• Applying relative, absolute and mixed cell references appropriately
• Understanding calculation order and nested expressions
• Copying formulas efficiently across larger ranges
• Displaying, reviewing and tracing formulas
• Identifying and correcting common calculation errors
Module 2: Intermediate functions
• Performing logical tests with IF
• Combining multiple conditions with AND and OR
• Managing error values with appropriate error-handling functions
• Creating conditional calculations with SUMIF and COUNTIF
• Applying multiple criteria to calculations
• Combining and nesting functions effectively
Module 3: Building structured calculation worksheets
• Separating input, calculation and output areas
• Managing assumptions and fixed values centrally
• Calculating prices, quantities, discounts, taxes and percentages
• Building consistent and maintainable formulas
• Documenting calculation sheets with labels and guidance
• Visually distinguishing input cells from calculated results
Module 4: Organising, sorting and filtering data
• Converting data ranges into structured tables
• Sorting data by multiple criteria
• Applying text, number and date filters
• Defining custom filter conditions
• Filtering data by values or formatting
• Reviewing and processing filtered results
Module 5: Analysing and summarising data
• Calculating key measures with SUM, AVERAGE, MIN and MAX
• Examining datasets with COUNT, COUNTA and conditional functions
• Calculating results within filtered lists
• Grouping data and creating clear subtotals
• Calculating variances, proportions and changes
• Presenting and validating analysis results
Module 6: Linking worksheets and workbooks
• Creating references between different worksheets
• Performing calculations across multiple worksheets
• Consolidating similarly structured monthly, departmental or project sheets
• Referencing data stored in other workbooks
• Updating and reviewing external links
• Avoiding problems caused by moved, renamed or deleted source data
Module 7: Creating and formatting charts
• Selecting suitable chart types for different messages
• Creating column, bar, line and pie charts
• Selecting and adjusting chart data ranges and series
• Editing chart titles, axes, legends and data labels
• Adjusting number formats and chart scales
• Updating, moving and integrating charts into reports
Module 8: Practical case study and final project
• Building a complete calculation worksheet from source data
• Applying intermediate formulas and conditional calculations
• Linking information from multiple worksheets
• Filtering and analysing relevant records
• Creating a suitable chart to present the results
• Reviewing, documenting and presenting the completed analysis
Hands-on learning with expert instructors at your location for organizations.
Master new skills guided by experienced instructors from anywhere.
