Skip to main content
HomeTutorialsSpreadsheets

The 15 Basic Excel Formulas Everyone Needs to Know

Learn how to add arithmetic, string, time series, and complex formulas in Microsoft Excel.
Updated Sep 22, 2024  · 15 min read

What is an Excel Formula?

Microsoft Excel is a popular tool for managing data and performing data analysis. It is used for generating analytical reports, business insights, and storing operational records. To perform simple calculations or analyses on data, we need Excel formulas.

Even simple Excel formulas allow us to manipulate string, number, and date data fields. Furthermore, you can use if-else statements, find and replace, mathematics and trigonometry, finance, logical, and engineering formulas.

Unlike programming languages, you will be writing the formula name and arguments. That’s it, nothing complex. You can also use Excel-assisted user interference to add formulas.

The basic formulas in Excel are SUM(), MIN()MAX(), AVERAGE(), COUNT(), POWER(), CEILING(), FLOOR(), CONCAT(), TRIM(), REPLACE(), SUBSTITUTE(), LEFT(), RIGHT(), MID(), UPPER(), LOWER(), PROPER(), NOW(), TODAY(), DATEDIF(), VLOOKUP(), HLOOKUP(), and IF(). See below for guidance on each of these.

Learn Excel Fundamentals

Gain skills to use Excel effectively—no experience required.
Start Learning for Free

Explore essential Excel Formulas with hands-on exercises in our Introduction to Excel course.

Basic Excel Formulas

Why Are Excel Formulas Important?

Excel formulas are essential for several reasons:

  1. Efficiency: They automate repetitive tasks, saving time and reducing manual errors.
  2. Data analysis: Excel's range of formulas enables sophisticated data analysis, crucial for informed decision-making.
  3. Accuracy: Formulas ensure consistent and accurate results, essential in fields like finance and accounting.
  4. Data manipulation: They allow for efficient sorting, filtering, and manipulation of large datasets.
  5. Accessibility: Excel provides a user-friendly platform, making complex data analysis accessible to non-technical users.
  6. Versatility: Widely used across various industries, proficiency in Excel formulas enhances employability and career advancement.
  7. Customization: Excel offers customizable formula options to meet specific data handling needs.

In essence, Excel formulas are a foundational tool for effective data management, analysis, and decision-making.

Advance Your Career with Excel

Gain the skills to maximize Excel—no experience required.

Start Today for Free

How to Use Excel Formulas

Adding the Excel formula is relatively easy. It will come to you naturally if you are familiar with any business intelligence software.  

The most effective and fast way to use formulas is by adding them manually. In the example below, we are calculating the BMI (Body Mass Index) of the athletes shown in the table.

BMI = weight (KG)/ (Height (m))2 

  1. Choose the cell for the resulting output. You can use the mouse to select the cell or use the arrow key to navigate. 
  2. Type “=” in the cell. The equal sign will appear in the cell and formula bar. 
  3. Type the address of the cell that we want to use for our calculation. In our case, it is E2 (weight/KG).
  4. Add divide sign “/
  5. To convert height from centimeters to a meter, we will divide the D2 by 100.
  6. Take the squared “^2” of the height and press Enter. 

Note: To get the address of any cell, you need to look at the column name (A, B, C, … ) and combine it with a row number (1, 2, 3, …). For example, A2, B5, and C12   

That’s it; we have successfully calculated the BMI of A Dijiang.

Adding Excel formulas

Adding Excel formula | Author

We can also add the Excel formula by using assisted GUI. It is simple. 

In the example below, we will be using GUI to add an IF formula to convert ‘M’ to ‘Male’ and ‘F’ to Female.

  1. Click on the fx button next to the formula bar.
  2. It will pop up in the window with the most used function.
  3. You can either search for the specific formula or select the formula by scrolling. In our case, we will be specifying the IF formula. 
  4. Add the logic B2=’M” into the logical_test argument.
  5. Add “Male” in value_if_true argument and “Female” in value_if_false argument. 

The formula works similarly to the if else statement. If the logical_test statement is TRUE, the formula will return “Male” otherwise “Female.”

Excel formula using U

Excel formula using UI | Author

How to insert formulas in excel for an entire column

