Recently released Microsoft Excel 2016 version has 484 functions, out of these, 360 existed prior to Excel 2010. Here in this post, i have gathered most used advanced Excel formulas for Data Analysis in industry.
Microsoft Excel since after released in 1987 and after version 5 which was released in 1993, became the widely applied spreadsheet software and became the industry standard for spreadsheets. Till now, Excel is the go-to tools for business professionals when it comes to analyzing data and visualizing it. This software is not only capable of doing basic data computations, but you can also perform data analysis using it.
If you’re a data scientist or data analyst, 80% of your time you invest in three important steps of data analysis i.e. Data Cleaning, where you transform, rearrange the data to give it a proper shape to make it suitable for data analysis. Next is performing calculations to extract useful information from your dataset and make it ready for the final step which is Data Visualization, where you choose suitable graphs or other types of visualization technique to show insight and important information that your data contains. Below we have shared excel formulas for data analysis.
Let’s see now advanced Excel formulas for Data Analysis purpose:
MEAN() function in Excel
Basic Syntax
=AVERAGE(number1, [number2],..)
MEDIAN() function in Excel
Median is a function which is used to find the middle number in a given array of numbers. To find a Median, generally you have to sort the data in an ascending order but in Excel, just use the Median function formula and the rest task is performed automatically.
Basic Syntax
=MEDIAN(number1, [number2],..)
MODE() function in Excel
When you are work on a large dataset, this function will be handy in finding out the value that occurs the most number of times.
Basic Syntax
=MODE(number1, [number2],..)
STDEV() function in Excel
The standard deviation in Excel helps you to understand, the spread of your data i.e. how much of your data points are spread from the Average or Mean.
Basic Syntax
=STDEV(number1, [number2],..)
SUM() function in Excel
SUM() function will give you the sum of all the numbers that are passed inside this function.
Basic Syntax
=SUM(number1, [number2],..)
MAX() and MIN() functions in Excel
MAX() and MIN() will provide the Maximum and minimum number from given numbers or array.
Basic Syntax
=MAX(number1, [number2],..)
=MIN(number1, [number2],..)
CORREL() function in Excel
CORREL() or correlation function will give us an idea of how two arrays are correlated to each other. It gives us a value between 0 and 1 where 0 means no correlation and 1 means highly correlated.
Basic Syntax
=CORREL(array1, array2)
VLOOKUP() function in Excel
VLOOKUP is one of the most used Excel functions which can match data from a table with an input (lookup) value. Basically, it can return only a single value. The V in VLOOKUP stands for vertical search (in a single column).
Basic Syntax
=VLOOKUP(Lookup_value, table_array, col_index_num, [range_lookup])
HLOOKUP() function in Excel
HLOOKUP is another most used Excel functions which can match data from a table with an input (lookup) value. Basically, it can return only a single value. The H in HLOOKUP stands for horizontal search (within a single row).
Basic Syntax
=HLOOKUP(Lookup_value, table_array, col_index_num, [range_lookup])
CONCATENATE() function in Excel
CONCATENATE() function will add or concatenate all the text that is provided inside this.
Basic syntax
=Concatenate(Text1, Text2,.....Textn)
INDEX MATCH function in Excel
The INDEX MATCH MATCH formula is the combination of two functions in Excel: INDEX and MATCH and are one of the best excel formulas for Data Analysis purpose. INDEX() returns the value of a cell in a table based on the column and row number whereas MATCH() returns the position of a cell in a row or column. When combined, both can look up and return the value of a cell in a table based on vertical and horizontal criteria.
Basic Syntax
=INDEX(array, row_num,[column_num])
=MATCH(lookup_value, lookup_array, [match_type])
IF() function in Excel
The IF() function in excel, can perform a logical test and return one value for a TRUE result, and another for a FALSE result.
Basic Syntax
=IF(logical_test, [value_if_true], [value_if_false])
LEN() function in Excel
LEN() function in excel returns the length of the specified string.
Basic Syntax
=Len(text)
LOWER(), UPPER() and PROPER() functions in Excel
UPPER() changes text into upper case, LOWER() changes everything into lower case and PROPER() changes everything into proper case i.e. first character uppercase and following characters into lower case.
Basic Syntax
=LOWER(text)
=UPPER(text)
=PROPER(text)
TRIM() function in Excel
The TRIM() function in Excel is used to remove unnecessary spaces between words in a string. It can also return a text value with the leading and trailing spaces removed.
Basic Syntax
=TRIM(text)
SUMIF() function in Excel
SUMIF() function in Excel is used to sum cells that meet a single criterion. It can be used to sum cells based on dates, numbers, and text that match specific criteria. this function also supports logical operators and wildcards for partial matching.
Basic Syntax
=SUMIF(range, criteria, [sum_range])
COUNTIF() function in Excel
COUNTIF function in Excel is used for counting cells within a specified range that meet a certain criterion, or condition.
Basic Syntax
=COUNTIF(range, criteria)
Time functions in Excel
Basic Syntax
WEEKNUM() is used to get the particular number of the week at a particular date.
=WEEKNUM(serial_number, [return_type])
WORKDAY() gives the date of the working day when the number of days is specified.
=WORKDAY(serial_number, [return_type])
NETWORKDAYS() gives the number of working days between two supplied dates by excluding the weekends and holidays.
=NETWORKDAYS(start_date, end_date, [holidays])
YEARFRAC() allows a user to find out the fraction of year when two dates are supplied.
=YEARFRAC(start_date, end_date, [basis])
EDATE() gives the specific date when the number of days after a particular date is mentioned.
=EDATE(start_date, months)
TYPE() function in Excel
TYPE() function in excel returns a numeric code based on a data type you passed inside this function. For example for numbers it will return 1, for text it will return 2, for logical it will return 4, for error it will return 16, and for arrays, it will return 64.
Basic Syntax
=TYPE(value)
If you have a product key, you can download it from here – Excel 2013 or Excel 2016. Also if you want to learn other languages like Python for data analysis, check out our posts.
Hope you like our post and these advanced Excel formulas for Data Analysis will be proved helpful for you.
Leave a Reply