Effective Use of Excel – Intermediate to Advance Level

Introduction:

This course is intended for students & professionals who want to learn intermediate to advanced level skills and want to be a excel expert.

Learning Outcomes:

• Advanced Tips & Tricks, formulas and Logics.
• Advanced sorting & filtering Techniques with complex criteria
• Work with tables, slice & dice & table nomenclature.
• Database transformation, merging, appending, cleaning & cleansing
• Create stunning & Outstanding Reports & Dashboards.

Methodology:

• Hands-on exercises provide you with practical experience using Excel
• Assignments consist of real corporate based problems
• Discussion of problems with example
• Solution and its practice

Target Audience:

• Students
• Managers
• Department heads

Course Outline:

Module 1:

1. Interface of Application
2. Advanced Tips & Tricks
3. Flash Fill
4. Excel Working behavior

Module: 02

1. Working with Formulas, Aggregate functions
2. lookup & references Formulas
3. Conditional formulas & its variant for decision making
4. Name and label cells and ranges of cells.
5. Use names and labels in formulas.
6. Create formulas that span multiple worksheets.
7. New formulas of Excel 2019/365- Xlookup, sort, filter, & etc.

Module 03:

Advanced Sorting & Filtering with complex criteria

1. Horizontal & vertical Sorting with quick & custom sort
2. Advanced sorting technique for Variance Calculation
3. Filtering Data in a List & extract unique record list
4. Data filtering with complex criteria

Module 04:

Working with Table

1. Convert dead range/list in to live data
2. Cross filtering in table by using slicer
3. Understand table nomenclature & its importance
4. Call table name or range in formulas
5. Behavior of table on list & charts

Module 05:

Create Pivot Reports

1. Data Shape requirement For Pivot table
2. Classic Pivot layout
3. Slice & dice in pivot by using slicer & Time line
4. Text, Numbers & Date grouping in pivot
5. Create multiple reports by using report filter pages
6. Advanced calculations, horizontal & vertical analysis, ranking, growth & many more
7. Custom calculation & Pivot Charts
8. Dashboards with pivot Table

Module 06:

Dashboards with Power BI

1. Create Stunning & Outstanding dashboards with power View
2. Use cards & built-in visualizations
3. Customization of visualizations

Module 07:

Database Management by using Power Query

1. Convert reports back in to database
2. Data transformation
3. Data merging & Appending
4. Data cleaning & cleansing

Module 08:

Charts & Graphs

1. Create charts & graphs
2. Editing charts layout & component
3. Customization of charts

Module 09:

Macros

1. Understanding of macros
2. Setting macros security
3. Macros recording, saving & Calling
4. Record a simple macro
5. Running of recorded macro
6. Macro charged reports

Date: 28 & 29 Dec 2021

Mode: classroom

Time: 9:00 am to 5:00 pm

Training Fees: PKR 32,500/- + GST/-

Further Details:

Email: bushra.umais@wordpress-764963-3017117.cloudwaysapps.com / uzair.tariq@wordpress-764963-3017117.cloudwaysapps.com

Number: 0333-2406753 / 021-35857484/ 03319763144

Click here to Download Flyer

Cancellation Policy:

PSTD Cancellation Policy – Cancellations made at least 5 working days prior to the program will be refunded 50%. If a booking is canceled in less than 5 working days, no refunds can be given. Cancellations must be confirmed by a letter or email. Substitutions may be made at any time for the same program only. In case of the participant not showing up on the day of the training a replacement can be sent.