We have learned to add the formula to a single row. Now, we will learn to apply the same formula to the entire column. 

There are multiple ways to add formulas: 

  • Dragging down the fill handle: when you select the cell, you will see the small green box at the bottom right. It is called a fill handle. Click and hold the fill handle and drag it down to the last row. It is commonly used to apply formulas to selected rows. 
  • Double click the fill handle: select the cell with the formula and double click the fill handle. Within seconds it will apply the formula to the entire column. 
  • Shortcut: select the cell with the formula and the empty cells below it. Press CTRL + D to apply the formula. Make sure you are not selecting anything above the formula cell. 
  • Copy-pasting: copy the cell with the formula (CTRL + C), select the empty rows in a column, and paste it (CTRL + V). Make sure you are not using a fill handle to select the rows. 

The visual representation below shows all the ways we can apply the formula to multiple cells. 

Formula for an entire column

Formula for an entire column | Author

Basic Formulas in Excel 

Now that we know how to use Excel formulas, it’s time to learn about 15 basic Excel formulas on a small subset of the Olympics dataset from DataCamp. To keep things simple, we will mainly use the name, sex, age, height, and weight columns of four athletes' records. 

1. SUM

The SUM() formula performs addition on selected cells. It works on cells containing numerical values and requires two or more cells.  

In our case, we will be applying the SUM formula to a range of cells from C2 to C5 and storing the result on C6. It will add 24, 23, 21, and 31. You can also apply this formula to multiple columns.

=SUM(C2:C5)

Excel SUM

2. MIN and MAX

The MIN() formula requires a range of cells, and it returns the minimum value. For example, we want to display the minimum weight among all athletes on the E6 cell. The MIN formula will search for the minimum value and show 60.

=MIN(E2:E5)

Excel MIN

The MAX() formula is the opposite of MIN(). It will return the maximum value from the selected range of cells. The formula will look for the maximum value and return 82. 

=MAX(E2:E5)

Excel MAX

3. AVERAGE

The AVERAGE() formula calculates the average of selected cells. You can provide a range of cells (C2:C5) or select individual cells (C2, C3, C5).

To calculate the average of athletes, we will select the age column, apply the average formula, and return the result to the C7 cell. It will sum up the total values in the selected cells and divide them by 4.

=AVERAGE(C2:C5) 

Excel AVERAGE

4. COUNT

The COUNT() formula counts the total number of selected cells. It will not count the blank cells and different data formats other than numeric. 

We will count the total number of athlete weights, and it will return 4, as we don’t have missing values or strings. 

=COUNT(E2:E5)

Excel COUNT

To count all types of cells (date-time, string, numerical), you need to use the COUNTA() formula. 

The COUNTA() formula does not count missing values. For blank cells, use COUNTBLANK().

5. POWER

In the beginning, we learned to add power using “^”, which is not an efficient way of applying power to a cell. Instead, we recommended using the POWER() formula to square, cube, or apply any raise to power to your cell. 

In our case, we have divided D2 by 100 to get height in meters and squared it by using the POWER formula with the second argument as 2.   

=POWER(D2/100,2)

Excel POWER

6. CEILING and FLOOR

The CEILING() formula rounds a number up to the nearest given multiple. In our case, we will round 3.24 up to a multiple of 1 and get 4. If the multiple is 5, it will round up the number 3.24 to 5.  

=CEILING(F2,1)

Excel CEILING

The FLOOR() rounds a number down to the nearest given multiple. As we can see in the image below, instead of converting 3.24 to 4, it has rounded the number to 3. 

=FLOOR(F2,1)

Excel FLOOR

7. CONCAT

The CONCAT() Excel formula joins or merges multiple strings or cells with strings into one. For example, if we want to join the age and sex of the athletes, we will use CONCAT. The formula will automatically convert a numeric value from age to string and combine it. 

“24”+“M” = “24M”

=CONCAT(C2,B2)

Excel CONCATENATE

8. TRIM

TRIM() is used to remove extra spaces from the start, middle, and end. It is commonly used to identify duplicate values in cells, and for some reason, extra space makes it unique.  

For example:

  1. There are extra two spaces at A3 “A   Lamusi”, and it has been successfully removed by TRIM.
  2. At A4 “ Christie Jacoba Aaftink”, there is extra space at the start, and without writing any complex formula, the TRIM has removed it. 
