PROFESSIONAL DEVELOPMENT PROGRAM

 

Note:

-- Lunch, Tea Break & Refreshment will be arranged by the organizer
-- Training Materials will be provided by the organizer
-- Seats are limited and will be served on 'First Come-First Serve' basis
-- Participants will be awarded certificates at the end of the session

Who Should Attend

Regular Excel users having basic Excel Knowledge and want to enhance their Excel productivity, data analyzers, management reporters, decision makers and students who wish to gather comprehensive Excel knowledge.

Microsoft Excel - Advanced Professional Tips, Tricks and Functions



Date : 20 Jan 2017-21 Jan 2017
Time : 10:00AM to 6:00PM
Fee  : 7000.00 BDT
Training venue :
UPDATE
UTC Tower (Ground Floor), 8 Panthapath, Dhaka (Beside Basundhara Shopping Complex)
Training Objective
Overview of this Training:
Microsoft Excel is the most essential and powerful application of Microsoft Office package. It is widely used for data recording, summarizing, interactive reporting and various types of critical data analysis. Sometimes you may struggle and kill huge times to do your daily excel works and fulfill your management’s requirements. If you are aware about the capability of Excel’s most productive built-in tools, techniques and features, you can save huge times and retrieve the core information easily in very short time professionally.

This professional training will help you through various functions, tools, shortcuts and time saving tips & tricks to boost up your Excel productivity and you will learn how to map and handle Excel data professionally that will work for you. After completion this training, you will be able to solve your excel related work confidently. Your daily excel work will be easier and you will be interested to learn the magic of excel.

Objective of this Training:
You can prepare any kind of report very easily within short time if you have sound knowledge in data mapping, various formula building techniques, direct and indirect linking, data consolidation and summarization, visual presentation, Excel’s built-in Business Intelligence (BI) tools, techniques to handle millions of data and Excel’s amazing productive tricks. These wonderful features will definitely enhance your productivity and make your life easier.

Learning Methodologies
Presentation with Excel 2010 (If you use Excel 2007, 2013 or mac, you will get instant guideline)
Hand working with trainer’s provided working data
Completely interactive and participative
Training booklet that will be interactively connected with working data
Problem solving and Brainstorming
Open discussion (Q & A session)
Training Content
Cell Reference (Absolute Vs Relative):
What is relative & absolute cell reference?
Different types of use of references (A4, $A4, A$4, $A$4)
Managing cell reference with complex formula in easy rule

Data Re-Arranging techniques
Changing data type- Text to number
Changing data type- Number to Text
Highlighting all blanks/errors/texts within a range
Date formatting customization
Data import from text file
Finding duplicates and removing techniques
Copying/moving cell values except hidden cells
Data transposing using function and tricks

Workbook, Worksheet and PageCustomization
Row(s) to be printed at top on every page
Fixing number of pages to be printed of whole sheet
Professional printing tricks-horizontal & vertical
Do you have 100 sheets? View all sheets in a list
Auto saving techniques
Bookmarking the most using Excel files
About Excel’s compatibility checker

Ribbon and Quick Access Toolbar Customization
Activate inactivated tabs
Change or remove existing group
Add new groups in the ribbon
Add/remove tools in Quick Access Toolbar

Use Essential Functions for Restructuring Usable Data Table
Joining multiple cells
Extracting the required portion of a cell value
Removing unwanted extra spaces
Convert a cell text to your required case
Making a function as a variable
Converting a Text number to a computable pure number or date
Rounding a cell value in different ways
Join a text with formatted number.

Convert Number in Words Automatically
Procedure to convert a number into words

Logical Functions and Operators
IF() Condition and expression of logical test
Use of AND, OR and NOT
Nested IF() with other functions

Lookup & References
Calling the required information matching a text/number
Vertical Lookup function- VLOOKUP
Horizontal Lookup function- HLOOKUP
Complex uses of VLOOKUP/HLOOKUP
2-way lookups- auto matching lookup value and column heading
MATCH function and INDEX function
Combination of MATCH and INDEX function
Dynamic reporting using INDIRECT function
OFFSET function

Naming in Excel
Examples of various types of name
Name type-Cell reference, range of cells, formula and constant value
Use name in a formula and avoid complexity

Date Functions:
Do you know how many mistakes are you doing every day using date??
Separating DAY, MONTH and YEAR from a complete date
Presenting day name and month name from a date
Combining date with a text (Report Date: 31/Dec/2016)
Calculating future date adding years, months and days
Calculating date difference using DATEDIF()
Preparing age calculator (Example: Your age is 25 Years 05 Months 16 Days)

Data Validation and Protection
Protect your workbook so that it can be used by anyone with a limited range
Locking and protecting cells
Hiding Excel formula
Restriction incorrect data entry with data validations
Creating list with static values or a dynamic range
Creating error validation messages
Create your Workbook password protected

