30 Basic Excel Formulas for Beginners

Excel formulas are the essential starting point for anyone looking to harness the power of Microsoft Excel. In this guide, I will introduce you to 30 basic Excel formulas that will empower you to perform calculations, analyze data, and make informed decisions within your spreadsheets.

What is an Excel Formula?

An Excel Formula is a fundamental element in Microsoft Excel, serving as a powerful tool for performing mathematical, logical, and data manipulation operations within spreadsheets. It comprises a combination of operators, functions, cell references, and constants that, when entered into a cell, enables automated calculations and decision-making based on data.

Excel formulas are vital for everything from basic calculations to advanced data analysis and modeling, building the foundation for effective spreadsheet usage. Here, in cell D7 I used the SUM function and division operator (/) to create an Excel formula that calculates GPA.

Here’s the formula,

=SUM(D2:D5)/SUM(C2:C5)

Working with Excel Formulas

The Excel formulas are the secret sauce that transforms spreadsheets from static data repositories into dynamic tools for calculations, data analysis, and decision-making.

In this guide, I’ll break down the basics of using formulas in Excel so you can perform calculations and manage data effortlessly.

Let’s assume, we have to calculate GPA in Excel.

Formula

GPA=∑(Credit Hour*Grade)/Total Credit Hour

Here are the steps to use formulas in Excel:

1. Select a Cell: Start by selecting the cell where you want the formula result to appear. Click on the cell to activate it.

2. Begin with an Equal Sign: In Excel, all formulas must start with an equal sign (=). This tells Excel that you’re entering a formula.

3. Enter the Formula: After the equal sign, enter the formula expression. You can use numbers, cell references, mathematical operators (+, -, *, /, ^), and functions in your formula.

4. Use Cell References: Excel formulas often involve cell references (e.g., A1, B2) to perform calculations using the values in those cells. You can either click on the cell or manually type its reference in the formula. For instance, =B2*C2 multiplies the values in cells B2 and C2.

5. Use Functions: Excel offers a wide range of built-in functions (e.g., SUM, AVERAGE, IF) that you can use to perform specific tasks. Functions are followed by parentheses containing their arguments.

6. Complete the Formula: After entering the formula, press the ENTER key to calculate the result. The result will appear in the selected cell.

7. AutoFill: To apply the same formula to adjacent cells, you can use Excel’s AutoFill feature. Click and drag the Fill Handle (a small square at the bottom-right corner of the selected cell) to populate neighboring cells with the formula. Excel will adjust cell references automatically.

8. Mathematical Operators: You can use mathematical operators within formulas to perform arithmetic operations. Here, I have divided the values of cell D2 by cell C2.

List of 30 Basic Formulas in Excel for Beginners

Now, here I’ve discussed 30 basic Excel formulas for anyone who wants to start learning Microsoft Excel. So, let’s get started.

1. SUM

The SUM formula is versatile in Excel. We can use it to calculate total values within a selected range of cells, even with the numbers. By using the SUM function, Excel users can easily perform calculations to find the sum of numbers, making it an essential tool for various tasks, including financial analysis, budgeting, and data manipulation.

Syntax

=SUM(range)

Formula

=SUM(B2:B5)

In the dataset, I have records of electricity bills for four months. Our target is to calculate the total cost of electricity bills throughout these months.

Formula Breakdown

In this formula, you can select a range of numbers in a column or row. As we set B2 to B5, it will add the value of cells B2, B3, B4, and B5. It will store the result in cell B6.

2. SUMIF

The SUMIF formula in Excel is used to sum values based on specific criteria or conditions. It allows you to add up numbers in a range that meets a specific condition.

Syntax

=SUMIF(range, criteria, [sum_range])

Formula

=SUMIF(A1:A7, "Product A", C1:C7)

This formula calculates the sum of total sales of product A. Here,

Formula Breakdown

• A1:A7: This is the range of cells that contains the products (the range to evaluate).
• Product A: This is the criteria to apply to the cells in the range A1:A7. In this case, I want to find instances where the product is Product A.
• C1:C7: This is the range of cells that contains the sales values (the range to sum).

3. SUMPRODUCT

The SUMPRODUCT formula in Excel is a handy tool for doing math with lists of numbers. SUMPRODUCT allows you to multiply corresponding values from different arrays and then sum up the results, providing a versatile way to analyze and manipulate data in Excel.

