Basic Excel formulas & functions with examples

The tutorial provides a list of Excel basic formulas and functions with examples and links to related in-depth tutorials.

Being primarily designed as a spreadsheet program, Microsoft Excel is extremely powerful and versatile when it comes to calculating numbers or solving math and engineering problems. It enables you to total or average a column of numbers in the blink of an eye. Apart from that, you can compute a compound interest and weighted average, get the optimal budget for your advertising campaign, minimize the shipment costs or make the optimal work schedule for your employees. All this is done by entering formulas in cells.

This tutorial aims to teach you the essentials of Excel functions and show how to use basic formulas in Excel.

The basics of Excel formulas

Before providing the basic Excel formulas list, let's define the key terms just to make sure we are on the same page. So, what do we call an Excel formula and Excel function?

  • Formula is an expression that calculates values in a cell or in a range of cells.

    For example, =A2+A2+A3+A4 is a formula that adds up the values in cells A2 through A4.

  • Function is a predefined formula already available in Excel. Functions perform specific calculations in a particular order based on the specified values, called arguments, or parameters.

For example, instead of specifying each value to be summed like in the above formula, you can use the SUM function to add up a range of cells: =SUM(A2:A4)

You can find all available Excel functions in the Function Library on the Formulas tab:
Basic Excel functions

There exist 400+ functions in Excel, and the number is growing by version to version.  Of course, it's next to impossible to memorize all of them, and you actually don't need to. The Function Wizard will help you find the function best suited for a particular task, while the Excel Formula Intellisense will prompt the function's syntax and arguments as soon as you type the function's name preceded by an equal sign in a cell:

Excel Formula Intellisense

Clicking the function's name will turn it into a blue hyperlink, which will open the Help topic for that function.

Tip. You don't necessarily have to type a function name in all caps, Microsoft Excel will automatically capitalize it once you finish typing the formula and press the Enter key to complete it.

10 Excel basic functions you should definitely know

What follows below is a list of 10 simple yet really helpful functions that are a necessary skill for everyone who wishes to turn from an Excel novice to an Excel professional.

SUM

The first Excel function you should be familiar with is the one that performs the basic arithmetic operation of addition:

SUM(number1, [number2], …)

In the syntax of all Excel functions, an argument enclosed in [square brackets] is optional, other arguments are required. Meaning, your Sum formula should include at least 1 number, reference to a cell or a range of cells. For example:

=SUM(B2:B6) - adds up values in cells B2 through B6.

=SUM(B2, B6) - adds up values in cells B2 and B6.

If necessary, you can perform other calculations within a single formula, for example, add up values in cells B2 through B6, and then divide the sum by 5:

=SUM(B2:B6)/5

To sum with conditions, use the SUMIF function: in the 1st argument, you enter the range of cells to be tested against the criteria (A2:A6), in the 2nd argument - the criteria itself (D2), and in the last argument - the cells to sum (B2:B6):

=SUMIF(A2:A6, D2, B2:B6)

In your Excel worksheets, the formulas may look something similar to this:
Using SUM formulas in Excel

Tip. The fastest way to sum a column or row of numbers is to select a cell next to the numbers you want to sum (the cell immediately below the last value in the column or to the right of the last number in the row), and click the AutoSum button on the Home tab, in the Formats group. Excel will insert a SUM formula for you automatically.

Useful resources:

AVERAGE

The Excel AVERAGE function does exactly what its name suggests, i.e. finds an average, or arithmetic mean, of numbers. Its syntax is similar to SUM's:

AVERAGE(number1, [number2], …)

Having a closer look at the formula from the previous section (=SUM(B2:B6)/5), what does it actually do? Sums values in cells B2 through B6, and then divides the result by 5. And what do you call adding up a group of numbers and then dividing the sum by the count of those numbers? Yep, an average!

The Excel AVERAGE function performs these calculations behind the scenes. So, instead of dividing sum by count, you can simply put this formula in a cell:

=AVERAGE(B2:B6)

To average cells based on condition, use the following AVERAGEIF formula, where A2:A6 is the criteria range, D3 is he criteria, and B2:B6 are the cells to average:

=AVERAGEIF(A2:A6, D3, B2:B6)

Using an Average formula in Excel

Useful resources:

