Learn Basic and Advance Excel

1. Basic Formula

UPPER

=Upper(Select Text)

LOWER
=Lower(Select Text)

PROPER
=Proper(Select Text)

LEFT
=Left(Select Text,Count of char first Word)

MID
=Mid(Select Text,Count of char first word with space,Count of char midle word with space)

RIGHT
=Right(Select Text,Count of char right word)

JOINT WORD
=Concatenate(Select First word,Select Second word,Select Third Text)

COUNT OF WORD CHAR
=Len(Select Text)

COUNT
=Count(Select all numerical value)

COUNTA
=Counta(Select All Numerical value and alphabets)

COUNTIF (THREE TYPES)
=Countif(Select all numericals values,"Put the any value")

=Countif(Select all numerical values,">50")

=Countif(Select all numerical values,"<50")

2. HOW TO FIND IN AGE/T_HOURS, T_MINUTES & T_SECONDS

TD (TODAY DATE)
=Today()

YY (YEAR)
=Datedif(Select DOB,Select TD,"Y")

MM (MONTH)
=Datedif(Select DOB,Select TD,"YM")

DD (DAYS)
=Datedif(Select DOB,Select TD,"MD")

TBD (TOTAL BIRTH OF DAYS)
=Days360(Select DOB,Select TD,0)


TOTAL HOURS
=Select TBD*24

TOTAL MINUTES
=Select T_Hours*60

TOTAL SECONDS
=Select T_Minutes*60

                                     3. LOAN SHEET

RATE AMOUNT
=Select Loan Amount*Rate

LARA (LOAN AMOUNT RATE AMOUNT)
=Select Loan Amount+Rate Amount

I YEAR
=Select LARA/Select Year

II YEAR
=Select LARA/Select Year

III YEAR
=Select LARA/Select Year

TBLA (TOTAL BALANCE LOAN AMOUNT)
=Select LARA-(Select I Year+II Year+III Year)

                                     4. MARK SHEET

TOTAL
=Sum(Select All Subject)

MAXIMUM
=Max(Select All Subject)

MINIMUM
=Min(Select All Subject)

PERCENTAGE
=Select Total/Number of Subject (Example: E2/300) Change to percentage format press key CTRL+SHIFT+5

AVERAGE
=Average(Select All Subject)

GRADE
=If(Select Ave>=60,"A",If(Select Ave>=50,"B",If(Select Ave>=40,"C","D")))

PASS AND FAIL
=If(And(Select First Sub>=33,Select Second Sub>=33,Select Third Sub>=33),"Pass","Fail")

FULL RESULT
=If(Select M Sub>=33," M Pass "," M Fail ")&If(Select S Sub>=33," S Pass "," S Fail ")&If(Select E Sub>=33," E Pass "," E Fail ")

5. SALARY SHEET

ABS (ABSENT BASIC SALARY)

=Seletc E Salary*Select EWDM/26

TA (TAVELING ALOWANCE)
=Select ABS*12%

DA (DEARNESS ALOWANCE)
=Select E Salary*10%

HRA (HOUSE RENT ALOWANCE)
=Select E Salary*35%

ESI (EMPLOYEE STATE INSURANCE)
=Select E Salary*4%

PF (PROVIDENT FUND)
=Select E Salary*12.5%

GROSS
=Sum(Select ABS to PF)

IN HAND
=Select Gross-(Select HRA+Select ESI+Select PF)

6. DEFINE ARRAY

ARRAY
=Count(if((Select All Name=Select Find Name)*(Select All City Name=Select Find City Name),(Select All S. No.)))
After press key CTRL+SHIFT+ENTER

7. Filter 

Step1: Create a sheet then select any main heading then Press Key ALT+D+F+F shwo to filter and remove to filter.
Step2: Use to filter click filter heading then search name vice and number vice.

8. Advance Filter

Step1: Create a sheet example: Mark sheet. Then select all main heading then paste on blank cells two time.

Step2: First copy heading choose any heading example Maths then put value example >75

Step3: Drop the cursur main sheet and go to data tool then select option advance
  List Range: Select All data
Criteria range: Select source example Math and >75
Copy to: Select second copy main heading then extra row after click unique records only then OK.

9. Goal Seek

Step1: Create a sheet example: Name, Class, Maths, Eng, Total

Step2: Select any formula value cell then go to data tool and select what-if analysis after select Goal Seek option
Set cell: Formula value cell
To value: Put the value example: 200
By changing cell: Select change to value subject example: 45
Then OK

10. STRING FUNCTION

=MID(Select Source,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},Select Source&"0123456789")),LEN(Select Source))

11. Advance Vlookup

Step1: Create a sheet example: Name, Class, Maths, Eng
Step2: Fill the all data then select all main heading then copy after paste any blank cell. (CTRL+C = COPY & CTRL+V = PASTE).
Step3: Select Class to Scie cell then put the formula vlookup. Example:

=Vlookup(Select Source {Change to Dollar Sign press key F4},Select All data {Change to Dollar Sign press key F4},Column(Select First two column main sheet {Press key F2 then put Dollar sign in front side only press key 4}),0)
Press Key CTRL+ENTER.

Example: =VLOOKUP($A$14,$A$5:$E$9,COLUMNS($A5:B5),0)