Microsoft Excel continues to be one of the most powerful tools available for data analysis, financial modeling, and automation for professionals. There are various basic functions like SUM, AVERAGE, ADD, etc., which are most commonly used.
However, Excel formulas will take your data working productivity and precision with numbers to a whole new level. In this guide, we’re going to dive into some powerful Excel formulas and how you can effectively use them.
INDEX MATCH: A Better Option To VLOOKUP
INDEX MATCH approach is more flexible and efficient compared to VLOOKUP. Instead of searching for static data like VLOOKUP requires INDEX MATCH lets you search dynamically. Let’s discuss the formula:
Formula Syntax:
=INDEX(range, MATCH(lookup_value, lookup_range, 0))
For Example: Querying the salary of an employee by their ID:
=INDEX(B2:B10, MATCH(E2, A2:A10, 0))
This finds the ID in column A and returns the associated value from column B.
XLOOKUP: The Most Powerful Lookup Function
XLOOKUP is a new function in Excel to replace both VLOOKUP and HLOOKUP and comes with greater functionality while removing common constraints.
Formula Syntax:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Example: given the name of a product find its price:
=XLOOKUP(“Product A”, A2:A10, B2:B10, “Not Found”)
This finds “Product A” in column A and returns its price from column B.
3. TEXTJOIN: Combine Multiple Text Values
TEXTJOIN is also quite useful for merging multiple text strings with a specified delimiter.
Formula Syntax:
=TEXTJOIN(delimiter, ignore_empty, text1, text2, …)
Example: Combining first and last names with a space:
=TEXTJOIN(” “, TRUE, A2, B2)
This merges values in A2 and B2, ignoring empty cells.
4. ARRAYFORMULA: Automating Calculations in Google Sheets
If you use Google Sheets, ARRAYFORMULA allows calculations across multiple rows without dragging formulas.
Formula Syntax:
=ARRAYFORMULA(expression)
Example: Multiplying values in column A by column B automatically:
=ARRAYFORMULA(A2:A10 * B2:B10)
This applies multiplication to the entire range without manual copying.
5. SEQUENCE: Generate Number Series Dynamically
The SEQUENCE function creates an array of sequential numbers, perfect for numbering rows dynamically.
Formula Syntax:
=SEQUENCE(rows, [columns], [start], [step])
Example: Creating a sequence from 1 to 10:
=SEQUENCE(10,1,1,1)
This generates numbers 1 through 10 in a column.
6. FILTER: Extract Data Based on Conditions
FILTER helps extract specific data based on criteria, making it a dynamic alternative to manual filtering.
Formula Syntax:
=FILTER(range, condition1, [condition2], …)
Example: Extracting sales above $500:
=FILTER(A2:B10, B2:B10>500)
This pulls only rows where the sales value is greater than 500.
7. LET: Define Variables in Formulas for Efficiency
LET allows you to define variables within a formula, making complex calculations easier to read and manage.
Formula Syntax:
=LET(name1, value1, calculation)
Example: Calculating profit with defined variables:
=LET(cost, A2, price, B2, price – cost)
This assigns cost and price variables before performing the calculation.
8. SHOW FORMULAS: Display All Formulas in a Worksheet
The SHOW FORMULAS feature allows you to display all formulas in a worksheet instead of their results. This is very useful for auditing and troubleshooting complex spreadsheets.
Shortcut:
Ctrl + ` (grave accent key)
Alternatively, you can enable it through:
- Go to the Formulas tab in the Excel ribbon.
- Click on Show Formulas under the Formula Auditing group.
This instantly reveals all formulas in your sheet, making it easier to review calculations and debug errors.
Conclusion
Excel, which remains the same efficient for data management, analysis, and reporting. Now, it provides users with new and Advanced formulas to use unique features and functionalities available in Excel to annotate data.
Professionals can handle complex datasets with ease whether they are using INDEX-MATCH, XLOOKUP, or FILTER, by learning these formulas’ working. This enables Excel users to save time, reduce errors, and sharpen new insights from their data to get results with ease.
