Curated list of Excel 2013, 2016 formulas

Over the past 20+ years i have been working with MS Office product and in particular i couldn’t deny the love i have for Excel. No matter what technology is advanced or data science grows exponentially. MS Excel is a tool that we all love to reach out to perform a wide range of operations right from a very basis sum to a highly complex table lookup and complex formulas.

So i thought why not curate a list of very frequent use cases and the relevant fomula for this in ms excel.

Excel is a widely used spreadsheet software that offers a vast array of functions and formulas to perform calculations, manipulate data, and analyze information. Whether you’re a beginner or an experienced user, knowing the right formulas can help you optimize your work in Excel and increase your productivity. In this blog, we will explore a curated list of Excel formulas that are available in Excel 2013 and 2016, and how they can be used to perform various tasks.

1. VLOOKUP (Vertical Lookup): VLOOKUP is a powerful formula used to search for a specific value in the leftmost column of a range of data, and return a value in the same row from a specified column. It is commonly used for tasks such as data validation, data merging, and data analysis. =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
2. SUMIF (Sum with Condition): SUMIF is a formula used to calculate the sum of values in a range based on a given condition. It allows you to specify a condition and sum only the values that meet that condition. =SUMIF(range, criteria, [sum_range])
3. CONCATENATE (Join Text): CONCATENATE is a formula used to join or concatenate text strings from multiple cells into one cell. It can be used to merge data from different columns or rows into a single cell for further analysis or reporting. =CONCATENATE(text1, text2, ...)
4. IF (Conditional Statement): IF is a formula used to perform conditional calculations based on a given condition. It allows you to specify a condition, and if that condition is met, it returns one value, and if not, it returns another value. =IF(logical_test, value_if_true, value_if_false)
5. AVERAGE (Calculate Average): AVERAGE is a formula used to calculate the average or mean of a range of numeric values. It can be used to calculate the average of test scores, sales data, or any other numeric data. =AVERAGE(number1, number2, ...)
6. COUNTIF (Count with Condition): COUNTIF is a formula used to count the number of cells in a range that meet a given condition. It can be used to count the occurrence of specific values, words, or patterns in a range of data. =COUNTIF(range, criteria)
7. TEXT (Format Text): TEXT is a formula used to format a value as text with a specific number format. It can be used to convert dates, times, and numbers into text with a specific format for reporting or presentation purposes. =TEXT(value, format_text)
8. LEFT/RIGHT/MID (Extract Text): LEFT, RIGHT, and MID are formulas used to extract text from a cell based on the position or length of the text. These formulas are useful for extracting parts of text strings, such as extracting first names, last names, or other substrings from a larger text. =LEFT(text, num_chars) =RIGHT(text, num_chars) =MID(text, start_num, num_chars)
9. DATE (Calculate Date): DATE is a formula used to create a date value based on the year, month, and day specified as arguments. It can be used to calculate dates for various purposes, such as tracking deadlines, scheduling events, or analyzing time-based data. =DATE(year, month, day)
10. INDEX/MATCH (Lookup and Retrieve): INDEX and MATCH are formulas used together to perform powerful lookup and retrieval operations. INDEX is used to retrieve the value from a specified cell within a range, and MATCH is used to search for a specified value in a column or row and return its position. When used together, INDEX/MATCH can be a powerful alternative to VLOOKUP or HLOOKUP, allowing you to perform more flexible and dynamic lookups in your Excel spreadsheets. =INDEX(array, row_num, [column_num]) =MATCH(lookup_value, lookup_array, [match_type])

These are just a few examples of the many powerful formulas available in Excel 2013 and 2016. Excel provides a vast library of formulas that cater to various needs, from basic calculations to complex data analysis and reporting. Learning and mastering these formulas can greatly enhance your productivity and efficiency when working with Excel.

In addition to the formulas mentioned above, there are many other functions such as MAX, MIN, IFERROR, DATEVALUE, TEXTJOIN, and many more that can be immensely useful in different scenarios. Excel also provides statistical, financial, logical, and other specialized functions for more advanced calculations and analysis.

In conclusion, Excel 2013 and 2016 offer a rich and comprehensive library of formulas that can help you perform various tasks and analysis efficiently. Whether you’re a student, a professional, or a business owner, knowing these formulas can greatly enhance your ability to work with data in Excel and make better decisions based on accurate calculations. So, take the time to explore and familiarize yourself with these formulas, and unlock the full potential of Excel for your data management and analysis needs.