Learn Advance Excel Formulas & Functions

In the world of business and data analysis, proficiency in Microsoft Excel is a valuable skill. While many individuals are familiar with basic Excel functions, unlocking the full potential of this powerful tool requires a deep understanding

advance excel formulas 

Read further to explore some of the most essential advanced Excel functions, providing insights and practical examples to help you elevate your Excel expertise through an Advanced Excel course in Gurgaon.

VLOOKUP and HLOOKUP

One of the fundamental challenges in Excel is efficiently finding and retrieving specific information from a dataset. The `VLOOKUP` (Vertical Lookup) and `HLOOKUP` (Horizontal Lookup) functions are indispensable tools for this purpose.

VLOOKUP

The `VLOOKUP` function searches for a value in the first column of a table and returns a corresponding value in the same row from another column. Its syntax is as follows:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• `lookup_value`: The value to search for in the first column of the table.
• `table_array`: The range of cells that contains the data.
• `col_index_num`: The column number in the table from which to retrieve the value.
• `[range_lookup]`: An optional argument specifying whether to find an exact match (FALSE) or an approximate match (TRUE).

Let’s look at an  example. Assume you have a table of employee information, and you want to find the salary of an employee with a specific ID.

=VLOOKUP(A2, B2:D10, 3, FALSE)

In this formula:

• `A2` is the lookup value (employee ID).
• `B2:D10` is the table array containing the data.
• `3` indicates the column index number (salary is in the third column).
• `FALSE` ensures an exact match.

HLOOKUP

Similar to `VLOOKUP`, the `HLOOKUP` function searches for a value in the first row of a table and returns a corresponding value in the same column from another row. Its syntax is analogous to `VLOOKUP`:

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

The usage of `HLOOKUP` is the same as `VLOOKUP`, with the primary difference lying in the orientation of the table.

INDEX and MATCH

While `VLOOKUP` and `HLOOKUP` are powerful, they have limitations, especially when dealing with large datasets. The combination of `INDEX` and `MATCH` functions provides a more flexible and efficient alternative.

INDEX

The `INDEX` function returns the value of a cell in a specified row and column of a given range. Its syntax is as follows:

=INDEX(array, row_num, [column_num])

• `array`: The range of cells containing the data.
• `row_num`: The row number in the array from which to retrieve the value.
• `[column_num]`: An optional argument specifying the column number in the array.

MATCH

The `MATCH` function searches for a specified value in a range and returns the relative position of that item. Its syntax is as follows:

=MATCH(lookup_value, lookup_array, [match_type])

• `lookup_value`: The value to search for.
• `lookup_array`: The range of cells containing possible matches.
• `[match_type]`: An optional argument specifying the type of match (1 for less than, 0 for an exact match, -1 for greater than).

Now, let’s see how `INDEX` and `MATCH` work together. Assume you have a table of product information, and you want to find the price of a product with a specific code.

=INDEX(B2:B10, MATCH(E2, A2:A10, 0))

In this formula:

• `B2:B10` is the array containing the prices.
• `MATCH(E2, A2:A10, 0)` finds the position of the product code in the array of product codes (exact match).
• `INDEX` then retrieves the corresponding price from the specified position.

This dynamic duo provides a robust solution for looking up values in a table without the constraints of fixed column indices.

IFERROR

In complex Excel models, errors are inevitable. The `IFERROR` function allows you to manage errors more gracefully by providing a value or expression to use in case of an error.

=IFERROR(value, value_if_error)

• `value`: The expression or value to evaluate.
• `value_if_error`: The value to return if an error occurs.

Let’s consider an example. You have a formula that divides revenue by the number of units sold.

=C2/D2

However, if the number of units sold is zero, this formula will result in a division by zero error. To handle this situation more gracefully, you can use `IFERROR`:

=IFERROR(C2/D2, “N/A”)

Now, if an error occurs (e.g., division by zero), the formula will return “N/A” instead, making your spreadsheet more robust and user-friendly.

SUMIFS and COUNTIFS

