Discover the key Excel functions for spreadsheet modellers
Microsoft has been busy releasing lots of new and powerful functionality into Excel recently. The rate of change is pretty mind blowing. It's very easy to be distracted and overwhelmed by all this new shiny stuff.
For most modellers however it is much more important to focus on your core skills and ensure that you master them.
In this series of articles I am going to focus on four key areas that all modellers need to master before they move onto Excel's more advanced functionality:
- The Excel functions that every modeller needs to master
- Using Excel Tables to transform the way you model
- What's so great about Pivot Tables anyway?
- Why you should be modelling in line with a Best Practice Standard
Let's make a start with the key functions:
Building reports
A core part of any modeller's role is turning data into insightful information. Invariably this means creating charts and tables from a dataset. The go-to function here is SUMIFS.
The SUMIFS function returns the total value for rows in a dataset that meet the specified criteria.
Mapping data
In modelling we regularly need to add more information to our main dataset by looking up information from a reference (or mapping / dimension table). Here we turn to the XLOOKUP function which "maps" data from one table into another based on a unique reference.
XLOOKUP is a much improved version of existing lookup methods such as VLOOKUP, HLOOKUP and INDEX MATCH.
Working with dates
As modellers we often find ourselves needing to transform dates within our Excel models. A common requirement is to turn a date into the last day of the month so that we can use that date for reporting. The EOMONTH function does exactly that:
Working with Text
Transforming text is another key skill for modellers. We regularly need to tidy text, extract elements of text or combine text together.
The key functions to learn to manipulate text are TEXTSPLIT and TEXTJOIN:
Note that TEXTSPLIT is a dynamic array function as it "spills" the results over multiple cells rather than just the one cell.
IF Statements
Last, and certainly not least is the humble, yet powerful, IF function.
The IF function returns a specified value based on the result of a logical (True/False) test.
For example if we would like to check if the value in A1 is the equal to 1 and if its is equal to 1 then we would like to return "Yes", if it isn't then we would like to return "No". The formula would be:
=IF(A1 = 1, "Yes", "No")
The Excel Fundamentals Bootcamp
If you would like to learn more about these fundamental Excel skills then join me at my Excel Fundamentals Bootcamp.
Over five two hour sessions I will cover all of these core modelling skills:
- Core Excel functions
- Excel Tables
- Pivot Tables and Charts
- Best Practice Modelling
- Handy tips and tricks
Find our more about my Excel Fundmanetals Bootcamp here.
