LEADS Corporation Limited
HOME > LTCL > Training > Advanced MS Excel

Advanced MS Excel

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.

COURSE OUTLINE
TimeTopicDescription / Coverage AreaProcess / ApproachTake-away
Day – I
9:00 AM

To

9:15 AM

Welcome & IntroductionIce breaking, discussion regarding Excel’s capability, weakness and awarenessShowing some awesome automated Excel filesSet the interest to learn excel
9:15 AM to

09:25 AM

General mistakes done by us•Show some general mistakes.

·         Show the solution of General Mistake

Practical case studyObtaining clear concept
9:25 AM to

09:55 AM

Cell Reference (Absolute Vs Relative):•Understanding Cell reference

•Relative & Absolute cell reference

•Managing cell reference with complex formula

Practical case study with facilitator’s provided Excel fileObtaining clear concept to use cell reference using appropriate $ sign.
09:55 AM

To

10:45 AM

Use Essential Functions for Restructuring Usable Data Table•Joining multiple cells

•Extracting the required portion of a cell value

•Removing unwanted extra spaces

•Convert a cell text to your required case

•Converting a Text number to a computable pure number or date

•Rounding a cell value in different ways

•Join a text with formatted number or date.

Practical case study with facilitator’s provided Excel fileClear concept to re-organize raw data to manageable structured data so that user can generate report easily
TEA BREAK             
11:00 AM

To

11:15 AM

Logical Functions and Operators•IF() Condition and expression of logical test

•Use of AND, OR and NOT

•Nested IF() with other functions

Practical case study with facilitator’s provided Excel fileUsing IF() condition in different ways for different purposes
11:15 AM

To

11:25 AM

Convert Number in Words Automatically•Excel will convert a number value to wordsPractical case study with facilitator’s provided Excel fileActivation of Add-Ins and learning how to manage it.
11:25 AM

To

01:00 PM

Lookup & References•Calling the required info matching a text/number

•Vertical Lookup function- VLOOKUP

•Horizontal Lookup function- 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

Practical case study with facilitator’s provided Excel fileThis topic will help user for

•Reconciliation

•Importing data

•Filling data matching cell value

02:15 PM

To

02:30 PM

Naming in Excel•Examples of various types of name

•Name type-Cell reference, range of cells, formula and constant value

•Use name in a formula and avoid complexity

Practical case study with facilitator’s provided Excel fileUnderstanding name in Excel and using for dynamic model
LUNCH BREAK                  
02:30 PM

To

03:00 PM

Data Summarizing Techniques from Large Data•Basic & advanced SUMPRODUCT function

•COUNTIFS, SUMIFS and AVERAGEIFS functions

•Use these functions with multiple criteria and conditions

•Data summarizing using INDIRECT linking

Practical case study with facilitator’s provided Excel filePreparing summarized dynamic reports using functions from large dataset
03:00 PM

To

03:30 PM

Data Consolidation from Multiple Data Range•Data Consolidation Using 3-D References

•Data Consolidation Using “Data Consolidate”

•Preparing a sample OPEX budget

Practical case study with facilitator’s provided Excel fileTechniques to consolidate data in one structured sheet from multiple sources
03:30 PM

To

04:00 PM

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 date with a text (Report Date: 31/Dec/2014)

•Completion a date from separate DAY, MONTH and YEAR columns

•Calculating future date adding 3 years 8 months and 18 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)

Practical case study with facilitator’s provided Excel file•Different uses of Date functions those should be known to the Excel users

•Users will also learn how many mistakes they are doing every day for using date.

•Date based notification system

04:00 PM

To

04:30 PM

Decorate Your Report Using 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

Practical case study with facilitator’s provided Excel file•Highlight cells/values by formatting in different way for great presentation those meets the required conditions.

•Data reconciliation

TEA BREAK              
04:45 PM

To

05:15 PM

Advanced Data Sorting & Filtering•Multi-level data Sorting using multiple conditions in different angles

•Finding unique records in a range and paste at another place

•Removing Duplicates at the same range

•Data filtering using multiple conditions

•Multi-level data filtering with customized conditions

•Customized filtering using IF () condition

Practical case study with facilitator’s provided Excel file•Data sorting & filtering in different advanced ways to fulfill management’s requirements

Learning the carefulness for sorting and filtering

05:15 PM

To

05:30 PM

Overview of Day-1Open discussion, cross question, brainstorming, quiz and wrap-up of Day-1Open Discussion with ExcelConsolidation of Day-1
DAY – II
09:00 AM

To

09:30 AM

