Online Course:Basic to Advanced Excel
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