Syntax

=SUMPRODUCT(array1, [array2], [array3], ...)

Formula

=SUMPRODUCT(B2:B7, C2:C7)

The formula calculates total sales for all products.

Formula Breakdown

• B2:B7: It represents the first range of values. In this case, it’s a range of values from cell B2 to B7. Here these values represent units of products.
• C2:C7: It represents the second range of values. It’s another range of values from cell C2 to C7, and in this context, these values represent the prices per unit of the items corresponding to the quantities in the first range.
• SUMPRODUCT: The SUMPRODUCT function adds up these individual products. This formula multiplies the units sold (column B) by the Sales (column C) for each row and then adds up these products for all rows.

4. AVERAGE

You can easily calculate the mean of a range of numbers using the AVERAGE formula. The AVERAGE formula sums up all the numbers in a specified range and then divides the sum by the count of those numbers to provide the average value.

Syntax

=AVERAGE(Range)

Formula

=AVERAGE(B2:B8)

This function helps to find out the mean value of a range of numbers.

Formula Breakdown

• B2:B8: This is the range of cells containing the numerical values for the subjects. In this case, it includes the marks obtained in Literature, Geography, Physics, Chemistry, Mathematics, Sociology, and Economics.
• AVERAGE(B2:B8): This part of the formula instructs Excel to calculate the average of the values in the specified range (B2 to B8).

5. COUNT

The COUNT formula is a useful tool in Excel for counting numeric values within a defined range, making it valuable for different data analysis and reporting purposes. It doesn’t count the cells that contain text values.

Syntax

=COUNT(range)

Formula

=COUNT(B2:B8)

The COUNT formula counts the numeric values within cells B2 to B8.

Formula Breakdown

• B2:B8: This is the range of cells for counting.
• COUNT: It calculates the total number of cells that have numeric values only in the specific range. Within cells B2 to B8, cell B4 doesn’t have a numeric value, so it counts except for cell B4.

6. COUNTIF

COUNTIF is a formula in Excel that allows you to count the number of cells within a specified range that meets a specified condition or criteria.

Syntax

=COUNTIF(range, criteria)

Formula

=COUNTIF(B2:B8,">70")

The COUNTIF function counts the number of cells within a designated range that satisfies specific conditions.

Formula Breakdown

• B2:B8: This is the range of cells (from cell B2 to B8) that contains the scores of the students. It’s the range I want to evaluate.
• >70: This is the condition or criteria we are applying to the cells within the specified range. In this case, we are checking if the score in each cell is greater than 70.
• COUNTIF: This counts cells in a given range based on a specified condition. Here it gives a result of 3, the students who scored more than 70.

7. IF

The IF formula in Excel is used to perform conditional calculations by evaluating a specified condition and returning one value if the condition is true and another value if it’s false.

Syntax

=IF(logical_test, value_if_true, [value_if_false])

Formula

=IF(B2>40, "Pass", "Fail")

The IF function acts as a logical operator, providing different outcomes depending on whether a specified condition is satisfied or not.

Formula Breakdown

• B2 > 40: It is the logical test, which evaluates whether the value in cell B2 is greater than 40. This logical test can result in either TRUE (if B2 is greater than 40) or FALSE (if B2 is not greater than 40).
• “Pass”: If the logical test B2 > 40 is TRUE (meaning that the value in cell B2 is indeed greater than 40), then the formula will return the text “Pass.” This is the value to display if the condition is met.
• “Fail”: If the logical test B2 > 40 is FALSE (meaning that the value in cell B2 is not greater than 40), then the formula will return the text “Fail.” This is the value to display if the condition is not met.
• IF: This function works as a logic: If a given condition is met (evaluates to TRUE), it returns one value; otherwise (if the condition is FALSE), it returns another value.

8. IFS

The IFS is a versatile formula that allows you to test multiple conditions and specify different results for each condition.

Syntax

=IFS(Something is True1, Value if True1, Something is True2, Value if True2, Something is True3, Value if True3)

Formula

=IFS(B2<40, "F", B2<50, "D", B2<60, "C", B2<70, "B", B2<80, "A", B2>=80, "Excellent")

The formula is useful in multiple conditions to evaluate, simplifying your formulas and improving readability. Here,

