One Stop Digital Education Portal
Training Outline Microsoft Excel–2016 Beginner to Advance 10 Days Long Professional Excel Training Program Course Duration: 30 Hours A sister concern of- LEADS Corporation Ltd. ISO 9001 : 2008 Certified Course Outline CREATE AND MANAGE WORKSHEETS AND WORKBOOKS Working with Sheets, workbooks, columns, rows, ribbon Hide/unhiding the ribbon Clear concept of Save and Save As Auto saving techniques Creating, editing and deleting hyperlink Changing Sheet tab color, sheet name, order Hiding, unhiding and deleting sheets Resizing columns and rows Hiding/unhiding columns, rows Inserting and deleting columns and rows Changing color theme of your Excel CUSTOMIZE OPTIONS AND VIEWS FOR WORKSHEETS AND WORKBOOKS Customizing Quick Access Toolbar Customizing the ribbon Change Workbook’s views Creating, changing and deleting custom view Managing document properties Changing screen’s magnification Freezing rows and columns CONFIGURE WORKSHEETS AND WORKBOOKS BEFORE PRINTING Set Print Scaling (% of normal Size) Row(s) to be printed at top on every page Fixing number of pages to be printed of whole sheet Managing orientation, paper size, margin Customizing header and footer Printing specific a range Inserting, editing and removing page breaks Printing both sides INSERT DATA IN CELLS AND RANGES Finding a specific text/value and replace by something Clarification of all paste special commands Auto filling techniques with flash fills FORMAT CELLS AND RANGES Merge cells – Merits and it’s demerits Various number formatting Various text formatting Various date/time formatting Customizing your required format Format painter – Learn the magic in formatting CREATE AND MANAGE TABLES Clear concept of Excel table Create an Excel table from a cell range Convert from table to normal cell range Add row/column in the table Managing table styles and options Filtering and sorting the table data START WORKING WITH FUNCTIONS & FORMULAS IN EXCEL The formula structure in Excel Using SUM, MIN, MAX, AVERAGE and COUNT function CELL REFERENCE (RELATIVE VS ABSOLUTE) Techniques to use $ sign in a formula Locking a specific row B$2 Locking a specific column $B2 Locking both row and column $B$ Complex example to use $ sign in a formula COMMON FUNCTIONS FOR TEXT FORMATTING AND DATA TRANSFORMING Extracting text from left, mid and right of cell values Removing unwanted extra spaces Converting to Upper case, lower case and Capitalize Each Word case Joining multiple cell values with plain text Finding the required text using function Counting functions Rounding functions LOGICAL FUNCTION WITH OPERATOR Understanding the IF() condition Using AND(), OR() function in IF() condition Nested IF with AND, OR function WORKING WITH NAME IN EXCEL Creating, changing, deleting and using name Using the name manager DATA SUMMARIZATION WITH CONDITIONAL FUNCTIONS SUMIFS functions, COUNTIFS function AVERAGEIFS function, SUMPRODUCT function CREATE AND CUSTOMIZE CHARTS AND OBJECTS Data mapping for your required chart Choosing the right chart for your data Customizing Column, bar, pie, line chart Formatting the charts Charts for Main category-sub category data presentation Charts for target-achievement data presentation Present multiple chart types in the same chart Creating PivotChart LOOKUP AND REFERENCES DATA MATCHING AND TRANSFORMING Data matching and cross-match techniques Vertical and horizontal Lookup function- VLOOKUP, 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 DATA SORTING & FILTERING Multi-level data filtering with customized conditions Finding unique records in a range and paste at another place Removing Duplicates at the same range Customized filtering using IF () condition Filtering using color Searching techniques for filtering Multi-level data sorting with different conditions ERROR HANDLING AND FORMULA AUDITING Knowing the errors when those happen Circular reference when it happens and how to resolve #VALUE error, #DIV/0! error, #N/A error, #NAME? error, #NUM! error and #REF! Error handling techniques Formula auditing techniques CONFIGURING DATA VALIDATION: STOP TYPING INVALID DATA Restriction incorrect data entry with data validations Creating list with static values or a dynamic range Set condition in a range for accepting only dates, numbers or specific range of values Creating error validation messages DATE FUNCTIONS Do you know how many mistakes you are doing every day for date?? Date formatting using formula and shortcut Presenting day name and month name of a date Combining text with a formatted date Calculating future date adding XX years XX months and XX days Calculating date difference between two dates (Difference of Days, Months and Years) Preparing age calculator (Example: Your age is 25 Years 05 Months and 18 Days) FILE SECURITY & PROTECTION: STOP UNAUTHORIZED ACCESS Protect workbook so that it can be used by anyone with a limited use of Excel Steps for locking and protecting cells Create your workbook or worksheet password protected APPLY RULES FOR 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 Conditional formatting for ranking DATA LINKING TECHNIQUES Data linking between multiple worksheets Data linking between multiple workbooks Managing linking and updating Difference between direct and indirect linking PIVOTTABLES, PIVOTCHART AND SLICERS 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 Inserting a calculated field and calculated item Drill-down to the Pivot data Summarizing data by month, quarter, year etc. from date Sorting and filtering techniques in PivotTable data Slicer-Creating, changing and formatting Make your PivotTable dynamic using slicer Creating PivotChart from existing workbook data Make your PivotTable data source dynamic INTEGRATION OF EXCEL & WORD Write thousands of letters in word taking data form Excel within minutes Steps to prepare mail merge Data linking techniques from Word to Excel Field linking techniques in word from Excel Publishing the all letters in a new word file Saving the file and use in again VBA AND MACRO: AUTOMATE YOUR EXCEL Overview of macro in Excel step by step Create your first macro without having any programming knowledge Automation techniques using macro
Price: 7000 Post By: Mehedi সর্ব-শেষ হাল-নাগাদ: 15 Saturday 2020

