Share this article

Improve this guide

30 Excel Basic Formulas You Must Know

Note down these down and it will help you transform how you use Excel

17 min. read

Published onSeptember 27, 2024

published onSeptember 27, 2024

Share this article

Improve this guide

Read our disclosure page to find out how can you help Windows Report sustain the editorial teamRead more

Excelis a popular spreadsheet software that offers a wide range of functionalities and is used by students, business professionals, or anyone who wants to organize information. Therefore, learning basic formulas is essential to enhance your data analysis and management skills. In this guide, we will mention 30 essential Excel formulas that you should know, ranging from operations like calculations, data manipulation, and logical operations.

What are the basic formulas you should know in Excel?

Basic Formulas: A Quick Overview

If any of yourExcel formulasdisappearafter you save your workbook, it could be because the formula is too complex and exceeds the memory capacity or is too long/complex; read this guide to learn more about it and the solutions to fix it.

1. SUM

The SUM function is a basic Excel formula that can be used to add a series of numbers together. You can quickly calculate the totals from the selected range. For example, if you want to find the total sales from cells B2: B13 as in the picture, type= SUM(B2: B13)and pressEnter.This will add all the values within the selected range. The SUM function’s efficiency allows it to handle large datasets and simple totals, ensuring you can complete your numerical analysis quickly and accurately.

If theSUM formula doesn’t add the values correctly, it could be due to wrong cell references, hidden rows or columns, or cells with text or errors; read this guide to learn more.

2. AVERAGE

The AVERAGE function calculates the mean of selected numbers, giving insights into overall trends and is useful for measuring performance.

For example, if you want to find the average sales amount in cells B2: B13, useAVERAGE(B2: B13)and pressEnter. This function adds up the values in the selected range and divides them by the count of numbers to calculate the average. This can be beneficial in financial forecasting and performance reviews.

3. SUBTOTAL

The SUBTOTAL function allows you to calculate aggregate values in a filtered database. It only calculates the relevant entries without including the excluded data. Unlike SUM functions, SUBTOTAL doesn’t include hidden rows, making it ideal for summarizing visible data.

For example, if you use=SUBTOTAL(9, B2: B13)and hitEnter, you will get the total of the visible values in that range. The number 9 in the formula represents the SUM function, so don’t change it. You can use it on pivot tables and filtered lists.

4. MIN and MAX

The MIN and MAX functions help you find the smallest and largest values in a selected range. For example, if you type=MIN(B2: B13)and hitEnter, it will show you the smallest value from the specified range. However, you will get the largest number if you type=MAX(B2: B13)and pressEnter.

These functions are theoretical concepts and practical tools that you can use to identify extremes in your data. Whether it’s the lowest and highest sales figures, test scores, or any other data point, the MIN and MAX functions help you make sense of your data.

5. COUNT

The COUNT function counts the number of cells containing numeric values within the selected range. For example, if you type=COUNT(B2: B13)and pressEnter, you will get the number of cells in that range containing numbers.

This basic Excel formula can be helpful in statistical analysis, as it allows you to understand how many data points are numeric. It can also help you in data validation and ensure the datasets are complete, making data management easier.

6. COUNTA

COUNTA is a formula that counts all non-empty cells in a selected area, regardless of whether they contain text, numbers, or errors.

For instance, the formula=COUNTA(A1:C13)will give you the number of cells with data in the area. This can help you track progress in datasets, such as counting completed tasks. Its efficiency in providing data usage insights surpasses COUNT’s, making it an essential tool in data analysis.

7. REPLACE

The REPLACE function allows you to change a portion of a text string based on chosen parameters. For example, if you type=REPLACE(B1, 1, 6, “AMT”)and pressEnter, it will replace the first six characters in cell A1 with AMT starting with the first character.

This function allows you to correct errors or update specific text parts, such as typos. This is important for streamlining data management and enhancing efficiency in text processing.

8. SUBSTITUTE

SUBSTITUTE replaces specific occurrences of a text string within another string, which gives you precise control over text changes.

For instance, if you type=SUBSTITUTE(B1, “Amount”, “AMT”)and hitEnter, it will replace Amount with AMT in the text found in cell B1. You can use this formula to make changes in multiple places in a document. This basic formula of Excel can help change outdated terms, correct repeated errors, and ensure clarity in your data.

9. NOW

The NOW formula shows the current date and time, which can be helpful when time-stamping data entries or tracking changes.

To get the exact date and time, you must type=NOW()and hitEnter. This is a dynamic function, so the date and time are updated every time the spreadsheet recalculates. This can benefit project management, scheduling, and real-time event tracking.

10. TODAY

The TODAY function gives the current date without the exact time, which can be ideal for date-based timelines.

