MS Excel Training Layout with topics

Module 1: Recapping Basics

• • Absolute, Relative References Basic calculations
  • Inserting and deleting sheets
  • Changing the default number of workbook sheets
  • Naming Cell Ranges

Printing and Page Setup

• • Shrink to Fit
  • Adjust page breaks within Page Break Preview
  • Repeat columns and rows
  • Auto and manual scaling
  • Print Grid-lines
  • Save print settings

Module 2: Formatting Workbooks and Templates and managing tables

• Format as Table
• Templates
• Styles
• Paste Special
• Creating Tables
• Naming the Table / Range
• Changing the Table Style
• Creating a Total Row
• Creating a calculated column
• Using Filtering in Table
• Removing the Duplicate Record

Module 3: Sort and Filter Data with Excel

• Sort and filtering (number, color  and text) data
• Custom filtering
• Removing filters from columns
• Conditional Formatting with custom ( formula )

Module 4: Databases

• Sorting Data
• Database Functions
• Sub-totaling
• Outlining and Grouping
• Filtering
• Advanced Filter
• Remove Duplicates
• Data Validation (list, dependent list, decimal, text length, date, whole number)
• Custom Data validation
• Consolidate data
• Text to column

Module 5: The IF Function

• • The Syntax Of IF and nesting the IF Statement
  • Use The AND & OR Operator
  • SUMIF/S, COUNTIF and AVERAGEIF, Subtotal
  • Multiple Criteria Within The Same SUM AVERAGE And COUNT Functions
  • Trim, concatenate, left, right, change case

Module 6: Create Effective Charts

• Present Data Visually
• Inserting Column, Pie chart etc.
• Create an effective chart with Chart Tools- Design, Format, and Layout options
• Adding chart title
• Changing layouts
• Chart styles

• Editing chart data range
• Editing data series
• Changing chart
• Saving chart as template

Module 7: Analyze Data Using PivotTables and Pivot Charts

• Understand PivotTables, Create a PivotTable
• Framework Using the PivotTable and PivotChart
• Create Pivot Chart from pivot Table
• Removing A Pivot Table
• Making Use Of The Report Filter Option
• Sorting A Pivot Tables Columns
• Displaying Values As A Percentage
• Dealing With Empty (NULL) Cells
• Exploring The Additional Pivot Table Options
• Introducing The Slicer Tool
• Managing Your Slices
• Formatting Your Slices
• With date, yearly / quarterly / monthly summarize

Pivot Charts

• • Creating A Pivot Chart
  • Altering Chart Types Formats And Layouts
  • Advanced Layout Control Of A Pivot Chart
  • Filtering A Pivot Chart
  • Hiding Pivot Chart Elements
  • Moving A Pivot Chart Between Sheets

Module 8: Spark lines and Other Useful Functions

Spark lines

• • Creating A Sparkline
  • Change The Design Of Sparklines
  • Dealing with Empty Cells
  • Comparing One Sparkline To Another by Altering Vertical Scale
  • Removing Sparklines From A Sheet

Outlining

• • Outlining Explained
  • Creating An Outline Automatically
  • Creating An Outline Manually
  • Manually Removing Data From An Outline
  • Removing The Outlining From A Worksheet
  • Adjusting A Grouping Created By Automatic Outlining

Custom Views

• • Creating A Custom View Of A Worksheet
  • Changing From One Custom View To Another
  • Editing A Custom View
  • How To Delete A Custom View

Other Useful Functions

• • Welcome To IS Functions
  • Error Checking With ISERROR ISNA And IFERROR
  • The OFFSET Formula Explained
  • Dynamic Named Ranges Using The OFFSET Function
  • Use The INDIRECT Function To Build Dynamic Formulas
  • Dealing With INDIRECT Errors
  • Use Formulas To Determine An Excel Filename And Or Sheet Name

Module 9: Auditing and Troubleshooting Formulas

• • Description Of Tracer Arrows
  • Tracing Precedents And Dependents
  • Remove Tracer Arrows
  • Error Checking Using Auditing Tools
  • Step By Step Processing Of Formula To Help With Troubleshooting
  • Utilizing The Watch Window

Module 10: Protecting and Sharing of workbook

• Protecting a workbook with a password
• Allow user to edit ranges
• Protect to appropriate formula or area
• Track changes
• Working with Comments
• Insert Excel Objects and Charts in Word Document and PowerPoint Presentation

Module 11: Performing Lookups

VLOOKUP Explained

• Applied Examples For VLOOKUP
• HLOOKUP Explained
• HLOOKUP In Action
• Index and match
• Concept about Xlookup

Module 12:Perform Calculations with Functions

• Creating Simple Formulas
• Setting up your own formula
• Date and Time Functions and calculation, Financial Functions
• Find future and past dates
• Logical Functions, Lookup and Reference
• Functions Mathematical Functions
• Working with Templates

Module 13 :Analytical Tools for Scenario analysis

• Goal Seeking
• Data Tables
• Solver
• Setting up a scenario and entering values
• Display the scenario values
• Editing the values of a scenario
• Deleting a scenario

• • Merge scenarios from different sheets
  • Forecasting Trends
  • Trend lines in Charts
  • Getting a Summary of all scenarios

Module 14: Values

• PPMT, ipmt, Future Value (EMI tracking )
• DB (fixed declining balance method ( depreciation), SLN ( straight line method ( depreciation )

Module 15: Prediction and forecast

• • Forecast sheet for prediction
  • Forecast linear / ets