Training Outline

Microsoft Excel–2016

Beginner to Advance

10 Days Long Professional Excel Training Program

Course Duration: 30 Hours

A sister concern of-

LEADS Corporation Ltd.

ISO 9001 : 2008 Certified

Course Outline

CREATE AND MANAGE WORKSHEETS AND WORKBOOKS

Working with Sheets, workbooks, columns, rows, ribbon
Hide/unhiding the ribbon
Clear concept of Save and Save As
Auto saving techniques
Creating, editing and deleting hyperlink
Changing Sheet tab color, sheet name, order
Hiding, unhiding and deleting sheets
Resizing columns and rows
Hiding/unhiding columns, rows
Inserting and deleting columns and rows
Changing color theme of your Excel


CUSTOMIZE OPTIONS AND VIEWS FOR WORKSHEETS AND WORKBOOKS

Customizing Quick Access Toolbar
Customizing the ribbon
Change Workbook’s views
Creating, changing and deleting custom view
Managing document properties
Changing screen’s magnification
Freezing rows and columns


CONFIGURE WORKSHEETS AND WORKBOOKS BEFORE PRINTING

Set Print Scaling (% of normal Size)
Row(s) to be printed at top on every page
Fixing number of pages to be printed of whole sheet
Managing orientation, paper size, margin
Customizing header and footer
Printing specific a range
Inserting, editing and removing page breaks
Printing both sides


INSERT DATA IN CELLS AND RANGES

Finding a specific text/value and replace by something
Clarification of all paste special commands
Auto filling techniques with flash fills


FORMAT CELLS AND RANGES

Merge cells – Merits and it’s demerits
Various number formatting
Various text formatting
Various date/time formatting
Customizing your required format
Format painter – Learn the magic in formatting


CREATE AND MANAGE TABLES

Clear concept of Excel table
Create an Excel table from a cell range
Convert from table to normal cell range
Add row/column in the table
Managing table styles and options
Filtering and sorting the table data


START WORKING WITH FUNCTIONS & FORMULAS IN EXCEL

The formula structure in Excel
Using SUM, MIN, MAX, AVERAGE and COUNT function


