6 useful Excel functions examples you should know

Author: Zuzana Kocáková

Digitization

4 reading minutes

Excel offers a huge range of features that can improve your ability to perform important tasks that are also relevant to your job. It doesn’t matter if you’re the head of a company, work in an office or are just a casual Excel user. We bring you the most practical Excel functions list and some formulas that you definitely need to know and will speed up your work. Excel spreadsheets won’t be a bogey for you from now on, but a useful helper. Here are six steps on how to work in Excel efficiently. Excel spreadsheets can be a real pain sometimes. Apart from creating spreadsheets in Excel, filtering in Excel, locking cells in Excel, diagrams in Excel, contingency table in Excel, Excel multiplication function or how to round numbers in Excel, there are many other tricks to speed up your work in Excel. Think of it as a little Excel training so you’ll have Excel functions in the palm of your hand. Are you ready to learn something new or do you already know everything about all these functions? Don’t let an Excel spreadsheet intimidate you anymore!

Eliminate unnecessary gaps with TRIM

This is the most basic, but also the most important function, of Excel. It is used to trim and remove extra spaces from any cell. Excel’s TRIM function is useful if you pull any data out of a database and then see a lot of gaps. The TRIM function will remove every space from the text, except for single spaces between words. Example: You copy the sample data into a spreadsheet and then paste it into cell A1, which is in a new sheet in Excel. If you want the formula to show the relevant results, you highlight them, click F2, and finally press ENTER. You enter the formula: =TRIM (” Last year’s profits “). The function will remove trailing and leading spaces that are found in the text of the given formula. The result will be: “Last year’s profits”. Once you have mastered similar formulas in Excel, your work will be much more easier.

Learn how to use the Excel VLOOKUP function

Excel VLOOKUP is one of the most well-known and best features of this program, because it connects the data from the spreadsheet as requested by the user. This Excel function finds specific information from a spreadsheet and copies it to another one. Excel’s vlookup function is useful when you need to find the contents in a spreadsheet or range by row. This can be used to look up the price of automotive components by part number or the name of an employee by their identification. Excel’s vlookup functions are handy aid that will save you a lot of time.

You need 4 pieces of information to build a syntax function:

1. The value you want to seek.
2. The range within which the search value is located. The VLOOKUP function must always be in the first column of the range only. If the value you are searching for is in cell C2, the range must start with column C.
3. The number of the column in the range in which the returned value is given. If you specify the range C2:D10, you should count column C as the first column and column D as the second column.
4. Approximate match or FALSE/TRUE value. If you need an approximate match or a FALSE value, you can enter TRUE instead. This way you get an exact match of the returned value. If you enter nothing, the default value will still be just TRUE or an approximate match.

The HLOOKUP function is similar – there is only a small difference between it and VLOOKUP. VLOOKUP (V) searches for information vertically, that is, it searches for it in columns. The HLOOKUP (H) function searches for information horizontally, that is, in rows. TIP: Try the XLOOKUP function. This is an improved version of the VLOOKUP function, because this Excel function works in any direction and also returns exact matches to the spreadsheet.

Count the cells according to the given criteria with Excel SUMIF function

The SUMIF function is used to count cells according to the specified criteria. For example, you can use it to count the profits you have made in a certain period of time. The SUMIF function is written in the form: =SUMIF(range, criterion). [rozsah_súhrnu]).

What does the data in brackets mean?

• We specify the range for the cells we need to evaluate.
• The criteria restrict or select only a certain part of the spreadsheet or cell to be evaluated.
• The sum_range refers to the cells that need to be counted after evaluating the criteria.

Example: if you have, for example, order numbers, prices, branches and all order data in a spreadsheet and you want to find out the sum of prices only for a certain branch, this function will help you a lot. Excel’s SUMIF function is thus another tool you should start using actively. The Excel logical IF function is needed to evaluate a given condition and return one of the result values. The primary condition is evaluated as TRUE, which means truth, the so-called fulfilled requirement. The opposite case is FALSE, which means falsehood, an unfulfilled requirement. Requirements in Excel are one of the basic functions of the program. The Excel IF function is useful for example if you need to see in a spreadsheet whether a client has made a purchase over a certain amount. If the condition is fulfilled, he will receive a gift, otherwise he will receive nothing. Excel’s IF function is perfect for this type of task. Try the IF function in Excel and you will see for yourself that it is a very handy tool.

What does the IF function notation look like?

=IF(logical_test;[value_if_true];[value_if_false])[hodnota_ak_pravda])[hodnota_ak_nepravda])

• The logical_test is any value you need to evaluate. The value will be checked to determine whether or not it has been satisfied.
• The value_if_true represents the value, formula or text that you get when the entry in the logical_test field evaluates to TRUE, that is satisfied.
• The value_if_false impersonates the value, formula or text that will be returned if the expression in the logical_test field evaluates to FALSE, that is not satisfied.

Calculate individual values with Excel SUM function

The SUM function is used for simple addition of values, where individual values, ranges or cell references can be added. It is possible to combine the three mentioned data, i.e. value, range and cell reference. For example, if you have 6 cells with numeric figures in the range C7:C12, the SUM function will quickly add them. Excel’s SUM function avoids manual addition and makes your work more efficient.

Excel functions RIGHT, LEFT, MID

These Excel text functions are used to grab specific sections from the text. Thanks to them, you can select only a part of a numeric and text string. Each function, according to its name, helps to select a certain number of text or numeric characters from the right, left or center. The RIGHT function is needed to return the specified number of characters from the right side of the text string. You write the function as follows: =RIGHT(text; [num_chars]). The LEFT function is used to return the specified number of characters from the left side of the text string. The function notation looks like this: =LEFT(text; [num_chars]). The MID function is ideal for returning some portion of a text string from a specified position and based on a specified number of characters. The notation of the MID function is as follows: =MID(text; start_num; num_chars).

What does the data in brackets mean?

• Text indicates a text string – it contains the characters you need to extract.
• The start_num (starting position) is the position of the very first character you want to select.
• The number_chars (number of characters) specifies the number of characters to be in the final result.

Working with Excel can be a breeze

Do you know all of these basic functions in Excel? Knowing them will save you a lot of time doing work that would otherwise take you hours. Plus, knowing them can advance your career. Of course, Excel packs an incredible amount of features that could be written about endlessly. We’ve shown you a brief basics that you should know too. If any feature in Excel makes your work more efficient, it is a success!

Share article

6 useful Excel functions examples you should know

Zuzana Kocáková

At msg life Slovakia I take care of the company culture, events and I am part of the marketing team. I like a job where I can be creative and contribute to making my colleagues at msg feel good. I regularly prepare for you not only news and interesting things from the world of information technology and insurance, but also from behind the scenes events at msg life Slovakia.