Formula Breakdown

• B2: This refers to the value in cell B2, which represents a student’s score or numeric result.
• B2<40: This is the first condition. It checks if the score in cell B2 is less than 40. If this condition is true, it assigns the grade “F”.
• B2<50: This is the second condition. It checks if the score in cell B2 is less than 50. If this condition is true (meaning the score is between 40 and 49), it assigns the grade “D.”
• B2<60: This is the third condition. It checks if the score in cell B2 is less than 60. If this condition is true (meaning the score is between 50 and 59), it assigns the grade “C.”
• B2<70: This is the fourth condition. It checks if the score in cell B2 is less than 70. If this condition is true (meaning the score is between 60 and 69), it assigns the grade “B.”
• B2<80: This is the fifth condition. It checks if the score in cell B2 is less than 80. If this condition is true (meaning the score is between 70 and 79), it assigns the grade “A.”
• B2>=80: This is the last condition. It checks if the score in cell B2 is 80 or greater. If this condition is true, it assigns “Excellent.”
• IFS: The formula evaluates the conditions one by one, from top to bottom, and as soon as it finds a condition that is true, it returns the corresponding value. If none of the conditions are true, the formula won’t return anything, which is acceptable because the grades are only assigned when specific score ranges are met.

9. ROUND

The ROUND formula in Excel is a powerful tool used for adjusting the precision of numeric values within your spreadsheets. Its primary purpose is to round a number to a specified number of decimal places or to the nearest whole number.

Syntax

=ROUND(number, num_digits)

Formula

=ROUND(B2,2)

The ROUND formula operates on the numeric value in cells, rounding it to two decimal places.

Formula Breakdown

• B2: This part of the formula refers to a specific cell in Excel, namely cell B2. This cell is where you have the numeric value that you want to round.
• 2: This indicates the number of decimal places to which you want to round the value in cell B2. In this case, it’s set to 2, which means you want to round to two decimal places.
• ROUND: The function takes the numeric value in cell B2, which might be something like 15.349, and rounds it to the specified number of decimal places.

10. RANDBETWEEN

The RANDBETWEEN formula generates a random integer number between two specified values. It’s a useful function for scenarios where you need to generate random data for simulations, testing, or other purposes.

Syntax

=RANDBETWEEN(bottom,top)

Formula

=RANDBETWEEN(B2, C2)

This formula produces a random number between the values specified in cells.

Formula Breakdown

• B2: This cell contains the minimum value of the range of random numbers to generate.
• C2: This cell contains the maximum value of the range of random numbers to generate.
• RANDBETWEEN: This random number falls within the specified range, as defined by the values in cells B2 and C2. Here, it chooses a random test score for each student.

11. MAX & MIN

The MAX and MIN functions in Excel are used to find the maximum (largest) and minimum (smallest) values within a range of numbers or a list of values. These functions are essential for analyzing data and identifying extremes or boundaries in your dataset.

Syntax

=MAX(range)

=MIN(range)

Formula

=MAX(B2:B7)

This formula evaluates the range of values from cell B2 to B7 and returns the maximum value from that range.

Formula Breakdown

• B2:B7: This part of the formula represents a range of cells. In this example, it covers cells B2 through B7, which contain a list of numeric values.
• MAX: The MAX function is used to find the maximum value within the specified range.

Formula

=MIN(B2:B7)

This formula evaluates the range of values from cell B2 to B7 and returns the minimum value from that range.

Formula Breakdown

• B2:B7: This part of the formula represents a range of cells. In this example, it covers cells B2 through B7, which contain a list of numeric values.
• MIN: The MIN function is used to find the minimum value within the specified range.

12. LEFT, RIGHT & MID

The LEFT, RIGHT, and MID formulas in Excel are used for text manipulation and extraction. The LEFT function extracts characters from the beginning (left side) of a text string, the RIGHT function extracts characters from the end (right side), and the MID function extracts characters from anywhere within the string based on the starting position and length you specify.

Syntax

=LEFT(text,num_chars)

=RIGHT(text,[num_chars])

=MID(text, start_num, num_chars)

Formula

=LEFT(A2, LEN(A2)-3)

This formula removes a specific number of characters from the end of a text string.

Formula Breakdown