The `SUMIFS` and `COUNTIFS` functions are advanced versions of `SUMIF` and `COUNTIF`, allowing you to apply multiple criteria when summing or counting data.

SUMIFS

The `SUMIFS` function adds up values based on multiple criteria. Its syntax is as follows:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

• `sum_range`: The range of cells to sum.
• `criteria_range1`, `criteria_range2`, …: The ranges to check against the corresponding criteria.
• `criteria1`, `criteria2`, …: The criteria to meet.

Imagine you have a sales dataset with columns for product, region, and sales. To find the total sales for a specific product in a particular region, you can use `SUMIFS`:

=SUMIFS(C2:C100, A2:A100, “Product_A”, B2:B100, “North”)

This formula sums the values in column C where the product is “Product_A” and the region is “North.”

COUNTIFS

Similarly, the `COUNTIFS` function counts the number of cells that meet multiple criteria. Its syntax is analogous to `SUMIFS`:

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)

Let’s say you want to count the number of orders for a specific product in a particular region:

=COUNTIFS(A2:A100, “Product_B”, B2:B100, “South”)

This formula counts the number of occurrences where the product is “Product_B” and the region is “South.”

TEXT and CONCATENATE

Working with text data often involves manipulating strings, extracting information, or combining text from different cells. The `TEXT` and `CONCATENATE` functions are invaluable in such scenarios.

TEXT

The `TEXT` function converts a value to text in a specific number format. Its syntax is as follows:

=TEXT(value, format_text)

• `value`: The numeric value to convert to text.
• `format_text`: The format to apply to the value.

For instance, suppose you have a date in cell A2 and want to display it in the format “YYYY-MM-DD.”

=TEXT(A2, “YYYY-MM-DD”)

This formula converts the date in cell A2 to the specified format.

CONCATENATE (or &) 

The `CONCATENATE` function combines multiple text strings into one. You can also use the “&” operator for the same purpose.

=CONCATENATE(text1, [text2], …)

or

=text1 & [text2] & …

Consider you have first and last names in separate columns (B and C), and you want to create a full name in column D.

=CONCATENATE(B2, ” “, C2)

or

=B2 & ” ” & C2

Both formulas achieve the same result, combining the first and last names with a space in between.

ARRAY Formulas

Array formulas are a powerful feature in Excel that allows you to perform calculations on multiple items in one or more ranges of data. These advance excel formulas can significantly enhance your ability to analyze and manipulate data.

SUMPRODUCT

The `SUMPRODUCT` function multiplies corresponding components in the given arrays and returns the sum of those products. This is particularly useful for calculating weighted sums or performing operations on multiple arrays.

=SUMPRODUCT(array1, array2, …)

Imagine you have a dataset with sales and the corresponding quantity sold, and you want to calculate the total revenue.

=SUMPRODUCT(B2:B100, C2:C100)

This formula multiplies the quantity (column C) by the price (column B) for each row and then sums up the results, giving you the total revenue.

TRANSPOSE

The `TRANSPOSE` function flips the rows and columns of a given range.

=TRANSPOSE(A1:B2)

If your data is in cells A1:B2, this formula would transpose it, swapping rows for columns. Array formulas can be complex, but they offer unparalleled flexibility in data analysis.

PIVOT TABLES

Pivot tables are a robust feature in Excel for summarizing, analyzing, and presenting data in a dynamic and interactive way. They allow you to reorganize and summarize selected columns and rows of data from a table to obtain desired insights. To create a pivot table:

• Select the range of cells containing your data.
• Go to the “Insert” tab and click on “PivotTable.”
• Choose where you want to place the pivot table (new worksheet or existing worksheet).
• Drag and drop fields into the “Rows” and “Values” areas to organize and summarize your data.

Pivot tables are especially useful when dealing with large datasets, as they allow you to quickly and easily analyze and visualize information without complex formulas.

DATE and TIME Functions

Excel offers a variety of functions to handle date and time data, making it easier to perform calculations and analysis involving temporal information.

NOW and TODAY

