Excel Formulas everyone should know.
Excel is a widely known application and is used in many professions including:
- Accounting and finance for budgeting, financial modelling and tracking expenses.
- Tracking sales and analysing customer data and managing the inventory of a business.
- For organising, analysing and visualising data.
- For data analysis and simulations in the field of engineering and science.
Data is inevitable, and Microsoft Excel is one of the most used tools in almost all professions and businesses to provide insights and analytics of the market and their organisation’s performance vis-a-vis the competitor firms. The availability of many in-built applications, excel formulas that truncate efforts, a user-friendly interface, robust visualisation capabilities that allow users to create bar graphs, and pie charts, and integration with other Microsoft products where you can link the item to the original sheet, make it a widely used application.
Excel formula is an expression that calculates a value based on values in cells. These formulas return results, even if there is an error. Formulas in excel begin with a sign (=), and utilise mathematical operators, cell references and functions to perform calculations on values in the cells of the excel sheet. In addition to performing elementary computations like addition, subtraction, multiplication and division, you can also find out averages and calculate percentage formula in excel for a range of cells, find the minimum and maximum value in a cell, change date and time values, etc.
Build Advance Excel Model using Advanced Formulas, VBA, Power Query
Last Updated: 2022-05-03
Design Robust Excel Models for Accounting and Reporting, learn to Build Automated Dashboards using Power Query and VBA
Before we recommend some widely known excel formulas, remember these steps to carry out the function:
- Select the cell you want to carry out the mathematical calculation.
- Enter the equal sign (=)
- Enter the address of the selected cell
- Enter an operator
- Enter the address of the next cell to the selected cell.
- Press ‘enter’
EXCEL FORMULAS:
Excel formulas are important to know since it is efficient and saves time. Following are some excel formulas or excel functions that are relevant.
- SUM:
The SUM () function is used to give the total of the selected range of cell values. This formula is used to perform the mathematical function of addition. The syntax is =SUM(number 1, [number 2],…), where number 1 is the first cell, and number 2 is a reference to the subsequent range of cells that needs to be included in addition. So, if, for example, column A has entries of ‘quantity sold’ and column B has entries of ‘price’ and column C, we need to find the sale of the product, then we include all the number of cells we need, in order to find the total, i.e, =SUM(C2:C12) [assuming there are 10 rows]
- AVERAGE:
When we need to find the average of the selected range of cell values. The syntax is =AVERAGE(number 1, [number 2],…), where number 1 is the first cell, and number 2 is a reference to the subsequent range of cells that needs to be included to find out the arithmetic mean. The formula =AVERAGE(C1:C9) will calculate the average of the cell values from C1 through C9.
- COUNT:
The function COUNT() counts the total number of cells in a range containing a series of numbers. The syntax is =COUNT(value 1, [value 2],…), where value 1 is the first cel reference, and value 2 is a reference to any additional range of cells that needs to be included in the count. The cell count can be absolute or relative. If you want to count the cells with numerical values, or any other data format, use COUNT(A). Note this function will not provide any answer to a blank cell.
- MIN and MAX:
To find out the smallest and the largest value in a series of range, there are two steps:
- If the cells are in contiguous rows or columns, then select the below right of the cell or the range of numbers, then click on the home tab, select editing, and select autosum. From there, the options of min and max will pop up. Press enter.
- If the cells are not continuous, use the syntax =MIN(number 1, [number 2],…) for minimum, and =MAX(number 1, [number 2],…) to find the largest value.
- VLOOKUP FUNCTION:
This stands for the vertical lookup to find a particular value in the leftmost column of a table. This is used if you are looking for a specific value in the first column of a table, to return the value in the same row from a specified column.
The syntax: =VLOOKUP(look_up value); =VLOOKUP(table_array); =VLOOKUP(col_index).
- CONCATENATE EXCEL:
The function is used to merge several text strings into one text string. If you want to join two cells into a single cell, use the syntax: =CONCATENATE(text 1, [text 2],…)
There are many more excel formulas that can be used to efficiently work in the spreadsheet.Learnfly offers many more tricks and tips to excel in MS excel. Join our platform to learn more.