• A2: This part of the formula refers to cell A2, which contains the original text string you want to manipulate.
• LEN(A2): The LEN function calculates the length (the number of characters) of the text string in cell A2.
• LEN(A2)-3: This part subtracts 3 characters from the length of the text string. In this case, it means removing the last 3 characters from the text.
• LEFT: The LEFT function is then used to extract the leftmost characters from the text string in cell A2. It extracts the characters starting from the left and continues up to the length calculated.

Formula

=RIGHT(A2, 2)

The RIGHT formula extracts the rightmost (last) 2 characters from a text string located in cell A2.

Formula Breakdown

• A2: This part of the formula refers to cell A2, which contains the original text string you want to extract characters from.
• RIGHT: The RIGHT function is used to extract characters from the right side of the text string. In this case, it extracts the last 2 characters.

Formula

=MID(A2, 3, 5)

The MID formula removes a substring from a text string located in cells.

Formula Breakdown

• A2: This part of the formula refers to cell A2, which contains the original text string you want to extract characters from.
• MID: The MID function is used to extract a portion of the text string, starting from a specific position and including a specified number of characters.

13. UPPER, LOWER & PROPER

The UPPER, LOWER & PROPER formulas allow you to effortlessly change the case of text within cells, making your data consistent and visually appealing. Whether you need to convert text to uppercase, lowercase, or proper case with the first letter of each word capitalized, these functions have you covered.

Syntax

=UPPER(text)

=LOWER(text)

=PROPER(text)

Formula

=UPPER(A2)

The UPPER function converts all the letters in the specified text to uppercase (capital letters).

Formula Breakdown

• A2: This part of the formula refers to cell A2, which contains the original text you want to convert to uppercase.
• UPPER: The function converts the text of any cell to capital letters.

Formula

=LOWER(A2)

The LOWER function converts all the letters in the specified text to lowercase, which means it changes all the characters in the text to small letters.

Formula Breakdown

• A2: This part of the formula refers to cell A2, which contains the original text you want to convert to lowercase.
• LOWER: The function converts the text of any cell to small letters.

Formula

=PROPER(A2)

The PROPER function capitalizes the first letter of each word in the specified text while making all other letters lowercase. It essentially converts the text to “Proper Case.”

Formula Breakdown

• A2: This part of the formula refers to cell A2, which contains the original text you want to convert to the proper case.
• PROPER: The function capitalizes the first letter of each word while converting the rest of the letters to lowercase.

14. LEN

The LEN formula counts the number of characters in a text string. It’s a simple yet powerful function that helps you determine the length of a text value, including spaces and special characters.

Syntax

=LEN(text)

Formula

=LEN(A2)+LEN(B2)

This formula calculates the total number of characters in two different text values located in cells.

Formula Breakdown

• LEN(A2): It calculates the number of characters in the text value in cell A2.
• LEN(B2): It calculates the number of characters in the text value in cell B2.

15. REPLACE and SUBSTITUTE

The REPLACE and SUBSTITUTE functions are valuable for text manipulation tasks in Excel, such as cleaning data, making specific replacements, or formatting text as needed.

REPLACE is used for one-time replacements at a specific position within the text. It performs a one-time replacement at the specified position. You need to specify the original text, the starting position where the replacement should begin, the number of characters to replace, and the replacement text.

SUBSTITUTE is used to replace all occurrences of a specified substring throughout the text. It requires you to specify the original text, the text you want to replace within the original text, and the replacement text.

Syntax

=REPLACE(old_text, start_num, num_chars, new_text)

=SUBSTITUTE(text, old_text, new_text, [instance_num])

Formula

=REPLACE(A2, 1, 2, "OM")

This REPLACE formula replaces a portion of text in cell A2 with the text “OM.”

Formula Breakdown

• A2: This part of the formula refers to cell A2, which contains the original text you want to modify.
• 1: This is the starting position within the text where the replacement will begin. In this case, it starts with the first character of the text.
• 2: This indicates the number of characters to replace, starting from the specified position. In this case, it replaces the first two characters.
• “OM”: This is the new text that will replace the specified portion of the original text. In this case, it replaces the first two characters with “OM.”

Formula

=SUBSTITUTE(B2, B2, "2023")

The SUBSTITUTE formula replaces all occurrences of the content in cell B2 with the text “2023” within the same cell.

Formula Breakdown

