4 new Excel functions to boost your CV (from a data expert)

Be honest – does your CV state Microsoft Excel as a skill? And be really honest – do you actually feel confident with it? The truth is, yes, you can use the software at a surface level. But if you have the know-how, there are also long-standing and new Excel functions that make it an incredibly powerful tool.

•   About partnership content

Since its launch in 1985, Excel has become somewhat of a staple for many workers. The latest estimates suggest that over one billion people worldwide use it, as well as the majority of businesses.

During that time, it has undergone constant development – particularly in the last few years. Now, Microsoft 365 subscribers automatically receive updates and improvements.

But still, you need to have the confidence and knowledge to use new features effectively.

Happily, there are experts out there who have Excel mastered – including Ally Mitchell, who leads the Elevate service line at finance recruitment and advisory firm Hutcheon Mearns.

A data expert’s pick of the new Excel functions

Ally has identified four additions to Excel he thinks you should incorporate into your skillset. These will enhance your proficiency as an Excel user, helping you and your CV to stand out to your current or prospective employers.

And, of course, it will be quite satisfying to show off your new skills to colleagues in the office!

Here are four new Excel functions to master:

1 How to use the XLOOKUP Function

The XLOOKUP function is a powerful new lookup function that allows you to search for a value in a table or range and return a corresponding value from the same row or column. This function replaces several other lookup functions, such as VLOOKUP and HLOOKUP, and is more versatile and efficient.

To use XLOOKUP, you need to specify:

• the lookup value (what you’re searching for)
• the array or range where you want to search
• the column or row where the corresponding value should be returned from.

You can also specify additional parameters such as whether to return an exact match or an approximate match, and what to return if no match is found.

Gone are the days of counting how many columns are between your lookup values and return values! Here’s an example:

=XLOOKUP(B4,G4:G15,H4:H15,”Not Found”)

This formula searches for the value entered in cell B4 within the range G4:G15 and returns the corresponding value from the range H4:H15. If the value doesn’t exist, it will return “Not Found”.

2 How to use the LET Function

The LET function allows users to declare and define variables within a formula. This means that instead of repeating the same calculations within a formula, you can use the LET function to assign a name to a value, and then use that name throughout the formula. This can simplify complex formulas and make them easier to read and understand.

To use the LET function:

• Start by typing “LET(” into a cell.
• Then, define your variables by naming them and assigning values, separated by commas.
• Finally, enter the rest of your formula and close the function with a closing parenthesis.

Here’s a simple example:

=LET(a,10,b,20,a+b)

This formula assigns the value 10 to the variable “a” and the value 20 to the variable “b”, and then adds them together.

3 What are Dynamic Arrays?

When it comes to new Excel functions, we can’t forget about dynamic arrays. This is a new feature that allows formulas to automatically spill into neighbouring cells. This means that instead of manually copying and pasting formulas, you can write a formula once and let Excel do the rest.

Dynamic arrays are particularly useful for functions that return multiple values, such as the new functions FILTER, SORT, and UNIQUE.

• FILTER allows you to extract and output a limited subset of data from a table or range based on specific criteria.
• SORT is a function that allows you to sort data in ascending or descending order based on one or more columns.
• UNIQUE is a function that allows you to extract a list of unique values from a range or table, removing any duplicates.

These functions can be used individually or in combination to save you time in manipulating and analysing data.

4 What does Excel Power Query let you do?

Power Query is a data transformation that allows you to extract, transform and load (‘ETL’) data from various sources prior to use within your workbook. Example sources may be data direct from a website, other workbooks or even a live system such as your accounting software.

Power Query uses a user-friendly interface to perform tasks such as filtering, sorting, merging and other data transformation, meaning the data you need for analysis is always in a clean and ready to use state.

The data transformation steps created can then be automatically reused every time the data needs to be refreshed – enabling users to work more efficiently and effectively, saving time and reducing the likelihood of errors.

Power Query is also a key component of Power BI, Microsoft’s business intelligence and data visualisation tool – meaning that if you learn these skills, they can be leveraged across both platforms.

Get expert help transforming your business finance with data

Using data and tools (including new Excel functions) can transform your business finances, helping you to keep your information in tip-top shape, make efficiencies through automation and analyse what’s happening effectively.

If you know you don’t have the skills or time to do this effectively, there are experts who can help take this off your hands.

Ally Mitchell leads Hutcheon Mearns’ Elevate service line, focusing on business intelligence, process improvement and automation. As a computing graduate who is now a practice-trained and industry qualified accountant, his unique skillset allows him and his team to transform ways of working. This results in more accurate and meaningful analysis of financial and/or operational data.

Find out how Hutcheon Mearns’ Elevate service could improve your business.