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 the value of a cell.

    For example, =A2+A2+A3+A4 is a formula that adds up the values in cells A2 to 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(A2:A6) - adds up values in cells A2 through A6.

=SUM(A2, A6) - adds up values in cells A2 and A6.

=SUM(A2:A6)/5 - adds up values in cells A2 through A6, and then divides the sum by 5.

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 Editing 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 last formula from the previous section (=SUM(A2:A6)/5), what does it actually do? Sums values in cells A2 through A6, 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!

So, instead of typing =SUM(A2:A6)/5, you can simply put =AVERAGE(A2:A6)

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(A2:A6)

=MIN(A2:A6)

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 Excel 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 A contain numbers, use this formula:

=COUNT(A:A)

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

=COUNTA(A:A)

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

The following screenshot shows the difference:
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 instructs Excel to check the value in A2 and return "OK" if it's greater than or equal to 3, "Not OK" if it's less than 3:

=IF(A2>=3, "OK", "Not OK")

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 cells referenced in your formula (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)

Need to find out how many characters are in cell A2? Just type =LEN(A2) into another cell.

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 of the conditions are met, FALSE otherwise.
  • OR returns TRUE if any of the conditions 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 quantity in 2 columns and return "Good" if both values are greater than zero, you use the following IF formula with an embedded AND statement:

=IF(AND(A2>0, B2>0), "Good", "")

If you are happy with just one value being greater than 0 (either A2 or B2), then use the OR statement:

=IF(OR(A2>0, B2>0), "Good", "")

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.

