Advanced Excel

  • Introduction to MS Excel
  • Ribbon Bar
  • Excel Screen
  • Worksheets and Toolbar
  • Sheet, Row & Columns and Cells
  • Sheet Navigation and Sheets tab switching
  • Working with Shortcuts
  • Creating Records and Formatting Range & Selection
  • Alignment & Wrap text
  • Understanding File Formats: .xls, .xlsx, .xlsm, .csv etc.
  • About Cell, Name Box, Formula Bar
  • About Cell Referencing
  • Creating Pivot Table
  • Using Pivot table options
  • Changing and updating Data range
  • Formatting Pivot Table and making Dynamic Pivot Tables
  • Using Slicers
  • Creating Pivot Chart
  • Types of Pivot Charts and Their Usage
  • Formatting Pivot Charts and making Dynamic Pivot Charts
  • Paste Special & Format Painter
  • Cell Formatting, Text Controls, Categories
  • Advanced Conditional Formatting
  • Sorting & Filtering
  • Flash Fill
  • Fill Series and Justify
  • Advanced Find &GoTo Special
  • Creating & Running Macros
  • Charts, Pictures, SmartArt, Screenshot
  • Insert links and Header & Footers
  • Page Setup, Margins and Print Settings
  • Print Area, Workbook views, Freeze Panes
  • Scale to Fit, Sheet Options & Print Titles
  • Insert Comments, Spelling & Grammar
  • Protect Sheet & Workbook
  • Share Workbook
  • Track changes
  • Allow Users to Edit Range
  • Advanced Conditional Formatting
  • Advanced Filter, Filter To Cross Sheet and workbook
  • What if Analysis(Goal Seek, Data Tables and Scenario Manager)
  • Searching Technique with GoTo Special…
  • Lookup to Left with Index & Match
  • Loan EMI Calculator In excel
  • Tax Calculator in Excel
  • Advanced Chat Technique, How To Make Dynamic Charts, Bar Charts, Pie Charts, Scatter Chart, Line
  • Chart, Column Chart, Speedometer Chart, Gantt Chart
  • Protecting Excel File, Protecting worksheet, Workbook, Enable / Disable Range,

FINANCIAL FORMULAS

  • PMT
  • FV
  • PV
  • RATE
  • NPER
  • IPMT
  • PPMT
  • DB
  • SLN
  • SYD
  • CUMIPMT
  • CUMPPMT

LOGICALFORMULAS

  • IF
  • IF ELSE
  • IF (GRADE)
  • IF(BONUS)
  • IF (OK PENDING)
  • IF(A2=A1)
  • IF(STD,LOCAL)
  • AND
  • OR
  • FALSE
  • NOT
  • TRUE

TEXT FORMULAS

  • CHAR
  • CLEAN
  • CODE
  • CONCATENATE
  • DOLLAR
  • EXACT
  • FIND
  • FIXED
  • LEFT
  • LEFT ADVANCE
  • LEN
  • LOWER
  • MID
  • MID (ADVANCE)
  • PROPER
  • REPLACE
  • REPT
  • REPT(ADV)
  • RIGHT
  • RIGHT (ADV)
  • SEARCH
  • SUBSTITUTE
  • SUBSTITUTE(ADV)
  • T
  • TEXT
  • RANK
  • MATHS & TRIG
  • ABS
  • CEILING
  • EVEN
  • FACT
  • FACTDOUBLE
  • FLOOR
  • MOD
  • ODD
  • POWER
  • PRODUCT
  • RAND
  • RANDBETWEEN
  • ROMAN
  • ROUND
  • ROUNDDOWN
  • ROUNDUP
  • SQRT
  • SUBTOTAL
  • SUM
  • SUMIF
  • SUMIFS
  • SUMPRODUCT

DATABASE FORMULAS

  • DAVERAGE
  • DCOUNT
  • DCOUNTA
  • DGET
  • DMAX
  • DMIN
  • DPRODUCT
  • DSUM
  • DATEDIF(D)
  • DATEDIF(M)
  • DATEDIF(Y)
  • DATEDIF(A1,B1,”y”)
  • DATEDIF(A1,B1,”Ym”)
  • DATEDIF(A1,B1,”MD)

LOOKUP FORMULAS

  • ADDRESS
  • CHOOSE
  • COLUMN
  • COLUMNS
  • HLOOKUP
  • HLOOKUP (ADV)
  • HYPER LINK
  • INDEX
  • INDIRECT
  • LOOKUP
  • MATCH
  • OFFSET
  • ROW
  • ROWS
  • TRANSPOSE
  • VLOOKUP
  • VLOOKUP (ADV)

DATE TIME FORMULAS

  • DATE
  • DATEVALUE
  • DAYS
  • DAYS360
  • EDATE
  • EOMONTH
  • HOUR
  • MINUTE
  • MONTH
  • NETWORKDAYS
  • NOW
  • SECOND
  • TIME
  • TIMEVALUE
  • TODAY
  • WEEKDAY
  • WEEKNUM
  • SUMSQ(3,4)

STATICAL FORMULAS

  • AVERAGE
  • AVERAGEA
  • AVERAGEIFS
  • COUNT
  • COUNTA
  • COUNTBLANK
  • COUNTIF
  • COUNTIFS
  • FORECAST
  • FREQUENCY
  • GROWTH
  • DISCOUNT
  • VAT TAX
  • LARGE
  • MAX
  • MAXA
  • MEDIAN
  • MIN
  • MINA
  • MODE(444,555)
  • CONVERT
  • TYPE
  •