Call Me
Excel Formulas

Do you know as to why are you really using excel?

Excel has not just limited its scope to data entry; it has delved into the ever-booming domain of ANALYTICS!
Yes! Excel is used as a data science tool to conduct analysis of data.
Where basic excel helps you tweak with the current data, automation gives you the authority to not tediously monitor data, but let VBA-MACRO handle the data for you.The story however doesn’t end here. In fact, it commences here!
Excel is the most basic tool used in Analytics. It's a bread and butter for most of us today strongly signaling us to update our analytical skills already by learning more potent and advanced tools like R, SAS, HADOOP, etc.


Basic Functions

Sl .no. Function Syntax Description
F1 LEFT =LEFT("TEXT","NUMBER OF CHARACTERS") Used to extracct the characters from a string from the left. Use this function to extract the starting characters of a string
F2 RIGHT =RIGHT("TEXT","NUMBER OF CHARACTERS") Used to extracct the characters from a string from the Right. Use this function to extract the ending characters of a string
F3 MID =MID(Source Cell Reference,Starting position,"NUMBER_OF_CHARACTERS") Used to extract a custommised set of characters from within a string
F4 FIND =FIND("Text/char to find",cell reference,Starting position) Used to find the position of a character within a string. Usually used to make your text functions dynamic. It is Case Sensitive
F5 SEARCH =SEARCH("Text/char to find",cell reference,Starting position) Used to find the position of a character within a string. Usually used to make your text functions dynamic
F6 REPLACE =REPLACE(source cell ref, starting position, num of characters to be replaced, "New Text" Used to replace the characters at a specific position in a string with a new text
F7 SUBSTITUTE =SUBSTITUTE(Source Cell Reference, "Old Text to be replaced", "New Text", instance) Used to change specific characters/text with new text in a string. This is irrespective of the position
F8 UPPER =UPPER(Source cell reference) Used to convert the text in upper case
F9 LOWER =LOWER(Source cell reference) Used to convert the text in Lower case
F10 PROPER =PROPER(Source cell reference) Used to convert the text in Proper case
F11 SUM =SUM(number1,number2…..) Used to calculate the total of the values of multiple cells together
F12 YEAR =YEAR(Source date) Used to extract the year part from a date
F13 MONTH =MONTH(Source date) Used to extract the Month part from a date
F14 DAY =DAY(Source date) Used to extract the Month part from a date
F15 WEEKDAY =WEEKDAY(Source date) Used to extract the weekday part from a date. Output will be numeric between 1 to 7. where 1 means Sunday by default
F16 NETWORKDAYS =NETWORKDAYS(start date, end date, holidays) Used to calculate the total business days between two dates. Additionally allows us to consider a custom range of dates as holidays.
F17 EOMONTH =EOMONTH(Start date,number of months) Used to Find the last date of the month based on a specific date

Advanced functions

Sl .no. Function Syntax Description
F1 COUNTA =COUNTA(Value1,value2,….) Used to count the number of non empty cells in a selecetd range
F2 COUNTIF =COUNIF(Range, Criteria) Used to count the number of times a specific value is repeated in a range
F3 COUNTIFS =COUNIFS(Range1, Criteria1,Range2, Criteria2…..) Used to count the number of times a combination of specific values have repeated in a data range
F4 SUMIF =SUMIF(Range, Criteria, Sum Range) Used to get the total of the values corresponding to a specific value in a range
F5 SUMIFS =SUMIFS(Sum Range,Range1, Criteria1,Range2, Criteria2…..) Used to get the total of the values corresponding to a combination of specific values in a range
F6 AVERAGEIF =AVERAGEIF(Range, Criteria, Average Range) Used to get the total of the values corresponding to a specific value in a range
F7 AVERAGEIFS =AVERAGEIFS(Average Range,Range1, Criteria1,Range2, Criteria2…..) Used to get the total of the values corresponding to a combination of specific values in a range
F8 INDEX =INDEX(array, row number, column number) Used to return a value from a range when position of the value is determined in terms of rows and columns
F9 MATCH =MATCH(lookup value, lookup array, match type) Used to return the position of a value in an array when the value is specified. (opposite of an INDEX Function)
F10 VLOOKUP =VLOOKUP(lookuo value, table array, col number, range lookup) Used to find the value in a Column range and return the required value from the corresponding columns.
F11 HLOOKUP =HLOOKUP(lookuo value, table array, Row number, range lookup) Used to find the value in a Row range and return the required value from the corresponding rows.