by The tutorial explains how to write formulas in Excel, beginning with very simple ones. You will learn how to create a formula in Excel using constants, cell references and defined names. Also, you will see how to make formulas using the function wizard or enter a function directly in a cell.
In the previous article we've started to explore a fascinating word of Microsoft Excel formulas. Why fascinating? Because Excel provides formulas for almost anything. So, whatever problem or a challenge you are facing, chances are that it can be solved by using a formula. You just need to know how to make a proper one :) And it is exactly what we are going to discuss in this tutorial.
For starters, any Excel formula begins with the equal sign (=). So, whatever formula you are going to write, begin by typing = either in the destination cell or in the Excel formula bar. And now, let's have a closer look at how you can make different formulas in Excel.
How to make a simple Excel formula by using constants and operators
In Microsoft Excel formulas, constants are numbers, dates or text values that you enter directly in a formula. To create a simple Excel formula using constants, just do the following:
- Select a cell where you want to output the result.
- Type the equal symbol (=), and then type the equation you want to calculate.
- Press the Enter key to complete your formula. Done!
Here is an example of a simple subtraction formula in Excel:
=100-50
How to write formulas in Excel using cell references
Instead of entering values directly in your Excel formula, you can refer to the cells, containing those values.
For example, if you want to subtract a value in cell B2 from the value in cell A2, you write the following subtraction formula: =A2-B2
When making such a formula, you can type the cell references directly in the formula, or click the cell and Excel will insert a corresponding cell reference in your formula. To add range reference, select the range of cells in the sheet.
Note. By default, Excel adds relative cell references. To switch to another reference type, press the F4 key.
A big advantage of using cell references in Excel formulas is that whenever you change a value in the referred cell, the formula recalculates automatically without you having to manually update all the calculations and formulas on your spreadsheet.
How to create an Excel formula by using defined names
To take a step further, you can create a name for a certain cell or a range of cells, and then refer to that cell(s) in your Excel formulas by simply typing the name.
The fastest way to create a name in Excel, is to select a cell(s) and type the name directly in the Name Box. For example, this is how you create a name for cell A2:
A professional-like way to define a name is via the Formulas tab > Defined names group or Ctrl+F3 shortcut. For the details steps, please see creating a defined name in Excel.
In this example, I've created the following 2 names:
- revenue for cell A2
- expenses for cell B2
And now, to calculate the net income, you can type the following formula in any cell on any sheet within the workbook in which those names were created: =revenue-expenses
In the same manner, you can use names instead of cell or range references in arguments of Excel functions.
For example, if you create the name 2015_sales for cells A2:A100, you can find a total of those cells by using the following SUM formula: =SUM(2015_sales)
Of course, you can get the same result by supplying the range to the SUM function: =SUM(A2:A100)
However, defined names make Excel formulas more understandable. Also, they can significantly expedite creating formulas in Excel especially when you are using the same range of cells in multiple formulas. Instead of navigating between different spreadsheets to find and select the range, you just type its name directly in the formula.
How to make Excel formulas by using functions
Excel functions are nothing else than predefined formulas that perform the required calculations behind the scene.
Each formula begins with an equal sign (=), followed by the function name and the function arguments entered within the parentheses. Each function has specific arguments and syntax (particular order of arguments).
For more information, please see a list of the most popular Excel functions with formula examples and screenshots.
In your Excel spreadsheets, you can create a function-based formula in 2 ways:
Create a formula in Excel by using the Function Wizard
If you do not feel very comfortable with Excel spreadsheet formulas yet, the Insert Function wizard will give you a helpful hand.
1. Run the function wizard.
To run the wizard, click the Insert Function button on the Formulas tab > Function Library group, or pick a function from one of the categories:
Alternatively, you can click the Insert Function button 
to the left of the formula bar.
Or, type the equal sign (=) in a cell and pick a function from the drop-down menu to the left of the formula bar. By default, the drop-down menu displays 10 most recently used functions, to get to the full list, click More Functions...
2. Find the function you want to use.
When the Insert Function wizard appears, you do the following:
- If you know the function name, type it in the Search for a function field and click Go.
- If you are not sure exactly what function you need to use, type a very brief description of the task you want to solve in the Search for a function field, and click Go. For example, you can type something like this: "sum cells", or "count empty cells".
- If you know what category the function belongs to, click the small black arrow next to Select a category and choose one of the 13 categories listed there. The functions belonging to the selected category will appear in the Select a function
You can read a short description of the selected function right under the Select a function box. If you need further details regarding that function, click the Help on this function link near the bottom of the dialog box.
Once you've found the function you wish to use, select it and click OK.
3. Specify the function arguments.
In the second step of the Excel function wizard, you are to specify the function's arguments. Good news is that no knowledge of the function's syntax is required. You just enter the cell or range references in the arguments' boxes and the wizard will take care of the rest.
To enter an argument, you can either type a cell reference or range directly into the box. Alternatively, click the range selection icon next to the argument (or simply put the cursor into the argument's box), and then select a cell or a range of cells in the worksheet using the mouse. While doing this, the function wizard will shrink to a narrow range selection window. When you release the mouse button, the dialog box will be restored to its full size.
A short explanation for the currently selected argument is displayed right under the function's description. For more details, click the Help on this function link near the bottom.
Excel functions allow you to perform calculations with cell residing on the same worksheet, different sheets and even different workbooks. In this example, we are calculating the average of sales for 2014 and 2015 years located in two different spreadsheets, which in why the range references in the above screenshot include the sheet names. Find more about how to reference another sheet or workbook in Excel.
As soon as you've specified an argument, the value or array of values in the selected cell(s) will be displayed right to the argument's box.
4. Complete the formula.
When you have specified all the arguments, click the OK button (or just press the Enter key), and the completed formula is entered into the cell.
Write a formula directly in a cell or formula bar
As you've just seen, creating a formula in Excel by using the function wizard is easy, thought it's quite a long multi-step process. When you have some experience with Excel formulas, you might like a faster way - typing a function directly into a cell or formula bar.
As usual, you start by typing the equal sign (=) followed by the function name. As you do this, Excel will perform some kind of incremental search and display a list of functions that match the part of the function's name you've already typed:
So, you can either finish typing the function name on your own or select from the displayed list. Either way, as soon as you type an opening parenthesis, Excel will show the function screen tip highlighting the argument you need to enter next. You can type the argument in the formula manually, or click a cell (select a range) in the sheet and have a corresponding cell or range reference added to the argument.
After you've input the last argument, type the closing parenthesis and hit Enter to complete the formula.
Tip. If you are not quite familiar with the function's syntax, click the function name and the Excel Help topic will pop-up right away.
This is how you create formulas in Excel. Nothing difficult at all, is it? In the next few articles, we will continue our journey in the intriguing realm of Microsoft Excel formulas, but those are going to be short tips to make your work with Excel formulas more efficient and productive. Please stay tuned!
88 comments
Hi
Imagine I have two firms. the market size is 1000 and one firm has a price of $5 and one of $7. How do we split market share? Next one firm spends $30 on advertaing with a price of 45 and the other spends $10 (price $7). How do we combine the two variable price and advertising spend. and what about more variables. Is there a simple equation?
I want to make a shop drawings log , need to calculate total number of drawings, Approval & comments by alphabetic words (exp. A for approval C for to be revised.)
can some one help me please
How can I add this no in excel
110abc + 12
Dear
i am paying bounce on producton to my employees working in my factory.and i want a formula for this plz help me if production is 400,000 then bounce is 100 or if production is 425,000 then bounce is 150 or as below
Production Bounce
400,000 100
425,000 150
450,000 200
475,000 250
500,000 400
Hi,
* I had been looking for a new formula which"ll feed me with my kinda stuff.
After several searches and experiments, i didn't stopped at nothing.
I'm still in a thirst for it.
* Its hard to explain, but lets see if i'm a good teacher
* Consider a single cell containing a paragraph and there are "n" number of cells and what i need is to extract numbers or alpha's or both of a specific term which is repeated in each cell.
* For Example, Consider 2 cells of paragraph containing Phone numbers
In Cell 1 - ABCDEFGHIJKLMNOPQRSTUVWXYZ Ph no. 123456789 asdfasdfasdf
In Cell 2 - ABCDEFGHIJKLMNOPQRSTUVWXYZ Ph no. 987654321 asdfasdfasdf
and the formula i need is will be like ...
=SEARCH("Ph no. ") and the main thing i'm looking to add with this is... i need all the numerics or alpha's set (till the position in which it is NOT seperated by a space or comma) to be derived to the cell i want
* And now consider Cell 3 to be a place where i am entering the formula i need from Cell 1 and same with Cell 4 to be a place where i am entering the formula i need from Cell 2
* With refence to above example Cell 3's visible data what i need is should be
"Ph no. 123456789"
* With refence to above example Cell 4's visible data what i need is should be
"Ph no. 987654321"
* Irrespective of the position where the " Ph. no " in a cell is placed
* I know you might think im crazy LoL but it'd really be helpful to me if u help me in this..
Hi I need help
I would like to split 1.5x 2.5 which is in the same column.
I want data to be in different column or seperated to be
1.5
2.5
How can I do that
Hi Madilin,
Thank you for contacting us.
If we understand your task correctly, please have a look at our Split Cells tool. It can help you separate your text into multiple columns or rows at a glance.
I am trying to find a formula to figure out 6% sales tax.
Sales ÷tax = expenses
Thank you
Carley:
I think what you want is the formula:
=sales cell address/.06
I am trying to write the following formula in excel
x=100[((4a+b+c+d)e)-6]/254 where a, b, c, d, e have numerical values in various cells.
Can some help !
Many thanks
Mushtak:
I think this will provide the result you're after:
=100*(((4*(A1)+B1+C1+D1)*E1)-6)/254
Note the A1, B1, C1, D1 and E1 are cell addresses that you can modify.
Note the asterisk Excel uses to signify multiply.
Note there are no square brackets.
if i want that no.enter in the cell should be divided by 1000 and round upto one decimal place. like 6145 should be converted into 6.1
what will be the formula .
Virendra:
The simplest way to get what you want is to apply some arithmetic and then formatting a cell as a number to display one digit. If you want to do this "automatically" you'll need to build some VBA code and that's beyond the scope of this blog. I'll show you the easy method.
Let's say your number is in cell A2. Maybe it is 6145.
First, in cell B2 enter =A2/1000.
Next you'll format the cell B2 as a number with one decimal place.
To format the cell, right click on B2, choose Format Cells, then choose Number, then select 1 decimal place. Click OK.
The number displayed is "6.1".
Some arithmetic, some formatting and you're home.
if i want that no.enter in the cell should be divided by 1000 and round upto one decimal place. like 6145 should be converted into 6.1
what will be the formula .
Hi, Can anyone tell me how to program this: I want h31 to be multiplied by 3.5% that i have in G32 and want the total of h31 g32 to total in h32.
In developing a formula to solve a mortgage question with time/term being the unknown, how do I use PMT & 'What If' to create it??? Or is there a better way??? ie:
PV: $100,000
I: 5.0%
Pmyt: $536.83
FV: $00
Solve for Unknown 'N': ???
Thank you for your insight & assistance, jt
hellow i need some help!
1) In regards with the formulas of Excel can i create my own formulas and How?
2) I need to Insert Particular Page No of a Cell Located in Different Sheets
for which i am unable to find the formulas in excel
can i get some help
will be Thankful for that
Hi I’m trying to find a formula or combined for predicting a future date from a set date depending on a cell value in another work sheet.
Example:
01/01/2018 = future date?
Based on a score of 1 with an argument of next date is 20 days when a score is between 1-5.
6-10 =15 days
Hello,
I have a calculation, which needs to be integrated in sheet. I am unable to figure out the formula for it -
EG:
Freight (10 X 8)
(+) 200
Result of above * 16%
(+) 50
Result of above * 18%
Hellow
Im a beginer with the use of MS Excel.. Could you please help with these questions;
Write an Ms Excel Formula for each of the following measures:
a)the statistical range of all values in cells K2,L3,M4 and the cell range AZ4:BB6
b)the interest given the principle in A2,the annual interest rate in A3 and the number of years in A4,where interest is compounded every two months.
Your kind response in help will be much appreciated.
Hi
How can i write equation in excel?
I need to have a date to mail out notices 90 days after a date in another column depending on if another column says needs testing.
Is there a formula to do this, or do I just do this myself?
hi, i have an issue writing a formula and am not sure if it could be done, but i would love to here your input.
i have a spreadsheet made up for my work team and need a formula to turn particular arguments into a number.
i have 5 guys on a team.
when they come in for a shift i use a date picker to select the day which includes an abbreviation of the day i.e (wed)
i also have to take into account if its a night shift or day shift so i fill the cell colour red for night and blue for day.
i also need to add to these arguments a figure regarding to the day of the week (wed) and the colour of the cell (day / night shift).
unless there is a date picker around i could modify so it would ask what shift was done on the day/date selected.
so for example. if i selected a cell the right click and choose a date from the date picker, the formula would then calculate a figure answer with the day of week and shift. shifts have different values.
is it possible? and if so how would i go about this?
thanks your help will be greatly appreciated.
regards Andrew.
Hi,
I'm looking for a formula that I have seen on google (mostly from your blog) but now I forgot and I searched lot on google but I didn't find what I'm looking for.
In my formula I want to change/increase only row number/reference by '1' not column number/reference.
***Not cell value
like this
A1
A2
A3
A4