Course Overview

• Getting Started With Microsoft Excel 2013

• Performing Calculations
  • Excel Formulas, Formula Bar, Elements of an Excel Formula
  • Common Mathematical Operators, The Order of Operations
  • Functions & Function Library, Common Functions in Excel
  • Formula AutoComplete, Cut, Copy, Paste, Move, Special Paste Option
• Modifying a Worksheet
  • Undo & Redo Command, AutoFill Features, Transpose Option
  • Live Preview & The Clear Button
  • Insert, Delete, Hide, Unhide — Row, Column
  • Column Width, Row Height Alternation Method
  • Cell Names & Range Names, Find & Replace Command
• Formatting a Worksheet
  • Modify Fonts (Font Dialog Box), Format Painter, Galleries, Live Preview, Mini Toolbar
  • Border, Sheet Background, Number Format
  • Alignment, Indent, Wrap Text, Orientation, Merge & Center, Cell Styles

• What is relative & absolute cell reference?
• Different types of use of references (A4, $A4, A$4, $A$4)
• Managing cell reference with complex formula in easy rule.

• Joining multiple cells
• Extracting the required portion of a cell value
• Removing unwanted extra spaces
• Convert a cell text to your required case
• Making a function as a variable
• Converting a Text number to a computable pure number or date
• Rounding a cell value in different ways
• Join a text with formatted number.

• Convert texts to UPPER, LOWER or PROPER case
• Join multiple cell values with CONCATENATE & Break or extract a portion of text with LEFT, MID & RIGHT function
• Converting text to numbers quickly
• Finding total number of characters in a text

• LEN, TEXT & TRIM

• IF() Condition and expression of logical test
• Use of AND, OR and NOT
• Nested IF() with other functions

• The database functions perform basic calculations, such as sum, average, count, etc. but they also use criteria arguments that allow you to only perform the calculation for a specified subset of the records in your database. Other records in the database are ignored.

After completion of the session participants will be able to:

• Do some analytical activities through excel
• Use various methods for data analysis in excel
• Use different formulas, logic for various purposes
• Reduce work time up to 40% in excel
• Analyze data for strategic decision making
• Present report and data in a professional manner
• Create and maintain database for generating different report
• Use excel functions in different database

• Type of errors and when it occurs
• Circular reference
• Error handling using ISERROR & IFERROR function
• Magic tips to find error cells and cleaning
• Formula tracing and debugging 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

• Data Consolidation Using 3-D References
• Data Consolidation Using “Data Consolidate”

• 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
• Customizing subtotal at any row label data and subtotal category
• Changing PivotTable data source
• Inserting a calculated field and calculated item
• Sorting and filtering techniques in PivotTable data
• What is slicer in PivotTable?
• Creating, changing and formatting slicer
• Make your PivotTable dynamic using slicer
• Creating PivotChart from existing workbook data

Procedure to convert a number into words

• Calculate Future value of an investment with FV function
• Calculate Present value of an investment with PV function
• Calculate EMI or Periodic Payment with PMT function
• Net Present Value with NPV function
• Calculate Interest Rate per period (RATE)
• Calculate Number of payment periods (NPER)
• Calculates the Interest part of the regular scheduled payment (IPMT)
• Calculates the Principal part of the regular scheduled payment (PPMT)

• Type a Product ID, Excel will return its description automatically matching Product ID from a defined range
• 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

• Do you know how many mistakes you are doing every day for date??
• Separating DAY, MONTH and YEAR from a complete date
• Date formatting using formula
• Presenting day name and month name of a date
• Combining date with a text (Report Date: 31/Dec/2016)
• 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)

• 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

• Hide formulas inside cells from others

• 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

• 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

• Data summarizing from a large dataset using functions
• Basic & advanced SUMPRODUCT function
• COUNT,COUNTIF,COUNTIFS, SUMIFS and AVERAGEIFS functions
• Use these functions with multiple criteria and conditions

• Data visualization using Sparkline’s
• 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

• The GOAL SEEK command
• Sensitivity Analysis with DATA TABLE
• Business projection using SOLVER
• Using SCENARIO manager

• 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!

• Export Excel Data. (XML, HTML, TXT, CSV, PDF, XPS)
• Import a Delimited Text File.
• Integrate Excel Data with the Web.

• Format Painter, Wrap Text, Merge & Center
• Printing Excel & Print Titles
• Transposing Tricks, Scaling, Gridlines
• Show Negative numbers in Red Color and Inside Brackets
• Headlines, Background adding
• Name Manager, Copying Sheets, Freezing Panes
• Function: COUNTA, MAX, MIN, AVERAGE, ROUND, ROUNDUP, ROUNDDOWN, TRUE, FALSE …………
• Function: WEEKDAY, NOW, TODAY, PERCENTILE, STDEV, RANK, GROWTH …………

Your Content Goes Here

Your Content Goes Here

Your Content Goes Here

Your Content Goes Here