15 Functions Every Aspiring Excel Pro Should Know
When you first start using Excel, you’ll quickly become familiar with using basic functions such as SUM, COUNT, MIN, AVERAGE
Before long, you’ll be using VLOOKUP’s, IF’s and creating Pivot Tables in order to start bringing you data to life.
However, it’s around this phase that I often see people plateauing in their Excel learning curve. In fact, Pivot Tables and Vlookups formed the core part of the ‘advanced’ Excel course I went on (many years ago admittedly)
Whilst there’s no doubt you can achieve a lot at this level, when you’re working with more complex requirements there’s a good chance you’ll spend more time trying to achieve your goal than you need to.
Once you manage find your way out of this ‘safe zone’ of Excel, you’ll quickly find yourself achieving a new level of efficiency in your work, and will ultimately open you up to more of what excel really has to offer – and realise than in Excel, you should never assume it’s not possible to do something – it’s just a matter of working out how.
So, to help you, here is my hand-picked list of the 15 Excel Functions every aspiring Excel Pro should know.
Some of these may seem very simple – but don’t be deceived – many of these functions have a few tricks up their sleeves above and beyond what is immediately obvious.
TEXT is a function which converts a value into a text string. It has many uses, but the two most useful are…
Converting a Date into a Text String (perhaps so you can use it in a sentence)
="Today, the date is " & TEXT(TODAY(),"yyyy-mm-dd")
Padding out a numeric value with extra leading characters
This is useful when you want to force a numeric value to be a certain length, adding extra leading 0’s
To achieve the same result with a text string, we have to take a slightly different approach
And to pad with extra characters at the end of the string…
=TRIM([String to Remove Spaces From])
Nice simple one – but a useful tool, especially if you’re having issues getting VLOOKUPS (and similar matching functions) to work.
It removes excess whitespace in your string…
- All leading and trailing white space
- Any additional whitespace between words (leaving 1 space)
Oddly, the official documentation only mentioned removing the leading and trailing spaces, but says nothing about mid-string spaces.
If you want to remove all white space in a string, you can use the SUBSTITUTE function
Prior to the introduction of the IFERROR function, error trapping was a fairly painful experience. You had to do something along the lines of
=IF(ISERROR([My formula]),"No Match",[My Formula])
Which worked fine, but had a couple of significant drawbacks. Because you had to repeat your formula twice – once for testing if it created an error, and once again for bringing the value back when it did not generate an error…
- It was prone to error, as you had to maintain the same (often complex) formula twice
- The formula itself has to be calculated twice – this could really slow your spreadsheet down
Ok, so both of these could be overcome by putting the primary formula in once column, then doing the IF(ISERROR()) on the result of that column, but it was still a pain.
IFERROR simplifies things, as you simply provide your formula you want to test, and the value if the result if an error. If there’s no error, the value of your original formula is returned.
It’s possible you’re already using SUBTOTAL, as Excel inserts these automatically when you use DATA>SUBTOTAL or when you use DATA>GROUP
Conceptually, it’s a fairly straightforward function – it performs an aggregation on a dataset, but you set the aggregation as a parameter. For example, SUBTOTAL(1,[Range]) gives the average, SUBTOTAL(9,[Range]) gives the SUM.
However, it’s possible to tweak the behaviour of the function. By default, if you hide a row, it will continue to be included in the SUBTOTAL figure. But what if you want to exclude hidden rows? Simple, we use a different set of aggregation options. Just add 100 to the original value to get the ‘new’ value.
RANK.EQ / RANK.AVG
Because I do most of my data transformations in the source data using SQL, I often overlook the RANK functionality in Excel. It’s not as powerful as what SQL can offer you, but it’s useful for many situations.
The original RANK function has now been retired (as of 2010) in favour of RANK.EQ and RANK.AVG, with RANK.EQ matching the behaviour of the original RANK function.
All three require a single value, the list of values in which you want to rank it against, and whether you want to rank in ascending or descending order.
For RANK and RANK.EQ, if two values are of equal rank, both will return the same, higher ranking, whereas RANK.AVG, when there are matching rank values, it being back the average of the rank values for each item.
(with RANK.AVG, value 64 takes place 2 and 3 – the average is therefore 2.5)
Introduced in Excel 2016 (and even then, only to Office 365 subscribers), this is one of my favourite functions.
If you want to concatenate a list of values, perhaps with a comma or space between each value, your formula could very quickly become unwieldy, where you have to build up your string inserting your commas between each value
Not always such a problem, but this is simplified with TEXTJOIN. Instead, we provide a list of the values we want to join, and what we want to use as a delimiter – and Excel handles the rest
Where this function really pulls its weight, it when you have a long list of values that perhaps you want to use elsewhere, but needs formatting a certain way. For example, if I want to run an SQL query for a list of product codes, I can now quickly create a list of values with quote marks and commas as required
NETWORKDAYS / NETWORKDAYS.INTL is another example of a fairly straightforward function which can be further exploited beyond what initially appears possible
(NETWORKDAYS.INTL is the newer version. NETWORKDAYS does not accept the [Weekend] parameter)
In its simplest form, you can provide just [Start Date] and [End Date], and it will calculate the number of business days between those two dates
However, there are two further parameters we can exploit.
If you use NETWORKDAYS.INTL rather than NETWORKDAYS, you have the [Weekend] parameter. This simply allows you tell the formula which days should be regarded as Working Days.
But, what isn’t obvious is that you can define your own set of workings days by providing a series of 1’s and 0’s to denote working days
Perhaps you want to know the number of working days your part-time member of staff has. If they only work Tuesday, Wednesday & Thursday, we can represent this in the same formula.
(1 = non-working day, 0 = working day. First digit is Monday)
But this isn’t all! There’s one last parameter to examine.[Holidays] allows us to provide a range of dates which represent any Holidays – these dates will also be excluded from the calculation
The obvious use for this is for Public Holidays – but don’t limit yourself to these – you can include any dates in this list
Ultimately, put these together, and you can create some powerful formulas with this function. I personally used this to resource plan a team of people – it was linked into both fixed holiday dates and also each users’ planned vacation dates
This function is again fairly straightforward – like NETWORKDAYS, it allows us to calculate the duration between two dates.
Unlike NETWORKDAYS, you can set the date part you want to count – eg Days, Months, Years
In the above example, I can calculate the number of months between the two dates, but there are other Date Parts we can use
But, what makes this function a little bit more unique is that you’d struggle to know it existed. There’s very little mention on it, and typing it doesn’t provide the usual prompt for the required parameters.
The reason for this is that it exists for backwards compatibility with Excel’s old competitor, Lotus 1,2,3 – but is a useful function in its own right.
SWITCH is another new function to Office 365 versions of Excel 2016, though SWITCH will be familiar to many with a coding background – and the premise is the same.
SWITCH gives us a new way to test a single value for multiple conditions, and return a different value depending on the result
So perhaps I have a series of country codes, but I want to convert it to a country. Naturally, I could use a VLOOKUP. Alternatively, a series of Nested IF functions would work, but would be very messy and difficult to read. SWITCH however offers another approach.
We provide an expression to test (which can also be a formula itself), and then provide a series of values we want to test it for. We can also give a default result at the end if we so wish
If you want more flexibility, but without nested IF functions, look into the new IFS function as well.
LARGE & SMALL
=LARGE([Range],[nth_Value]) / =SMALL([Range],[nth_Value])
This pair of functions behave in a similar fashion to MIN and MAX to return the lowest and highest value in a list.
However, where MIN & MAX can only return the highest/lowest value, LARGE and SMALL allow you to set the nth Value to return – eg, the 1st, 2nd or 3rd value
I can think of several occasions where I’ve written overly complicate conditional MAX Array Formulas to return a second highest value – when LARGE would have done it for me with ease.
OFFSET is yet another example of a function where there’s a lot more to it than meets the eye.
In its simplest form, it returns the value of a cell a certain number of Rows and Columns from the Start_Reference. When combined with a MATCH, it allows for some useful LOOKUP style formulas
However, there are two additional (optional) parameters; Height and Width
When you use the Height and Width parameters, you will first OFFSET your cell reference, but it then extends the range referenced by the formula.
This allows us to create dynamic-sized ranges. Consider where we want to show the total sales, but we always add the next day below, we might want a formula that always picks up the full range.
Couple this is with SUBTOTAL and COUNT functions to create powerful dynamic ranges
The same approach can be used to create dynamic named ranges.
With the introduction of Excel Named Tables, the need to create Dynamic Ranges and Dynamic Named Ranges has reduced somewhat, but is still a powerful function to have in your arsenal
Convert is a really simple function, but incredibly useful. It can convert a given value between a huge range of units
The list of units is large – The official documentation is your best guide here.
One thing to watch out for though…
You’ll notice that whilst “m” is listed for Meters, Kilometers is not listed. Similarly, “g” is listed, but not Kilograms, Bytes but not Kilobytes, Megabytes, Gigabytes etc.
Instead, you append a ‘multiplier’ prefix to your unit – eg, “km” for Kilometers, “Mbyte” for Megabyte. (Note, these prefixes are case-sensitive!)
Note that in the case of Information units (bytes), it uses the decimal multiplications (so 1 gigabyte – 1,000 megabytes) rather than the Binary multiplication (where 1 GB = 1,024 MB)
A pretty simple function, it allows you to replace all instances of a string of characters (or a single character) with something else.
Using the optional [Instance] parameter, you can change just a single instance of any matching characters.
One particularly useful usage is to insert special characters, such as carriage returns (char(10))
=SUMPRODUCT([Array 1]*[Array 2],...)
Arguably one of the most powerful functions in Excel. At one point, it really was a must to master this function, but with the introduction of extra conditional aggregation functions in more recent versions of excel, it’s taken a bit of a back seat.
But it’s still a hugely versatile function, and well worth getting to grips with it.
At its core, it multiplies arrays of data together.
But it can be used for complex tasks – such as conditional summing (with more or less as many conditions as you like), but can also be used to create conditional weighted averaging – one of the most popular articles on this site.
INDEX & MATCH
Index & match is a powerful pairing of functions.
For years, excel users have used to simulate a VLOOKUP where the value you want to return it to the LEFT of the lookup value, rather than the RIGHT, which is a requirement of VLOOKUP
With the introduction of Excel data Tables, the INDEX/MATCH combination becomes even more useful, as you can use the Data Table Names in the parameters.
There is growing support for Microsoft to create a ‘new’ LOOKUP function which takes the flexibility of INDEX / MATCH but as a simpler, purpose made function, but for now, we can use this power-couple.