Excel is an essential tool in the modern workplace, with applications across various industries and job roles. Mastering Excel formulas can significantly improve your productivity and efficiency at work.

This guide will walk you through the most important Excel formulas and provide practical examples to help you get the most out of this powerful tool. Let's dive in!

Basic Excel Formulas

Before we explore advanced Excel formulas, it's crucial to understand the basics. We'll start by covering the fundamental arithmetic operations and basic functions you frequently use in Excel.

Arithmetic Operations

• Addition: Use the "+" operator to add values.

A1 + A2

• Subtraction: Use the "-" operator to subtract values.

A1 - A2

• Multiplication: Use the "*" operator to multiply values.

A1 * A2

• Division: Use the "/" operator to divide values.

A1 / A2

Basic Functions

• SUM: Adds up a range of values.

SUM(A1:A5)

• AVERAGE: Calculates the average of a range of values.

AVERAGE(A1:A5)

• MAX: Returns the highest value from a range.

MAX(A1:A5)

• MIN: Returns the lowest value from a range.

MIN(A1:A5)

• COUNT: Counts the number of cells containing numbers in a range.

COUNT(A1:A5)

Text Manipulation Formulas

Working with text is a common task in Excel. Let's look at the essential formulas for text manipulation, allowing you to concatenate, extract, and modify text data in your spreadsheets.

CONCATENATE

• CONCATENATE: Combines two or more text strings into one.

CONCATENATE("Hello, ", "World!")

LEFT, RIGHT, and MID

• LEFT: Extracts a specified number of characters from the left side of a text string.

LEFT("Excel", 2) returns "Ex"

• RIGHT: Extracts a specified number of characters from the right side of a text string.

RIGHT("Excel", 2) returns "el"

• MID: Extracts a specified number of characters from a text string, starting at a specific position.

MID("Excel", 2, 2) returns "xc"

TRIM

• TRIM: Removes leading, trailing, and extra spaces within a text string.

TRIM(" Excel ") returns "Excel"

UPPER, LOWER, and PROPER

• UPPER: Converts a text string to uppercase.

UPPER("Excel") returns "EXCEL"

• LOWER: Converts a text string to lowercase.

LOWER("Excel") returns "excel"

• PROPER: Converts a text string to proper case, capitalizing the first letter of each word.

PROPER("excel formulas") returns "Excel Formulas"

SUBSTITUTE and REPLACE

• SUBSTITUTE: Replaces all occurrences of a specified text string with another text string.

SUBSTITUTE("Excel 2019", "2019", "2023") returns "Excel 2023"

• REPLACE: Replaces a specified number of characters in a text string with another text string, starting at a specific position.

REPLACE("Excel 2019", 6, 4, "2023") returns "Excel 2023"

Date and Time Formulas

Managing dates and times is a common task in Excel. Here are the date and time formulas to help you work more efficiently with time-sensitive data.

TODAY and NOW

• TODAY: Returns the current date.

TODAY()

• NOW: Returns the current date and time.

NOW()

DATE and TIME

• DATE: Returns a date based on the specified year, month, and day.

DATE(2023, 3, 24)

• TIME: Returns a time based on the specified hour, minute, and second.

TIME(12, 30, 0)

DATEDIF

• DATEDIF: Calculates the difference between two dates in days, months, or years.

DATEDIF(A1, A2, "d")

EDATE and EOMONTH

• EDATE: Returns a date that is a specified number of months before or after a given date.

EDATE(A1, 3)

• EOMONTH: Returns the last day of the month that is a specified number of months before or after a given date.

EOMONTH(A1, 3)

DAY, MONTH, and YEAR

• DAY: Returns the day of the month for a given date.

DAY(A1)

• MONTH: Returns the month for a given date.

MONTH(A1)

• YEAR: Returns the year for a given date.

YEAR(A1)

WEEKNUM and WEEKDAY

• WEEKNUM: Returns the week number for a given date.

WEEKNUM(A1)

• WEEKDAY: Returns the day of the week for a given date.

WEEKDAY(A1)

Logical Formulas

Logical formulas are essential for making decisions based on certain conditions. These important logical formulas in Excel will enable you to create dynamic spreadsheets that adapt to changing data.

IF

• IF: Evaluates a logical condition and returns one value if the condition is true and another value if the condition is false.

IF(A1 > 0, "Positive", "Negative")

AND, OR, and NOT

• AND: Returns TRUE if all specified conditions are true. Otherwise, returns FALSE.

AND(A1 > 0, A2 < 10)

• OR: Returns TRUE if at least one specified condition is true. Otherwise, returns FALSE.

OR(A1 > 0, A2 < 10)

• NOT: Reverses the result of a logical expression, returning TRUE if the expression is FALSE and vice versa.