CELL REFERENCE (RELATIVE VS ABSOLUTE)

Techniques to use $ sign in a formula
Locking a specific row B$2
Locking a specific column $B2
Locking both row and column $B$
Complex example to use $ sign in a formula


COMMON FUNCTIONS FOR TEXT FORMATTING AND DATA TRANSFORMING

Extracting text from left, mid and right of cell values
Removing unwanted extra spaces
Converting to Upper case, lower case and Capitalize Each Word case
Joining multiple cell values with plain text
Finding the required text using function
Counting functions
Rounding functions


LOGICAL FUNCTION WITH OPERATOR

Understanding the IF() condition
Using AND(), OR() function in IF() condition
Nested IF with AND, OR function


WORKING WITH NAME IN EXCEL

Creating, changing, deleting and using name
Using the name manager


DATA SUMMARIZATION WITH CONDITIONAL FUNCTIONS

SUMIFS functions, COUNTIFS function
AVERAGEIFS function, SUMPRODUCT function


CREATE AND CUSTOMIZE CHARTS AND OBJECTS

Data mapping for your required chart
Choosing the right chart for your data
Customizing Column, bar, pie, line chart
Formatting the charts
Charts for Main category-sub category data presentation
Charts for target-achievement data presentation
Present multiple chart types in the same chart
Creating PivotChart


LOOKUP AND REFERENCES DATA MATCHING AND TRANSFORMING

Data matching and cross-match techniques
Vertical and horizontal Lookup function- VLOOKUP, 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


DATA SORTING & FILTERING

Multi-level data filtering with customized conditions
Finding unique records in a range and paste at another place
Removing Duplicates at the same range
Customized filtering using IF () condition
Filtering using color
Searching techniques for filtering
Multi-level data sorting with different conditions


ERROR HANDLING AND FORMULA AUDITING

Knowing the errors when those happen
Circular reference when it happens and how to resolve
#VALUE error, #DIV/0! error, #N/A error, #NAME? error, #NUM! error and #REF!
Error handling techniques
Formula auditing techniques


CONFIGURING DATA VALIDATION: STOP TYPING INVALID DATA

Restriction incorrect data entry with data validations
Creating list with static values or a dynamic range
Set condition in a range for accepting only dates, numbers or specific range of values
Creating error validation messages


DATE FUNCTIONS

Do you know how many mistakes you are doing every day for date??
Date formatting using formula and shortcut
Presenting day name and month name of a date
Combining text with a formatted date
Calculating future date adding XX years XX months and XX days
Calculating date difference between two dates (Difference of Days, Months and Years)
Preparing age calculator (Example: Your age is 25 Years 05 Months and 18 Days)


FILE SECURITY & PROTECTION: STOP UNAUTHORIZED ACCESS

Protect workbook so that it can be used by anyone with a limited use of Excel
Steps for locking and protecting cells
Create your workbook or worksheet password protected


APPLY RULES FOR 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
Conditional formatting for ranking


DATA LINKING TECHNIQUES

Data linking between multiple worksheets
Data linking between multiple workbooks
Managing linking and updating
Difference between direct and indirect linking


PIVOTTABLES, PIVOTCHART AND SLICERS

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
Inserting a calculated field and calculated item
Drill-down to the Pivot data
Summarizing data by month, quarter, year etc. from date
Sorting and filtering techniques in PivotTable data
Slicer-Creating, changing and formatting
Make your PivotTable dynamic using slicer
Creating PivotChart from existing workbook data
Make your PivotTable data source dynamic


INTEGRATION OF EXCEL & WORD

Write thousands of letters in word taking data form Excel within minutes
Steps to prepare mail merge
Data linking techniques from Word to Excel
Field linking techniques in word from Excel
Publishing the all letters in a new word file
Saving the file and use in again


VBA AND MACRO: AUTOMATE YOUR EXCEL

Overview of macro in Excel step by step
Create your first macro without having any programming knowledge
Automation techniques using macro