LEADS Corporation Limited
HOME > LTCL > Training > Online Training: MS Excel- For Beginners to Advanced

Online Training: MS Excel- For Beginners to Advanced

Last Date of Registration: 27 September, 2020

Date: 28 September, 2020

Time: Monday & Wednesday (6:30 PM – 8:30 PM)

Duration: 20 Hours

Course Fee: 1700.00 TK (Including VAT and TAX)

Contact: 01811-449483, 01847-179477, 01844-051958

 

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

 



Start Course
Teacher: