Data Analytics: Analyzing Data with Ms-Excel (Advanced Level)

 4^th-5^th March, 2025                                            12^th-13^th June, 2025

 18^th– 19^th August, 2025                                      5^th-6^th November, 2025

 Introduction

Effective data management is essential for any organization to succeed, and when utilized appropriately, Microsoft Excel can be a very powerful tool for organizing, analyzing, and storing your data.

Those who are already familiar with Excel and want to learn more about its many time-saving features and advanced data analysis capabilities should take this Advanced level course for Microsoft Excel.

Prerequisites:

Equivalent knowledge of Microsoft® Office Excel Levels (Basic and Intermediate) is necessary to assure your success. Experienced Excel users who have been managing huge volumes of data and utilize pivot tables to summarize and analyze the data and decide to step further in data analytics. To help them make business decisions, users will master more sophisticated features and Excel tools.

Learning Objectives

Participants will learn among others:

v Data Analysis and Manipulation: Advanced Formulas and Functions; data consolidation, data table, sparkline;

v Power Query: Data transformation; import and export of data from different source.

v Automation and Efficiency: Macro, introduction to Visual Basic, form control

v Collaboration and Security: Worksheet and Workbook Protection

v Advanced Charting and Visualization: Custom Chart Creation; chart customization

v General Excel Proficiency: Advanced Navigation; custom formatting; error handling

 Course-Contents

1. Advanced Functions & Formulas:

·       Lookup Functions: VLOOKUP, INDEX, MATCH, XLOOKUP.

·       Logical Functions: IF, AND, OR, IFERROR, Nested IF statements.

·   Math & Statistical Functions: SUMIFS, COUNTIFS, AVERAGEIFS, SUMPRODUCT, NPV, IRR, PMT.

·       Text Functions: CONCATENATE, LEFT, RIGHT, MID, TRIM, TEXT, FIND/SEARCH.

·       Date & Time Functions: DATE, EOMONTH, TODAY, NETWORKDAYS.

·       Array Formulas: Understanding and using array formulas for advanced calculations.

·       Formula Auditing: Techniques for debugging and understanding complex formulas. 

2. Data Analysis & Manipulation:

·       Pivot Tables: Creating, customizing, and analyzing data using pivot tables. 

·       Data Validation: Implementing rules to control data entry and ensure data accuracy. 

·       Data Consolidation: Combining data from multiple sources or worksheets. 

·       Conditional Formatting: Applying formatting rules based on cell values or formulas. 

·    Power Query: Using Power Query (Get & Transform) to import, clean, and transform data from various sources. 

·  Charts & Slicers: Creating and customizing various chart types and using slicers for interactive filtering. 

·    What-If Analysis: Using Goal Seek, Scenario Manager, and Solver for analyzing different scenarios. 

3. Automation & VBA:

·       Introduction to Macros: Understanding the purpose and functionality of macros. 

·       Recording Macros: Recording a sequence of actions to automate repetitive tasks. 

·  VBA (Visual Basic for Applications): Introduction to VBA programming for more advanced automation. 

·    Working with VBA Editor: Accessing and using the VBA editor to modify and create macros. 

·   Assigning Macros: Assigning macros to buttons, keyboard shortcuts, and other controls.

4. Additional Topics:

·       Excel Security: Protecting workbooks and worksheets. 

·       Printing: Setting up print areas, headers, and footers. 

·       Excel Dashboards: Creating interactive dashboards for data visualization. 

·   Working with Named Ranges: Creating and using named ranges for easier formula creation. 

·       Using Form Controls: Adding form controls like buttons, combo boxes, and scroll bars to interact with spreadsheets. 

Duration: Two (2) days                       Fee: N200,000