5 functions you need to excel at Excel

For a working professional, proficiency in Excel is imperative in order to analyse, sort and present data. 

Microsoft Excel functions are easy to understand and use, which has added to its popularity and widespread usage in the business world. Certain industries such as accountancy rely heavily on Excel, depending on it for regular data entries. Despite this, many functions of the software are either unknown or not nearly used as much as they should be. This blog will outline some of the most useful Excel functions and how you can apply them at work. 

How many functions does excel have?

Microsoft Excel is part of the Microsoft Office suite. It is a spreadsheet programme used to create grids of texts, numbers and more. Excel commands can also be used to import data from external sources. Excel is of great importance for business as it used for segregating data efficiently and recording expenditures and income. It can also be used to chart information, plan budgets and present data in a compact manner.

The total list of Excel functions is constantly revised, but the current number stands at 479. 

What are the most useful Excel functions?

While there are numerous Excel functions, we often limit ourselves to a few regularly used ones. Many Excel users are also not acquainted with formulas that can make their work vastly simpler. Here are some well-known and yet highly under-used functions of Excel that you need to look into: 

1. Flash-Fill

This feature was introduced in 2013 and essentially gave the software a mind of its own. It is among the top Excel functions to know as it automatically fills data in your sheet whenever it notices a pattern. 

You may have a list of things marked in the first 20 cells of a certain column, for instance ‘BOP001’ to ‘BOP020’, and want to get rid of the letters and only keep the numbers. Here, flash fill can be used to eliminate ‘BOP’. All you need to do is set the pattern by typing 001 in the first blank cell and then as you type the next set, the flash fill will recognise the pattern and fill all the cells. To enable this function, you need to go to file option and turn on Flash Fill, or you can manually use Ctrl+E.

2. INDEX-MATCH 

Even though VLOOKUP is among the most used Excel formulas, it has its limitations as it can only search for values from left to right. Alternatively, INDEX and MATCH can search values from the entire table. VLOOKUP can only properly operate if the value is on the left of the table, while the others are not regulated by table positions. 

When you use INDEX and MATCH together in combination, you can pull information from a large dataset easily. While mastering this formula may take a little time, it will sufficiently help you to be more efficient and faster.

3. CTRL Z / CTRL Y

Perhaps the most useful, yet the least used Excel function is Ctrl Y/ Ctrl Z. Working on an Excel spreadsheet is a relatively simple but often professionals make mistakes and occasionally need to rectify accidental changes. 

Ctrl Y and Ctrl Z are the two magic keys that are extremely helpful in these situations. While many know that Ctrl Z that helps undo mistakes, they are unaware of Ctrl Y which does the opposite which is 'redo'. The two work well when dealing with modifications on Excel. 

4. Freeze Panes

Working on Excel means dealing with a huge amount of data when means that while scrolling, it is easy to lose track of the columns. In this situation, freeze panes can be used to ‘freeze’ a specific row and column in order to check data in other cells against it. Select the cells on the right of the columns or beneath the rows, then go to ‘view’ tab and select freeze panes. Manually, the Alt WF command can also be used.

5. Remove Duplicates

Removing duplicates is a necessary requirement, especially with a large set of data. You can also manually select data and remove duplicates and, if you wish to simply highlight duplicate entries, then use the Alt H L command (located on the home ribbon under styles). 

Sufficient knowledge of Excel is essential for professional courses such as ACCA and AAT. To guarantee success in these renowned qualifications it is important to find a course offered by a leading institution such as the London School of Business and Finance. The school offers award-winning teaching which ensures relevant skill-building for a successful career. 

This article is written by Nandita Kaushal and edited by Amelia Hayward-Cole