How to enter basic formulas and calculations in Excel
If you're getting started with Excel, creating formulas is one of the first things you should learn. In this lesson you'll learn how to create simple formulas and calculations in Excel.
At its heart, Excel is a giant calculator. In fact, a simple way to think about Excel is to consider each cell in a worksheet like an individual calculator. An Excel spreadsheet has millions of cells, which means you have millions of individual calculators to work with. Not only that, but you can create formulas that link different cells together (e.g. add the value in this cell to the value in that cell). You can create formulas that link cells in different worksheets together. And you can even create formulas that link cells in different workbooks together.
How to enter a formula in Excel
In Excel, each cell can contain a calculation. In Excel jargon we call this a formula. Each cell can contain one formula. When you enter a formula in a cell, Excel calculates the result of that formula and displays the result of that calculation to you. In fact, when you enter a formula into any cell, Excel will recalculate the result of all the cells in the worksheet. This normally happens in the blink of an eye so you won't normally notice it, although you may find that large and complex spreadsheets can take longer to recalculate.
When entering a formula, you have to make sure Excel knows that's what you want to do. You start by typing the = (equals) sign, then the rest of your formula. If you don't type the equals sign first, then Excel will assume you are typing either a number or a text. You can also start a formula with either a plus (+) or minus (-) symbol. Excel will assume you're typing a formula and insert the equals sign for you.
Here are some examples of some simple Excel formulas and their results:
In this example, there are four basic formulas:
- Addition (+)
- Subtraction (-)
- Multiplication (*)
- Division (/)
In each case, you would type the equals sign (=), then the formula, then press Enter to tell Excel you've finished.
- Sometimes Excel will show you a warning rather than just entering your formula. This will happen if the formula you've typed is invalid, i.e. is not in a format that Excel recognises. It will usually also give you some indication of what you did wrong.
- Other times, Excel may enter the formula you have typed correctly but then show you an error such as #VALUE. This means that you have entered a formula that was value, but Excel could not calculate a valid result from your formula.
Creating formulas that refer to other cells in the same worksheet
Excel's power comes from allowing you to create formulas that refer to the values in other cells.
In the example above, you'll notice the headings across the top (A, B) and down the left (1,2,3,4,5). By comining these values, we have a unique reference each cell in a worksheet (A1, A2, A3, B1, B2, B3, and so on).
When you create a formula, you can refer to other cells using these cell references to incorporate the values in other cells into a formula. The value in another cell might be a simple number, or another cell containing a formula. When you create a formula that refers to another cell that also contains a formula, your formula will use the result of the formula in that other cell. Then, if the result of the formula in that other cell changes, so too does the result in your formula.
Here are some examples of some Excel formulas that refer to other cells:
In this example, rows 6-8 build on the earlier examples to link cells together:
- B6 adds the values in B2 and B3 together. If you change either of the values in B2 or B3 the result in B6 will change too.
- B7 and B8 subtract and multiply the values in other cells.
- B9 goes a step further and divides B8 by B3. Note that B8 in turn multiplied B5 and B2 together. So changing the values in either B5 or B2 will have a domino effect, where the value in B8 will change, and so the value in B9 will change too. Note that Excel handles all of this the moment you finish entering a change in either B5 or B2.
Creating formulas that refer to cells in other worksheets
When you first open Excel, you start with a single worksheet. However, Excel allows you to have more than one worksheet inside a single spreadsheet file (known as a workbook). In fact, in earlier versions of Excel a new workbook automatically started out with 3 worksheets inside it.
Earlier we saw how to link two cells together within a worksheet by referring to other cells using their cell reference value. Referring to a cell inside another worksheet works in much the same way, but we need to provide more information about the location of that cell so Excel knows which cell we're talking about.
Here are some examples of formulas that refer to cells in another worksheet inside the same workbook:
In this example, the formulas in B10 and B11 refer to cells in another worksheet called Data.
- B10 multiples the value in B9 by the value in cell A2 in the worksheet called Data
- B11 takes the value A4 in the worksheet called Data and divides it by the value in B9.
In other words, we've told Excel to go to the worksheet called Data and use values in that worksheet in our formulas.
There are a couple of ways to create formulas like this:
- Type the formula in by hand. In the above example, you would create the reference to the other worksheet by typing the worksheet name followed by an exclamation mark (!); the exclamation mark tells Excel that you're referring to another worksheet.
- Start typing the formula by typing the equals sign (=), then click on the name of the other worksheet. Excel will switch to the other worksheet, and you can click on the cell you want to reference in your formula. You can then press Enter to finish entering the formula, or you can click back on the original worksheet name and finish typing your formula before pressing Enter.
Note that if you rename the worksheet called Data, the formulas that refer to Data will automatically update to reflect the new name. Here's what the above examples look like if we change the name of the worksheet called Data to Daily Data.
Note how Excel has put apostrophes around the name of the worksheet called Daily Data. This is because of the space in the worksheet name. Excel does this to make sure that the reference still works; if you manually type the formula without the apostrophes then Excel will not be able to validate the formula, and will not let you enter it.
Creating formulas that link to other workbooks
As you might imagine what we've already covered, it is also possible to create a formulat that refers to cells in another workbook (i.e. another file). Once again, it's simply a matter of correctly referring to the cell in the other workbook.
The following example shows what this looks like:
In this example, B12 contains a formula that refers to cell D6 in a worksheet called Data in a file called Excel-data-table-xlsx.
- The square brackets are used to indicate the filename, i.e. [filename]. Be aware that if the file referred to is not currently open, the square brackets may also include the full file path to that file, so that Excel can still read the value from the cell being referred to even though the file is not open.
- The apostrophes are used to enclose the full file name and worksheet name.
- Then, Excel uses absolute references to identify the cell being referred to. This means that if you move (not copy) the contents of cell D6 in the Data worksheet, your formula will still work. The $ signs are used to denote an absolute reference (as opposed to a relative reference). Absolute and relative references are out of scope for this lesson, but you can read about them in this lesson.
Summary
Learning to use Excel formulas is one of the most important things you'll learn to do with Excel. Hopefully this lesson has set you on the right path, and you'll be creating spreadsheets with formulas of your own in no time at all. If you have any feedback or questions on this lesson, please comment below!
Our Comment Policy.
We welcome your comments and questions about this lesson. We don't welcome spam. Our readers get a lot of value out of the comments and answers on our lessons and spam hurts that experience. Our spam filter is pretty good at stopping bots from posting spam, and our admins are quick to delete spam that does get through. We know that bots don't read messages like this, but there are people out there who manually post spam. I repeat - we delete all spam, and if we see repeated posts from a given IP address, we'll block the IP address. So don't waste your time, or ours. One other point to note - if you post a link in your comment, it will automatically be deleted.
Comments on this lesson
Your IF function is testing for a space, not an empty cell
Hi Michelle
You are so close! Your IF function is checking if the Date of Birth field includes a space " ", where in fact you want to check if it is empty - "".
Looking at your formula, I would suggest you consider ROUNDDOWN function instead of INT. It rounds a number down to the number of decimal places you want. In your case, I'm assuming you want no decimal places. Obviously the INT function is working for you, but the ROUNDDOWN function gives you more options. I'd also suggest you use the TODAY() function to calculate the current date, unless your spreadsheet needs to calculate someone's age to a specific date that isn't today. Here's how the basic formula looks.
=ROUNDDOWN((TODAY()-$C14)/365,0)
I'm assuming that $C14 contains a typed Date of Birth.
This function still has the issue with an odd number appearing if there is no value in the Date of Birth field. It's actually not odd - it's the number of years that have elapsed since January 1, 1900. Excel stores all dates as numbers starting with that date as 0.
Anyway, to display nothing if the Date of Birth field is empty, change my formula above to use the IF function like this:
=IF($C14="","",ROUNDDOWN((TODAY()-$C14)/365,0))
As you'll see, my formula uses "" to check if the cell is empty whereas yours was using " ", i.e. it was checking if the value in the cell was a space.
Regards
David
Workbooks
How do I create a workbook. I have four worksheets I would like to combine to be able to look at one after the other while working with them
am a bit loss. Struggling to
am a bit loss. Struggling to find how to produce my figures in my result row .when I type 10+10 the answer stays in... you type row. How can I move it to result row
Reply to : am a bit loss. Struggling to
Hi Ezra,
The details that is provided in this article is probably indication purpose. But there is a way to get what you want.
Assuming you have two columns : YOU_TYPE and RESULT in A and B respectively.
The value under YOU_TYPE should be with an '=' sign, this will calculate the arithmetic operation it is suppose to do. So, it is like "=10+10". This will provide you with answer. But this remains in Column A, i.e YOU_TYPE.
Assuming that the value '=10+10' is typed in 2nd raw, i.e A2, type the formula "=A2" under RESULT in B2
You make my life easier :)
Thank you for this lesson!
I am not really a beginner, but I am still at the beginning of using excell. I have to create statistics at work with different files and workbooks. The last part of this lesson has opened my eyes (mind) and will make it much quicker now to create weekly, monthly and yearly statistics!
Thank you so much
Elisabeth
calculating filtered data
Please help, I have been at this for 2 days.
I have a spread sheet that 1369 rows & A-K columns (with headers).
In the "Unfiltered" status this is the formula that I am using: =COUNTIF(I2:I1368,"0") which works great; because I want it to calculate and count all data in column "I" with the specific criteria of "0". However, when its time to filter the data by grade and teacher this formula doesn't work because it does not recalculate the visible data.
Again, I would greatly appreciate some assistance on this. I have watched countless videos and have read quit a bit of data. That did not solve my current issue.
Building formulas
It is really simple once you get a grasp of the basics, once I got used to what the arithmetic and comparison operators represent I really got the hang of it.
formulae
I'm a beinggner in excel,I really got all ur explanation,but wen I'm try to enter formulars under A,d result still comes under A
Secondly how will I get division sign in an excel
I'm trying to get the
I'm trying to get the formulas to be on one column and the results to be on another as it is in your examples, but I can't prevent the cells from automatically giving me the result even though I only want a formula in the one column. How do I for example get the formula on A1 and the result of that formula to be on B1 and so on as you have done In your examples?
Adding/multiplying cells
I have a formula already in place but need to add to it. My formula is =(b2)*(c2) total comes out on g2. I have 258 cells that total up on g266 with a sales tax added from g267 totaled again on g269. Now I need to add the formula =(c2)*(e2) to add up with the first one
I'm trying to calculate people's ages in cell $A15 by using a typed in date in cell B8. I figured out that the formula for $A15 is: =INT($B$7-$C14)/365). But if I don't have a date of birth (DOB) entered into column C, the INT function a puts a weird number in column A.
To fix this, I in addition to the INT function, I tried to do add an IF function in cell $A15 with these parameters: if $C15 was blank Excel would return a blank cell to $A15.
How do I correctly put the INT and the IF functions together in cell $A15? This is what I tried in cell $A15, but it didn't work: =IF($C15=" "," ",INT($B$7-$C14)/365))
Oh ye mighty and wise Excel guru...please help. I've spent 3 hours trying to figure this out. Thank you so much!!!