MAX & MIN

The MAX and MIN formulas in Excel get the largest and smallest value in a set of numbers, respectively. For our sample data set, the formulas will be as simple as:

=MAX(B2:B6)

=MIN(B2:B6)

Using MIN and MAX formulas in Excel

COUNT & COUNTA

If you are curious to know how many cells in a given range contain numeric values (numbers or dates), don't waste your time counting them by hand. The Excel COUNT function will bring you the count in a heartbeat:

COUNT(value1, [value2], …)

While the COUNT function deals only with those cells that contain numbers, the COUNTA function counts all cells that are not blank, whether they contain numbers, dates, times, text, logical values of TRUE and FALSE, errors or empty text strings (""):

COUNTA (value1, [value2], …)

For example, to find out how many cells in column B contain numbers, use this formula:

=COUNT(B:B)

To count all non-empty cells in column B, go with this one:

=COUNTA(B:B)

In both formulas, you use the so-called "whole column reference" (B:B) that refers to all the cells within column B.

The following screenshot shows the difference: while COUNT processes only numbers, COUNTA outputs the total number of non-blank cells in column B, including the the text value in the column header.

COUNT and COUNTA formulas in Excel

Useful resources:

IF

Judging by the number of IF-related comments on our blog, it's the most popular function in Excel. In simple terms, you use an IF formula to ask Excel to test a certain condition and return one value or perform one calculation if the condition is met, and another value or calculation if the condition is not met:

IF(logical_test, [value_if_true], [value_if_false])

For example, the following IF statement checks if the order is completed (i.e. there is a value in column C) or not. To test if a cell is not blank, you use the "not equal to" operator (<>) in combination with an empty string (""). As the result, if cell C2 is not empty, the formula returns "Yes", otherwise "No":

=IF(C2<>"", "Yes", "No")

Using an IF formula in Excel

Useful resources:

TRIM

If your obviously correct Excel formulas return just a bunch of errors, one of the first things to check is extra spaces in the referenced cells (You may be surprised to know how many leading, trailing and in-between spaces lurk unnoticed in your sheets just until something goes wrong!).

There are several ways to remove unwanted spaces in Excel, with the TRIM function being the easiest one:

TRIM(text)

For example, to trim extra spaces in column A, enter the following formula in cell A1, and then copy it down the column:

=TRIM(A1)

It will eliminate all extra spaces in cells but a single space character between words:
Excel TRIM formula

Useful resources:

LEN

Whenever you want to know the number of characters in a certain cell, LEN is the function to use:

LEN(text)

Wish to find out how many characters are in cell A2? Just type the below formula into another cell:

=LEN(A2)

Please keep in mind that the Excel LEN function counts absolutely all characters including spaces:
Using a LEN formula in Excel

Want to get the total count of characters in a range or cells or count only specific characters? Please check out the following resources.

Useful resources:

AND & OR

These are the two most popular logical functions to check multiple criteria. The difference is how they do this:

  • AND returns TRUE if all conditions are met, FALSE otherwise.
  • OR returns TRUE if any condition is met, FALSE otherwise.

While rarely used on their own, these functions come in very handy as part of bigger formulas.

For example, to check the test results in columns B and C and return "Pass" if both are greater than 60, "Fail" otherwise, use the following IF formula with an embedded AND statement:

=IF(AND(B2>60, B2>60), "Pass", "Fail")

If it's sufficient to have just one test score greater than 60 (either test 1 or test 2), embed the OR statement:

=IF(OR(B2>60, B2>60), "Pass", "Fail")

IF formulas with embedded AND/OR statements

Useful resources:

CONCATENATE

In case you want to take values from two or more cells and combine them into one cell, use the concatenate operator (&) or the CONCATENATE function:

CONCATENATE(text1, [text2], …)

For example, to combine the values from cells A2 and B2, just enter the following formula in a different cell:

=CONCATENATE(A2, B2)

To separate the combined values with a space, type the space character (" ") in the arguments list:

=CONCATENATE(A2, " ", B2)

Using a CONCATENATE formula in Excel

Useful resources:

TODAY & NOW

To see the current date and time whenever you open your worksheet without having to manually update it on a daily basis, use either:

=TODAY() to insert the today's date in a cell.

