Essential 30 Basic Excel Formulas You Need to Master
Excel is a widely-used spreadsheet software that provides a variety of functionalities, appealing to students, business professionals, and anyone looking to organize data. Understanding fundamental Excel formulas is vital for improving your data analysis and management capabilities. This guide highlights 30 essential Excel formulas, covering operations such as calculations, data manipulation, and logical operations.
What are the basic formulas you should know in Excel?
Basic Formulas: A Quick Overview
| Formula | Syntax | Usage | Practical application |
| SUM | =SUM(number1, [number2], …) | Adds up the selected range of numbers | Useful for summing monthly expenses |
| AVERAGE | =AVERAGE(number1, [number2], …) | Calculates the average of a group of numbers | Determining the average score of a student |
| SUBTOTAL | =SUBTOTAL(function_num, ref1, …) | Calculates a subtotal in a list or database | Summarizing data in filtered lists |
| MIN | =MIN(number1, [number2], …) | Shows the smallest number in a range. | Tracking the lowest temperature over a month |
| MAX | =MAX(number1, [number2], …) | Shows the largest number in a range. | Tracking the highest temperature over a month |
| COUNT | =COUNT(value1, [value2], …) | Counts the number of cells that contain numbers | Tracking the number of sales transactions in a month |
| COUNTA | =COUNTA(value1, [value2], …) | Counts the number of non-empty cells. | Tracking completed tasks in a project |
| REPLACE | =REPLACE(old_text, start_num, num_chars, new_text) | Replaces part of a text string with another string. | Correcting a typo in product description |
| SUBSTITUTE | =SUBSTITUTE(text, old_text, new_text, [instance_num]) | Replacing occurrences of a specified text in a string. | Substituting old terms with new ones in large documents |
| NOW | =NOW() | Displays the current date and time | – |
| TODAY | =TODAY() | Shows the current date | Calculating the number of days remaining before a project submission |
| TIME | =TIME(hour, minute, second) | Shows time in hour, minute, and second formats. | Scheduling events by generating timestamps. |
| HOUR | =HOUR(serial_number) | Shows the hour from a time value. | Extracting the hour from timestamps |
| MINUTE | =MINUTE(serial_number) | Shows the minutes from a time value. | Checking time spent on various tasks. |
| SECOND | =SECOND(serial_number) | Shows the seconds from a time value. | Calculating the duration of video clips |
| MODULUS | =MOD(number, divisor) | Returns the remainder after division | Checking if a number is odd or even |
| LEFT | =LEFT(text, [num_chars]) | Shows the specified number of characters from the start of a text string. | Extracting area codes from phone numbers |
| RIGHT | =RIGHT(text, [num_chars]) | Shows the specified number of characters from the end of a text string. | Extracting the last four digits of a Social Security number |
| MID | =MID(text, start_num, num_chars) | Returns a specific number of characters from a text string, starting at a given position. | Extracting information from larger texts |
| IF | =IF(logical_test, value_if_true, value_if_false) | Performs a logical test and returns one value if TRUE and another if FALSE | Determining whether a student has passed or failed based on a set threshold |
| DATEDIF | =DATEDIF(start_date, end_date, unit) | Calculates the difference between two dates in defined units | Calculating age based on birthdate |
| POWER | =POWER(number, power) | Calculates a number raised to a specified power | Calculating compound interest |
| CEILING | =CEILING(number, significance) | Rounds a number up to the nearest specified multiple. | Estimating project costs by rounding up to the nearest dollar |
| FLOOR | =FLOOR(number, significance) | Rounds a number down to the nearest specified multiple | Adjusting values for budgeting |
| VLOOKUP | =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) | Looks for a value in the first column of a table and returns a value from the same row in a specified column. | Searching prices of products based on their IDs |
| HLOOKUP | =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) | Looks for a value in the first row of a table and returns a value from the same column in a specified row. | Fetching data from horizontal tables |
| TRIM | =TRIM(text) | Eliminates extra spaces from text, ensuring single space between words | Cleaning up data entries |
| UPPER | =UPPER(text) | Changes text to uppercase | Properly formatting names and codes |
| LOWER | =LOWER(text) | Changes text to lowercase | Formatting email addresses |
| PROPER | =PROPER(text) | Capitalizes the first letter of each word in a text string. | Formatting names in a list |
| CONCATENATE | =CONCATENATE(text1, [text2], …) | Combines multiple text strings into a single string | Merging first and last names into a full name |
| JUST | =LEN(text) | Counts the total number of characters in a text string. | Checking the length of inputs in forms |
| INDEX-MATCH | =INDEX(array, MATCH(lookup_value, lookup_array, [match_type])) | Uses INDEX and MATCH together to search for values in a table more flexibly than VLOOKUP | Efficient data retrieval in large datasets |
| COUNTIF | =COUNTIF(range, criteria) | Counts the number of cells that match a certain condition. | Tracking the number of sales above a specific amount. |
| SUMIF | =SUMIF(range, criteria, [sum_range]) | Sums values in a range that meet a certain condition | Calculating total sales of a product |
| IF-ELSE | =IF(condition, value_if_true, IF(condition2, value_if_true2, value_if_false)) | IF statements to assess multiple conditions | Classifying performance ratings based on multiple criteria |
| IFERROR | =IFERROR(value, value_if_error) | Returns a specified value if the formula shows an error; otherwise, it returns the formula result. | Managing errors in calculations during data processing |
If your Excel formulas disappear after saving your workbook, it may be due to overly complex formulas that exceed memory capacity; refer to this guide for potential solutions.
1. SUM
The SUM function is a fundamental Excel formula used to add a series of numbers together. You can easily calculate totals from the selected range. For instance, to find the total sales from cells B2 to B13 as shown in the picture, simply type =SUM(B2:B13) and press Enter. This will sum all values within the selected range. The efficiency of the SUM function allows it to handle large datasets and simple totals, ensuring quick and accurate numerical analysis.
If the SUM formula fails to give correct totals, it could result from incorrect cell references, hidden rows or columns, or cells containing text or errors; refer to this guide for more information.
2. AVERAGE
The AVERAGE function calculates the mean of selected numbers, providing insights into overall trends and serving as a measure of performance.
To find the average sales amount in cells B2 to B13, use AVERAGE(B2:B13) and press Enter. This function sums up the values in the selected range and divides by the count of numbers to calculate the average, which can be beneficial for financial forecasting and performance reviews.
3. SUBTOTAL
The SUBTOTAL function is useful for calculating aggregate values in a filtered database. It only computes relevant entries while excluding hidden data. Unlike SUM functions, SUBTOTAL does not count hidden rows, making it ideal for summarizing visible data.
For example, using =SUBTOTAL(9, B2:B13) and hitting Enter will give the total of the visible values in that range. The number 9 indicates the SUM function, so it should not be altered. This function is applicable to pivot tables and filtered lists.
4. MIN and MAX
The MIN and MAX functions are designed to identify the smallest and largest values in a specified range. Typing =MIN(B2:B13) and pressing Enter yields the smallest value from that range, while =MAX(B2:B13) produces the largest value.
These functions not only encapsulate theoretical concepts but also provide practical tools for determining 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 enhance your data analysis capabilities.
5. COUNT
The COUNT function is used to count the number of cells containing numeric values within a specified range. For instance, typing =COUNT(B2:B13) and pressing Enter will yield the total number of cells in that range with numeric entries.
This basic Excel formula is beneficial for statistical analysis, allowing you to ascertain how many data points are numeric. It also aids in data validation, ensuring datasets are complete, and simplifying data management.
6. COUNTA
COUNTA is a formula that counts all non-empty cells in a defined area, regardless of their content being text, numbers, or errors.
For example, =COUNTA(A1:C13) provides the count of cells with entries in that range, assisting in tracking progress in datasets, such as counting completed tasks. Its ability to reveal data utilization insights makes it a crucial tool in data analysis.
7. REPLACE
The REPLACE function allows the modification of a portion of a text string based on specified parameters. For example, using =REPLACE(B1, 1, 6, “AMT”) and pressing Enter will replace the first six characters in cell A1 with AMT starting from the first character.
This function is valuable for correcting errors or updating specific text segments, like typos, enhancing data management, and improving text processing efficiency.
8. SUBSTITUTE
SUBSTITUTE replaces specific occurrences of a text string within another string, giving you precise control over text modifications.
For instance, using =SUBSTITUTE(B1, “Amount” , “AMT”) and pressing Enter will replace the term Amount with AMT found in cell B1. This formula facilitates changes in numerous locations within a document and aids in ensuring clarity in your data by replacing outdated terms or repeated errors.
9. NOW
The NOW function displays the current date and time, which is useful for time-stamping data entries or monitoring changes.
To obtain the exact date and time, type =NOW() and press Enter. This is a dynamic function, updating the date and time each time the spreadsheet recalculates, making it beneficial for project management, scheduling, and real-time event tracking.
10. TODAY
The TODAY function provides the current date without the specific time, which can be ideal for managing date-based timelines.
To obtain today’s date, simply type =TODAY() and press Enter. This formula is useful in establishing deadlines and project time tracking, as it automatically updates when the spreadsheet is opened, ensuring reports and plans remain current.
11. TIME()
The TIME function returns a time value based on specified hour, minute, and second inputs.
For example, typing TIME(14, 30, 0) and pressing Enter results in 2:30 PM. This function is beneficial for event scheduling and calculating time intervals between tasks, promoting better organization and time management.
12. HOUR, MINUTE, SECOND
The HOUR, MINUTE, and SECOND functions extract the respective components from a time value. For example, typing =HOUR(L1) and hitting Enter will yield the hour from the time in cell L1. Similarly, =MINUTE(L1) or =SECOND(L1) provides the minutes and seconds from the specified cell.
These basic Excel formulas can be utilized to analyze time and break down time data for detailed reports, including time management for tasks.
13. MODULUS
The MODULUS function calculates the remainder after division, which is helpful for mathematical checks. For instance, typing =MOD(J1, 2) and pressing Enter will determine whether the value in cell J1 is even or odd (0 indicates even). This function can categorize data and facilitate periodic tasks, making complex calculations simpler and expediting processes.
14. LEFT, RIGHT, MID
The LEFT, RIGHT, and MID functions display specific characters from text. The LEFT function will show a specified number of characters from the start. For example, typing =LEFT(K1, 3) and pressing Enter will return the first three characters.
Typing =RIGHT(K1, 4) will extract characters from the end, and =MID(K1, 2, 5) retrieves middle characters starting from a specified position.
These functions are useful for data parsing tasks, such as extracting area codes from phone numbers.
15. IF
The IF function performs a logical test and returns different values based on the result.
For example, typing =IF(L1 >= 60, “Pass” , “Fail”) will evaluate whether the value in L1 meets the threshold of 60, returning “Pass” if true and “Fail” if false.
This formula is commonly used for decision-making scenarios like performance evaluations and grading systems.
16. DATEDIF
DATEDIF calculates the difference between two dates in specified units like years, months, and days. For instance, typing =DATEDIF(M1, N1, “Y”) will yield the number of years between the dates specified in cells M1 and N1. This function automates date calculations, saving time.
17. POWER
The POWER function raises a number to a specified power. For example, using =POWER(3, 2) yields a result of 9.
This function enables efficient execution of complex calculations, essential for financial projections and scientific or mathematical computations.
18. CEILING
The CEILING function rounds a number in a cell up to the nearest specified multiple. For instance, typing =CEILING(O1, 10) and pressing Enter will round the value in cell O1 to the nearest ten.
This calculation is particularly useful for estimating budgets or costs, delivering accurate rounded values for enhanced clarity.
19. VLOOKUP
The VLOOKUP function searches for a value in the first column of a table and returns a corresponding value from another column.
For instance, typing =VLOOKUP(B3, B2:E7,2, FALSE) will look for the value in B3 within the first column of the range B2:E7 and display the related value from the second column.
This function facilitates data retrieval in large datasets, enabling swift access to related information such as prices or product details.
20. HLOOKUP
The HLOOKUP function operates similarly to VLOOKUP, but it searches horizontally across the first row of a table. Typing =HLOOKUP(Q1, A1:E5, 3, FALSE) and pressing Enter will locate the value in Q1 in the first row and return the corresponding value from the third row.
This function can be leveraged for horizontal tables containing survey results or tabulated metrics, making data retrieval straightforward.
21. TRIM
The TRIM function cleans up text by removing additional spaces from a string, deleting leading spaces (those before the first character), trailing spaces (those after the last character), and extra spaces between words.
For instance, if cell A1 contains ” Hello World “, you can type =TRIM(A1) and press Enter to achieve “Hello World” .
This function becomes crucial when importing data from external sources, where excessive spaces can cause issues during data processing.
22. UPPER, LOWER, PROPER
The UPPER, LOWER, and PROPER functions allow for changing the text case in a cell. The UPPER function modifies all characters in a text string to uppercase. To apply it, type =UPPER(R1) and press Enter.
Similarly, the LOWER function converts all characters to lowercase. To implement this, type =LOWER(R1) and press Enter.
The PROPER function capitalizes the first letter of each word. To execute this, type =PROPER(R1) and hit Enter. Utilizing these functions ensures uniform text presentation, enhancing readability.
23. FLOOR
The FLOOR function rounds a number down to the nearest specified multiple. For example, typing =FLOOR(O1, 5) and pressing Enter will round the value in cell O1 to the nearest five.
This function is particularly applicable in discount calculations or budgeting scenarios, ensuring accuracy in financial reporting.
24. CONCATENATE
The CONCATENATE function joins multiple text strings into one single string. For instance, typing =CONCATENATE(T1, ” “, U1) and hitting Enter will merge the contents of T1 and U1 with a space in between.
This function is often employed to create full names from first, middle, and last names, as well as to combine information from various cells into a single field.
25. ONLY
The LEN function calculates the number of characters, including spaces and punctuation, in a text string. For example, typing =LEN(V1) and pressing Enter provides a character count of the text in cell V1.
This function is useful for data entry, content creation, and more, validating input lengths in forms while effectively managing text.
26. INDEX-MATCH
The INDEX-MATCH combination enhances data lookup capabilities and is considered an improved alternative to VLOOKUP.
INDEX retrieves the value of a cell from a selected row and column, while MATCH finds the position of a value within the chosen range.
For instance, by typing =INDEX(A1:A10, MATCH(W1, B1:B10, 0)) and pressing Enter, Excel searches for the value in W1 within B1 to B10, returning the corresponding value from A1 to A10. This combination formula simplifies working with extensive datasets.
27. COUNTIF
The COUNTIF function counts the number of cells that meet a specified criterion within a given range. For example, typing =COUNTIF(X1:X10, “>100”) and pressing Enter counts the values in the range X1 to X10 that exceed 100.
This function aids in tracking sales that surpass a target and counting occurrences of specific conditions, providing valuable insights into datasets for more informed decision-making.
28. SUMIF
The SUMIF function aggregates the values in cells based on a specified condition within a chosen range. For instance, typing =SUMIF(Y1:Y10, “>100” , Z1:Z10) and pressing Enter adds the values in Z1 to Z10 corresponding to cells in Y1 to Y10 that are greater than 100.
This function proves invaluable for budgeting and reporting, allowing the calculation of totals under specific criteria.
29. If-Else
The If-Else function in Excel applies conditional logic within formulas, functioning similarly to the IF function but accommodating multiple conditions.
For example, you can use =IF(A1 > 90, “Excellent” , IF(A1 > 75, “Good” , “Needs Improvement”)) to categorize scores. This function facilitates complex decisions in data analysis and categorizes data across various scenarios.
30. If-Error
The IFERROR function captures and manages errors in formulas, returning an alternate result when an error occurs. For instance, typing =IFERROR(A1/B1, “Error in Calculation”) and pressing Enter (where A1 is 10 and B1 is 0) will yield “Error in Calculation” since division by zero is not permissible.
This formula helps avoid confusing error messages, enhancing the robustness of data analysis and improving the overall user experience.
Conclusion
These basic Excel formulas can revolutionize how you analyze data. As you become proficient with these functions, you will gain deeper insights into your data, leading to more informed decisions.
If you find your Excel file not opening when double-clicked, it could be related to DDE settings; refer to this guide to explore other solutions. If you encounter the “There’s a Problem With This Formula” message in Excel, it may stem from syntax errors or incorrect system settings; read this guide for further assistance.
Which Excel formula do you frequently use? Share your experience with our readers in the comments section below. We would love to engage with you.
Leave a Reply