• B2: This part of the formula refers to cell B2, which is the text you want to replace.
• B2: The second occurrence of B2 refers to the text you want to replace within cell B2 itself.
• “2023”: This is the new text that will replace the existing content of cell B2. In this case, it will replace the entire content of cell B2 with “2023.”

16. CONCAT

The CONCAT formula in Excel is used to combine or concatenate multiple text strings into a single text string. It’s especially useful when you need to join the contents of several cells or text strings together.

Syntax

=CONCAT(text1, [text2],…)

Formula

=CONCAT(A2, B2)

This formula concatenates two text strings located in cells A2 and B2 into a single text string.

Formula Breakdown

• A2: This part of the formula refers to cell A2, which is the first text string you want to concatenate.
• B2: This part of the formula refers to cell B2, which is the second text string you want to concatenate.

17. DATE

The DATE formula in Excel is used to create a date based on the year, month, and day values provided as arguments.

Syntax

=DATE(year, month, day)

Formula

=DATE(C2, B2, A2)

The DATE formula creates a date value based on the values in cells A2, B2, and C2.

Formula Breakdown

• C2: This cell contains the year value.
• B2: This cell contains the month value (1 to 12).
• A2: This cell contains the day value (1 to 31).
• DATE: The DATE function takes these values and combines them to generate a date. The result will be a date in Excel’s date format, such as “mm/dd/yyyy.”

18. DAY, MONTH

In Excel, the DAY and MONTH functions are used to extract specific components from a date, such as a day or month.

Syntax

=DAY(serial_number)

=MONTH(serial_number)

Formula

=DAY(A1)

The DAY formula extracts the day (a number between 1 and 31) from a date.

Formula Breakdown

• A1: This part of the formula refers to cell A1, which contains the date from which you want to extract the day.
• DAY: This is the Excel function used to extract the day component from a date.

Formula

=MONTH(A1)

The MONTH formula extracts the month (a number between 1 and 12) from a date.

Formula Breakdown

• A1: This part of the formula refers to cell A1, which contains the date from which you want to extract the month.
• MONTH: This is the Excel function used to extract the month component from a date.

19. TIME

The TIME formula in Excel is used to create a time value based on the specified hour, minute, and second values. It’s useful for working with time-related calculations and data.

Syntax

=TIME(hour, minute, second)

Formula

=TIME(A2, B2, C2)

We can use this formula to construct a time value by specifying the hour, minute, and second as separate components.

Formula Breakdown

• A2: This cell contains the hour value (0 to 23).
• B2: This cell contains the minute value (0 to 59).
• C2: This cell contains the second value (0 to 59).
• TIME: This function takes these values and combines them to generate a time value. The result will be a time value in Excel’s default time format, typically displayed as “hh:mm: ss AM/PM.”

20. HOUR, MINUTE, SECOND

In Excel, the HOUR, MINUTE, and SECOND formulas are used to extract specific components (hour, minute, and second) from a time value or a date and time value.

Syntax

=HOUR(serial_number)

= MINUTE(serial_number)

=SECOND(serial_number)

Formula

=HOUR(A2)

The HOUR formula allows you to isolate and work with the hour portion of a time or date and time value in Excel.

Formula Breakdown

• A2: This part of the formula refers to cell A2, which contains the time or date and time value from which you want to extract the hour.
• HOUR: This formula extracts the hour component from a time value or date and time value.

Formula

=MINUTE(A2)

The MINUTE formula allows you to isolate and work with the minute portion of a time or date and time value in Excel.

Formula Breakdown

• A2: This part of the formula refers to cell A2, which contains the time or date and time value from which you want to extract the minute.
• MINUTE: The formula extracts the minute component from a time value or date and time value.

Formula

=SECOND(A2)

The SECOND formula allows you to isolate and work with the second portion of a time or date and time value in Excel.

Formula Breakdown

• A2: This part of the formula refers to cell A2, which contains the time or date and time value from which you want to extract the second.
• SECOND: The formula extracts the second component from a time value or date and time value.

21. ROW

The ROW formula returns the row number of a cell or a range of cells. It is helpful in various scenarios, such as when you need to perform calculations or create dynamic formulas based on the position of data within a worksheet.

Syntax

=ROW([reference])

Formula

=ROW(B2:B5)

The ROW formula returns an array of row numbers corresponding to the cells in the specified range, which is B2:B5 in this case.

