Thank you for sending your enquiry! One of our team members will contact you shortly.
Thank you for sending your booking! One of our team members will contact you shortly.
Duration 28 hours
Course Outline
Excel Introduction
- Overview of Excel features and interface layout
- Comprehending the structure of rows, columns, and cells
- Interface navigation and essential keyboard shortcuts
Basic Data Entry and Editing
- Inputting data into individual cells
- Selecting, copying, pasting, and applying cell formats
- Applying basic text styles (font, size, color, etc.)
- Distinguishing between data types (text, numbers, dates)
Simple Calculations and Formulas
- Executing fundamental arithmetic operations (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 opening new workbooks
- Managing multiple worksheets (renaming, deleting, inserting, moving)
- Configuring basic print settings (page layout, print area)
Basic Data Formatting
- Formatting cell data (numbers, dates, currency)
- Adjusting row heights and column widths (including hiding/unhiding)
- Applying cell borders and shading
Introduction to Charts and Graphs
- Generating simple chart types (bar, line, pie)
- Formatting and modifying existing charts
Basic Data Sorting and Filtering
- Sorting data based on text, numbers, or dates
- Applying basic data filters
Advanced Formulas and Functions
- Implementing logical functions (IF, AND, OR)
- Using text-based functions (LEFT, RIGHT, MID, LEN, CONCATENATE)
- Employing lookup functions (VLOOKUP, HLOOKUP)
- Applying mathematical and statistical functions (MIN, MAX, COUNT, COUNTA, AVERAGEIF)
Working with Tables and Ranges
- Creating and managing structured tables
- Sorting and filtering data within tables
- Using structured references in table formulas
Conditional Formatting
- Defining rules for conditional formatting
- Customizing visual indicators (data bars, color scales, icon sets)
Data Validation
- Configuring data entry rules (e.g., drop-down lists, numeric limits)
- Setting error alerts for invalid data inputs
Data Visualization with Charts and Graphs
- Advanced chart customization and styling
- Building combination charts (e.g., integrating bar and line elements)
- Incorporating trendlines and secondary axes
Pivot Tables and Pivot Charts
- Constructing pivot tables for detailed data analysis
- Generating pivot charts for visual data representation
- Applying grouping and filtering techniques in pivot tables
- Enhancing data interaction using slicers and timelines
Data Protection
- Securing specific cells and worksheets
- Applying password protection to workbooks
Basic Macros
- Learning to record simple macros
- Executing and modifying macro commands
Advanced Formulas and Functions
- Building complex logic with nested IF statements
- Mastery of advanced lookup functions (INDEX, MATCH, XLOOKUP)
- Utilizing array formulas and functions (SUMPRODUCT, TRANSPOSE)
Advanced Pivot Tables
- Defining calculated fields and items within pivot tables
- Establishing and managing relationships between 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
- Introduction to Power Query for importing and transforming data
- Connecting to external data sources (e.g., databases, web interfaces)
- Cleaning and transforming datasets within Power Query
Power Pivot
- Building data models and defining relationships
- Creating calculated columns and measures using DAX (Data Analysis Expressions)
- Enhancing pivot tables with Power Pivot capabilities
Advanced Charting Techniques
- Developing dynamic charts using formulas and data ranges
- Customizing chart behavior through VBA
Automation with Macros and VBA
- Introduction to Visual Basic for Applications (VBA)
- Developing custom macros to automate repetitive processes
- Creating user-defined functions (UDFs)
- Debugging code and implementing error handling in VBA
Collaboration and Sharing
- Sharing workbooks for co-authoring
- Tracking changes and managing version control
- Collaborating via Excel integration with OneDrive and SharePoint
Summary and Next Steps
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,
