10 Noteworthy Microsoft Excel Functions and How to Use Them

Microsoft Excel is one of the most powerful and popular tools used by businesses, small and large. In the early days, Microsoft Excel was considered technical and difficult to understand with all the formulas, functions, spreadsheets, graphs, and histograms. But, over the years, the user experience with Excel has evolved drastically, and employees proudly add Excel skills to their resume. Did you know that there are over a billion Microsoft Office users across the globe? Each organization has its own ways of using Excel to improve efficiency, productivity, and data management. Not just professionals, even students, and households use it for their school projects, budget tracking, and more.

There are 100s of formulas in Excel, right from simple mathematical to logical, statistical to engineering. So, users often get confused about what formula is right for a particular analysis. Don’t get overwhelmed! Here are the top 10 noteworthy Microsoft Excel functions that you certainly need to know of:

1. RAND Function

A really traditional and simple function, RAND generates random numbers between 0 and 1. It is used commonly in cryptography, statistical analysis, probability theory, gambling, and gaming. This function returns an evenly distributed real number at random, every time a worksheet is calculated. The numbers are generated using the Mersenne Twister algorithm (wiki).

Syntax of RAND function:  RAND ()

There are no arguments required for this function. So, if want to generate a random number between a and b, the function to be used is –

=RAND ()*(b-a) +a

If you do not want the generated number to change every time, add the function in the formula bar and press F9. 

Variations of RAND function:

=RAND ()*100 – Generates a random number between 0 to 100 (Less than 100 and greater than or equal to 0)

=INT (RAND ()*100) – Generates a whole number between 0 and 100 (Less than 100 and greater than or equal to 0)

Recommended for you: Tips for Self-development as a Remote Software Developer in COVID-19.

2. CHOOSE Function

 The purpose of the CHOOSE function is to get a value from a list based on their position.

This function requires 3 arguments – index_num, value1 and value2. It is a very powerful and easy to understand function. This function is an advanced Microsoft Excel formula that can be used instead of an IF function in complex calculations. With a simple function, you will hardly come across any user-facing difficulties while implementing this one. 

Syntax of CHOOSE function: CHOOSE (index_num, value1, [value2], [value3].)

Here index_num is the position of the value that needs to be returned and Value1, Value2, and so on are the values to choose from. There can be a maximum of 254 values. It is compulsory to have Value1. The rest of the values are optional.

The best part of the CHOOSE function is that values can be numbers, cell references, text values, defined names, or even formulas. Combined with other Microsoft Excel functions, the CHOOSE function can be quite useful.

3. VLOOKUP Function

One of the most popular Microsoft Excel functions, VLOOKUP helps find data in a table or range by row. Though it sounds simple, finding data in long Excel worksheets full of numbers can be manually tiring and time-consuming. This is where VLOOKUP comes to your rescue. This function looks up data in any table that has been organized in a vertical manner.  It is one of the most useful functions for data analysis.

VLOOKUP supports –

• Exact match.
• Approximate match.
• Partial matches for the wild card.

Syntax of VLOOKUP is VLOOKUP (value, table, col_index, [range lookup])

In the above function, value stands for the value that needs to be searched, table stands for the table from which value needs to be retrieved, col_index stands for a column in that table from which value needs to be retrieved. Range lookup is optional and its value is FALSE in case of exact match and TRUE in case of an approximate match.

The values that need to be looked up must appear in the first column of the table passed in the function. You can find a more detailed explanation of VLOOKUP here in this article.

4. IFERROR Function

IFERROR function does truly what its name says. It is a function used to catch errors and replace those cells. The error cells can be replaced with a value, blank cell, or a custom message. It is an excellent function to trap, locate, and handle errors in a worksheet. This function can be used with VLOOKUP and INDEX function.

Syntax of IFERROR is IFFERROR (value, value_if_error)

Here value stands for the error that needs to be checked for. It can be a value, formula, cell reference, or an expression. Value_if_error stands for a value that needs to be returned in case an error is found. It can be a blank cell, numeric value, text message, or another formula. It is possible to use IFERROR in nested functions and even in arrays. This is a great way to avoid a bunch of errors coming up in your worksheet and gives you a way to handle errors the way you want.

