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.
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)
Flexibility in the course delivery and the interactive approach. The trainer was open to questions, clarified doubts clearly and also considered participants suggestions during the sessions. The training was well structured and informative.
Soundarya Mohan - Mizuho Bank Europe N.V.
Course - Financial Analysis in Excel
the trainer's patience,