Excel is ideal for helping business leaders implement better workflow and end-to-end processes because a lot of business data is already stored in Excel, and most people have a general knowledge of how Excel works.

When extending this basic knowledge, some of the most important Excel capabilities to understand include:

Note: You should know the Meaning, Syntax, Example, Input Value and Output Value such that you should know when and where to apply Excel Function on Data.

I.       DATE & TIME FUNCTIONS

S.NO Function Name S.NO Function Name
1 DATE 11 SECOND
2 DAYS360 12 TODAY
3 HOUR 13 YEAR
4 NOW 14 EDATE
5 TIME 15 EOMONTH
6 DATEDIF 16 NETWORKDAYS
7 DATEVALUE 17 TIMEVALUE
8 DAY 18 WEEKDAY
9 MINUTE 19 WEEKNUM
10 MONTH 20 WORKDAY

II.      MATHEMATICAL & TRIGONOMETRICAL FUNCTIONS

S.NO Function Name S.NO Function Name
21 ABS 36 EVEN
22 FACT 37 FLOOR
23 SQRT 38 MOD
24 SUM 39 MROUND
25 SIN 40 ODD
26 COS 41 PI
27 TAN 42 POWER
28 SUMIF 43 RAND
29 SUMIFS 44 RANDBETWEEN
30 AGGREGATE 45 RADIANS
31 CEILING 46 ROMAN
32 COMBIN 47 ROUND
33 COT 48 ROUNDDOWN
34 CSC 49 ROUNDUP
35 DECIMAL 50 SUMPRODUCT

III.     STATISTICAL FUNCTIONS

S.NO Function Name S.NO Function Name
51 AVERAGE 72 AVERAGEA
52 CORREL 73 AVERAGEIF
53 COUNT 74 AVERAGEIFS
54 COUNTA 75 COUNTBLANK
55 COUNTIF 76 COUNTIFS
56 MAX 77 F.DIST
57 MIN 78 F.TEST
58 PERMUT 79 T.DIST
59 FORECAST 80 T.TEST
60 FREQUENCY 81 Z.TEST
61 LARGE 82 TREND
62 MEDIAN 83 NORM.DIST
63 MODE 84 NORM.INV
64 RANK 85 PEARSON
65 SMALL 86 GROWTH
66 STDEV 87 GEOMEAN
67 VAR 88 HARMEAN
68 VAR.P 89 MAXA
69 VAR.S 90 MAXIFS
70 VARA 91 MINA
71 STDEVA 92 MINIFS

IV.     LOOKUP & REFERENCE FUNCTIONS

S.NO Function Name S.NO Function Name
93 HLOOKUP 99 COLUMN
94 VLOOKUP 100 COLUMNS
95 LOOKUP 101 CHOOSE
96 MATCH 102 ROW
97 INDEX 103 ROWS
98 TRANSPOSE 104 HYPERLINK

V.      TEXT FUNCTIONS

S.NO Function Name S.NO Function Name
105 CHAR 117 LOWER
106 CLEAN 118 PROPER
107 CODE 119 UPPER
108 CONCATENATE 120 REPT
109 DOLLAR 121 SUBSTITUTE
110 EXACT 122 REPLACE
111 FIND 123 TEXT
112 FIXED 124 TRIM
113 LEFT 125 CONCAT
114 LEN 126 SEARCH
115 RIGHT 127 T
116 MID 128 VALUE

VI.     LOGICAL FUNCTIONS

S.NO Function Name S.NO Function Name
129 AND 134 IFERROR
130 OR 135 IFS
131 NOT 136 TRUE
132 FALSE 137 XOR
133 IF 138 IFNA

VII.     Database Functions

S.NO Function Name S.NO Function Name
139 DAVERAGE 142 DGET
140 DCOUNT 143 DMAX
141 DCOUNTA 144 DMIN

VIII.       FINANCIAL FUNCTIONS

S.NO Function Name S.NO Function Name
145 DB 151 NPER
146 DDB 152 NPV
147 DURATION 153 NOMINAL
148 FV 154 PMT
149 IPMT 155 PV
150 IRR 156 RATE

IX.         INFORMATION FUNCTIONS

S.NO Function Name S.NO Function Name
157 CELL 166 ISTEXT
158 INFO 167 NA
159 ERROR.TYPE 168 SHEET
160 ISBLANK 169 TYPE
161 ISERR 170 ISEVEN
162 ISERROR 171 ISREF
163 ISLOGICAL 172 SHEET
164 ISNUMBER 173 SHEETS
165 ISODD 174 TYPE

X.      OTHER  TOOLS

S.NO Tool Name S.NO Tool Name
175 CUSTOM LIST 184 CUSTOM SORT
176 DATA TABLE 185 FILTER
177 GOAL SEEK 186 ADVANCED FILTER
178 PIVOT TABLE 187 FREEZE PANES
179 PIVOT CHART 188 MACRO
180 DATA VALIDATION 189 WRAP TEXT
181 SCENARIO MANAGER 190 MERGE
182 CONDITIONAL FORMATTING 191 FORMAT AS TABLE
183 SORTING 192 VIEW – NORMAL, PAGE BREAK, PAGE LAYOUT

XI.   DATA ANALYSIS (ADD-INS)

S.NO Function Name S.NO Function Name
193 WHAT-IF ANALYSIS 194 CHARTS – COLUMN, LINE, PIE, BAR, HISTOGRAM, SCATTER

You may also check the following link to know more for Excel:

Excel Theory Notes

Important Information for Microsoft Excel

Practical Exercise’s on Ms Excel

Learn Excel – Quick & Easy Manner

Excel Books

How fast can you work with EXCEL?

10 Excel Formulas that help you in your Job

Some Videos to know Excel for Actuarial Science Students…