Top Excel Formulas Every Data Analyst Must Know (2025 Updated)

Microsoft Excel is a powerful tool used by millions worldwide for data analysis, financial modeling, and much more. Mastering Excel formulas can significantly enhance your productivity and efficiency. This article will guide you through some of the top Microsoft Excel formulas that you should learn to maximize your potential with this versatile software.

Table of Contents

Basic but Powerful Formulas

XLOOKUP (The Modern VLOOKUP Replacement)

A1: Employee ID

B1: Employee Name

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

Function =XLOOKUP(D2,A:A,B:B)

IFS (Multiple Conditions)

Syntax: =IFS(logical_test1, value_if_true1, logical_test2, value_if_true2, ...)

Real-life Example:

Intermediate Level Formulas

FILTER (Dynamic Data Filtering)

Syntax: =FILTER(array, include, [if_empty])

In the above example using this > =FILTER(Table1,(Table1[Category]=”Clothing”)*(Table1[Sales]>5000))

I have filtered Clothing category with above 5000 sales, and it results in 2 matching entries.

UNIQUE (Remove Duplicates)

Syntax: =UNIQUE(array, [by_col], [exactly_once])

Real-life Example:

Advanced Analytics Formulas

SEQUENCE (Generate Arrays Dynamically)

Syntax: =SEQUENCE(rows, [columns], [start], [step])
Use Case: SEQUENCE is perfect for generating dynamic arrays of numbers for analysis or simulations.
Example: =SEQUENCE(5, 1, 1, 2) generates a column of 5 numbers starting at 1 and increasing by 2 (1, 3, 5, 7, 9).

LET (Simplify Complex Formulas)

Syntax: =LET(name1, value1, [name2], [value2], ..., calculation)
Use Case: LET allows you to define variables within a formula, making complex calculations easier to read and maintain.
Example:excelCopy=LET( sales, A1:A10, tax_rate, 0.1, total_tax, SUM(sales) * tax_rate, total_tax ) This calculates the total tax for a range of sales values.

LAMBDA (Custom Functions)

Syntax: =LAMBDA(parameter1, parameter2, ..., calculation)(input1, input2, ...)
Use Case: LAMBDA lets you create reusable custom functions directly in Excel.
Example:
Define a custom function for calculating compound interest:excelCopy=LAMBDA(principal, rate, periods, principal * (1 + rate)^periods)(1000, 0.05, 10) This calculates the future value of $1,000 at a 5% annual interest rate over 10 years.

New Features in 2024

Dynamic Array Enhancements

Microsoft continues to improve dynamic arrays, enabling more intuitive and powerful formulas like TEXTSPLIT and TEXTJOIN.
Example: Use TEXTSPLIT to break a string into an array:excelCopy=TEXTSPLIT("Excel,Formulas,2024", ",") This splits the string into “Excel”, “Formulas”, and “2024”.

IMAGE Function

Syntax: =IMAGE(source, [alt_text], [sizing], [height], [width])
Use Case: Insert images directly into cells, making dashboards and reports more visually appealing.
Example:excelCopy=IMAGE("https://example.com/logo.png", "Company Logo")

Expanded AI Integration

Excel’s AI now suggests formulas, automates repetitive tasks, and integrates with Power BI more seamlessly.

Real-world Applications

Financial Modeling

Use XLOOKUP, LET, and LAMBDA to build dynamic and scalable financial models.
Example: Calculate loan repayments using PMT combined with LET for cleaner formulas.

Data Cleaning

Combine UNIQUE, FILTER, and TEXTSPLIT to clean and organize large datasets.
Example: Remove duplicates and filter rows matching certain criteria in one step.

Sales Dashboards

Leverage SEQUENCE, IMAGE, and SPARKLINE to create visually appealing and interactive sales dashboards.

Tips and Best Practices

Master Dynamic Arrays: Learn how to use dynamic array formulas like FILTER, SORT, and UNIQUE to simplify your workflow.
Use Named Ranges: Use named ranges or the LET function to make formulas easier to read.
Error Handling: Always include error handling with IFERROR or IFNA to make your formulas robust.
Document Your Work: Add comments to complex formulas or use ALT+ENTER to break them into readable lines.
Stay Updated: Regularly explore new Excel features and updates to stay ahead.

Microsoft Excel Shortcuts Keys:

Shortcut	Action	Description
Ctrl + C	Copy	Copies the selected cells to the clipboard.
Ctrl + V	Paste	Pastes the content from the clipboard into the selected cells.
Ctrl + X	Cut	Cuts the selected cells and moves them to the clipboard.
Ctrl + Z	Undo	Reverses the last action.
Ctrl + Y	Redo	Repeats the last action.
Ctrl + S	Save	Saves the current workbook.
Ctrl + P	Print	Opens the print dialog box.
Ctrl + F	Find	Opens the Find dialog box to search for content in the workbook.
Ctrl + H	Replace	Opens the Replace dialog box to find and replace content.
Ctrl + A	Select All	Selects the entire worksheet or all data in the current range.
Ctrl + T	Create Table	Converts the selected range into a table.
Ctrl + Arrow Key	Navigate to Edge	Moves to the edge of the current data region (row or column).
Ctrl + Spacebar	Select Column	Selects the entire column of the active cell.
Shift + Spacebar	Select Row	Selects the entire row of the active cell.
Ctrl + Shift + L	Apply/Remove Filter	Toggles filters on and off for the selected range.
Ctrl + Shift + “+”	Insert Row/Column	Inserts a new row or column.
Ctrl + “-“	Delete Row/Column	Deletes the selected row or column.
Ctrl + 1	Format Cells	Opens the Format Cells dialog box.
Ctrl + Shift + $	Apply Currency Format	Formats the selected cells as currency.
Ctrl + Shift + %	Apply Percentage Format	Formats the selected cells as percentages.
Alt + =	AutoSum	Automatically sums up adjacent cells.
Ctrl + ;	Insert Current Date	Inserts the current date into the selected cell.
Ctrl + Shift + :	Insert Current Time	Inserts the current time into the selected cell.
Ctrl + `	Toggle Formula View	Toggles between showing formulas and their results in the worksheet.
F2	Edit Cell	Activates editing mode for the selected cell.
F4	Repeat Last Action/Toggle Absolute References	Repeats the last action or toggles between absolute and relative references.
Ctrl + Alt + V	Paste Special	Opens the Paste Special dialog box.
Ctrl + Shift + Arrow Key	Extend Selection	Extends the selection to the edge of the current data region.
Ctrl + Tab	Switch Between Workbooks	Switches between open Excel workbooks.
Ctrl + Page Up/Down	Switch Between Worksheets	Moves to the previous or next worksheet in the workbook.
Ctrl + Shift + U	Expand/Collapse Formula Bar	Toggles the size of the formula bar.

Conclusion

Mastering Microsoft Excel formulas is a game-changer for anyone working with data. From basic to advanced levels, these formulas empower you to analyze, visualize, and manage data efficiently. By incorporating new features and best practices, you can take your Excel skills to the next level and unlock its full potential. Whether you’re a beginner or an expert, there’s always something new to learn in Excel!