8 Days Long Professional Excel Training Program
COURSE BACKGROUND
Microsoft Excel is the most essential and powerful application of Microsoft Office package. Most of the times we need to work with millions of data and we fail to provide the required information from our spreadsheets because we do not preserve data in right way. We can make our routine and special jobs easier if we have comprehensive knowledge in excel with most common to advanced functions, tools, shortcuts, various productive tips & tricks etc.
Managers and executives having excellent spreadsheet knowledge can change the organization’s financial position and grow up in career faster than others. The students also can get job quickly if they have good knowledge in Excel.
This project based practical professional training will help you through various functions, tools, tips and tricks to boost up your Excel productivity and you will learn how to map, record and handle big data that will work for you and generate the interactive reports describing the core information professionally. You will also learn how to produce information into a logical framework, summarize it into a meaningful format, and then display the summary into easy-to-read tables and graphs. Then you will be able to build up various types of data model independently.
LEARNING METHODOLOGIES
Presentation with Excel 2016 (If you use Excel 2007, 2010, 2013 or mac, no problem; you will get instant guideline)
100% practical and hand working with real business case study
The trainer will share 12-15 working files to the all participant’s computer before the training starts
Completely interactive and participative
Training booklet that will be interactively connected with working files
Problem solving, Practical projects and Brainstorming
Open discussion (Q & A session)
WHO SHOULD ATTEND?
This course is designed for the management trainee, productive executives/managers, students and teachers who want to gather widespread knowledge of excel basic, intermediate and advanced level and want to become as an awesome excel master.
CONTENTS OF THIS TRAINING
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 & POWERPOINT
Present your Excel report in PowerPoint presentation
Make built-in your Excel report in PowerPoint
Change the Excel and save live without escaping your presentation
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
Creating macro to generate report using criteria
Carefulness of macros!