=TRIM(A4)

Excel TRIM

9. REPLACE and SUBSTITUTE

REPLACE() is used for replacing part of the string with a new string.

REPLACE(old_text, start_num, num_chars, new_text) 

  • old_text is the original text or cell containing the text.
  • start_num is the index position that you want to start replacing the character. 
  • num_chars refers to the number of characters you want to replace.
  • new_text indicates the new text that you want to replace with old text. 

For example, we will change A Dijiang with B Dijiang by providing the positing of character, which is 1, the number of characters that we want to replace, which is also 1, and the new character “B”. 

=REPLACE(A2,1,1,"B")

Excel REPLACE

The SUBSTITUTE() formula is similar to REPLACE(). Instead of providing the location of a character or the number of characters, we will only provide old text and new text. 

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

In our case, we are replacing "Jacoba" with "Rahim" to display the result on A4 cell “Christine Rahim Aaftink.”

This formula is quite useful as it does not change the text without “Jacoba” as shown below in cell A5, “Per Knut Aaland.” Whereas, REPLACE will replace the text every time. 

=SUBSTITUTE(A4,"Jacoba","Rahim")

Excel SUBSTITUTE

10. LEFT, RIGHT, and MID

The LEFT() formula returns the number of characters from the start of the string or text. 

For example, to display the first name from the text “Christine Jacoba Aaftink”, you will use LEFT with 9  numbers of characters. As a result, it will show the first nine characters; “Christine.”

=LEFT(A2,9)

Excel LEFT

The MID() formula requires starting position and length to extract the characters from the middle. 

For example, if you want to display a middle name, you will start with “J” which is at the 11th position, and 6 for the length of the middle name “Jacoba”.

=MID(A2,11,6)

Excel MID

The RIGHT() formula will return the number of characters from the end. You just need to provide a number of characters. 

For example, to display the last name “Aaftink,” we will use RIGHT with seven characters. 

=RIGHT(A2,7)

Excel RIGHT

11. UPPER, LOWER, and PROPER

The UPPER(), LOWER(), and PROPER() formulas are basic string operations. You can find similar in Tableau or in Python. These formulas only require a text, the location of the cell containing string, or the range of cells with string. 

UPPER() will convert all the letters in the text to uppercase. 

=UPPER(A1:F1)

Excel UPPER

LOWER() will convert the selected text lower case.

=LOWER(A1:F1)

Excel LOWER

PROPER() will convert the string to the proper case. For example, the first letter in each word will be capitalized, and the rest of them will be lowercase. 

=PROPER(A1:F1)

Excel PROPER

12. NOW and TODAY

NOW() returns the current time and date, and TODAY() returns only the current date. These are quite simple, and we will use them to extract a day, month, year, hours, and minutes from any date time data cell. 

The example below returns the current date and time.

=NOW()

Excel NOW.

To extract the seconds from the time, you will use the SECOND() formula. 

=SECOND(NOW())

Excel SECOND

Similarly, TODAY() will return only the current date. 

=TODAY()

Excel TODAY

To extract the day, you will use the DAY() formula.

Furthermore, you can extract month, year, weekday, day names, hours, and minutes from the date time data field. 

=DAY(TODAY())

Excel DAY TODAY

13. DATEDIF

It is the most used formula for time series data sets. The DATEDIF() calculates the difference between two dates and returns the number of days, months, weeks, or years based on your preference. 

In the example below, we want to return the date difference in days by providing “d” for unit arguments. Make sure that the first argument is the start date and the second argument in the formula is the end date. 

start_date < end_date

=DATEDIF(A2,B2,"d")

Excel DATEDIF

14. VLOOKUP and HLOOKUP

The worksheet1 that we will use in this section contains all the data from the Olympics dataset.

Excel WORKSHEET1

worksheet1

The VLOOKUP() formula searches for the value in the leftmost column of the table array and returns the value from the same row from the specified columns. 

VLOOKUP(lookup_value, table_array, col_index, range_lookup)

  • lookup_value: the value you are looking for that is present in the first column. 
  • table_array: the range of the table, worksheet, or selected cell with multiple columns. 
  • col_index: the position of the column to extract the value.
  • range_lookup: “True” is used for the approximate match (default), and “FALSE” is used for the exact match.