Formula Breakdown

• ROW: It’s the ROW function, which returns the row number of a specified cell or range.
• B2:B5: It specifies the range of cells (B2 to B5) from which you want to retrieve the row numbers.

22. COLUMN

The COLUMN formula returns the column number of a specified cell or range of cells. It is really useful for various tasks, such as referencing specific columns in formulas, data analysis, or formatting.

Syntax

=COLUMN([reference])

Formula

=COLUMN(A3:D3)

The above formula provides the column numbers for each cell within that range.

Formula Breakdown

• COLUMN: It’s the COLUMN function, which returns the column number of a specified cell or range.
• A3:D3: It specifies the range of cells (A3 to D3) from which you want to retrieve the column numbers.

23. SORT

The SORT formula helps to sort a range of data and return the sorted values in a new array, without altering the original data.

Syntax

=SORT(array, [sort_index], [sort_order], [by_col])

Formula

=SORT(A2:C10, 3, 1)

This formula sorts a table of data based on a specific range based on one or more columns.

Formula Breakdown

• A2:C10: This specifies the range of cells that contains the dataset you want to sort.
• 3: This is the “sort_index” argument, indicating that you want to sort the data based on the values in the third column (Age column). In Excel, columns are indexed starting from 1, so column A is 1, column B is 2, and column C is 3.
• 1: This is the “sort_order” argument, specifying that you want to sort the values in ascending order. The value 1 represents ascending order.
• SORT: The formula rearranges the values in cells in order and replaces the original unsorted data with the sorted values.

24. IFERROR

The IFERROR formula handles errors that may occur in a formula or expression. It allows you to specify a value or action to be taken if an error occurs, providing a way to handle errors gracefully in your Excel worksheets.

Syntax

=IFERROR(value, value_if_error)

Formula

=IFERROR(VLOOKUP(C12, B2:D10, 3, FALSE), "Not Found")

This formula handles errors in a formula by specifying a value to display if an error occurs.

Without Using IFERROR Function_

After Using IFERROR Function_

Formula Breakdown

• C12: This is the value you want to look up in the first column of the range B2:D10.
• B2:D10: This is the table or range of data in which you want to perform the VLOOKUP It consists of three columns: the first column is where the lookup value (C12) is searched, and the third column (the one specified as the result column) is where the function retrieves data if a match is found.
• 3: This argument specifies that you want to retrieve data from the third column of the range B2:D10 (column D) when a match is found.
• FALSE: The fourth argument indicates that you want an exact match. If you don’t find an exact match, it will return an error.
• “Not Found”: This is the value that the formula will display if the VLOOKUP operation results in an error, such as when the lookup value (C12) is not found in the first column of the range.
• IFERROR: This function is for error handling to specify a value or action to take when a formula or expression produces an error.

25. INDEX, MATCH

The combination of the INDEX and MATCH functions in Excel is a powerful way to look up and retrieve data from a dataset. This formula performs a lookup operation based on a specified search value in a given range and retrieves a corresponding value from another range.

Syntax

=INDEX(range, row_num, [column_num])

=MATCH(lookup_value, lookup_array, [match_type])

Formula

=INDEX(C2:C7, MATCH(B4, B2:B7, 0))

This formula returns a value from a specified range (in this case, the range C2:C7).

Formula Breakdown

• C2:C7: This is the lookup range in which you have to search.
• MATCH(B4, B2:B7, 0): The MATCH function is used to search for a value (B4) within a specified range (B2:B7) and determine its position or relative row number within that range. The “0” as the third argument indicates that an exact match is required.
• B4: It is the value you want to find in the range.
• B2:B7: The range in which you’re searching for the value.

Formula

=MATCH(C4, C2:C7,0 )

This formula searches for a specified value (in this case, the value in cell C4) within a given range (C2:C7) and determines its relative position or row number within that range.

Formula Breakdown

• C4: This is the value you want to find within the specified range.
• C2:C7: This is the range in which you are searching for the value.
• 0 (third argument): This argument specifies that you are looking for an exact match. In other words, it will only return a result if it finds the value in cell C4 exactly matching one of the values in the range C2:C7.

26. VLOOKUP

VLOOKUP is a formula in Excel that searches for a value in a dataset retrieves related information from a specified column, and displays the result.