To get today’s date, type=TODAY()and pressEnter. You can use this formula when setting deadlines and tracking time in projects.

The TODAY function is dynamic, meaning it automatically updates the date when the spreadsheet is opened. This feature is particularly useful for maintaining up-to-date reports, planning tools, and more.

11. TIME()

The TIME function shows you a time value in specified hour, minute, and second inputs.

For example, if you typeTIME(14, 30, 0)and pressEnterto execute it, you will get 2:30 PM as the output. You can use this function to schedule events and calculate time intervals in tasks.

This basic formula of Excel allows you to generate precise time values, which can be helpful time management, promoting better organization and planning

12. HOUR, MINUTE, SECOND

HOUR, MINUTE, and SECOND functions retrieve hour, minute, or second from the time value selected. For example, if you type=HOUR(L1)and pressEnter, this will extract the hour component from the time in cell L1. Similarly, if you type=MINUTE(L1)or=SECOND(L1)and pressEnter, you can get the minutes and seconds mentioned in the cell.

You can use these basic Excel formulas while analyzing time and breaking down the time data for detailed reporting, including time spent on tasks.

13. MODULUS

The MODULUS function shows the remainder after division, which can be helpful for mathematical checks. For example, type=MOD(J1, 2)and pressEnter, it determines whether the value in cell J1 is even or odd( it returns 0 for even).This function can categorize data or implement periodic tasks. It can also simplify complex calculations, speed up processes, and facilitate batch processing.

14. LEFT, RIGHT, MID

The LEFT, RIGHT, and MID functions show you specific characters from text. Left will display the number of characters from the start. Type=LEFT(K1, 3)and pressEnter, and you will get the first three characters.

However, if you type=RIGHT(K1, 4)and hitEnter, you will extract characters from the end. For MID, type=MID(K1, 2, 5)and pressEnterto get the middle characters starting from the specified position.

You can also use these functions for data parsing, like extracting area codes from phone numbers.

15. IF

The IF formula allows you to perform a logical test and gives different values based on the result.

For example, if you type=IF(L1 >= 60, “Pass”, “Fail”)and hitEnter, it will evaluate whether the value in L1 meets the threshold of 60, so it will return “Pass” if true and “Fail” if false.

It is usually used for decision-making scenarios like performance evaluations and grading systems.

16. DATEDIF

DATEDIF calculates the difference between two dates in specified units: years, months, and days. For example, if you type=DATEDIF(M1, N1, “Y”)and pressEnter, you will get the years between the dates mentioned in cells M1 and N1.The DATEDIF function is a time-saving tool that automates date calculations. Whether calculating ages, tracking project durations, or simplifying date comparisons for planning and reporting, this function can handle it all, saving you time and effort.

17. POWER

The POWER function raises a number to a certain power. For example, if you type=POWER(3, 2)and pressEnter, you will get a result of 9.

This function enables you to perform complex calculations efficiently and is essential in financial projections and mathematical and scientific calculations.

18. CEILING

The CEILING function rounds up the number in a cell to the nearest specified multiple. For example, if you type=CEILING(O1, 10)and hitEnter, the value or number in cell O1 will be rounded off to the nearest ten.

You can use it in financial calculations, such as estimating budgets or costs, as it accurately rounds off values and provides a clear understanding.

19. VLOOKUP

The VLOOKUP function looks for a value in the first column of a table and returns a related value from another column.

For example, if you type=VLOOKUP(B3, B2:E7,2, FALSE)and hitEnter, it will look for the value inB3within the first column of the rangeB2:E7and will show you the corresponding value from the second column.

YOu can use this function for data retrieval in large datasets, which makes accessing related information quickly, like prices or product details.

20. HLOOKUP

The HLOOKUP function works similarly to VLOOKUP, but it searches horizontally across the first row of a table. For example, if you type=HLOOKUP(Q1, A1:E5, 3, FALSE)and pressEnter, it will find the value in Q1 in the first row and show you the corresponding value from the third row.

You can use it for horizontal tables like survey results or tabulated metrics, as it will help you retrieve data easily.

21. TRIM

