Top 10 Excel Formulas to make you a PRO!

excel read watch Apr 27, 2023
A confident analyst using excel

Excel is no doubt a powerful tool from Microsoft and has a vast array of features and formulas that can make work really easy reduce time wasting and increase your efficiency as you use it. 

There are over 450 functions and formulas in Excel and this number continues to grow with each new version of the software. 

Let's look at the top 10 formulas in Excel 2023 that you should be familiar with and utilize in your everyday use of Excel to make work easier and faster.

  • TextBefore/TextAfter

The "Text Before" and "Text After" functions are used to extract a specific portion of text from a longer text string. These functions can be helpful when you need to extract and work with a specific piece of information, such as a name or date, that is buried within a larger text field.

  • TextSplit

Text Split is a more efficient way to split data from one column into different columns without having to manually enter the "Texttocolumn" function when working with large pieces of data.

You can use this function effectively by combining an array of arguments and delimiters into the column you want to split your data into. Example =TEXTSPLIT(D21,",")

  • Sequence

This function makes it easy to create serial numbers and dates for your rolls and columns in Excel so you don't manually input numbers or drag down that small + sign next to your cell to populate your rolls and column. Formula =SEQUENCE(insert instruction you want to deploy) i.e =SEQUENCE(1000), =SEQUENCE(10,,Date(2023,1,1),7)

  • ChooseCols

Choosecols function allows you to choose specific columns when working with a wide array of data that has several column headings. To use this function enter the formula, highlight your data sheet and select the column you will like to separate. Formula =CHOOSECOLS(H49:J64,1,3)

  • Vstack

Vstack function helps you to consolidate data in your spreadsheet especially when you have data that are in separate tables. You don't have to copy and paste tables which can be time-consuming and not very effective. Utilize the Vstack function for this. Formula =VSTACK(array1, array2)

  • Hstack

The Hstack functions similarly to the Vstack but this function allows you to stack your data horizontally. Formula =HSTACK(array1,array2)

  • ToCol/ToRow

This formula makes cleaning your data easier in Excel. It allows you to consolidate data into columns and rolls as you'd like. Formula =TOCOL(J135:J147) or =TOROW(G28:I40)

  • Take

 This function allows you easily collect a specific range of data in your spreadsheet. You might want to take the first 5, 10, or 20 rows of data on your sheet you can easily do this with the Take function. Formula =TAKE(array1,noofrows), =TAKE(J135:J147,5)

  • Drop

 This functions similarly to Take and it allows you to select a specific number of data from one table to another while working in your spreadsheet. Formula=DROP(array1,noofrows), =DROP(G27:H27,10)

  • Filter

The Filter function in Excel helps to filter specific information from a wide range of data in your spreadsheet. You can filter specific names, Id, departments etc easily. Formula=FILTER(array,include,[if_empty]) 

Exploring and consistently practicing the different functions and formulas in Excel will help you grasp mastery of these functions. There's a vast number of ways to use these formulas and this is dependent on what you are trying to achieve in your reporting or spreadsheet.

Watch this Video tutorial above on how to use these functions to see how else you can use and utilize them to enhance your productivity and become a Pro with Excel.

Take our Productivity Essentials with Microsoft 365 Course to learn the latest tips and tricks using Excel here >>>>>