Refresh of previous day’s topics & Solve AssignmentOpen discussion and revision taking the previous day’s topicsOpen Discussion with ExcelRefresh of previous day’s discussion
09:30 AM

To

10:00 AM

Error Handling and Cleaning Excel Data/Reports•Type of errors and when it occurs

•Circular reference and Carefulness for errors!

•Error handling using ISERROR, IFERROR and ISNA function

•Magic tips to find error cells and cleaning

•Formula tracing and debugging techniques

Practical case study with facilitator’s provided Excel file•Understanding the type of errors

•the reason of error occurring

•The process to handle errors

10:00 AM

To

10:15 AM

Data Linking Techniques•Introduction of data linking

•Data linking between multiple worksheets

•Data linking between multiple workbooks

•Managing linking and updating

•Difference between direct and indirect linking

Practical case study with facilitator’s provided Excel file•Data linking process with various data sources and the process to manage links.

•Carefulness of internal and external links

TEA BREAK              
10:30 AM

To

01:15 PM

Describe Your Data Using PivotTables, PivotChart and Slicers•Data mapping to prepare a PivotTable

•Understanding PivotTable Field List

•Row Labels, column labels, values area, report filter

•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

•Changing PivotTable data source

•Inserting a calculated field and calculated item

•Drill-down to the Pivot data

•Summarizing data by month, quarter, year etc. from date

•Decorating reports and changing styles

•Containing customized data format that will not be changed on refresh

•Containing customized column width that will not be changed on refresh

•Applying conditional formatting to a PivotTable that will not be changed on refresh

•Decorating PivotTable report using “Data Bar”, “Color Scales” and “Icon Sets”

•Sorting and filtering techniques in PivotTable data

•What is slicer in PivotTable?

•Creating, changing and formatting slicer

•Make your PivotTable dynamic using slicer

•Filling empty cells with zero (0)

•Creating PivotChart from existing workbook data

•Make your PivotTable data source dynamic

Practical case study with facilitator’s provided Excel file•Knowing the purposes of PivotTable, general rules to create PivotTable, data mapping and creating a lot of PivotTables in different ways

•Automated reporting techniques in PivotTable using tabular data, slicers, dropdown and charts

•Drill down the hierarchy from summarized values to the end break-down

•Report interaction techniques in PivotTable

LUNCH BREAK                                                                                         
02:30 PM

To

03:00 PM

Business Modeling Using What-If Analysis•The GOAL SEEK command

•Sensitivity Analysis with DATA TABLE

•Business projection using SOLVER

•Using SCENARIO manager

Practical case study with facilitator’s provided Excel fileUnderstanding What-If analysis to take critical decision for your proposed project in different ways
03:00 PM

To

03:30 PM

Creating Chart and Visual Presentation•Data visualization using Sparklines

•Choosing the perfect chart for your data

•Creating various types of Charts-Column, Bar, Pie, Line etc.

•Customize your chart’s labels, axes and background

•Use different chart type for different series within a chart

Practical case study with facilitator’s provided Excel file•Data visualization using various charts/graphs

•Selection the appropriate charts so that you can give your data meaning

3:30 PM

To

4:00 PM

Macros and VBA: Automate Your Excel Action and Reports•Overview of macro & VBA 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!

Practical case study with facilitator’s provided Excel file•Obtaining clear concept of macros.

•Learning automation techniques of your regular excel action to ease repeated job

TEA BREAK
4:15 PM

To

04:50 PM

Bullet-Proofing Your Excel Workbook or Report (Protection & Security)•Protect your workbook so that it can be used by anyone with a limited use of Excel

•Locking and protecting cells

•Restriction incorrect data entry with data validations

•Creating list with static values or a dynamic range

•Creating error validation messages

•Create your Workbook password protected

Practical case study with facilitator’s provided Excel file•Making the workbook secured and validated so that the end user can not enter invalid data and destroy the workbook

•User cannot open the workbook without valid password

04:50 PM

To

05:`10 PM

Mail merge details and errors·         Steps of Mail Merge

·         Error of Mail Merge

Practical casePrepare a mail merge.
05:10 PM

To

05:40 PM

Overview of Day-2 and problem solving session•Share your Excel problem

•The facilitator will share some critical real cases

•Consolidation of 2 day’s learnings

•Feedback

Practical case study with cross questions•Practical problem solving session

•Evaluation of learning for the training

05:40 PM

To

06:00 PM

Wrap Up SessionCertificate distribution, photo, open discussionPresentationMotivation to learn and practice Excel

 



Start Course