Microsoft Excel Intermediate Level

Category:

Description

This comprehensive two-day training programme builds upon your existing Excel knowledge and takes your skills to the next level. Throughout the day, we will dive into intermediate-level concepts and techniques that will empower you to manipulate data, perform advanced calculations, and create visually appealing reports and analyses.

This training is specifically tailored for individuals who are familiar with the basics of Excel and are eager to expand their proficiency in this powerful spreadsheet software. Whether you’re a business professional, analyst, student, or anyone working with data, this training will equip you with the tools and techniques to work more efficiently and effectively in Excel.

The course has been carefully structured to cover a wide range of topics, including data manipulation and analysis, formulas and functions, advanced formatting techniques, data analysis tools, data validation, and collaboration features. By the end of the day, you will have gained valuable skills and knowledge that can be immediately applied to your everyday work tasks.

Active participation is encouraged throughout the training, as you will have opportunities to practice the concepts through hands-on exercises and engage in discussions. Additionally, you will be provided with valuable resources and references to support your continued learning beyond this training.

This course is HRD Corp (HRDC / HRDF) claimable.

Additional information

Mode

Physical, 2-days
In-House

✓

Course Details

Learning Objectives

Apply advanced data manipulation techniques

  • Sort and filter data efficiently.
  • Utilise advanced filtering options for complex data analysis.
  • Work effectively with tables and structured references.
  • Implement conditional formatting to highlight data patterns.

Demonstrate proficiency in using formulas and functions

  • Utilise intermediate-level functions such as IF, VLOOKUP, and SUMIF.
  • Apply logical functions (AND, OR, NOT) for complex calculations.
  • Nest functions to perform advanced calculations.

Implement advanced formatting techniques

  • Customise cell formatting to enhance visual appeal.
  • Apply styles and themes for consistent formatting.
  • Create and modify templates for efficient document creation.
  • Utilise conditional formatting to visually represent data trends.

Utilise data analysis tools effectively

  • Create and modify PivotTables and PivotCharts for data analysis.
  • Use slicers and timelines to interactively analyse data.
  • Apply What-If Analysis tools (Goal Seek, Scenario Manager) for decision-making.

Learning Benefits

  • Enhanced data management skills for efficient organisation and analysis.
  • Improved analysis and decision-making abilities through advanced formulas and functions.
  • Professional formatting techniques for visually appealing presentations.
  • Proficiency in advanced data analysis tools, including PivotTables and What-If Analysis.
  • Understanding of data validation and security measures for data integrity.
  • Improved collaboration and sharing capabilities for efficient teamwork.
  • Practical application to real-life scenarios, increasing confidence in Excel usage.
  • Access to continuous learning resources for ongoing skill development.

Target Audience

  • Employees of all levels with basic knowledge of Microsoft Excel

Course Outline

DAY 1

Advanced Formulas and Functions

  • Using logical functions (e.g., IF, AND, OR)
  • Working with text functions (e.g., CONCATENATE, LEFT, RIGHT)
  • Date and time functions
  • Lookup and reference functions (e.g., VLOOKUP, HLOOKUP, INDEX, MATCH)

Data Analysis Tools

  • Using filters and advanced filtering
  • PivotTables and PivotCharts
  • Data analysis with scenarios
  • Goal Seek and Solver

Advanced Data Management

  • Consolidating data from multiple sources
  • Data validation techniques
  • Text-to-columns and splitting data
  • Removing duplicates and cleaning data

Advanced Charting and Graphical Elements

  • Advanced chart types (e.g., radar, doughnut, waterfall)
  • Formatting and customising charts
  • Adding secondary axes and combination charts
  • Working with sparklines and slicer

DAY 2

Advanced Data Analysis

  • What-If Analysis (e.g., data tables, scenarios)
  • Statistical analysis using Excel functions
  • Regression analysis and trendlines
  • Introduction to Power Query

Automating Tasks with Macros

  • Recording and running macros
  • Editing and debugging macros
  • Assigning macros to buttons and shortcuts
  • Introduction to VBA (Visual Basic for Applications)

Collaboration and Data Sharing

  • Protecting and sharing workbooks
  • Tracking changes and comments
  • Sharing workbooks via email and cloud storage
  • Introduction to Excel Online and co-authoring

Advanced Tips and Tricks

  • Using keyboard shortcuts for productivity
  • Customising the Excel environment
  • Tips for optimising large datasets
  • Troubleshooting common Excel issues

Related products