In the last few years, there has been a peak in new software programs. However, Microsoft Excel stays at the top for analysing data and a lot of people still continue to use it. So, it is worth finding out functions that you might not familiar with. Here are 5 functions that you need to know in Excel.

  1. INDEX MATCH

This is an advanced alternative to the VLOOKUP or HLOOKUP formulas (which have several drawbacks and limitations). With the exception of VLOOKUP, INDEX and MATCH is the most commonly used search functions in Excel. The INDEX and MATCH combination is efficient and customizable, and you can see it used in all sorts of formulas, from simple to very advanced. INDEX MATCH is a powerful combination of Excel formulas that will take your financial analysis and financial modelling to the next level.

  • INDEX returns the value of a cell in a table based on the column and row number.
  • MATCH returns the position of a cell in a row or column.
  1. Flash Fill

Excel Flash Fill is a tool that analyses the information you insert and instantly fills the data when a pattern is found. The Flash Fill function was launched in Excel 2013 and is available in all subsequent versions of Excel 2016, Excel 2019, and Excel for Office 365. Flash Fill can be used in many different situations. It’s a great time saver when you need to enter or alter a lot of data easily and precisely.

For example, there is a list of product numbers in the first ten cells of column A, so ABC – 0001 to ABC – 0010, but you only need the number after the dash. You can easily discard the ‘ABC’ using Flash Fill. You can find it clicking on Data – Flash Fill, or manually Ctrl+E.

  1. Add Multiple Rows

Inserting new rows to the existing rows is very common in Excel, so there is a quick button to do that. The shortcut (Ctrl, shift, +) is very useful, particularly because you can adjust the + to add multiple rows. Sometimes, just highlight the number of rows you want to add (say 5), and, by right-clicking, inserting is easier when adding in bulk because it removes the number of rows you’ve highlighted.

  1. Freeze Panes

Sometimes scroll down a wide data table only to forget which columns are those?  It is common that when you scroll down a wide data table you forget which columns are which. The solution here is Freeze Panes. You may freeze the top row, the first column, or any number of rows. Identify the columns and rows in the area you want to freeze. Then pick the cell to the right of the columns and below the rows. Go to the Window section of the View and Freeze Panes page.

5.      F4

There are two particularly satisfying ways to use F4 in Excel. The first is when you create an Absolute Reference: F4 clickable buttons you through the different options. The second is one that few people know about, but that could significantly increase the Excel productivity. F4 repeats your last action, if appropriate. For example, if you have just applied a border to one cell, use F4 to apply it to others.