Microsoft Excel is a powerhouse tool for data analysis, financial modeling, and day-to-day office tasks. In 2019, Microsoft introduced some exciting new features and formulas that further empowered users to streamline their work and make data-driven decisions. In this article, we'll explore some of the most notable Excel formulas introduced in 2019 and how they can enhance your spreadsheet skills.
CONCAT and TEXTJOIN
Concatenating text has always been a common task in Excel, and in 2019, Microsoft introduced two new functions, CONCAT and TEXTJOIN, to simplify this process. CONCAT allows you to combine text from different cells into one, making it easier to create custom labels or descriptions. TEXTJOIN takes this a step further by allowing you to specify a delimiter, such as a comma or space, to separate the text.
Example:
CONCAT: =CONCAT(A1, ” – “, B1) combines the contents of cells A1 and B1 with a hyphen in between.
TEXTJOIN: =TEXTJOIN(“, “, TRUE, A1:A3) combines the values in cells A1 to A3 with a comma and space as the delimiter.
IFS and SWITCH
The IFS and SWITCH functions introduced in 2019 help simplify complex logical tests and make your formulas more readable. IFS allows you to evaluate multiple conditions and return the result for the first true condition. SWITCH, on the other hand, is particularly handy when you have multiple options and want to select one based on a given value.
Example:
IFS: =IFS(A1>90, “A”, A1>80, “B”, A1>70, “C”, TRUE, “D”) assigns grades based on the value in cell A1.
SWITCH: =SWITCH(A1, “Apple”, 1, “Banana”, 2, “Cherry”, 3) returns a numeric code for the fruit in cell A1.
XLOOKUP
XLOOKUP simplifies the process of searching for and retrieving data from a table or range. It can perform both horizontal and vertical lookups and handle multiple criteria elegantly. With its ability to return arrays, XLOOKUP is a powerful replacement for VLOOKUP and HLOOKUP.
Example:
=XLOOKUP(“Apples”, A1:A5, B1:B5) searches for “Apples” in column A and returns the corresponding value from column B.
UNIQUE and FILTER
The UNIQUE and FILTER functions allow you to extract unique values from a range and filter data based on specific criteria without the need for complex array formulas. This is particularly useful when dealing with large datasets.
Example:
UNIQUE: =UNIQUE(A1:A10) returns a list of unique values from cells A1 to A10.
FILTER: =FILTER(A1:B10, B1:B10 > 50) filters the data in range A1:B10 to include only rows where the value in column B is greater than 50.
SEQUENCE
The SEQUENCE function simplifies the task of creating sequences of numbers or dates. You can generate a series of values with specific step sizes, starting points, and lengths, making it useful for generating row or column headers.
Example:
=SEQUENCE(5, 1, 10, 2) creates a sequence of 5 numbers starting from 10 with a step size of 2.
Excel's new formulas in 2019 have expanded the capabilities of this indispensable tool. Whether you're a financial analyst, a data scientist, or a casual user, these formulas can help you work more efficiently and effectively with your data. By incorporating CONCAT, TEXTJOIN, IFS, SWITCH, XLOOKUP, UNIQUE, FILTER, and SEQUENCE into your spreadsheet skills repertoire, you can take your Excel proficiency to the next level and unlock new possibilities for data analysis and manipulation.
For More Info:-
Advanced Excel Course Online Southern Africa
