Part 2 - Top Excel functions and formulas in 2024

@TheDataGlue

This blog covers advanced Excel, including powerful functions like INDEX-MATCH, SUMIFS, and dynamic arrays. It also explores data analysis tools, text manipulation techniques, and error handling methods. You'll learn about new features in Excel 2024 and how to improve your Excel skills with tips like using keyboard shortcuts.

Bonus tip: don't miss at the end  Excel shortcuts for increased efficiency!

1. INDEX-MATCH

Syntax: `=INDEX(range, MATCH(lookup_value, lookup_range, [match_type]))

Explanation: This powerful combination allows you to look up values in a table and return a specific result. It's more flexible than the traditional VLOOKUP.

Example: To find the price of a product from a table based on its product code and store location: =INDEX(PriceRange, MATCH(ProductCode, ProductCodeRange, 0), MATCH(StoreLocation, StoreLocationRange, 0))

2. SUMIFS and COUNTIFS

Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...])

Explanation: These functions let you calculate sums or counts based on multiple conditions.

Example: To calculate the total sales for a specific product category in a specific region: =SUMIFS(SalesRange, CategoryRange, "Electronics", RegionRange, "West")

Data Analysis Formulas

1. Dynamic Arrays

• FILTER: =FILTER(array, include, [if_empty])
• SORT: =SORT(array, [sort_index], [sort_order])
• UNIQUE: =UNIQUE(array)

2. Statistical Analysis

• The AGGREGATE function in Excel is a powerful tool for performing calculations while ignoring specific values, such as errors or hidden rows. It's like a supercharged version of other functions like SUM, AVERAGE, COUNT, and more. 
• You can use it to calculate averages, sums, maximums, minimums, and other statistics, all while controlling which values are included or excluded. This makes it especially useful for cleaning up messy data and getting accurate results. 
• AGGREGATE: =AGGREGATE(function_num, options, range, [criteria_range1, criteria1, ...])

• FORECAST.ETS is an Excel function that predicts future values based on historical data. It's like a crystal ball for your data, using a complex method called Exponential Smoothing to account for trends and seasonal patterns.FORECAST.ETS: =FORECAST.ETS(x, known_y_values, known_x_values) 

By inputting your historical data, you can forecast future values, helping you make informed decisions.

Text Manipulation 

1. TEXTBEFORE and TEXTAFTER

Syntax: =TEXTBEFORE(text, delimiter)

Explanation: Extracts text before a specified delimiter.

Example: To extract the first name from a full name: =TEXTBEFORE(FullName, " ")

TEXTAFTER is an Excel function that extracts text from a string after a specified delimiter

TextAfter(text, delimiter, [instance_num], [match_mode])

Example:

• Text: "Product_A123"
• Delimiter: "_"
• Result: "A123"

It's useful for cleaning up data and extracting specific parts of text strings.

2. TEXTSPLIT

Syntax: `=TEXTSPLIT(text, delimiter, [ignore_empty])`

Explanation: Splits text into multiple cells based on a delimiter.

Example: To split a comma-separated list of names into individual cells: =TEXTSPLIT(Names, ",")

Lookup and Reference Champions

1. XLOOKUP

Syntax: `=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])`

Explanation: A versatile lookup function that can handle exact and approximate matches, as well as error handling.

2. XMATCH

Syntax: `=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])`

Explanation: Finds the position of a value within a range, similar to the MATCH function but with more flexibility.

Error Handling and Validation

1. IFERROR

Syntax: =IFERROR(value, value_if_error)

Explanation: Returns a specified value if a formula results in an error.

2. Data Validation

Here's how to navigate to Data Validation in Excel:

Excel WB->Data Tab->Data tools group ->Data validation 

1. Open your Excel workbook.
2. Click on the "Data" tab. This is usually located at the top of your screen, next to tabs like "Home," "Insert," and "Formulas."
3.  Look for the "Data Tools" group. It's a section on the Data tab with various tools.
4. Click on the "Data Validation" button. This button is usually located within the "Data Tools" group.

Uses : 

1. Once you click on the "Data Validation" button, a dialog box will appear where you can set up rules for your data, such as:
2. Data Type: Limiting input to numbers, text, dates, or specific values.
3. Range: Restricting values to a certain range (e.g., between 0 and 100).
4. List: Creating a dropdown list of allowed values.
5. Custom Formula: Defining more complex validation rules.

By using Data Validation, you can ensure that your data is accurate and consistent.

New Excel 2024 Features

1. IMAGE Function

Syntax: =IMAGE(image_url)

Explanation: Inserts an image from a URL into a cell.

2. Python Integration

Excel Allows you to use Python code within Excel to perform complex calculations and data analysis.

Tips for Formula Mastery

• Formula to autocomplete
• Keyboard Shortcuts click the Link to get the list of evey keyboard shortcuts in Excel 
• Named Ranges

Remember, the key to mastering advanced Excel formulas is practice. The more you use these functions, the more comfortable you'll become with them. If you are interested into learn the excel shortcuts then click the Link and get the notes.