Last Date of Registration: 19 January, 2020
Start Date: 20 January, 2020
Time: Monday, Wednesday (06:30 PM – 08:30 PM)
Duration: 20 Hours
Course Fee: 1,700.00 TK (Excluding VAT & TAX)
Contact: 01844-051958, 01811-449483, 01847-179477

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