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)

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.

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!