The `NOW` function returns the current date and time, while the `TODAY` function returns the current date.

=NOW()

=TODAY()

These functions are particularly useful when you need to timestamp entries or perform calculations based on the current date and time.

EOMONTH

The `EOMONTH` function returns the last day of the month before or after a specified number of months.

=EOMONTH(A1, 3)

This formula calculates the last day of the month that is three months after the date in cell A1.

NETWORKDAYS

The excel `NETWORKDAYS` function calculates the number of whole workdays between two dates, excluding weekends and specified holidays.

=NETWORKDAYS(A1, B1)

This formula calculates the number of workdays between the dates in cells A1 and B1. Understanding and utilizing these date and time functions can significantly enhance your ability to analyze temporal data efficiently.

OFFSET: Dynamic Range References

The `OFFSET` function is a powerful tool for creating dynamic range references in Excel. It returns a reference to a range that is offset from a starting cell or range of cells.

=OFFSET(reference, rows, cols, [height], [width])

• `reference`: The starting point for the offset.
• `rows`: The number of rows to offset from the reference.
• `cols`: The number of columns to offset from the reference.
• `[height]`: An optional argument specifying the height of the resulting range.
• `[width]`: An optional argument specifying the width of the resulting range.

Suppose you have a dataset with sales data, and you want to create a dynamic range that always includes the last 12 months of data. If your data is in columns A (date) and B (sales), you can use the `OFFSET` function as follows:

=OFFSET($B$1, COUNT($B:$B)-12, 0, 12, 1)

In this formula:

• `$B$1` is the reference point.
• `COUNT($B:$B)-12` calculates the number of rows to offset, ensuring the range includes the last 12 months.
• `0` offsets zero columns.
• `12` and `1` define the height and width of the resulting range.

This dynamic range will always adapt to include the last 12 months of sales data.

 XLOOKUP

Introduced in newer versions of Excel, `XLOOKUP` is a versatile and more powerful replacement for `VLOOKUP` and `HLOOKUP`. It can search a range, find the first match, and return a corresponding value.

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

• `lookup_value`: The value to search for.
• `lookup_array`: The range containing possible matches.
• `return_array`: The range containing values to return.
• `[if_not_found]`: An optional argument specifying the value to return if no match is found.
• `[match_mode]`: An optional argument specifying the type of match (exact match, less than, greater than).
• `[search_mode]`: An optional argument specifying the search mode (search first to last, last to first).

SUMPRODUCT with Multiple Conditions

Expanding on the excel `SUMPRODUCT` function, you can use it to perform calculations based on multiple conditions. This is especially useful when you want to sum values that meet specific criteria.

Let’s say you have a dataset with sales, and you want to calculate the total sales for a specific product in a particular region within a certain date range.

=SUMPRODUCT((A2:A100=”Product_A”)*(B2:B100=”North”)*(C2:C100>=DATE(2023,1,1))*(C2:C100<=DATE(2023,12,31)), D2:D100)

In this formula:

• `(A2:A100=”Product_A”)` checks if the product is “Product_A.”
• `(B2:B100=”North”)` checks if the region is “North.”
• `(C2:C100>=DATE(2023,1,1))` checks if the date is on or after January 1, 2023.
• `(C2:C100<=DATE(2023,12,31))` checks if the date is on or before December 31, 2023.
• `D2:D100` is the range of sales values.

This formula calculates the sum of sales for Product_A in the North region within the specified date range.

Final Verdict

Getting to know the advance excel formulas and functions  is crucial for professionals working with data. Whether you are a business analyst, financial analyst, or anyone dealing with large datasets, the excel tools discussed in this article can help you analyze and manipulate data more efficiently.

From lookup functions to text manipulation, error handling, and date-time calculations, Excel provides a diverse set of tools to empower users in their excel data analysis journey. Continuous practice and exploration of these functions in real-world scenarios will further solidify your Excel expertise and make you a more proficient data handler. So, dive into the world of advanced Excel functions, and unlock the full potential of this powerful tool!