Advanced Excel Short Course
Advanced Excel Short Course 21st to 4th Janaury 2021
This course is tailored for those who perform intensive data analysis which incorporates data from other systems, for example external sources. This may include consolidating corporate budgets and forecasts from various departments, cash flow management, tracking invoices, management reporting among others which require flexible knowledge and use of various Advanced Excel Functions and Features.
10 guided 2-hour sessions with the course facilitator (starting @ 6PM).
Certificates to be issued and delivered upon successful completion of the training
Training will be highly guided by your own needs and expectations
Rare opportunity to learn from a Senior Data Analytics Experts
Position yourself for the next big step in your career
Training sessions will be recorded
Training Fee: ZMK 1,800
Virtual: 2 Weeks (18:30 -20:00 Hrs.)
Days: Monday – Thursday
Time: 18:30 – 20:00 (Live Zoom Sessions). A link will be provided for downloading materials for each session.
To teach participants the advanced formulas as well as how to use which formula for which occasion.
To equip participants with the knowledge on how to debug and audit the advanced formulas.
To explore the magic of analyzing data using Advanced Excel
Apply advanced formulas to lay data in readiness for analysis
Use advanced techniques for report visualizations
Leverage on various methodologies of summarizing data
Understand and apply basic principles of laying out Excel models for decision making
Module 1: Introduction to Advanced Excel
Get a head start with templates and set up an Excel template
Create new default workbook and worksheet templates
Learn how modular templates can make your spreadsheets more consistent and much quicker to set up
Customizing the Excel interface to put the tools that you need at your fingertips.
How to build in checks and controls from the outset
Key techniques that reduce risk and increase automation and efficiency
Documentation and review tools
Module 2: Data Validation
Extended uses of Data Validation
Working with validation formulae
Other methods of tracking down invalid entries
Module 3: Preparing your data for analysis
Mastering lookup functions (INDEX, MATCH, VLOOKUP AND XLOOKUP)
Creating helper columns using DATE and TEXT functions
Applying NESTED-IF, AND, OR to organize data
Module 4: Methods of Summarizing Data
Using SUMIFS and COUNTIFS
Advanced uses of PIVOT-TABLE feature like Value Field Settings, Grouping Data and Slicers among others
Identify Major Customers, Top Products, Top/Bottom Sales reps…LARGE, SMALL, MAX, MIN
Advanced Range Names and Formula in Names
Calculations and reporting in Power Pivot – an introduction to Data Analysis Expressions (DAX)
Module 5: Report Visualization Techniques in Excel
Dynamic charts (using CHOOSE & OFFSET functions) & Sparklines for trends
Report Interactivity -Using Picklists, Form Controls like combo box & Excel Camera
Effectively using Conditional Formatting (formula-driven) for reporting
Exchanging information with VB code
Calculations in Power Pivot – an introduction to Data Analysis Expressions (DAX)
Knowledge & Skills for efficiency and productivity in development
Module 6: Decision Making with Excel
Applications of Financial functions (Amortization table, FV, NPV, IRR, etc.).
Sensitivity (“What-if”) analysis on models using Data Tables, Goal Seek, Scenarios.
Laying out a model in Excel (Budget Model case study);
Using Excel to help you make decisions
Create and manage alternative scenarios
Make more profit or incur less expense by using Excel Solver to identify the best solution
Use of External Data tools
This training targets research analysts, Data collectors, program staff, academia, students, and research consultants responsible for undertaking research related assignments.
All payments should be made using the link below:
If you are unable to pay using the online methods listed above please, contact Keno Institute for alternative methods of payment.
Registration: Interested participants should click on the link below https://kenoinstitute.org/keno-institute-register/ and send a copy of the proof of payment, full details (Full Name, Address, Email Address, contact Number) to email@example.com / firstname.lastname@example.org
Contact line: +260770346838 / +260761685388