=NOW() to insert the current date and time in a cell.

The beauty of these functions is that they don't require any arguments at all, you type the formulas exactly as written above.
Using the TODAY and NOW functions in Excel

Useful resources:

Excel formulas tips and how-to's

Now that you are familiar with the basic Excel formulas, these tips will give you some guidance on how to use them most effectively and avoid common formula errors.

Copy the same formula to other cells instead of re-typing it

Once you have typed a formula into a cell, there is no need to re-type it over and over again. Simply copy the formula to adjacent cells by dragging the fill handle (a small square at the lower right-hand corner of the cell). To copy the formula to the whole column, position the mouse pointer to the fill handle and double-click the plus sign.

Copying the formula to adjacent cells

Note. After copying the formula, make sure that all cell references are correct. Cell references may change depending on whether they are absolute (do not change) or relative (change).

For the detailed step-by-step instructions, please see How to copy formulas in Excel.

How to delete formula, but keep calculated value

When you remove a formula by pressing the Delete key, a calculated value is also deleted. However, you can delete only the formula and keep the resulting value in the cell. Here's how:

  • Select all cells with your formulas.
  • Press Ctrl + C to copy the selected cells.
  • Right-click the selection, and then click Paste Values > Values to paste the calculated values back to the selected cells. Or, press the Paste Special shortcut: Shift+F10 and then V.

For the detailed steps with screenshots, please see How to replace formulas with their values in Excel.

Do not enclose numbers in double quotes

Any text included in your Excel formulas should be enclosed in "quotation marks". However, you should never do that to numbers, unless you want Excel to treat them as text values.

For example, to check the value in cell B2 and return 1 for "Passed", 0 otherwise, you put the following formula, say, in C2:

=IF(B2="pass", 1, 0)

Copy the formula down to other cells and you will have a column of 1's and 0's that can be calculated without a hitch.

Now, see what happens if you double quote the numbers:

=IF(B2="pass", "1", "0")

At first sight, the output is normal - the same column of 1's and 0's. Upon a closer look, however, you will notice that the resulting values are left-aligned in cells by default, meaning those are numeric strings, not numbers! If later on someone will try to calculate those 1's and 0's, they might end up pulling their hair out trying to figure out why a 100% correct Sum or Count formula returns nothing but zero.
Enclose text values in double quotes, but not numbers

Don't format numbers in Excel formulas

Please remember this simple rule: numbers supplied to your Excel formulas should be entered without any formatting like decimal separator or dollar sign. In North America and some other countries, comma is the default argument separator, and the dollar sign ($) is used to make absolute cell references. Using those characters in numbers may just drive your Excel crazy :) So, instead of typing $2,000, simply type 2000, and then format the output value to your liking by setting up a custom Excel number format.

Match all opening and closing parentheses in your formulas

When crating a complex Excel formula with one or more nested functions, you will have to use more than one set of parentheses to define the order of calculations. In such formulas, be sure to pair the parentheses properly so that there is a closing parenthesis for every opening parenthesis. To make the job easier for you, Excel shades parenthesis pairs in different colors when you enter or edit a formula.

Make sure Calculation Options are set to Automatic

If all of a sudden your Excel formulas have stopped recalculating automatically, most likely the Calculation Options somehow switched to Manual. To fix this, go to the Formulas tab > Calculation group, click the Calculation Options button, and select Automatic.

If this does not help, check out these troubleshooting steps: Excel formulas not working: fixes & solutions.

This is how you make and manage basic formulas in Excel. I how you will find this information helpful. Anyway, I thank you for reading and hope to see you on our blog next week.

You may also be interested in:

