offers Excel lesson videos made simple!

Sign up or log in to Magoosh Excel.

How to Create a Formula in Excel: The Basics

If you don’t know how to create a formula in Excel, you are missing out on the convenience of using this amazing program! There are so many benefits to using Excel, and in this article we will start with the basics. First, let’s briefly talk about what Microsoft Excel is.

What Is Microsoft Excel, and Why Do I Care?

Microsoft has always been on the forefront regarding their platform based applications. They are actually the pioneer in the industry when it comes to developing applications for the benefit of users. One such application that has found wide application in the business world is Microsoft Excel. In fact, Excel has been the number one application since it proved to be quite useful in the field of storing data—and performing various mathematical and statistical operations on that data.

Never before has an application been developed that performs multiple operations simultaneously, while radically reducing the time it takes to carry out complex calculations. Microsoft Excel is a spreadsheet-based application, which was developed to match the growing demand in the standards of spreadsheets.

how to create a formula in excel - magoosh

By Alexey Godzenko

Excel can be implemented in a number of ways. However, the most basic and the most used feature of the Excel spreadsheet is the formulas. Excel formulas are both derived as well as stored in the Excel library functions. As a matter of fact, the Excel compiler even has some of the formulas stored in its memory in the form of functions.

These mathematical formulas are what make the calculations, the laying of charts, and the macro programming much easier. From simple addition to complex pie chart formulas, everything can be easily done using Excel sheets.

And if you think Excel can support only the inbuilt formulas, then you are highly mistaken! To steer you in the right direction, let’s look at how you can actually create formulas of your own in an Excel worksheet.

How to Create a Formula in Excel: Basic Formulas

In the Excel platform, you can create the formulas easily. All that you need to know are a few tricks and methodologies. The basic Excel formula can be created in two ways:

  1. By simply writing the operation in the designated cell
  2. By using the cell reference technique in the cell where you want the calculated value

Even if you have no idea how to deal with complex macro programming, Excel has made the creation of formulas easy! Excel uses the standard operators for the various operations and so you won’t have to worry about searching for them.

The Excel compiler even has the feature of evaluating nested functions. Nested formulas or functions are those that use two or more formulas, each separated by brackets.

For example, when you need to find the average of three cells, you can write a formula as:
=(D3+D5+G5)/3

Here the addition and division will both occur simultaneously. However, the enclosure using the first bracket has prioritized the addition function.

how to write a formula in excel, spreadsheet - magooshBy Keith Bell

How to Create a Formula in Excel with Value Reference

The most basic way to create a formula is to write the data that you want to operate on in your desired cell.

The steps to create a formula following this value-based method are:

  1. Select the desired cell in your spreadsheet. Make sure the cell is empty.
  2. Double-click on the cell to have the cursor inside the open space. Then you will be able to write the formula.
  3. Always remember, every formula in Excel starts with an “equal to” sign. So, for writing the formula, simply put an equal to sign and then the operation. For example, say you want to find the percentage of a certain value. Then the way of writing the formula in the desired cell is:
    =(75/100)

    Likewise, if you want to find the sum of five numbers, the way to write the formula is:
    =(5+8+3+5+2)

The answer to the calculation is displayed in the same cell. However, make sure that whatever formula you write has a perfect answer and not something ambiguous. The problem with the latter is that instead of numeric values, you get an answer in pound sterling symbols. This means that your answer is too large for the given column width, so simply drag the column boundary and extend it show the entire answer.

How to Create a Formula in Excel with Cell Reference

Using values can be sometimes difficult since you have to write them carefully without missing anything. In addition, for formulas that are quite large, the earlier discussed process is not at all feasible.

To make things a bit easier in this situation, you can use the cell reference method to create your own formula on the Excel spreadsheet. In this case, the cell in which the operand’s value is written is chosen. You must be aware that Excel, being a spreadsheet, will have columns numbered from A and rows from 1.

Each cell has its own location that is specified by a particular two-digit address. The first digit is the column number and the second one reflects the row of the cell. For example, when you write F4, it means that you are pointing to the cell in column F and row 4.

This particular knowledge is used in the cell reference where all we need is to know the address of the cell.

The steps to create the formula are:

  1. Select a cell where you want to have the formula. Make sure that you are selecting an empty cell otherwise the value will be overwritten.
  2. After a double click, the cursor will appear and you can write in the cell.
  3. You can either write your formula directly in the cell as described above, or you can choose to write the formula in the empty bar above the spreadsheet.
  4. Start with an equal to sign. Without this particular sign, whatever you write will be accepted as a value entered into the cell—and not a formula. You could say the equal to sign is an identification symbol between a formula and normal text.
  5. The syntax of writing a cell-referenced formula is as:
    =D3*75
    The result will be a product of the value in cell D3 and 75.

    If you wish to apply the same formula to all the cells of that given column, then press Ctrl+D. This will assign values to the cells of the same column based on the formula =D3*75.

How to Create a Formula in Excel Using the Point and Click Method

This method results in more accuracy in the creation of formulas. Because it is easy to make mistakes when writing the cell reference address (since you must accurately notate both the column and the row), this method helps avoid those potential errors.

The steps are as follows:

  1. Click the cell where you want to have the result and write the equal to sign in the cell.
  2. After that, click the cell that contains the operand.
  3. This will add the cell address to the desired cell.
  4. Add the operator that you want, and again click the next operand cell.
  5. Go on with this method until you have all the operand cell’s addresses in the formula.
  6. Press enter and your calculated result will appear in the cell.

Whenever a reference is drawn, a blue dashed line appears around the referenced cell. This will let you know which cell’s reference has been used.

How to Create a Formula in Excel Using Stored Functions

Excel formulas can also be created using the functions that are stored in the Excel library. For example, if you want to add the values in three cells, then all you have to do is write the SUM function in the cell.

The steps are:

  1. Click the cell where you want to have the result
  2. Type in the function of the operation, for example, AVERAGE for finding out the average of a given range of data
  3. The format of writing the formula is:
    =SUM(D3,D6)

Here the function itself will act as the operator and so there’s no need to mention it.

I hope you found this tutorial useful! Please feel free to ask any questions below, and have a good time working with Excel!

Comments are closed.


Magoosh blog comment policy: To create the best experience for our readers, we will only approve comments that are relevant to the article, general enough to be helpful to other students, concise, and well-written! 😄 Due to the high volume of comments across all of our blogs, we cannot promise that all comments will receive responses from our instructors.

We highly encourage students to help each other out and respond to other students' comments if you can!

If you are a Premium Magoosh student and would like more personalized service from our instructors, you can use the Help tab on the Magoosh dashboard. Thanks!