Decorate Your Report Using Advanced Conditional Formatting
Format automatically when your required criteria meet true
Using multiple Conditional Formatting in a range
Using Data Bars, color scales and icon sets for great presentation
Advanced formula based Conditional Formatting

Advanced Data Sorting & Filtering
Multi-level data Sorting using multiple conditions
Finding unique records or duplicate records
Removing Duplicates at the same range
Data filtering using multiple conditions
Multi-level data filtering with customized conditions
Customized filtering using IF () condition

Error Handling and Cleaning Reports
Type of Excel errors and when it occurs
Circular reference
Error handling using ISERROR, IFERROR and other functions
Magic tips to find error cells and cleaning
Formula tracing and debugging techniques

Data Linking Techniques
Introduction of data linking
Data linking between multiple worksheets
Data linking between multiple workbooks
Managing linking and updating
Difference between direct and indirect linking

Describe Your Data Using PivotTables, PivotChart and Slicers
Data mapping to prepare a PivotTable
Understanding PivotTable Field List
Row Labels, column labels, values area, report filter
Examples of various types of PivotTable in different angles
Various types of report layout-report in compact form, report in tabular form
Customizing subtotal at any row label data and subtotal category
Changing PivotTable data source
Inserting a calculated field and calculated item
Drill-down to the Pivot data
Summarizing data by month, quarter, year etc. from date
Decorating reports and changing styles
Containing customized data format that will not be changed on refresh
Containing customized column width that will not be changed on refresh
Applying conditional formatting to a PivotTable that will not be changed on refresh
Decorating PivotTable report using “Data Bar”, “Color Scales” and “Icon Sets”
Sorting and filtering techniques in PivotTable data
What is slicer in PivotTable?
Creating, changing and formatting slicer
Make your PivotTable dynamic using slicer
Filling empty cells with zero (0)
Creating PivotChart from existing workbook data
Make your PivotTable data source dynamic

Data Summarizing Techniques from Large Data
Data summarizing from a large dataset using functions
Basic & advanced SUMPRODUCT function
COUNTIFS, SUMIFS and AVERAGEIFS functions
Use these functions with multiple criteria and conditions
Data summarizing using INDIRECT linking

Data Consolidation from Multiple Data Range
Data Consolidation Using 3-D References
Data Consolidation Using "Data Consolidate"
Preparing a sample OPEX budget

Creating Chart and Visual Presentation
Data visualization using Sparklines
Choosing the perfect chart for your data
Creating various types of Charts-Column, Bar, Pie, Line etc.
Customize your chart’s labels, axes and background
Use different chart type for different series within a chart

Macros (Basic): Automate Your Excel Action
Overview of macro in Excel step by step
Create your first macro without having any programming knowledge
Automation techniques using macro
Creating macro to generate report using criteria
Carefulness of macros!

Problem Solving Session
Share your Excel problem
The facilitator will share some critical real cases
Group work with brainstorming
Facilitator(s) Profile
 

MD. NAZMUL MUNEER
Excel Automation Expert & Professional Trainer
  Md. Nazmul Muneer is one of the key contributors in the Accounts & Finance department of Beximco Pharmaceuticals Ltd. with 8 years job experience and has previous work experience in Energypac Power Generation Ltd. Mr. Muneer also works as a freelance Excel automation consultant and trainer. In the last few years he has conducted various professional Excel training programs in many national and multinational corporate houses and educated in Excel more than 1,000 participants through effective training. He is a regular Excel trainer of some leading professional training institutes as a popular Excel trainer. He has also been providing consultation and technical services to different companies in the UK and USA on Excel Automation, helping them to create new processes, reports and streamlines those are already in live environment. He is expertise in developing automated formula based Excel dashboard, macro enabled & VBA based Excel workbook, strategic business model, various problem solving calculators, What-if Analysis model, data analysis dashboard using Business Intelligence (BI) tools, KPI automation etc. Mr. Muneer has obtained educational experience in two different disciplines. He is a post graduate in Accounting and has a Diploma in Professional Software Engineering. These have enabled him to acquire vast IT knowledge on developing Database Software, Advanced Excel with VBA, Access with programming, VB, SQL server, Crystal report, IBM AS/400, ORACLE E-Business Suit as well as various accounting packages.
Registration Details
Registration payment can be made by cash or A/C Payee cheque in favor of Business Express, before January 19, 2017, till 07:57 pm

Registration Point
Business Express H:38/A (3rd Floor),R: 9/A Dhanmondi, Dhaka-1209

Mob: 01717801435
E-Mail: businessexpressbd24@gmail.com

Last date of registration: Thursday, 19 Jan 2017
We offer In-House/Customized training on Sales, Leadership, Finance, HR, RMG or any Soft Skill Topics.