The Basics of Creating Formulas in Excel

Ever since Microsoft Excel was developed, it’s become the leading spreadsheet of choice in the business world. It’s ability to store data and perform numerous mathematical operations proved quite useful for people who were looking to record their transactions, plan their budgets, and organise their data. Never before has an application been created to execute multiple functions simultaneously while drastically reducing the time needed to make complex calculations.

Excel can be utilised in a variety of ways, but the most used feature of the spreadsheet is the formulas. The formulas in Excel make macro programming much easier thanks to its ability to lay charts and make calculations. Understanding these formulas is key to utilising Excel to its full advantage and today, we’ll take a look at the basics of creating formulas to help boost your productivity. For more information on our Microsoft Excel courses, contact us today.

Basic formulas in Excel

You can easily create formulas in Excel by understanding a few tricks and methodologies. Excel formulas are created in two ways:

1. Write the operation in the designated cell.
2. Use the cell reference technique where you want the value to be calculated.

There’s no need to worry about complex macro programming because Excel does it all for you. The spreadsheet uses standard operators for the formulas so you don’t have to search for them. The Excel compiler is also capable of evaluating nested functions. Nested functions or formulas are two or more formulas that are separated by brackets.

When finding the average of three cells, you can simply write a formula like:

=(A3+A5+C+5)/3

In this formula, both the addition and division occur simultaneously. Do note that the addition function is prioritized because of the enclosure using the first bracket.

Creating a formula with value reference

When creating a formula following a value-based method, consider the following steps:

1. Choose an empty cell in the spreadsheet.
2. Double-click on the cell and write the formula.
3. Always remember that every formula starts with an equal (=) sign.

When writing the formula, use the equal sign and follow that up with the operation. So if you want to find out the percentage of a specific value, you can write a formula like this:

• =(50/100)

If you want to find the sum of four numbers, the way you write a formula is this:

• =(1+3+5+7+9)

The answer to the formulas will be shown in the cell that you typed on. It’s important that whatever formula you are typing, it has a clear answer and isn’t something uncertain. When you type in ambiguous values, you receive an answer in sterling pound symbols. This indicates that the answer is too large for the column width. To alleviate this, just drag the column boundary to display the answer in full.

Creating a formula with cell reference

When using values, you have to be careful with typing them, especially when writing long formulas. This can prove tedious and time-consuming on your part. To make things a bit easier, you can create formulas using the cell reference method. In this method, the chosen cell is the one where the operand’s value is written. Since Excel is a spreadsheet, the columns are labeled by letters and the rows are labeled as numbers.

Each cell is designated with its own address. You can find out a cell’s address by looking at its column and row position. For example, when you write A5, that means the cell is located in column A at row 5. This particular knowledge is crucial when creating a formula with the cell reference method.

Here are the steps to create the formula.

1. Choose an empty cell in the spreadsheet.
2. Double-click on the cell and start writing.
3. You can choose to write the formula directly on the cell or on the empty bar on top of the spreadsheet.
4. Always remember that every formula starts with an equal (=) sign.
5. The syntax of writing a formula with cell reference is as follows:

• =A5*75

The result of the formula will be a product of the value in the A5 cell and the number 75. You can press Ctrl+D if you want to apply the same formula across all the cells of that given column.

Create a formula with point and click

Creating a formula with the point and click method is more accurate than the cell reference method because you can easily make mistakes when writing the cell address. To avoid such errors, consider writing a formula using this method.

Here are the steps to create the formula:

1. Choose an empty cell in the spreadsheet.
2. Write and equal sign and click on the cell containing the operand.
3. Add your desired operator and click the next operand cell again.
4. Continue with this method until you acquire all the cell addresses of the operand in the formula.
5. Hit enter to display the calculated result in the cell.

A blue dashed line around the cell will show up whenever a reference is drawn to notify you which cell’s reference has been utilised.

When it comes to calculating values, formulas are your bread and butter. It’s the most basic yet the most used function in the spreadsheet. You’ll find that the use of formulas influence everything you do in Excel, which in turn can really boost your productivity levels. Take the time to learn the basics of creating Excel formulas and you’ll be well on your way to mastering the spreadsheet through macro programming.