NOT(A1 > 0)

IFERROR and IFNA

• IFERROR: Returns a specified value if a formula results in an error. Otherwise, returns the formula's result.

IFERROR(A1 / A2, "Error")

• IFNA: Returns a specified value if a formula results in an #N/A error. Otherwise, returns the formula's result.

IFNA(VLOOKUP(A1, B1:C10, 2, FALSE), "Not Found")

SWITCH

• SWITCH: Evaluates an expression against a list of values and returns the corresponding result for the first matching value or a default value if no match is found.

SWITCH(A1, 1, "One", 2, "Two", "Other")

IFS

• IFS: Evaluates multiple conditions and returns the corresponding value for the first TRUE condition.

IFS(A1 > 0, "Positive", A1 < 0, "Negative", A1 = 0, "Zero")

Lookup and Reference Formulas

Lookup and reference formulas are crucial for extracting and organizing data in Excel. Here's how to use lookup and reference formulas to find and retrieve information from your spreadsheets.

VLOOKUP

• VLOOKUP: Searches for a value in the first column of a specified range and returns a value in the same row from another column.

VLOOKUP(A1, B1:C10, 2, FALSE)

HLOOKUP

• HLOOKUP: Searches for a value in the first row of a specified range and returns a value in the same column from another row.

HLOOKUP(A1, B1:J2, 2, FALSE)

INDEX and MATCH

• INDEX: Returns a value from a specified range based on row and column numbers.

INDEX(B1:C10, 2, 1)

• MATCH: Searches for a value in a specified range and returns the relative position of the value.

MATCH(A1, B1:B10, 0)

• Combining INDEX and MATCH: Use MATCH as a row or column argument in INDEX to create a more flexible lookup formula.

INDEX(C1:C10, MATCH(A1, B1:B10, 0))

CHOOSE

• CHOOSE: Selects a value from a list of values based on a specified index number.

CHOOSE(2, "A", "B", "C") returns "B"

INDIRECT and OFFSET

• INDIRECT: Returns the value of a cell specified by a text string that represents a cell reference.

INDIRECT("A1")

• OFFSET: Returns a value from a cell with a specified number of rows and columns away from a given reference cell.

OFFSET(A1, 1, 1)

Financial Formulas

Excel is widely used for financial analysis. These financial formulas can help you calculate present value, future value, loan payments, and more.

PV and FV

• PV: Returns the present value of an investment based on periodic, constant payments and a constant interest rate.

PV(0.05, 10, -1000)

• FV: Returns the future value of an investment based on periodic, constant payments and a constant interest rate.

FV(0.05, 10, -1000)

PMT, IPMT, and PPMT

• PMT: Calculates the periodic payment for a loan based on constant payments and a constant interest rate.

PMT(0.05, 10, -10000)

• IPMT: Calculates the interest payment for a specific period of an investment based on constant payments and a constant interest rate.

IPMT(0.05, 1, 10, -10000)

• PPMT: Calculates the principal payment for a specific period of an investment based on constant payments and a constant interest rate.

PPMT(0.05, 1, 10, -10000)

RATE and NPER

• RATE: Returns the interest rate per period of an investment based on constant payments and a constant present value.

RATE(10, -1000, 10000)

• NPER: Returns the number of periods for an investment based on constant payments, a constant interest rate, and a constant present value.

NPER(0.05, -1000, 10000)

NPV and IRR

• NPV: Returns the net present value of an investment based on a series of periodic cash flows and a discount rate.

NPV(0.1, A1:A5)

• IRR: Returns the internal rate of return for an investment based on a series of periodic cash flows.

IRR(A1:A5)

SLN, SYD, and DDB

• SLN: Returns the straight-line depreciation of an asset for one period.

SLN(10000, 1000, 5)

• SYD: Returns the sum-of-years' digits depreciation of an asset for a specified period.

SYD(10000, 1000, 5, 1)

• DDB: Returns the double-declining balance depreciation of an asset for a specified period.

DDB(10000, 1000, 5, 1)

Statistical Formulas

Statistical analysis is a common use case for Excel, as it helps you analyze and understand your data.

STDEV, STDEV.P, and STDEV.S

• STDEV: Calculates the standard deviation based on a sample.

STDEV(A1:A10)

• STDEV.P: Calculates the standard deviation based on an entire population.

STDEV.P(A1:A10)

• STDEV.S: Calculates the standard deviation based on a sample, which is an updated version of the STDEV function.

STDEV.S(A1:A10)

VAR, VAR.P, and VAR.S

• VAR: Calculates the variance based on a sample.

VAR(A1:A10)

• VAR.P: Calculates the variance based on an entire population.

VAR.P(A1:A10)

