For enquiries call:

Phone

+1-469-442-0620

HomeBlogothersTop 30 Excel Formulas and Functions in 2024

Top 30 Excel Formulas and Functions in 2024

Published
29th Mar, 2024
Views
view count loader
Read it in
10 Mins
In this article
    Top 30 Excel Formulas and Functions in 2024

    If you are a working professional, MS Excel may not be a new term for you. However, trying to organize and analyze spreadsheets in MS Excel can feel like hitting your head against the wall if you are unaware of Excel formulas. So, is there a better way to get rid of Excel's manual operations, you ask? Well, there is a way to speed up your Excel worksheet operations. Being a Data analytics professional, I can say using Excel functions and formulas may seem tricky, but it can make your work way much easier.

    Taking up an Artificial Intelligence course for beginners would help you learn Excel functions. These Excel formulas are the go-to tools for working with large datasets. Therefore, I have drafted a brief guide on the top Excel formulas in 2024 so that you will know how to streamline your work in Excel.

    What is an Excel Formula?

    Drawing from my experience, I can state that Excel formulas are tools that help determine the relationship between the values in your spreadsheet's cells. They perform mathematical calculations using those values and display the resulting value in the cell where you carry out the operation. The basic Excel functions are addition, subtraction, multiplication, percentage, division, average, and date or time.

    Every Excel formula allows you to manipulate strings, numbers, and data in a way that facilitates data analytics and the management of data-driven reports. In addition to basic Excel formulas, you can also use advanced ones such as if-else statements, find and replace, mathematics and trigonometry, finance, logical, and engineering formulas.

    Let me clarify that data analytics is not as complex as programming languages. You are simply required to write a hand-tested formula and its related arguments. For example, to add 10 + 6 using Excel, type “=10+6” in any cell and press Enter. The value “16” will be displayed in the cell because the sum of 10 and 6 is 16. It is that simple, you see. If you still find it overwhelming, I recommend you join AI bootcamp to know its nitty-gritty. Let me outline more of these formulas ahead.

    Why are Excel Formulas Needed?

    My experience working with Excel formulas shows that they are effective tools for managing data and decision-making. Let me tell you that these Excel formulas bring added value to the worksheet by allowing you to perform multiple operations.

    • Excel formulas save time, increase efficiency, and reduce manual errors.
    • Excel formulas provide accurate data outcomes.
    • Excel formulas aimed at sorting, filtering, and modifying even the most complex datasets, leading to enhanced data management capabilities.
    • MS Excel can even be used by non-technical users who can carry data nothing so complex, which makes it more approachable to a wider audience.
    • Excel formulas hypothetically give a cushion wherein you can handle data as per your requirement.
    • By mastering Excel spreadsheet formulas, your opportunity for job availability will increase.

    Top Excel Formulas to Know

    There are so many uses of Excel formulas. You can carry out various operations using Excel functions and formulas. That's why it is a unique tool of Microsoft Excel. I have curated the top 30 Excel formula list. These formulas have helped me work on Excel throughout my career this year.

    1. SUM

    It functions as its name suggests. The SUM() function means adding values. You can add single values, cell ranges or a mix of both. Thus, it presents the “sum” of values present in the selected cells.

    • Syntax: SUM(value 1, value 2)
    • Example: SUM(A2:A4), where the value of A2 and A4 is added to find its sum.

    2. DIVISION

    It is one of the simplest Excel functions. You are simply required to highlight any empty cell, type in an equals sign (=), and select the two values you want to divide using a forward slash (/).

    • Syntax: Division= Value 1/ Value 2
    • Example, =A1/B1, where A1 and B1 denote the cells being divided.

    3. VLookup Value

    The VLOOKUP formula looks up the value in the left column of the table array and returns the value from the same row.

    Syntax: VLOOKUP(lookup_value,table_array, col_index, range_lookup)

    Here, each term has a unique significance.

    • lookup_value: It is present in the first column.
    • table_array: It is the range of the spreadsheet, the table, or the cells selected.
    • col_index: It is the position of the column.
    • range_lookup: “TRUE” is used for approximate match, while “FALSE” is used for exact match.

    Example: Find the company name among 10 IDs with the ID 3. So, the lookup value will be 3, and the range will be 1-10

    4. AVERAGE

    The AVERAGE function is an Excel formula that finds the average of the selected range of values in the cell.

    • Syntax: AVERAGE(number1, number2, …): the first number or cell range whose average will be calculated. Number 2 is additional numbers or cell ranges added up to 255.
    • Example: AVERAGE = AVERAGE(A2, A3, A4)

    5. COUNT

    The COUNT function computes the total number of cells containing values in a selected range. It excludes blank cells or cells containing data other than numeric values.

    • Syntax: COUNT(value1, [value2], …)
    • Example: COUNT = COUNT (C1:C4), where numbers will be counted from the range C1 to C4.

    6. IF function

    The IF function bifurcates information based on certain criteria into true and false categories.

    • Syntax: IF(Criteria, True Value, False Value)
    • Example: =IF(C2=”Yes”,1,2); where IF(C2 = Yes, then return a 1, otherwise return a 2).

    7. MAX function

    It finds out the maximum value among the range of cells. You simply need to enter MAX on the function tab to find out the maximum value in the data.

    • Syntax: MAX(number1, [number2], …)
    • Example: To find out the largest value in the range A1:A4, the formula would be: =MAX(A1:A4)

    8. MIN function

    It finds out the minimum value among the range of cells. You can simply enter MIN in the function tab to identify the lowest value in the data table.

    • Syntax: MIN(number1, [number2], …)
    • Example: To find out the lowest value in the range A1:A4, the formula would be: =MIN(A1:A4)

    9. SUBTOTAL

    It is useful for analyzing referred data by generating groups and performing spreadsheet functions like SUM, COUNT, AVERAGE, PRODUCT, MIN, MAX, etc.

    • Syntax: SUBTOTAL(function_num,ref1,[ref2],...)
    • Example: =SUBTOTAL(6,A2:A5); which means the sum of the values ranging from cell A2 to A5 using 6 as the first argument.

    10. COUNTIF

    It counts the values instead of adding up the data. It counts if and only if the condition is met.

    • Syntax: =COUNTIF(range, criteria).
    • Example: COUNTIF(C2:C15 “Bill Gates") counts how many times Bill Gates appears on the list.

    11. AND function

    It returns true or false depending on two or more criteria.

    • Syntax: AND(logical1, [logical2], …); logical is the condition to be tested, which can result in either true or false.
    • Example: =AND(A2>1, A2<100). It will display TRUE if A2 is greater than 1 AND less than 100. Otherwise, it will display FALSE.

    12. LEFT

    It shifts data to the left corner of the selected column. The LEFT command makes it easy to specify the function.

    • Syntax: =LEFT(text, num_chars)
    • Example: =LEFT(A1, 6), where the first 6 characters will be extracted from the left side.

    13. RIGHT

    It shifts data to the right corner of the selected column. The function can be easily specified by using the RIGHT commands.

    • Syntax: =RIGHT(text, num_chars)
    • Example: =RIGHT(A1, 6), where the first 6 characters will be extracted from the right side.

    13. Range Lookup

    It shows either true or false. The right end of the data will provide information related to the variable you inserted. It will eventually determine whether the info is true or accurate. Conversely, false will show NA if the data doesn’t find the accurate result.

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

    14. CONCATENATION

    It combines the right and left actions. You can also create a new column of data by combining both left and right variables.

    • Syntax: CONCATENATE(text1, [text2], …)
    • Example: To concatenate the values of B4 and C4 with a semicolon, the formula is:

    =CONCATENATE(B4;";";C4)

    15. OR

    The OR function checks against only one criterion. It returns TRUE if any of its values are analyzed to TRUE and FALSE if all of its values are analyzed to FALSE.

    • Syntax: =OR(logical1, [logical2], …)
    • Examples: If either column B or C has a value less than 200, the formula is =IF(OR(B2>200, C2>200)

    16. PROPER

    Proper in excel
    educba

    This function allows the first letters of each sentence to be capitalized. This function can also alter the format as required.

    • Syntax: PROPER(text)
    • Example: The proper case to be inserted in “i am a girl”, the formula will be =PROPER(I am a girl) to change into capitalized letter.

    17. UPPER

    This function allows the selected letters of each sentence to be converted to uppercase alphabets.

    • Syntax: UPPER(text)
    • Example: To return all the uppercase text in cell A3, the formula will be UPPER(A3).

    18. LOWER

    This function allows the selected letters of each sentence to be converted to lowercase alphabets.

    • Syntax: LOWER(text)
    • Example: To return all the lowercase text in cell A3, the formula will be LOWER(A3).

    19. ROUND

    This function rounds up digits or data to simplify them. If your data has a lot of decimals, this function can simplify it easily.

    • Syntax: ROUND(number,num_digits)
    • Example: To round off 2.36 into one digit place, the formula would be ROUND(2.36,1)

    20. NOW

    The NOW function displays the current date and time and updates the value on the worksheet.

    • Syntax: NOW(), where NOW syntax has no argument.
    • Example: To return to the current date and time, type in =NOW()

    21. TRIM Formula

    TRIM formula
    educba

    It is used to fill the gaps between variables that may arise after copying a substantial amount of data to the Excel worksheet.

    • Syntax: TRIM(text)
    • Example: Use the TRIM function to clean up irregular spaces within the range A4:C20.

    22. Modify the Case

    This Excel formula is used to alter the letter case of all the cells jointly. You can modify or switch the letter cases from lower to upper or upper to lower as per the requirements using this formula.

    • Syntax: =PROPER(A2)

    23. SUMIF

    It is used to return the sum of variables in the cells that meet only one criterion.

    • Syntax: =SUMIF(range,criteria,sum_range)
    • Example: SUMIF(C2:C10, "<100") would return the sum of values between C2 and C10 from only those cells that are less than 100.

    24. CONVERT

    This formula converts various dimensions, such as lengths, currencies, and more. Instead of using Google to perform the conversion, you can insert the unit value in the conversion section.

    • Syntax: CONVERT(number,from_unit,to_unit)
    • Example: To convert 1 kg to 1 pound mass, use =CONVERT(1,"kg", “lbm”)

    25. REPT ()

    The REPT function is used to repeat different types of characters in the cells of the Excel worksheet.

    • Syntax: REPT(text, number_times)
    • Example: To display an asterisk and a dash 8 times, use =REPT("*-", 8)

    26. CHOOSE

    This function is used to determine the type of data contained in each cell, especially when you are working with someone else’s data.

    • Syntax: CHOOSE(index_num, value1, [value2], …)
    • Example: To pick up the values of cell A3 and the second list argument which has 5 columns, use =CHOOSE(2,A2,A3,A4,A5)

    27. TYPE

    This function is used to determine the type of data every cell contains, especially when you are working with someone else’s data.

    • Syntax: TYPE(value)
    • Example: To return the type of the values on A1, use formula, "=TYPE(A2)"

    28. BETWEEN

    It is used to add numbers randomly between variables. You can use it when you need to change data or mimic the behavior of data.

    • Syntax: =IF(AND(value>=MIN(number 1, number 2), value<=MAX(number 1, number 2)), "Yes", "No").”
    • Example: To check if your value is greater or equal to the given number, use =IF(AND(C2>=MIN(A2,B2),C2<=MAX(A2,B2))"Yes", "No").”

    29. PV function

    Excel sheets make it easier to perform complex financial operations. PV functions are used to find things like the rate of interest, future value, payment per period, and investment period. Excel acts like a finance expert, managing all financial operations.

    • Syntax: PV(rate, nper, pmt, [fv], [type])
    • Example: To find the present value of an annuity with the terms in A3:A5, use =PV(A3/12, 12*A4, A2, 0).

    How to Insert Excel Formulas?

    While we discussed Excel formulas and functions, you may wonder how to insert them, right? My take on this is very simple. Let me help you with this. To begin, go to the “Formulas” tab on the navigation toolbar. The horizontal menu appears, allowing you to find and insert Excel formulas into your preferred cell. Let me guide you through the steps to insert Excel formulas:

    Inserting formula in the excel formula bar
    wps.com

    • Select a cell.
    • Enter the equals sign inside the cell.
    • Enter the Excel formulas or functions. You can even choose from the drop-down Excel formula list.
    • Select the range of cells you want to include in the function.
    • Press Enter to activate the function. I would say you to look at the formula in the Excel formula bar.
    • Let me explain this with an example.
    • Assume, you choose SUM function from the drop down list or you can type in the SUM() function.
    • Type the formula A1+A2. If cell A1 has a value of 2 and cell A2 has a value of 3, then cell A3 will have a value of 5.
    • If you change the value of cell A1 to 3 then Excel will automatically recalculate the value of the cell A3 and it will display the value of 6.
    • Pro Tip: instead of typing A1 and A2, simply select cell A1 and cell A2.
    • If you want to edit the Excel formulas, then follow the steps outlined below:
    • Click in the formula bar and change the formula. For example, change the formula from A1+A2 to A1-A2.
    • Press Enter to confirm.

    If you're interested in mastering Excel and diving deeper into its functionalities, consider checking out KnowledgeHut's Artificial Intelligence course for beginners.

    Wrapping Up

    In a nutshell, MS Excel is a powerful tool for operating spreadsheets for reporting and data analytics. After going through this brief guide on Excel formulas and functions, you will have learned how to perform tasks faster and better on MS Excel. As per my experience, having trained many data analytics candidates, I can say that it has helped a lot of them shape their careers in various fields.

    If you find this a lucrative opportunity and are looking to advance your career in data analytics, look no further! I would recommend you take up KnowledgeHut’s Data Analytics bootcamp to learn advanced Excel functions for data analysis. This boot camp is designed so that you will work on real-world projects, get hands-on training, and personalized mentorship. So, don’t wait. Sign up Today!

    Frequently Asked Questions (FAQs)

    1. How to create formulas in Excel?

    You can use simple mathematical operations like addition, subtraction, division or multiplication to create Excel sheet formulas.  Following are the steps to create Excel formulas. 

    • Click on the cell on the Excel worksheet where you want to enter the formula. 
    • Enter an equal sign. After the equal sign, enter number values and operators such as the plus (+), minus (-), multiply (*), or divide by (/) symbol. 
    • Press the Enter key to have the effect. 
    • Finally, press Enter.
    2. How to make a Pivot Table?

    The pivot table generates reports quickly and is effective at managing data analytics. Let us understand how to build a pivot table: 

    • Choose a cell range that will contain a pivot table body. 
    • Tap the Insert View icon to indicate the placement of the cursor at the Pivot Table tab. 
    • If you look at the bottom of your screen, you should see the option that reads Pivot Table. Tap on it, and a dialog box with a pop-up will appear.
    • On the dialogue box paste the link where you would like the pivotable table to be – either on another sheet or the current one. 
    • Press the OK button. 
    3. How to create a graph in Excel?

    Follow the steps mentioned below to create a graph: 

    • Choose the data set that you would like to work with to create the chart. 
    • Click on INSERT, then select Recommended Charts from the drop-down list. 
    • Select the favorite chart from the list of possible charts among those available. 
    • If you fail to find the required chart in the chart list, tap on the icon of “All Charts” to refer to the additional charts. 
    • Having finally picked your chart, click on OK to confirm your choice thus resulting in the creation of the chart. 
    Profile

    Abhresh Sugandhi

    Author

    Abhresh is specialized as a corporate trainer, He has a decade of experience in technical training blended with virtual webinars and instructor-led session created courses, tutorials, and articles for organizations. He is also the founder of Nikasio.com, which offers multiple services in technical training, project consulting, content development, etc.

    Share This Article
    Ready to Master the Skills that Drive Your Career?

    Avail your free 1:1 mentorship session.

    Select
    Your Message (Optional)
    Course advisor icon
    Course Advisor
    Whatsapp/Chat icon