Course Outline
Microsoft Excel Fundamentals
- Excel interface overview and core components
- Structure of rows, columns, and cells
- Navigation techniques and essential keyboard shortcuts
Data Entry and Editing Procedures
- Inputting data into cell fields
- Selecting, copying, pasting, and formatting cell contents
- Text formatting options (font type, size, color)
- Distinguishing between data types (text, numeric, date values)
Fundamental Calculations and Formula Application
- Basic arithmetic operations (addition, subtraction, multiplication, division)
- Foundational formula usage (e.g., SUM, AVERAGE)
- Utilizing the AutoSum feature
- Differentiating between absolute and relative cell references
Managing Worksheets and Workbooks
- Creating, saving, and opening workbooks
- Handling multiple worksheets (renaming, deleting, inserting, moving)
- Configuring print settings (page layout, defining print areas)
Cell Formatting Standards
- Applying number, date, and currency formats to cells
- Adjusting row height and column width; hiding or unhide rows/columns
- Applying cell borders and background shading
Introduction to Visual Data Representation
- Generating standard charts (bar, line, pie)
- Formatting and modifying chart elements
Data Sorting and Filtering Techniques
- Sorting datasets by text, numeric, or chronological order
- Applying basic data filters to isolate specific criteria
Intermediate Formulas and Functions
- Logical functions (IF, AND, OR)
- Text manipulation functions (LEFT, RIGHT, MID, LEN, CONCATENATE)
- Lookup functions (VLOOKUP, HLOOKUP)
- Mathematical and statistical functions (MIN, MAX, COUNT, COUNTA, AVERAGEIF)
Working with Excel Tables and Data Ranges
- Creating and managing structured tables
- Sorting and filtering within Excel tables
- Utilizing structured references in table formulas
Conditional Formatting Applications
- Establishing rules for conditional formatting
- Customizing formats using data bars, color scales, and icon sets
Data Validation Rules
- Configuring entry constraints (e.g., drop-down lists, numeric limits)
- Defining error alerts for invalid data inputs
Advanced Data Visualization
- Customizing and formatting complex charts
- Constructing combination charts (e.g., overlaying bar and line graphs)
- Incorporating trendlines and secondary axes
Pivot Tables and Pivot Charts
- Building pivot tables for data summarization and analysis
- Generating pivot charts for visual insights
- Grouping and filtering data within pivot tables
- Using slicers and timelines to enhance data interaction, particularly for government reporting needs
Data Security and Protection
- Protecting cells and worksheets with password controls
- Restricting workbook editing capabilities
Introduction to Macros
- Recording basic macros for task automation
- Executing and modifying recorded macros
Complex Formulas and Functions
- Implementing nested IF statements
- Utilizing advanced lookup methods (INDEX, MATCH, XLOOKUP)
- Applying array formulas and functions (SUMPRODUCT, TRANSPOSE)
Advanced Pivot Table Management
- Defining calculated fields and items within pivot tables
- Establishing relationships between multiple pivot tables
- Maximizing the utility of slicers and timelines for detailed analysis
Data Analysis Tools and Optimization
- Consolidating data from multiple sources
- Performing What-If analysis (Goal Seek, Scenario Manager)
- Using the Solver add-in for complex optimization problems, especially in resource allocation scenarios for government agencies
Data Transformation with Power Query
- Importing and transforming data using Power Query
- Connecting to external sources (databases, web interfaces)
- Cleaning and shaping data structures via Power Query
Data Modeling with Power Pivot
- Constructing data models and defining relationships
- Calculating columns and measures using DAX (Data Analysis Expressions)
- Enhancing pivot table capabilities through Power Pivot integration
Advanced Charting Strategies
- Developing dynamic charts driven by formulas and variable data ranges
- Customizing chart appearances using VBA scripts
Automation via Macros and VBA
- Overview of Visual Basic for Applications (VBA)
- Writing custom macros to streamline repetitive tasks, ensuring efficiency in government workflows
- Developing user-defined functions (UDFs)
- Implementing debugging techniques and error handling in VBA code
Collaboration and File Sharing Protocols
- Enabling co-authoring and sharing workbooks with stakeholders
- Tracking changes and managing document versions
- Leveraging OneDrive and SharePoint for secure collaboration, critical for government interoperability
Program Summary and Future Learning Paths
Requirements
- Fundamental proficiency in computer operations
- Working understanding of core spreadsheet functionalities
Target Audience
- Data analysts serving in government agencies for government professionals seeking to enhance their analytical capabilities
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,
