Excel Short - Cut Keys

 

1.      TODAY- =TODAY()

2.      DATE-=DATE(YEAR,MONTH,DATE)

3.      DATEDIF-=DATEDIF(CELL1,CELL2,”Y/M/D”)

4.      SUM- =SUM()

5.      SUBTRACT- =CELL – CELL

6.      AVERAGE- =AVERAGE()

7.      ROUND =ROUND(CELL,NUM DIGITS)- NUM DIGIT DENOTES DECIMAL POINTS

8.      MROUND =MROUND(CELL, MULTIPLE) MULTIPLE DENOTES NEAREST NUMBER TO ROUND OFF EG- MULTIPLE OF 2 /5/10

9.      PRODUCT- =PRODUCT(CELL1, CELL2….)

10.  IF FUNCTION =IF(LOGICAL TEST,”VALUE IF TRUE”,”VALUE IF FALSE”) EG- =IF(CELL>200,”PASS”,”FAIL”)

11.  AND FUNCTION= AND(LOGIC 1, LOGIC 2,……..) EG- =AND(CELL>35,CELL>35,CELL>35……)

12.  IF & AND FUNCTION= IF(AND(LOGIC 1, LOGIC 2,……..), ”VALUE IF TRUE”,”VALUE IF FALSE”)

EG- = IF(AND(CELL>35,CELL>35,CELL>35……),”PASS”,”FAIL”)

13.  CONDITIONAL FORMATTING -HOME MENU-

14.  DATA VALIDATION- DATA MENU-DATA VALIDATION- SETTINGS, INPUT MESSAGE, ERROR ALERT

15.  SORT – HOME MENU-CUSTOM SORT

16.  FILTER-HOME MENU-NUMBER FILTER

17.  INSERT COMMENT- RIGHT CLICK-PROPERTIES

18.  GOAL SEEK- DATA MENU-WHAT IF ANALYSIS- GOAL SEEK- SET CELL; TO VALUE;BY CHANGING CELL

19.  GRAPH-INSERT MENU- CHARTS

20.  LOOKUP-

i) VLOOKUP (vertical lookup)-CREATE A NEW SHEET, USE THE GIVEN DATA =VLOOKUP(LOOKUPVALUE,TABLE_ARRAY,COLUMN INDEX NUMBER,TRUE) -column index number is the data need to be attained

ii) HLOOKUP( horizontal lookup)- CREATE A NEW SHEET, USE THE GIVEN DATA =HLOOKUP(LOOKUPVALUE,TABLE_ARRAY,ROW INDEXNUMBER,TRUE)

iii) XLOOKUP-hybrid lookup

21.  PASTE SPECIAL- COPY THE DATA – PASTE OPTIONS / PASTE SPECIAL- TRANSPOSE (T)

22.  RANK- =RANK(NUMBER,REF,ORDER)- NUMBER-DATA CELL eg-total marks, ref-entrie total  marks,order- ass/des

23.  REFERENCE-

$-FUNCTION-FN+F4

a.       RELATIVE

b.      ABSOLUTE

c.       HYBRID

24.  PROTECTION

A)     CELL PROTECTION- RIGHT CLICK-FORMAT CELLS-PROTECTION -TICK BOTH LOCKED AND HIDDEN, REVIEW-PROTECT SHEETS- GIVE PASSWORD

B)      SHEET PROTECTION- COMMENT BOX-RIGHT CLICK-FORMAT CELLS-PROTECTION -TICK BOTH LOCKED AND HIDDEN, REVIEW-PROTECT SHEETS- GIVE PASSWORD

C)      WORKBOOK PROTECTION- - COMMENT BOX-RIGHT CLICK-FORMAT CELLS-PROTECTION -TICK BOTH LOCKED AND HIDDEN, REVIEW-PROTECT WORKBOOK- GIVE PASSWORD

 

25.  COUNT=COUNT(CELLS)- ONLY FOR NUMBERS

26.  COUNTIF =COUNTIF(RANGE,CRITERIA)

27.  COUNTIFS=COUNTIFS(RANGE1,CRITERIA1, RANGE 2,CRITERIA 2)

28.  LENGTH = LEN

29.  LEFT =LEFT(TEXT,NUM CHAR)-COUNT FROM LEFT

30.  RIGHT = RIGHT(TEXT,NUM CHAR) COUNT FROM RIGHT

31.  MID =MID(TEXT, START NUM,NUM CHAR)- COUNT FROM LEFT

32.  CONCATENATE =CONCAT

33.  REPLACE= REPLACE(TEXT,START NUM,NUM CHAR,NEW TEXT)

34.  SUBTITUTE=SUBTITUTE(TEXT,OLD TEXT,NEW TEXT)

35.  UPPER =UPPER(TEXT)- TO CHANGE LETTERS IN CAPS

36.  LOWER =LOWER(TEXT) TO CHANGE THE LETTERS IN SMALL

37.  PROPER =PROPER(TEXT) TO CHANGE THE FIRST LETTERS IN CAPS

38.  INDEX=INDEX(array, row_num, [column_num])

39.  MATCH=MATCH(lookup_value, lookup_array, [match_type])