Working with Excel Formulas and Functions

One of the biggest advantages of using Microsoft Excel is the fact that it enables you to perform an advanced analysis of all types of data. Using Excel’s existing functions, we can do just about anything with our information, whether we are dealing with numbers or letters.

Excel has many functions to offer, from financial and mathematical ones, to logical functions and many more in between. We are also able to manually alter and enhance the existing functions using formulas.

Here are some basic tips on how to use functions and formulas in Excel.

1)      Entering functions

All Excel formulas begin with an equals sign (=) and can be manually inputted when you know the exact formula beforehand.

A simpler way to input formulas is to use the option Formulas – Insert function. Here you can pick the formula you want to use. You can also see a short description of what the formula does and what information is being used in the formula.

Now that you have selected the formula you want to use, select the information that you want the formula implemented on. You can do this by clicking on the cell in which the data you want to implement the formula on is located.

2)      Combining formulas

Standard math functions (+,-, *, /) can be used as functions and for combining functions. A formula which contains numbers (for example, 2+2) does not change, while the formula given to certain cells changes according to what is located within the cells. When combing them, you adhere to the basic rules of math. First you calculate what is in the parenthesis, then multiply or divide, and finally subtract or add.

3)      Showing all formulas

Click on Show formulas or CTRL+` to show all calculations as formulas. This enables you to easily analyze them. This is a good way to see in which cells that formulas are and which cells do not have formulas.

If you double-click on a single formula, you can see which cells are part of the formula, which makes it easier to work with them and control them.

4)      Showing links between formulas and cells

It’s easy to get lost when you are using many formulas at once. In times like this, it’s good to use the Trace Precedents and Trace Dependents options.

The Precedents cells are the cells that are part of a formula, while the Dependents cells are the cells that house the formula or function.

Here’s an example:

If you have a cell and want to know which formula belongs to that cell, select that cell and click Trace Dependents. You will then see an arrow which will point at the cell with the formula inside it. You can do the opposite by clicking on Trace Precedents. You will be able to erase the arrows that you want to erase by clicking Remove Arrows.