5. INDEX-MATCH Function

INDEX-MATCH can be considered to be an advanced function of VLOOKUP. It helps in data analysis when VLOOKUP cannot be applied. Actually, VLOOKUP can only be used to lookup values from left to right. It requires that the value that needs to be matched is in the first column on the left. INDEX and MATCH are 2 separate functions but work really well when they are combined together. When combined together, it is one of the most used and most powerful functions for data analysis. It can be used to precisely extract data from a large dataset.

Syntax of INDEX function is INDEX (array, row_num, [col_num], [area_num])

Here array stands for a constant array or range of cells, row_num stands for the row number from which data needs to be fetched, col_num stands for the column number from which data needs to be fetched and area_num is optional.  

Syntax of MATCH function is MATCH (lookup_value, lookup_array, [match_type]) 

Here lookup_value stands for the value you are looking for, lookup_array stands for range or array of cells in which the value needs to be searched and match type is optional. It can have any of the three values -1, 0, and 1.

You may like: 25 Lesser-Known Amazing Windows 10 Features You Need to Know.

6. CONCATENATE Function

When working on Excel sheets, there is data in various columns. Excel offers a whole set of tools to edit and organize this data. Sometimes, users may need to combine data from two separate columns. Here the CONCATENATE function comes in handy.  It is one of the text functions that allow the joining of different text cells into a single stream. It is also possible to create entire sentences by concatenating different words.

Syntax of CONCATENATE function is CONCATENATE (text1, [text2], [text 3] ...)

Here text 1 is the first cell that needs to be joined. Text2, text3, and so on are additional text that needs to be joined. There can be up to 255 texts that can be joined. Text1 can be a numeric value, text, or cell number.

7. TRANSPOSE Function

Do you often come across situations where you literally have to flip flop values in the Excel worksheet? Well, it can be a really tedious and tiring task, especially when the volume of data is high. Don’t worry! Try the TRANSPOSE function. It allows you to switch or rotate cells and flip the orientation of an array or any given range of cells.

Syntax of TRANSPOSE is like TRANSPOSE (array/ cell range that needs to be flipped)

When the array of cells is horizontal, the TRANSPOSE function converts it to vertical and vice versa.

8. XNPV Function

The XNPV function is highly popular among investment banking and accounting professionals. It is also used for accounting and finance students, or any person dealing in cash flows, corporate finance, equity research, and more. It is used to discount cash flow for specific dates.

Syntax of XNPV () is XNPV (Rate, Cash Flows, Dates of Cash Flow)

Here rate stands for a discount rate that needs to be used, cash flow represents income and payment and date is the date array for the period.

9. SUMIF Function

It is one of the most commonly used functions that every Microsoft Excel user needs to be aware of. It is basically used to perform an action depending on whether a statement is false or true. This is used in conditional formatting of data and sums up related cells.

Syntax of SUMIF is SUMIF (Range, Criteria, sum range)

Here the range is the group of cells that need to be checked for the condition, criteria are the cell you are looking for and sum range is actual cells to sum.

10. CONVERT Function

As the name suggests, the CONVERT function converts numbers between different measurement units. It is popularly used in the engineering industry and hence categorized as Engineering Function in Microsoft Excel.

Syntax of CONVERT function is CONVERT( number, from_units, to_units)

Here value to be converted is the number while from and to are units.

You may also like: Top 10 Tips to Detect and Remove Phone Spy Software (Spyware).

The Bottom Line

All of the above Microsoft Excel functions save a lot of time and resources. They help make data fruitful and easy to interpret. From budget planning to expense tracking, Microsoft Excel has been quite beneficial to small, medium, and large size businesses. It’s always a smart move to stay updated on the new functions and tools of Microsoft Excel to use the tool optimally.

Disclosure: Some of our articles may contain affiliate links; this means each time you make a purchase, we get a small commission. However, the input we produce is reliable; we always handpick and review all information before publishing it on our website. We can ensure you will always get genuine as well as valuable knowledge and resources.