In our case, we are looking for A Dijiang (A2) from selected columns and rows of worksheet1 (B2:H20). The VLOOKUP formula will check the name column in worksheet one and return the 6th column value that is team “China”.

=VLOOKUP(A2,worksheet1!B2:H20,6,FALSE)

Excel VLOOKUP

The HLOOKUP() searches for the value in the first row instead of the first column. It returns the value from the same column and the row you specified. 

HLOOKUP(lookup_value, table_array, row_index, range_lookup)

In our case, we will display A Dijaing’s sex on the D8 cell. The HLOOKUP formula will look for the name in the first row and return the value “M'' from the 2nd row of the same column. The range_lookup is kept FALSE in both cases for the exact match. 

=HLOOKUP(B1,B1;E5,2,FALSE)

Excel HLOOKUP

15. IF

The IF() Excel formula is straightforward. It is similar to an if-else statement in a programming language. We will provide the logic of the formula. If the logic is correct, it will return a certain value; if the logic is False, it will return a different value. 

For example, if the BMI of athletics is less than 23.9, the formula will return the string “Fit”, else “Unfit”. It is quite useful to convert numerical values into categories. 

=IF(G2<24.9,"Fit,"Unfit")

Excel IF

Upskilling Your Team in Excel

We've covered the Excel formulas that everyone needs to know, but upskilling multiple people in this area isn't always straightforward. We have a separate guide that covers corporate Excel training in depth, but it's worth mentioning here.

We've established that Excel is more than just a spreadsheet tool—it's often essential for effective data management, financial analysis, and decision-making. As such, investing in Excel training can boost your team's productivity, accuracy, and overall efficiency. Key benefits include:

  • Data analysis: Advanced Excel skills enable more insightful data interpretation, aiding better decision-making.
  • Efficiency: Automation through spreadsheets can reduce manual work and errors.
  • Financial precision: Mastery of functions like VLOOKUP and pivot tables enhances financial management.
  • Operational streamlining: Excel's data manipulation tools can improve the handling of large datasets, optimizing operations.
  • Competitive edge: Proficiency in Excel provides a significant advantage in refining processes and gaining insights.

If you're looking to train your team in Excel skills, from the basics to more advanced formulas, DataCamp For Business is the ideal tool. Whether your team is 2 people or 10,000 people, DataCamp For Business offers a host of dedicated Excel and spreadsheet learning materials, the ability to create custom tracks to scale and tailor your learning experience, and detailed reporting and insights to measure the impact of your training. Request a demo today to get started. 

Conclusion

Microsoft Excel is a powerful and easy-to-use application for data analysis and reporting. In this post, we have learned the importance of basic Excel formulas and how they provide us extra ability to perform complex calculations. Furthermore, we have learned about various ways of adding formulas to worksheets and looked in detail at the essential basic Excel formulas. 

To learn more about Excel and how to use these formulas for data analysis, take our Data Analysis in Excel course. It will teach you the basics of data exploration, processing, and analysis. If you are fond of the Google stack and use Google Sheets, try Data Analysis in Spreadsheet.

You can also take another step forward by learning the fundamentals of a spreadsheet. You will learn about data analysis, advanced data manipulation techniques, pivot tables, and data visualization. Finally, you can check out our Excel Formulas cheat sheet for a quick reference guide and learn more about data types in Excel

Get certified in your dream Data Analyst role

Our certification programs help you stand out and prove your skills are job-ready to potential employers.

Timeline mobile.png

Excel Formula FAQs

What is the easiest way to learn Excel formulas?

The easiest way to learn Excel formulas is by practicing the examples given in this article with real-world examples. Start with basic formulas and gradually progress to more complex ones. DataCamp tutorials, courses, and cheat sheets can also be helpful resources.

Can Excel formulas be used for large datasets?

Yes, Excel formulas are well-equipped to handle large datasets. They can perform calculations on thousands of rows of data efficiently. However, for extremely large datasets, performance might vary depending on your system's capabilities.

How do I debug an error in an Excel formula?

To debug an error, first check for common issues like incorrect cell references or syntax errors. Excel's built-in 'Error Checking' tool can also help identify and fix errors in formulas.

Are Excel formulas the same in all versions of Excel?

Basic Excel formulas remain consistent across different versions. However, newer versions may have additional functions and improved features.

Can Excel formulas replace manual data analysis methods?

Excel formulas can significantly reduce the need for manual calculations, but they complement rather than replace manual data analysis. They are tools to enhance efficiency and accuracy.

What are some best practices for using Excel formulas?

  • Use relative and absolute cell references appropriately. Use $ in cell references when you want to keep them fixed as you copy formulas.
  • Keep formulas simple. Break down complex formulas into smaller steps.
  • Document your formulas. Use comments to explain complex logic.
  • Test as you build. After entering a formula, check if it returns the expected result before moving on.

How do I apply formulas to entire columns or rows in Excel?

You can apply formulas to entire columns or rows by:

  1. Dragging the fill handle. Select the formula cell and drag the small square in the bottom-right corner to the desired range.
  2. Using shortcuts. Select the formula cell, press CTRL + D to copy it down the column, or CTRL + R to fill it across rows.
  3. Double-clicking the fill handle. This quickly applies the formula to all cells in a column when data is present in the adjacent column.

Can I nest Excel formulas?

Yes, you can nest Excel formulas by placing one function inside another. For example, you can nest IF within VLOOKUP to handle specific conditions. When nesting, be sure to match parentheses correctly to avoid errors. For example:

=IF(VLOOKUP(A2, B2:D10, 3, FALSE) > 50, "Pass", "Fail")

This checks the result of the VLOOKUP and applies a condition.

How can I protect formulas in Excel?

To protect your formulas:

  1. Select the cells containing formulas.
  2. Right-click and select Format Cells > Protection and check the box for Locked.
  3. Then, go to Review > Protect Sheet, choose the protection options, and set a password. This prevents others from editing the formulas while allowing them to interact with other cells as needed.

Photo of Abid Ali Awan
Author
Abid Ali Awan
LinkedIn
Twitter

As a certified data scientist, I am passionate about leveraging cutting-edge technology to create innovative machine learning applications. With a strong background in speech recognition, data analysis and reporting, MLOps, conversational AI, and NLP, I have honed my skills in developing intelligent systems that can make a real impact. In addition to my technical expertise, I am also a skilled communicator with a talent for distilling complex concepts into clear and concise language. As a result, I have become a sought-after blogger on data science, sharing my insights and experiences with a growing community of fellow data professionals. Currently, I am focusing on content creation and editing, working with large language models to develop powerful and engaging content that can help businesses and individuals alike make the most of their data.

Topics

Excel Courses

Course

Introduction to Excel

4 hr
75.3K
Master the Excel basics and learn to use this spreadsheet tool to conduct impactful analysis.
See DetailsRight Arrow
Start Course
See MoreRight Arrow
Related

cheat-sheet

Excel Formulas Cheat Sheet

Learn the basics of Excel with our quick and easy cheat sheet. Have the basics of formulas, operators, math functions and more at your fingertips.
Richie Cotton's photo

Richie Cotton

18 min

tutorial

How to Add, Subtract, Divide and Multiply in Spreadsheets

Learn how to apply operations like add, subtract, divide, multiply, and a lot more in Google Spreadsheets with the help of an actual dataset.
Aditya Sharma's photo

Aditya Sharma

9 min

tutorial

Conditional Formatting in Excel: A Beginner’s Guide

Explore conditional formatting in Excel with simple to advanced examples and their best practices.
Joleen Bothma's photo

Joleen Bothma

7 min

tutorial

Conditional Functions in Spreadsheets

Learn when and how to use conditional functions in spreadsheets.
Francisco Javier Carrera Arias's photo

Francisco Javier Carrera Arias

11 min

tutorial

Time Series Analysis with Spreadsheets Tutorial

In this tutorial, you'll learn basic time-series concepts and basic methods for forecasting time series data using spreadsheets.
Avinash Navlani's photo

Avinash Navlani

9 min

tutorial

Getting Started with Spreadsheets

This tutorial will give you a basic understanding of the terminology in spreadsheets along with learning how to create a basic table.
Ryan Sheehy's photo

Ryan Sheehy

5 min

See MoreSee More