Application Training

Microsoft Excel : Essential Advanced Features
Course Outline

Topic

Description / Coverage Area

Welcome & Introduction

Ice breaking, discussion regarding Excel’s capability, weakness and awareness

General mistakes done by us

•Show some general mistakes.

  • Show the solution of General Mistake

Cell Reference (Absolute Vs Relative):

•Understanding Cell reference

•Relative & Absolute cell reference

•Managing cell reference with complex formula

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.

Logical Functions and Operators

•IF() Condition and expression of logical test

•Use of AND, OR and NOT

•Nested IF() with other functions

Convert Number in Words Automatically

•Excel will convert a number value to words

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

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

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

Data Consolidation from Multiple Data Range

•Data Consolidation Using 3-D References

•Data Consolidation Using "Data Consolidate"

•Preparing a sample OPEX budget

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)

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

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

Overview of Day-1

Open discussion, cross question, brainstorming, quiz and wrap-up of Day-1

Refresh of previous day’s topics

Open discussion and revision taking the previous day’s topics

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

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

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

Business Modeling Using What-If Analysis

•The GOAL SEEK command

•Sensitivity Analysis with DATA TABLE

•Business projection using SOLVER

•Using SCENARIO manager

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

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!

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

Mail merge details and errors

  • Steps of Mail Merge
  • Error of Mail Merge

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

Wrap Up Session

Certificate distribution, photo, open discussion

 

Contact with

Please contact with Farhana Yousufi @ 01811448063 for any Queries

Share With