Syntax

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

Formula

=VLOOKUP("Product D", A2:C7, 2, FALSE)

This formula searches for “Product D” in column A of the range A2:C7 and returns the corresponding value from column B if an exact match is found.

Formula Breakdown

• “Product D”: It is the value you want to search for within the dataset.
• A2:C7: The range where Excel should search for the value and retrieve the related information.
• 2: It specifies the column number from which you want to retrieve the result (in this case, the “Category” column is the second column in the range A2:C7).
• FALSE: It specifies an exact match. This ensures that Excel looks for an exact match for “Product D.
• VLOOKUP: The formula searches for “Product D” in the “Product” column (A2:A7) and returns the corresponding category from the “Category” column (C2:C7).

27. HLOOKUP

The HLOOKUP formula in Excel is similar to the VLOOKUP formula but performs a horizontal lookup. It searches for a value in the first row of a specified range and then returns a value from the same column in a specified row.

Syntax

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

Formula

=HLOOKUP("Product C", B1:E3, 3, FALSE)

The formula scans for the value “Product C” in the first row (row 1) of the specified range B1:E3.

Formula Breakdown

• “Product C”: This is the value you want to find within the first row (headers) of the specified range.
• B1:E3: This is the range in which Excel should search for the value and retrieve related information. In this case, it includes three rows and four columns of data.
• 3: This number represents the row number from which you want to retrieve the result. Since you’re looking for “Product C” within the headers in the first row and want to retrieve information from the third row, you specify “3.”
• FALSE: This argument specifies an exact match. With FALSE, Excel looks for an exact match of “Product C” within the headers. If an exact match is not found, it returns an error.
• HLOOKUP: The formula searches for a specific value (“Product C”) within a horizontal range (B1:E3) and retrieves a corresponding value from the third row of that range.

28. XLOOKUP

The XLOOKUP formula is highly versatile and can handle a wide range of lookup scenarios, including approximate matches, exact matches, and more. It’s a valuable tool for data analysis and retrieval in Excel.

Syntax

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

Formula

=XLOOKUP("Product D", A2:A8, C2:C8)

The formula searches for the given lookup value in the specified range.

Formula Breakdown

• “Product D”: It is the value you want to search for within the “Product” column (column A).
• A2:A7: The range where Excel searches for the value (the “Product” column).
• C2:C7: The range from which Excel retrieves the corresponding value (the “Price” column).
• XLOOKUP: It searches for “Product D” in the “Product” column (A2:A7) and returns the corresponding price of “$400.”

29. FILTER

The FILTER formula is used to extract data from a range or table based on specified criteria. It’s particularly useful for creating filtered lists or tables from a larger dataset.

Syntax

=FILTER(array, include,[if_empty])

Formula

=FILTER(A2:A7, B2:B7="Electronics")

The formula filters values from the given range A2:A7 based on a condition.

Formula Breakdown

• A2:A7: This is the range of values you want to filter, which consists of product names in column A.
• B2:B7=”Electronics”: This is the condition you want to apply. It checks if the corresponding category in column B is “Electronics.”
• FILTER: The formula evaluates each row in column B (B2 to B7) to see if it matches the condition “Electronics.” If the condition is met, it includes the corresponding product name from column A in the filtered list.

30. UNIQUE

The UNIQUE function in Excel is used to extract unique values from a dataset, which means it returns a list of values without any duplicates.

Syntax

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

Formula

=UNIQUE(B2:B6)

The formula returns a list of unique values from the range B2:B6, meaning it will remove any duplicate values and display each unique value once.

Formula Breakdown

• B2:B6: This is the range of values you want to find unique values from. In this case, it represents a column of data containing several entries, and you want to identify the unique values within this column.
• UNIQUE: It eliminates duplicate entries and provides you with a clean list of unique values from the specified range.

Conclusion

Excel formulas form a versatile toolkit for managing, analyzing, and presenting data efficiently. These 30 basic formulas will allow you to perform essential calculations, such as adding, averaging, and counting values, as well as making decisions based on conditions. These basic Excel formulas serve as the foundation upon which more advanced Excel skills are built, making them a crucial tool for anyone working with data management and analysis in Excel.

Excel Glossary | Active Cell | Cell Address | Range | Function | Argument | Wildcard | Worksheet | Workbook