Learning About Microsoft Excel
Those who are new to Microsoft Excel or don’t use it regularly but find themselves in the need of the application, tend to understand that they need to learn more to get up to speed with data analysis, charting, conditional formatting, Excel automation or anything else they require to do their work efficiently. They tend to go on Excel training courses, learn via Google, read books on the subject. By doing that they learn everything, regardless whether it’s new or not.
But those of us who use Excel on a daily basis, who know Excel inside out, tend to become complacent. We tend to think that there is nothing else to discover. And we stop checking Microsoft updates. Perhaps when a new version is released? But now with subscription arrangements which are becoming more and more common, we have updates rather then new versions. You have to check them regularly to be up to date.
Those of us who use Microsoft Power BI know that updates are important. This is how Microsoft positioned Power BI from the start. They release new updates every month and everyone who works with Power BI fanatically checks out those updates. And yet – we ignore them when it comes to Excel or any other application.
Here, we’ve outlined some of Excel’s key new functions. Our outlines below aim to give you some insight into them, what they’re used for, and how you might be able to benefit from them. Please note – these functions are available in Excel desktop application. There are additional new functions available for Excel online. We will discuss those in the next blog as well as other new features (not just functions).
New Excel Functions
CONCAT replaces CONCATENATE. It is advisable to start using this function, though CONCATENATE will remain for compatibility with older Excel versions. CONCAT is superior to its predecessor – it can deal with ranges. Imagine that you have three columns which represent a unique record identifier:
You need to combine the three to create a unique code. If you use CONCATENATE you need to refer to individual cells:
With CONCAT it would look much neater:
Both functions will produce the same result: XYZ12ABC. However, neither provides a delimiter option. You can of course insert delimiters using &:
=CONCAT(A1 & “-” & B1 & “-” & C1)
But in this case why would you use any function at all? To achieve the same result, you can simply type:
=A1 & “-” & B1 & “-” & C1
The TEXTJOIN function joins the text from multiple ranges and/or strings. It includes a delimiter you specify between each text value. If there is a blank cell within a range, you can choose to include or ignore it.
If we take the example above, you can combine all three cells using the delimiter “-“.
Second argument is for Ignore Empty cells. TRUE to ignore or FALSE to include.
We all know IF function: If something is TRUE do one thing, if not – do another. Where IF becomes tedious – is when you need to test multiple conditions. Then you have to nest one IF within another, and another and so on. IFS is brilliant for replacing nested IF. The arguments are simple and go in pairs: Condition, True value. So if I have grades: A – above 120, B – between 80 and 120, C – between 50 and 80. The traditional IF would look like that:
=IF(A1>120,”A” ,IF(A1>80,”B”, IF(A1>50,”C”,”D”)))
The new IFS:
=IFS(A1>120, “A”, A1>80,”B”, A1>50,”C”, A1<=50,”D”)
Certainly more elegant. But note – it does not have “Else” argument. Hence the last pair: A1<=50,”D”
Another great alternative to nested IF. Imagine we have Order Priority in column A. There are four options: Critical, High, Medium and Low. The first two require Urgent delivery, the rest – non-Urgent. SWITCH allows us to test values in column A and return desired result based on condition. The first Argument is an expression – in our case reference to column A. After that arguments go in pairs: Value, Result. There is an optional argument at the end (“un-paired”) for Else:
We could use SWITCH instead of IFS in previous section:
MAXIFS and MINIFS
The two new functions work exactly the same way as SUMIFS and COUNTIFS which were introduced to Excel years ago and should be familiar to everyday day Excel users.
How can XL Help?
XL Intelligence’s Microsoft Office courses, more specifically our Excel training courses are ideal if you want to get the full experience of learning and maximising your potential with Microsoft Excel. Our trainers bring thorough and extensive knowledge of all Microsoft Office products (including Power BI and Microsoft Access) but also the skills of how to effectively apply the knowledge in real-world situations that you’ll inevitably face.
Our trainers will give you the skills necessary to maximise Excel features, formulas and tools regarding business intelligence and data visualisation to give you greater control over your data. Our Advanced Excel course is ideal for anyone experienced in how to use Excel and who has a solid understanding of Excel features. For anyone wanting to learn how to automate Excel reports, or how to create a Macro in Excel, we hope you’ll consider one of our highly recommended advanced Microsoft Office training courses..
Speak to us today to see how we can help.
Did you know?
Some useful Excel functions can be used by simply remembering a few handy shortcut keys.
- Alt+F1 – Creates embedded chart of data in current range
- Shift+F2 – Adds/edits cell comments
- F3 – Paste Name dialog box
- Shift+F3 – Insert function dialog
- F4 – Repeats the last command
- F8 – Turns Extend mode on or off
You can find a full list of shortcut keys here