• VAR.S: Calculates the variance based on a sample, which is an updated version of the VAR function.

VAR.S(A1:A10)

C. CORREL

• CORREL: Calculates the correlation coefficient between two data sets.

CORREL(A1:A10, B1:B10)

NORM.DIST and NORM.INV

• NORM.DIST: Returns the normal distribution for a specified mean and standard deviation.

NORM.DIST(50, 10, 5, TRUE)

• NORM.INV: Returns the inverse of the normal distribution for a specified probability, mean, and standard deviation.

NORM.INV(0.9, 50, 10)

FORECAST and TREND

• FORECAST: Calculates a future value based on existing values and a linear trend.

FORECAST(A11, A1:A10, B1:B10)

• TREND: Calculates a linear trend based on existing values.

TREND(B1:B10, A1:A10, A11:A15)

Array Formulas

Array formulas can perform multiple calculations and return multiple results, making them an advanced and powerful tool in Excel.

What are Array Formulas?

Array formulas perform calculations on entire ranges of cells rather than individual values.

Using Array Formulas in Excel

• To enter an array formula, press Ctrl + Shift + Enter instead of just Enter after typing the formula.
• Excel will automatically place curly braces {} around the formula, indicating that it is an array formula.

SUMPRODUCT

• SUMPRODUCT: Multiplies corresponding components in the given arrays and returns the sum of those products.

SUMPRODUCT(A1:A5, B1:B5)

TRANSPOSE

• TRANSPOSE: Transposes the rows and columns of a range or array.

TRANSPOSE(A1:C3)

FREQUENCY

• FREQUENCY: Returns a frequency distribution as an array, showing the number of values occurrences within specified data ranges.

FREQUENCY(A1:A10, B1:B3)

MMULT

• MMULT: Returns the matrix product of two arrays.

MMULT(A1:B2, C1:D3)

Excel Formula Tips and Tricks

Let's go through some useful tips and tricks to help you work more efficiently with Excel formulas and enhance your overall experience.

Absolute and Relative References

Understanding the difference between absolute and relative references is essential for effectively using Excel formulas.

• Relative references (e.g., A1) change when a formula is copied to another cell.
• Absolute references (e.g., $A$1) remain constant when a formula is copied to another cell.

Nested Functions

Nesting functions is a powerful technique that allows you to combine multiple functions within a single formula.

An example of a nested function is:

IF(AND(A1 > 0, B1 > 0), "Both positive", "Not both positive").

Named Ranges

Named ranges can make your formulas more readable and easier to maintain.

To create a named range, select the range of cells, then click on the Name Box, enter the name, and press Enter.

Using the Formula Evaluator

The Formula Evaluator helps you understand how Excel calculates a formula step-by-step.

To use the Formula Evaluator, go to the Formulas tab, click "Evaluate Formula," and follow the steps.

Shortcut Keys for Formulas

Learning shortcut keys for formulas can increase your efficiency in Excel. Some useful shortcuts when working with Excel formulas include:

• F2: Edit the active cell
• Ctrl + Shift + Enter: Enter an array formula
• F9: Calculate all worksheets in all open workbooks

Advanced Excel Formulas

Let's look at some slightly more advanced Excel formulas, such as conditional formatting formulas, dynamic array formulas, and custom functions using VBA.

Conditional Formatting Formulas

Conditional formatting formulas allow you to apply specific formatting to cells that meet certain conditions.

An example of a conditional formatting formula is:

=A1>100 to highlight cells greater than 100.

Dynamic Array Formulas

Dynamic array formulas are a new feature in Excel that can perform calculations on arrays and return multiple values, making them an advanced and powerful tool.

• SORT: Sorts the contents of a range or array.

SORT(A1:A10)

• FILTER: Filters the contents of a range or array based on specified criteria.

FILTER(A1:A10, A1:A10 > 100)

• UNIQUE: Returns the unique values from a range or array.

UNIQUE(A1:A10)

• SEQUENCE: Returns an array with a specified number of rows and columns, filled with a sequence of numbers.

SEQUENCE(5, 2)

Custom Functions using VBA

Visual Basic for Applications (VBA) allows you to create custom functions in Excel, also known as User Defined Functions (UDFs). UDFs can be used just like built-in Excel functions, but they are tailored to your specific needs and requirements.

To create a custom function, press Alt + F11 to open the VBA editor, then write your VBA code and save the file as an Excel Macro-Enabled Workbook (.xlsm).

Conclusion

Mastering Excel formulas is a valuable skill that can significantly improve your productivity and efficiency at work. This guide has provided you with a deep understanding of the most important Excel formulas and their use cases.

By practicing and applying these formulas in your daily work, you'll soon become an Excel expert and efficiently tackle any spreadsheet challenges.