302 Responses to "Basic Excel formulas & functions with examples"

  1. shravan says:

    if a C3 value is 4.65 then D3 should show 50%
    if a C3 value is between 4.20 and 4.65 then D3 should show 45%

    Please help to get a formula on this

  2. sunny sambharwal says:

    This content is very useful but example is not good

  3. Srishti says:

    I want to know about main formula of ms excel

  4. SUBHASIS HARICHANDAN says:

    Dear Team
    I want to know about the best techniques of the excel with formulas & functions.

    if you provide best techniques, i shall be obliged

  5. Sunil Damor says:

    Good work microsoft exel

  6. Waugh says:

    I want set up a spread sheet whereby i have a value on one cell and the percentage of that value in another cell,for example i have $1000 in cell B and in cell C i will want to have 25% of whatever value is in cell B(in this case $1000)
    regards
    Waugh

  7. Waugh says:

    I need assistance whereby i HAVE TWO CELLS,ONE WITH PRIMARY BORROWED AMOUNT THEN SECOND ONE WITH THE INTEREST OF THE BORROWED MONEY

  8. Akbar says:

    how to convert number in figures., as 1,000.00 (One thousand)

  9. Zaman says:

    Hello, I am trying to make a time sheet. Here is the example:
    Start time: 12:30 am
    Regular hours: 4.5 hours
    Extra hours: 40 minutes
    Break: 30 minutes if total hours more than 5 hours
    Finish Time: ??? I need the formula which should be 6:10 am as total hour more than 5.
    Total hours: 5.16 hours

    Thanks for your help.

  10. megala says:

    thank you

  11. Smitesh says:

    It takes me Only a day to learned all the formulas..
    Very Helpfull..

  12. Naveen says:

    More than this

  13. Tin Myo Aung says:

    If E2 is under 2000 C7 should show 30%
    If E2 is between 2001 and 4000 C7 should show 40%
    If E2 is under 4001 and over C7 should show 50%
    Please help to get a formula for this

  14. k.shanmugam says:

    i will apply numbers only

  15. k.shanmugam says:

    how to apply the number convert to text for example 12= twele and apply a1=112 but the solution b2= one hundred and twele

  16. NIKHIL says:

    IF I WANT TO SUBSTRACT A7 TO A11 CELLS FROM CELL A6
    HOW CAN I DO THIS ONE

  17. PRABHAT says:

    SIR,
    I WANT HELP IN MARKING SCHEME
    ATTAIN = +4
    WRONG = -1
    LEFT = 0
    KEY IF A AND B BOTH THEN 4 (FOUR OPTION A,B,C,D, GRACE) a formula for this

  18. krimesh raiyani says:

    i loved it and send me your email id in my email id

  19. Jose Raja says:

    I want to calculate TWO CELL also end of the calculation i want to add a TEXT (as a note)
    For Ex. :
    Key in info : =(A2-A3) Weeks
    want a result to be : 3 Weeks

    is it possible. pls answer me ASAP
    thanks

  20. tina says:

    this was helpful

  21. pitambar says:

    it is very useful but example is less

  22. i want to change the digit in words says:

    Ex- 24546 =twenty four thousand five hundred forty six only (in formulas)

  23. Arun Kumar H says:

    Hi
    I need an assistance in function formula for CTC Calculator
    We calculate Basic salary on fixed monthly CTC at 40%, Hence if the monthly basic is above 15k, let the amount takes whatever it get calculated on 40% but if the monthly salary itself below 15k then whatever the amount is should get reflected in Basic salary itself not in any other component.
    Please do the needful

  24. WARIS ALI TABASSUM says:

    Electricity Bill
    Units Rate

    1-100 2
    101-200 3
    200-above 4 Units Consumed Price
    50
    120
    80
    200
    100
    250

  25. JOHN says:

    WEEK EXAMPLES ACCORDING TO THEORY

  26. MALEBESE HENSON says:

    I need help in copying results from the next table with increasing numbers.
    For example
    Column b has the following:
    1. 20
    2. 30
    3. 40
    Then I need to transfer the info onto another sheet across a row
    A B C
    1. 20 30 40

  27. Saraswathi says:

    Very useful who is try to learn about excel and recall thanks good keep going on

  28. Jishnu Roy says:

    What is the formula for vlookup

  29. Murugesan says:

    excel function
    a=0, b=500, but c=500/2 = 250 i need this calculation

  30. prakash says:

    if 1510.99 comes the result should 1510 and when 1511.1 comes then result should come 1520 .
    How to make this result?

  31. pradeep says:

    all is good

  32. sathish says:

    how to select and paste formula cells

  33. kasujja joshua says:

    wow it was realy heipfull to me,thanks.

  34. mahesh says:

    want to deduct value of column 'g'items from column' h ' items total values and multiply with column 'k' values can u help for the same

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2019-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard