Course Outline
Fundamentals of Excel
- Introduction to Excel and its user interface
- Comprehending the structure of rows, columns, and cells
- Interface navigation and essential keyboard shortcuts
Essential Data Entry and Editing
- Inputting data into cells
- Selecting, copying, pasting, and formatting cell content
- Basic text styling (font, size, color, etc.)
- Distinguishing between data types (text, numbers, dates)
Simple Calculations and Formula Basics
- Performing basic arithmetic operations (addition, subtraction, multiplication, division)
- Introduction to core formulas (e.g., SUM, AVERAGE)
- Utilizing the AutoSum feature
- Understanding absolute versus relative cell references
Managing Worksheets and Workbooks
- Creating, saving, and opening workbook files
- Handling multiple worksheets (renaming, deleting, inserting, moving)
- Configuring basic print settings (page layout, print area)
Foundational Data Formatting
- Formatting cells for numbers, dates, and currency
- Adjusting row and column dimensions (width, height, hiding/unhiding)
- Applying cell borders and shading
Getting Started with Charts and Graphs
- Generating simple charts (bar, line, pie)
- Formatting and modifying chart elements
Basic Data Sorting and Filtering
- Sorting data based on text, numbers, or dates
- Applying simple data filters
Intermediate Formulas and Functions
- Using logical functions (IF, AND, OR)
- Applying text manipulation functions (LEFT, RIGHT, MID, LEN, CONCATENATE)
- Employing lookup functions (VLOOKUP, HLOOKUP)
- Utilizing math and statistical functions (MIN, MAX, COUNT, COUNTA, AVERAGEIF)
Working with Excel Tables and Ranges
- Creating and managing structured tables
- Sorting and filtering data within tables
- Using structured references in tables
Conditional Formatting
- Setting rules for conditional formatting
- Customizing formats (data bars, color scales, icon sets)
Data Validation Techniques
- Establishing data entry rules (e.g., drop-down lists, numeric limits)
- Configuring error messages for invalid entries
Advanced Data Visualization with Charts
- Refining chart formatting and customization
- Constructing combination charts (e.g., integrating bar and line elements)
- Incorporating trendlines and secondary axes
Pivot Tables and Pivot Charts
- Building pivot tables for analytical purposes
- Utilizing pivot charts for visual representation
- Applying grouping and filtering within pivot tables
- Enhancing data interaction with slicers and timelines
Securing Your Data
- Locking specific cells and worksheets
- Password-protecting entire workbooks
Foundations of Macros
- Introduction to recording basic macros
- Executing and modifying macro code
Sophisticated Formulas and Functions
- Constructing nested IF statements
- Advanced lookup techniques (INDEX, MATCH, XLOOKUP)
- Working with array formulas and functions (SUMPRODUCT, TRANSPOSE)
Advanced Pivot Table Techniques
- Creating calculated fields and items within pivot tables
- Establishing and managing relationships in pivot tables
- In-depth application of slicers and timelines
Advanced Data Analysis Tools
- Consolidating data from multiple sources
- Conducting What-If analysis (Goal Seek, Scenario Manager)
- Using the Solver add-in for optimization challenges
Power Query Mastery
- Introduction to Power Query for data import and transformation
- Establishing connections to external data sources (e.g., databases, web)
- Cleaning and transforming data within Power Query
Power Pivot and Data Modeling
- Building data models and defining relationships
- Creating calculated columns and measures using DAX (Data Analysis Expressions)
- Leveraging Power Pivot for advanced pivot table functionality
Advanced Charting Methods
- Developing dynamic charts using formulas and data ranges
- Customizing chart behaviors with VBA
Workflow Automation with Macros and VBA
- Introduction to Visual Basic for Applications (VBA)
- Developing custom macros to automate repetitive processes
- Creating user-defined functions (UDFs)
- Implementing debugging and error handling in VBA
Collaboration and Data Sharing
- Enabling co-authoring and sharing workbooks with others
- Tracking changes and managing version control
- Utilizing Excel integration with OneDrive and SharePoint for collaborative work
Conclusion and Path Forward
Requirements
- Foundational computer literacy
- Basic proficiency in Excel fundamentals
Target Audience
- Data analysts
Testimonials (3)
The shortcuts you can use to trigger certain actions much faster.
Melanie Fuchs - Hydrafacial Germany GmbH
Course - Excel: Basic, Intermediate, Advanced
Machine Translated
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,
