Course Outline
Fundamentals of Excel
- Overview of the Excel interface and capabilities
- Comprehending rows, columns, and cell structures
- Essential navigation and keyboard shortcuts
Core Data Entry and Editing
- Inputting data into cells
- Managing cell selection, copying, pasting, and formatting
- Applying basic text styles (font, size, color)
- Distinguishing between data types (text, numbers, dates)
Foundational Calculations and Formulas
- Performing basic arithmetic (addition, subtraction, multiplication, division)
- Getting started with formulas (e.g., SUM, AVERAGE)
- Utilizing the AutoSum feature
- Differentiating between absolute and relative cell references
Managing Worksheets and Workbooks
- Creating, saving, and accessing workbooks
- Handling multiple worksheets (naming, removing, inserting, reordering)
- Configuring basic print settings (layout, print area)
Basic Data Presentation
- Applying cell formats (number, date, currency)
- Modifying row and column dimensions (width, height, visibility)
- Adding borders and shading to cells
Starting with Charts and Graphs
- Building simple visualizations (bar, line, pie charts)
- Editing and styling charts
Basic Data Organization
- Sorting data based on text, numeric, or date values
- Applying simple filters
Intermediate Formulas and Functions
- Using logical functions (IF, AND, OR)
- Manipulating text (LEFT, RIGHT, MID, LEN, CONCATENATE)
- Employing lookup functions (VLOOKUP, HLOOKUP)
- Applying mathematical and statistical functions (MIN, MAX, COUNT, COUNTA, AVERAGEIF)
Managing Tables and Ranges
- Setting up and managing Excel tables
- Sorting and filtering data within tables
- Using structured references
Conditional Formatting
- Defining rules for conditional formatting
- Customizing appearances (data bars, color scales, icon sets)
Data Validation
- Establishing entry rules (e.g., dropdown lists, numeric bounds)
- Configuring error alerts for invalid inputs
Data Visualization Enhancements
- Advanced chart customization
- Building combination charts (e.g., mixed bar and line)
- Incorporating trendlines and secondary axes
Pivot Tables and Pivot Charts
- Constructing pivot tables for analytical insights
- Using pivot charts for visual storytelling
- Grouping and filtering pivot data
- Enhancing interaction with slicers and timelines
Securing Data
- Locking specific cells and worksheets
- Applying password protection to workbooks
Introduction to Macros
- Recording basic macros
- Executing and modifying macro code
Advanced Formula Mastery
- Building nested IF statements
- Advanced lookups (INDEX, MATCH, XLOOKUP)
- Working with array formulas (SUMPRODUCT, TRANSPOSE)
Advanced Pivot Table Strategies
- Adding calculated fields and items
- Managing complex pivot table relationships
- Deep-dive into slicers and timelines
Advanced Analytical Tools
- Consolidating data from multiple sources
- What-If analysis (Goal Seek, Scenario Manager)
- Using the Solver add-in for optimization
Power Query Essentials
- Introduction to Power Query for data ingestion and transformation
- Linking to external sources (databases, web)
- Cleaning and reshaping data within Power Query
Power Pivot Capabilities
- Constructing data models and relationships
- Creating calculated columns and measures with DAX
- Building advanced pivot tables via Power Pivot
Advanced Charting Methods
- Creating dynamic charts driven by formulas
- Customizing chart behavior using VBA
Workflow Automation with VBA
- Introduction to Visual Basic for Applications
- Developing custom macros for repetitive tasks
- Creating user-defined functions (UDFs)
- Debugging and error management in VBA
Collaborative Features
- Co-authoring and sharing workbooks
- Tracking revisions and version control
- Integrating Excel with OneDrive and SharePoint for team collaboration
Conclusion and Future Directions
Requirements
- Fundamental computer literacy
- Basic familiarity with Excel
Target Audience
- Data analysts
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,
