Get in Touch
 Duration 28 hours

Course Outline

Foundations of Excel

  • Comprehensive overview of Excel and its user interface
  • Understanding the structure of rows, columns, and cells
  • Interface navigation and essential keyboard shortcuts

Data Input and Basic Editing

  • Populating cells with accurate data
  • Selection, duplication, pasting, and general cell formatting
  • Applying basic text styles (font, size, color, and more)
  • Distinguishing between data types (text, numeric, and date values)

Elementary Calculations and Formula Construction

  • Performing core arithmetic operations (addition, subtraction, multiplication, division)
  • Getting started with formulas (e.g., SUM, AVERAGE)
  • Utilizing the AutoSum functionality
  • Differentiating between absolute and relative cell references

Managing Worksheets and Workbooks

  • Creating, saving, and retrieving workbooks
  • Managing multiple worksheets (renaming, removing, inserting, and reordering)
  • Configuring basic print settings (page layout and print area)

Essential Data Formatting

  • Formatting cell contents (number, date, and currency formats)
  • Modifying row and column dimensions (width, height, and visibility)
  • Applying cell borders and background shading

Introduction to Visual Data Representation

  • Generating basic charts (bar, line, and pie charts)
  • Editing and customizing chart elements

Foundations of Data Sorting and Filtering

  • Organizing data by text, numeric values, or dates
  • Implementing simple data filters

Sophisticated Formulas and Functions

  • Applying logical functions (IF, AND, OR)
  • Manipulating text with functions (LEFT, RIGHT, MID, LEN, CONCATENATE)
  • Executing lookups with VLOOKUP and HLOOKUP
  • Utilizing mathematical and statistical functions (MIN, MAX, COUNT, COUNTA, AVERAGEIF)

Managing Tables and Ranges

  • Establishing and maintaining structured tables
  • Sorting and filtering data within tables
  • Utilizing structured references for enhanced clarity

Conditional Formatting Strategies

  • Implementing rules for dynamic cell highlighting
  • Customizing visual indicators (data bars, color scales, and icon sets)

Enhancing Data Integrity

  • Defining data entry criteria (e.g., drop-down lists and numeric boundaries)
  • Configuring error alerts for invalid data inputs

Advanced Data Visualization

  • Refining chart formats and custom designs
  • Building combination charts (e.g., integrating bars and lines)
  • Incorporating trendlines and secondary axes for deeper analysis

Mastering Pivot Tables and Pivot Charts

  • Constructing pivot tables for multi-dimensional data analysis
  • Leveraging pivot charts for visual data storytelling
  • Grouping and refining data within pivot structures
  • Utilizing slicers and timelines for interactive data exploration

Securing Data Assets

  • Locking specific cells and worksheets to prevent edits
  • Applying password protection to entire workbooks

Foundations of Macro Automation

  • Recording simple macros to capture repetitive actions
  • Executing and modifying macro sequences

Complex Formula Architecture

  • Nesting IF statements for intricate logic
  • Advanced lookup techniques (INDEX, MATCH, XLOOKUP)
  • Working with array formulas and functions (SUMPRODUCT, TRANSPOSE)

Advanced Pivot Table Techniques

  • Creating calculated fields and custom items
  • Establishing and managing relationships between pivot data
  • In-depth application of slicers and timelines

Advanced Analytical Instrumentation

  • Consolidating data from multiple sources
  • Conducting What-If analyses (Goal Seek and Scenario Manager)
  • Using the Solver add-in for optimization modeling

Power Query Integration

  • Introduction to Power Query for robust data import and transformation
  • Connecting to external sources (e.g., databases and web interfaces)
  • Cleaning and restructuring data within the Power Query editor

Power Pivot Modeling

  • Building data models and establishing inter-table relationships
  • Developing calculated columns and measures using DAX (Data Analysis Expressions)
  • Enhancing pivot table capabilities with Power Pivot

Advanced Chart Engineering

  • Designing dynamic charts driven by formulas and data ranges
  • Programmatic chart customization using VBA

Workflow Automation with VBA

  • Introduction to Visual Basic for Applications (VBA)
  • Developing custom macros to eliminate repetitive manual tasks
  • Creating user-defined functions (UDFs) for specialized logic
  • Implementing debugging strategies and error handling protocols

Collaborative Workflows

  • Facilitating co-authoring by sharing workbooks with teams
  • Tracking modifications and managing document versions
  • Leveraging OneDrive and SharePoint for synchronized collaboration

Conclusion and Future Development

Requirements

  • Foundational computer literacy
  • General familiarity with basic Excel operations

Target Learners

  • Data analysts seeking to enhance their analytical toolkit

Custom Corporate Training

Training solutions designed exclusively for businesses.

  • Customized Content: We adapt the syllabus and practical exercises to the real goals and needs of your project.
  • Flexible Schedule: Dates and times adapted to your team's agenda.
  • Format: Online (live), In-company (at your offices), or Hybrid.
Investment

Price per private group, online live training, starting from 5800 € + VAT*

Contact us for an exact quote and to hear our latest promotions

Testimonials (2)

Upcoming Courses

Related Categories