The Trim function cleans up text by removing extra spaces from a string. It deleted leading spaces (spaces before the first character, trailing spaces (Spaces after the last character), and extra spaces between words.

For example, if cell A1 has the text ” Hello World “, you can type=TRIM(A1)and pressEnterto get “Hello World”.

This function can be used if you are importing data from external sources, where there are spaces that can cause issues with data processing.

22. UPPER, LOWER, PROPER

The UPPER, LOWER, and PROPER are used to change the case of text in a cell. Upper changes the characters in a text string to uppercase. To do that, type=UPPER(R1)and hitEnter.

On the other hand, Lower changes all the characters to lowercase. To do that, type=LOWER(R1)and pressEnter.

The Proper function capitalizes the first letter of each word. To do so, type=PROPER(R1)and hitEnter. You can use these functions to ensure the text in your data is uniform, enhancing readability.

23. FLOOR

The FLOOR function rounds a number down to the nearest specified multiple. For example, if you type=FLOOR(O1, 5)and pressEnter, the value or number in cell O1 will be rounded off to the nearest five.

You can use this function in calculating discounts or at the time of budgeting. This function makes precise calculations, ensuring accuracy in financial reporting.

24. CONCATENATE

The CONCATENATE function allows you to join multiple text strings into a single string. For example, if you type =CONCATENATE(T1, ” “, U1) and hitEnter, it will combine the content of both T1 and U1 with a space in between.

This function is used in creating full names from first, middle and last names correctly. You can also use it to merge information from different cells into a single field.

25. LEN

The LEN function counts the number of characters, including spaces and punctuation, in a text string. For example, if you type=LEN(V1)and pressEnter, you will get the character count of the text in V1 cell.

You can use this function for data entry, content creation and more as it can help validate input lengths in forms, and also helps manage text efficiently

26. INDEX-MATCH

The INDEX-MATCH combo can improve data lookup capabilities and is considered an improved version of VLOOKUP.

INDEX displays the value of a cell in a selected row and column, while MATCH finds the value’s position within the selected range.

For example, if you type=INDEX(A1:A10, MATCH(W1, B1:B10, 0))and hitEnter, it will search for the value mentioned in V1 within B1 to B10, returning to a matching value from A1 to A10. This combination formula makes handling large datasets easy.

27. COUNTIF

The COUNTIF function counts the number of cells if they meet a specific criterion within the selected range. For example, if you type =COUNTIF(X1:X10, “>100”) and pressEnter, it will count the values in range X1 to X10 greater than 100.

You can use it to track sales above a target or count occurrences of specific conditions. It can provide important insights into datasets, which helps you make data-driven decisions wisely.

28. SUMIF

The SUMIF function adds up the values in cells under a specified condition within the selected range. For instance, if you type=SUMIF(Y1:Y10, “>100”, Z1:Z10)and hitEnter, it will add up the values in Z1 to Z10 that match with cells in Y1 to Y10 that are greater than 100.

You can use this function to calculate totals based on specific criteria and, therefore, be helpful in budgeting and reporting.

29. If-Else

The If-Else function in Excel allows the application of conditional logic in formulas. It works like the IF function but comes with multiple other conditions.

For example, you can use=IF(A1 > 90, “Excellent”, IF(A1 > 75, “Good”, “Needs Improvement”))to distinguish scores. This function can help you make complex decisions in data analysis and categorize data based on various scenarios.

30. If-Error

The IFERROR function allows you to capture and manage errors in formulas as it provides an alternate result when an error appears.For example, if you type=IFERROR(A1/B1, “Error in Calculation”)and pressEnter, wherein A1 is 10 while B1 is 0, it will result in an Error in Calculation as you can’t divide a number by zero.

This formula can help you avoid confusing error messages and enhance the robustness of data analysis, thereby improving the overall user experience.

Conclusion

These are the basic Excel formulas you can use to transform the way you analyze your data. As you become familiar with these functions, you can gain deeper insights into your data and make better decisions.

Ifyour Excel file is not opening when double-clickedit could be due to DDE settings; read this guide to learn other solutions. In case, you see theThere’s a Problem With This Formulamessage in Excel, it could be due to syntax, or incorrect system settings; read this guide to learn more.

Which Excel basic formula do you use the most? Share it with our readers in the comments section below. We would love to interact with you.

30 Excel Basic Formulas You Must Know#

What are the basic formulas you should know in Excel?#

Basic Formulas: A Quick Overview#

1. SUM#

2. AVERAGE#

3. SUBTOTAL#

4. MIN and MAX#

5. COUNT#

6. COUNTA#

7. REPLACE#

8. SUBSTITUTE#

9. NOW#

10. TODAY#

11. TIME()#

12. HOUR, MINUTE, SECOND#

13. MODULUS#

14. LEFT, RIGHT, MID#

15. IF#

16. DATEDIF#

17. POWER#

18. CEILING#

19. VLOOKUP#

20. HLOOKUP#

21. TRIM#

22. UPPER, LOWER, PROPER#

23. FLOOR#

24. CONCATENATE#

25. LEN#

26. INDEX-MATCH#

27. COUNTIF#

28. SUMIF#

29. If-Else#

30. If-Error#

Conclusion#