Advance Excel Formulas – Advance Excel Shortcuts

• INDEX-MATCH:

INDEX: Returns the value of a cell in a specified row and column of a given range.
MATCH: Searches for a value in a range and returns the relative position of that value.
Example: =INDEX(B2:B10, MATCH(“SearchValue”, A2:A10, 0)) – This formula will find “SearchValue” in the range A2:A10 and return the corresponding value from B2:B10.

• SUMIFS / COUNTIFS / AVERAGEIFS:

SUMIFS: Calculates the sum of values in a range that meet multiple criteria.
COUNTIFS: Counts the number of cells in a range that meet multiple criteria.
AVERAGEIFS: Calculates the average of values in a range that meet multiple criteria.
Example: =SUMIFS(C2:C10, A2:A10, “Criteria1”, B2:B10, “Criteria2”) – This formula will sum values in C2:C10 where corresponding values in A2:A10 meet “Criteria1” and values in B2:B10 meet “Criteria2”.

• PivotTable:

A Pivot Table is a dynamic summary tool that allows you to analyze and summarize data from a larger dataset.
Example: Create a PivotTable to analyze sales data, with rows representing product categories and columns representing months, and the values showing total sales.

• IFERROR:

Returns a value you specify if a formula results in an error, otherwise, returns the formula’s result.
Example: =IFERROR(A1/B1, “Error: Division by zero”) – This formula will perform A1/B1 but display “Error: Division by zero” if an error occurs.

• SUMPRODUCT:

Multiplies corresponding components in arrays/ranges and returns the sum of those products.
Example: =SUMPRODUCT(A1:A5, B1:B5) – This formula will multiply each element in A1:A5 with the corresponding element in B1:B5 and sum the results.

• TEXTJOIN:

Combines text from multiple cells into a single cell with specified delimiters and optional criteria.
Example: =TEXTJOIN(“, “, TRUE, A1:A3) – This formula will combine the values in cells A1:A3, separated by commas.

• DATE / TIME Functions:

Excel offers various functions for working with dates and times, such as DATE, TIME, NOW, TODAY, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, etc.
VBA (Visual Basic for Applications):

VBA is a programming language integrated into Excel for creating custom macros and automating tasks.

• CHOOSE:

Returns a value from a list based on a specified index number.
Example: =CHOOSE(3, “Apple”, “Banana”, “Cherry”, “Date”) – This formula will return “Cherry” because 3 is the index chosen.

• ARRAY Formulas:

Array formulas perform calculations on arrays of data. They are created by entering a formula and then confirming it with Ctrl+Shift+Enter.
Example: {=SUM(A1:A10 * B1:B10)} – This array formula calculates the sum of products of corresponding elements in A1:A10 and B1:B10.