Top 5 Advanced Excel Functions You Must Know in 2021

 

In 2020, after 25 years of its launch, Microsoft excel still holds a credible place in the eyes of finance professionals. It is still relevant for finance professionals after decades of its launch because of its new features that help its users to save their time. In this article, we will outline the Top 5 Advanced excel functions that have made life easier for finance excel users.

1- Flash fill

Most of the beginners related to finance field are some time got confused that how they separate string of number easily as per their wish. Flash fill is the answer to that question. You have to manually type the digits you want in the first active cell of the column. You can use the command of CTRL-E to execute this function. Please see the pics given below to understand the usage of this function. 

 flashfill-1-Top-5-advanced-excel-functions. flashfill-2-Top-5-advanced-excel-functions flashfill-3-Top-5-advanced-excel-functions

If you are not able to use the flash fill option then make sure that Flash fill option is enabled in the advanced setting. You can use the instructions given below to enable flash fill in the Microsoft excel sheet

File>Options>Advanced>Editing options>Automatically Flash fill

2- Text to Columns

It is a very basic yet helpful function to convert one column into multiple columns. For example, you have a column with names and you want to split the first and last name from that column. You have to select the column and go to the Data tab. In Data tab click on the text to column option and select the parameters as per your data. You can use the pic given below for the demonstration of text to column function. Please make sure that you use the delimiters as per your data.

Text-to-columns-1-Top-5-Advanced-excel-Functions

3- Concatenate 

Now we understand that if we want to convert one column into two columns we can use text to column function. But, if we want to merge two columns then we can use concatenate function in advanced excel. It can be seen in the pics below that we want to merge the name column with ID column. So, we will use the concatenate function which will result in the merger of both columns.

concatenate-1-Top-5-advanced-excel-functions concatenate-2-

If you want to merge both columns and place a hyphen between them then we have to place the hyphen in the formula within commas.

 concatenate-3-Top-5-advanced-excel-function

4 – SUMIF 

Sometimes we have to extract data by applying filters. If we have two columns one of rank and other of salary and we want to check how much Salary people with rank 5 are getting. In this case, we can use the sumif function of advanced excel.

 Sumif-

Similarly, if we want to count the number of people in the rank 5 then we can use the COUNTIF function int the same way.

5- Conditional Formatting

In advanced excel, conditional formatting is one of the best ways to get the desired results. It also helps to make an attractive presentation of your work. Let’s suppose if we want to highlight the salaries greater than $30,000 then we will take help of conditional formating. We have to select the range, go to the home tab, click on the conditional formatting and select the option of greater than. You can select your customized colour as well. 

condittional-formatting-

Similarly, conditional formatting has a variety of options for your data. You can highlight the cells, you can set icons, you can make your own rule for formatting as well. It all depends on how you use conditional formatting as it is open for all with innovative thinking.

Now, you have seen the top 5 advanced excel functions that can make your life easy. But, there are not just these functions. Excel is that treasure box which can’t be used as per its potential. It is so much exciting and innovating that sometimes you get amazed by the things you can do with this tool. Even, this is a must-have tool for finance professionals. If we want to excel in your career then you have to master this tool. It is very easy to do if you give you 100% and full dedication.

About Muhammad Haris

Check Also

rich dad poor dad key points

Rich Dad Poor Dad Key Points – Robert Kiyosaki

Do you want to know how rich make money? Then the book of Robert Kiyosaki …

Leave a Reply

Your email address will not be published.