While preparing reports or analysing data, it becomes a task to do calculations manually or separately on a calculator. Microsoft Excel enables its users to do all the calculations in the software using Excel formulas while preparing reports or analysing the data. Excel is a widely used software application used in corporate offices and businesses to help employees ease their work. Here is a list of top MS Excel Formulas that are extremely useful. Learning and applying these formulas while working on Excel will help you upskill and work efficiently.
Useful MS Excel Formula
This function performs the mathematical operation of addition on the selected cell values. For example: To get the sum of items mentioned in column A, you have to simply type in the function ”=SUM(A2:A5)”. This will automatically add the content fill in cell A2 to A5, and the total will be displayed in the A6 cell.
The average function is used to find the average of the selected range of cell values. For example: if you want to find the average of total sales mentioned in column A, just type in the function “=AVERAGE(A2, A3, A4, A5)”. This will automatically find the average values filled in the cells A2 to A5 and display the result in your desired location.
The count function gives you the total count of cells in a range that hold numeric values. The cells which are blank and which do not hold data in numeric format are excluded from this function. Type in the function “COUNT(A1:A5)” to get the total count of cells.
To get the count of all the cells with numeric values, texts, and data in any other format, use the function COUNTA(). To get the count of blank cells, use the function “COUNTBLANK().
The Modulus function can be used when you want to display a remainder after dividing a number by the divisor. To get better clarity of the Modulus functions, here are some of the examples:
· Use function “=MOD(A2,3)” in the function (fx) tab if you want to divide the data in cell A2 by 3. For instance, if the number in the A2 cell is 10, you will get 1 as a remainder using the function, and the result will be stored in the desired cell.
· Similarly, if you use function =MOD(A3,4), you have divided the number in cell A3 by 4. The remainder will be stored in cell B3 or the cell of your choice.
Are you finding a superscript symbol in Excel? Well, you have to use the power function for the same. The power function Power () in Excel gives the result if the number is raised to a specific power like 103, 25, etc.
To use the power function in Excel, type =Power (box number, raised to number) like =Power(A2, 3). This means the number in box A2 will be raised to 3. If the number is 10, it will be raised to 3, and the result 1000 will be displayed in cell B2.
Do you want to run all decimals figures in the Excel sheet to round the number to its nearest multiple of significance? Use function =CEILING() for the same.
For instance, select the B2 cell or any other cell of your choice where you want the answer to display and then use function =CEILING(A2,5); the number in cell A2 will turn into the nearest multiple, i.e., suppose cell A2 holds the number 35.5, on applying ceiling function it will turn into 40.
If you use =CEILING(A2,1), the number in cell A2, i.e., 35.5, will turn into 36. Excel also has another similar function =CEILING.MATH rounds the number to its nearest integer or nearest multiple of significance.
This function is the opposite of CEILING. You use the function Floor to round a number down to its nearest significance. You use the function =FLOOR(box with number that is round down, places to be round off), i.e., =Floor (A2,5).
For example: If the number is 35.32, it will return 35. Also, if the number is 39.7, it will still return 35. If the function is =FLOOR (A2,1), the number 39.7 will return to 39.
CONCATENATE is used to join several text strings into one string. To understand better, here is the example:
· If cell A2 has the word Hello, B2 has the word World. Using function =CONCATENATE(A2, B2) will get HelloWorld in the selected box. You need to use function =CONCENTENATE(A2, “ “, B2) to include space. This will result in Hello World.
· If the box A2 has the word ‘How is’, whereas B2 has the word ‘my friend?’ You need to use function =CONCATENATE(A2&” “&B2) for proper spacing between words.
Want to count the number of characters in a string? You can use function LEN().
For instance, if you have the word Computer in cell A2, it will return the result in box B2, i.e., 8, using function =LEN(A2). The function is super easy to use. If there are two words, it will count space as a new character.
REPLACE function replaces a part of the text string with a different text string.
The syntax to use REPLACE is quite long but quite handy. To syntax =REPLACE(old_text,start_num,num_chars,new_text). The start_num is the index position of the text that is to be replaced. Num_chars is the number of characters that need to be replaced.
To understand better, here is an example:
· A2 has A101, i.e., the old _text, B2 has the result B101, the replaced number, i.e., new_text. Thus, we are replacing A101 with B101. The function will be =REPLACE(A2,1,1, “B2”).
· To replace Adam with Saam, use function =REPLACE(A2, 1, 2, “Sa”).
11. UPPER, LOWER, PROPER
These three functions convert a text string to uppercase, lowercase, and proper case, respectively.
For instance, to covert Apple into APPLE, use =UPPER(A2)
To convert APPLE to apple, use =LOWER(A2)
To convert aPple to Apple, use =PROPER(A2)
These are some common MS Excel Formulas that are commonly used and extremely helpful. To learn more about such MS Excel Formulas, join Select membership today!