Enclose text values in double quotes, but not numbers

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 text 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:

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

  1. virender says:

    I want to know if I type in the Cell/B2(Jan-18) and now i want to type the Cell/B2 in the Cell/D32 what the formulas or solution if any...

  2. Kedar says:

    Dear Sir/ Mam,

    --
    I like information, you providing,, Further request you to send me detail formulas specially for the condition "IF".I think it will help in future.
    --
    Kedar

  3. Tom says:

    Good morning. Is there a way to add letters into a cell after a simple sum calculation? ie I sum a list of hectare totals and now I want to add "ha" after. Thanks

    =SUM(L8:L161)

    • Hi Tom,

      You can use the CONCATENATE function or concatenate operator (&) like this:
      =SUM(L8:L161)&"ha"

      If you want a space between the number and text, use this formula:
      =SUM(L8:L161)&" ha"

      Please note, concatenation turns the output into a text string, and you won't be able to use the result in further calculations.

  4. jenu joy says:

    how to display sheet 2 value in sheet 1 by formula

  5. Akshat says:

    Thanks, very useful piece of information

  6. jembepori says:

    thanx for this information

  7. Jared says:

    Hi Svetlana,
    I am trying to figure out how to get excel to recognize if a cell populates or not.

    I want cell G39 to read cell G40. G40 has a formula that sometimes populates from a data worksheet. When it populates I want G39 to display cell G3. If it doesn't I want it to stay blank.

    What I have tried so far and both have not worked:
    =IF(NOT(ISBLANK(G40)),G3,"") This doesn't work because it reads the formula so the cell isn't really blank.
    =IF(G40"",G3,"") This seems to have the same problem.

    Not Sure what else to try...

  8. Jared says:

    Typo in the second formula which should read:
    =IF(G40"",G3,"")
    Still doesn't work

  9. Jared says:

    Your board isn't posting the greater than and less then signs that would come after the G40 and before the "" in the 2nd formula. That formula still doesn't work with those included.

    • Hi Jared,

      Sorry for messing around with your formula, it's because of a silly bug in our blog engine that we are unable to fix.

      =IF(G40<>"", G3, "") seems to work fine for me except when G40 returns an error. That is:

      if G40 has a value, G39 displays G3.
      if G40 is an empty string, G39 is blank (empty string).
      if G40 has an error, G39 displays an error.

      What is wrong about this behavior? Please clarify.

  10. Jared says:

    Hi Svetlana,
    The (G40"",G3,"") would work if I didn't have a formula in G40. I think the formula that's puling data from a different tab was being recognized.

    I was able to find a work around. Instead of using cell G40 I went to the Tab I was getting the information and went to the cell that G40's formula was populating from. This is what I came up with that worked:
    =IF(Data!B34"",G3,"")

    Thanks,

    Jared

  11. FARAN says:

    FANTASTICS

  12. AL says:

    hi, may I know that if a column have 2 words, but I want to separate it to 2 columns, what is the fomula?
    E.g:
    in column:
    123456 abc

    then I want to separate 123456 and abc to 2 columns.
    Thanks for the help in advance.

    • Doug says:

      Al:
      If you don't have too many of these things to separate, then just use the Text-to-Columns tool.
      Under Data select the Text-to-Columns and with your data use the Delimited option and select the space checkbox as the delimiter. That will separate the data into two columns.
      Otherwise where the data is in A1 you might want to use RIGHT(A1,3) to get the abc characters into a cell.
      There are several techniques to split text strings. AbleBits has a couple of good articles that explain some of these techniques.

  13. Dulanjali says:

    easy to understand this function. lot of thanks

  14. laxmikant pednekar says:

    5290 x 69 + 10%

  15. kamal Rana says:

    Good five

  16. RAMIVAS VERMA says:

    please send me simple formula for excel in my mail id .

    thanks
    RAMNIVAS VERMA

    9315509039

  17. VINAYAK says:

    V=0.30/3[A1+A2]ROOT[A1XA2]

  18. Kumaravadivel.R says:

    Thanks Very Useful

  19. YOGESH NATANI says:

    Dear Sir
    These Formulas are really helpfull .Please give detail info of If Formula.

  20. Loubie L says:

    Can you use formulas to calculate hours worked? I do lot of timesheet work and be good to know. For example 7.50am start to 6.20pm finish ?
    Thanks

  21. md naimuddin says:

    If 1kg of tomato costs 200 rupees, what will be the cost of 700 grams
    how to calculate in excel what formula use for this problem

  22. MANOJ KUMAR says:

    sir i convert to numeric to text formulla,
    means 549556 i change auto five lac fourty nine thousand five hundred fifty six only

  23. Jayeeta Mondal says:

    Help me understand the formulas of MS-Excel

  24. Terrence Martzial says:

    I have a spreadsheet with imported values from a bank statement. It’s saved as a spreadsheet not csv data. The first column is the date, the second is the transaction the third is the amount etc till the sixth which is my own description that I added. Is there a way to pick the whole line out and place it in another row with all the same data? Example, the last column could be house, apartment, nm house, condo or trailer park.

  25. Ralph says:

    Sub Button1_Click()
    Dim Str As String
    StrFolder = "C:\Users\heywh\Videos\Assorrted Credits\"
    ActiveWorkbook.FollowHyperlink Address:=StrFolder, NewWindow:=True
    End Sub

    How would i use an If Statement in the above Macro

    I want to show a msgbox so that if the c:\folder above nothing is selected with in the sub folder to this msgbox "No Month Was Selected" show in a MsgBox and if a Month is selected it goes right to the month selected.

    Thank You For your Time.

  26. Ronald Ebu says:

    plz gv mi da formula of RANKing

  27. Ujjawal Pandey says:

    Nice sir ji

  28. kalmesh says:

    i need one formula if we entering the employer code that page have to show name and his detail's

  29. ASAD HASSAN says:

    Dear Svetlana,

    i have gone through yours tutorial it is amazing thanking you too make our life easy. i have one more request from you, i do asset validation checking all the asset is correctly captured such that they can be processed in software to make life easy to our organization.
    Please suggest me number of formulas and Technic i can used to do my validation more accurate.
    currently i am using vlookup, pivot table, mid, clean, trim, iferrror, and many more.

  30. Neil Kashyap says:

    I want know how hloockup work in excel sheet and what is the use of this formulas .

  31. latha says:

    SIR WITH EXAMPLES SHOW IN EXCEL HOW TO CALCULATE COMPOUND INTEREST.

    FOR EXAMPLE PRINCIPAL AMOUNT RS.10,00,000/-
    DELAY DAYS 200 FOR WHICH INTEREST 15% PER ANNUM
    COMPOUNDED QUARTERLY
    INTEREST RATE 15% PER ANNUM COMPOUNDED QUARTERLY

    SIR KINDLY DO THE ABOVE CALCULATION IN EXCEL AND GUIDE ME

  32. Michael Tambawanda(0777969994). says:

    How can some one understand all this?

  33. Vinod Kumar says:

    Hii
    Excel sheet formula
    Please information

  34. Sam says:

    Thanks , so helpful.

  35. Bel says:

    Hi,

    I have filled the colour to the one of the cell in excel.
    if I doubleclick the coloured cell. it should show the value which I assighed it.
    my question: there is any formula to add value, example the value should be in background. by doubleclick the colourd cell, it shows the value.

    thank you in advance

  36. CHANDAN DAS says:

    GOOD

  37. Robert says:

    Hello,

    Can you please send me a link for a tutorial on creating a sales lead template with if and then being a 2 week window for contacting those leads?

  38. Dinesh says:

    How you use percentage formula in Excel.

  39. K.V.NAIDU says:

    I like information, you providing,, Further request you to send me detail formulas specially for the condition "IF".I think it will help in future.

  40. Jegan says:

    I have a problem in my excel coding

    (this coding
    Function Rup(amt As Variant) As Variant
    Dim FIGURE As Variant
    Dim LENFIG As Integer
    Dim i As Integer
    Dim WORDs(19) As String
    Dim tens(9) As String
    WORDs(1) = "ONE"
    WORDs(2) = "TWO"
    WORDs(3) = "TRE"
    WORDs(4) = "FUR"
    WORDs(5) = "FIV"
    WORDs(6) = "SIX"
    WORDs(7) = "SVN"
    WORDs(8) = "EIT"
    WORDs(9) = "NIN"
    WORDs(0) = "ZER"
    WORDs(11) = "Eleven"
    WORDs(12) = "Twelve"
    WORDs(13) = "Thirteen"
    WORDs(14) = "Fourteen"
    WORDs(15) = "Fifteen"
    WORDs(16) = "Sixteen"
    WORDs(17) = "Seventeen"
    WORDs(18) = "Eighteen"
    WORDs(19) = "Nineteen"
    tens(2) = "Twenty"
    tens(3) = "Thirty"
    tens(4) = "Fourty"
    tens(5) = "Fifty"
    tens(6) = "Sixty"
    tens(7) = "Seventy"
    tens(8) = "Eighty"
    tens(9) = "Ninety"
    FIGURE = amt
    FIGURE = Format(FIGURE, "FIXED")
    FIGLEN = Len(FIGURE)
    If FIGLEN < 12 Then
    FIGURE = Space(12 - FIGLEN) & FIGURE
    End If
    For i = 1 To 3
    If Val(Left(FIGURE, 2)) 0 Then
    Rup = Rup & WORDs(Val(Left(FIGURE, 2)))
    ElseIf Val(Left(FIGURE, 2)) > 19 Then
    Rup = Rup & tens(Val(Left(FIGURE, 1)))
    Rup = Rup & WORDs(Val(Right(Left(FIGURE, 2), 1)))
    End If
    If i = 1 And Val(Left(FIGURE, 2)) > 0 Then
    Rup = Rup & " Crore "
    ElseIf i = 2 And Val(Left(FIGURE, 2)) > 0 Then
    Rup = Rup & " Lakh "
    ElseIf i = 3 And Val(Left(FIGURE, 2)) > 0 Then
    Rup = Rup & " Thousand "
    End If
    FIGURE = Mid(FIGURE, 3)
    Next i
    If Val(Left(FIGURE, 1)) > 0 Then
    Rup = Rup & WORDs(Val(Left(FIGURE, 1))) + " Hundred "
    End If
    FIGURE = Mid(FIGURE, 2)
    If Val(Left(FIGURE, 2)) 0 Then
    Rup = Rup & WORDs(Val(Left(FIGURE, 2)))
    ElseIf Val(Left(FIGURE, 2)) > 19 Then
    Rup = Rup & tens(Val(Left(FIGURE, 1)))
    Rup = Rup & WORDs(Val(Right(Left(FIGURE, 2), 1)))
    End If
    FIGURE = Mid(FIGURE, 4)
    If Val(FIGURE) > 0 Then
    Rup = Rup & " Paise "
    If Val(Left(FIGURE, 2)) 0 Then
    Rup = Rup & WORDs(Val(Left(FIGURE, 2)))
    ElseIf Val(Left(FIGURE, 2)) > 19 Then
    Rup = Rup & tens(Val(Left(FIGURE, 1)))
    Rup = Rup & WORDs(Val(Right(Left(FIGURE, 2), 1)))
    End If
    End If
    FIGURE = amt
    FIGURE = Format(FIGURE, "FIXED")
    End Function
    in this coding i want 0 gentrate as ZER its not coming its coming like 0 as 0 how to i get it

  41. tasmiya says:

    Display the highest and lowest marks in each test also give one appropriate leading

  42. MAHER ALNAKHLI says:

    Hi, I'm tring to find a formela to divide the input value to the next cells as 100s.
    example: if A2= 200, then B2=100, C2=100, D2=0 and E2=0
    and if A2 changed to 300 then B2=100, C2=100, D2=100 and E2=0.

    Best regard.

  43. MAHER ALNAKHLI says:

    Update to clarify my request:

    The "300" in the input cell could be increased manually to any number 500, 1200 ..... or 5000
    A1 = "input cell", the next 12 cells B1 to M1: each cell should be filled with a 100 as long A1 increased by 100
    if A1= 1043; B1=C1=D1=E1=F1=G1=H1=I1=J1=K1=100; but L1=M1=0 & N1=43
    N1= A1-(sum(B1:M1)) if A1 >1200 or N1= 0 if A1 <= 1200

  44. ali says:

    hi

    could you please have you tel me where i get more information about condition formatting

  45. sohaib gujjr says:

    Good

  46. Olga says:

    I have in a cell a range date for example
    1-12 - 1-19 and I would like to change to a Jan-12 - Jan 19
    What is the formula for it?
    Thanks

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!
Excel add-ins and Outlook tools - Ablebits.com
33
Ultimate Suite 2018.5 for Excel
33
60+ tools for Excel
December offer: Dec. 8 – Dec. 17