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