Excel functions can help you get more done in your day-to-day office activities. Even while Microsoft Excel offers hundreds of formulas, most users only use a few of them, and you’ll probably never need to use the majority of them. That’s why in this blog we’ve put together some of Excel’s most innovative and practical features. Let’s have a look at some of the most commonly used functions.
- VLOOKUP & HLOOKUP: VLOOKUP is a well-known Excel function for matching data from a table to a user selection. HLOOKUP, a lesser-known function, performs the identical task but in a reversed search pattern. To look up and find a value, both functions require a table. VLOOKUP looks for the lookup value, whereas HLOOKUP looks in the first row. After locating the lookup value in the search column or row, both functions return the value in the specified column or row number in the table array selection. The final parameter of the formula determines whether it should search for an exact or approximate string (range lookup).
- IF, AND, OR: The IF function is a handy and straightforward tool. Excel may detect a logical test and assign a value to the cell or perform another calculation based on the test’s result, which can be True or False.
- Syntax: IF(logical_test, value_if_true, [value_if_false])
- SUM: The SUM function does exactly what it says. It adds things up. It adds up all of the numerical values given as inputs. A single cell, a range, or a static numerical value can be used as arguments. Up to 255 arguments can be passed to a single SUM function.
- Syntax: SUM(number1,[number2],…)
- SUBTOTAL: Let’s take a closer look at the subtotal function now. The SUBTOTAL() function returns the subtotal of a database. Depending on your demands, you can choose among average, count, sum, min, max, min, and other options.
- Syntax: SUBTOTAL(function_num,ref1,[ref2],…)
- AVERAGE: The average function work similarly to the SUM function. It calculates the average of all the numbers in the syntax. The argument limit is 255, just like the SUM function. Arguments can be static values, single cells, or ranges.
- Syntax: AVERAGE(number1,[number2],…)
- MODULUS: When a given number is divided by a division, the MOD() function returns the remaining.
- Syntax: MOD(number, divisor)
- REPLACE: The REPLACE() method, as its name implies, replaces a segment of a text string with another text string. start_num is the index position where you wish to start replacing the characters. num_chars indicate the number of characters you want to replace.
- SUMIF and COUNTIF: The SUMIF and COUNTIF function sum up or count the number of values when a set of criteria is met. Both functions search the range of arguments for the condition specified in the criteria parameters. The SUMIF function contains an optional argument entitled sum range that is used if the sum values and search values are in different ranges. The criterion argument can be set for logical circumstances such as equal, not equal, greater than, equal to or greater than, lesser than, and equal to or lesser than. For the equal to condition, you can use a static value or a cell reference, and operators for the others. For example “>”&C5 should be the criteria parameter for looking for values “greater than the value of C5 cell.”
- RIGHT, LEFT, MID: These are Excel’s fundamental text functions for extracting specific sections of text. Text is the first argument for all functions. While the RIGHT and LEFT functions each have an extra parameter to specify the number of characters (num bytes) to include, the MID function has two extra arguments to specify where the characters (start num) begin and how many characters (num bytes) to extract.
- Syntax:
LEFT(text, [num_chars])
RIGHT(text, [num_chars])
MID(text, start_num, num_chars)
- ROUND: The ROUND function allows you to alter the decimal component of a numeric number. There are two arguments required. The first is the numerical value itself (number). This can be a static number or a cell reference, as is typical. The number of decimals to round to is specified by the second option (num digits).
- Syntax: ROUND(number, num_digits)
- COUNT: COUNT() returns the total number of cells in a number-filled range. It rejects blank cells and cells with data in any format other than numeric. If you need to count all the cells with numerical values, text, or any other data format, use the COUNTA() function. COUNTA(), on the other hand, does not include any blank cells in its calculations.
- POWER: The method “Power()” returns the result of increasing an integer to a specified power.
- CEILING: A value is rounded to the next significant multiple via the CEILING() function.
- FLOOR: Unlike the ceiling function, the floor function rounds an integer to the next significant multiple.
- CONCATENATE: This function joins or combines several text strings into one.
Final Thought: There are a plethora of excel formulae available. However, not all of them are essential. The list above is good enough. Youtube is a good place to start if you want to learn more. One problem you’ll have with YouTube is deciding where to begin. Use Career Ninja‘s Learn Tube chrome addon, which is free. From the first to the final video, the extension will show you. You don’t have to click all over the place. Remember to use the formulas and practice them.
