Technology Training

How to use Relative and Absolute Cell References in Microsoft Excel 2016

When working with a Microsoft Excel 2016 data table you may find that it is useful to define a constant outside of the data table rather than repeating it in every cell. Suppose you are trying to apply three different tax rates to a data table and you need to be able to adjust those tax rates without having to change them in every cell. Learning how to use cell references will allow you to do this by changing the reference rather than changing every row or column.

First, you will need to know the difference between defining a reference as relative or absolute. If for example, you are trying to add two columns together and you are using the SUM tool, you will take the first two number sets from each column and add the two together in a third column. This will automatically create an equation that you can extend to the rest of the numbers in the remaining column simply by placing your cursor over the sum of the first two numbers and dragging down the third column. This will extend the SUM equation to the rest of the numbers in the first two columns providing you the sum for the remaining number sets. This equation is using a relative constant because it changes with every set of numbers.

For the purpose of this video, we are going to focus on how to use an absolute constant. This is the equivalent of giving an equation an address to reference and whatever information you enter into that address will be reflected in the equation. In this case, the address is the X and Y access of the cell that you choose to use as your absolute cell reference. In our previous example, you would need to set up three cell references to correlate with the three tax rates. By labeling each tax rate A, B and C you can reference the tax rate in the data table allowing Excel to know which absolute constant to use in each equation.

Technology Training

Microsoft Excel 2016 Training “IF” Formula with “NOT” and “OR” Operators

In the last two Microsoft Excel training videos, we focused on using the “IF” formula, its Boolean operators and how to nest an IF statement inside of another IF statement. Today we will finalize our training on Excel IF formulas by reviewing how to use “NOT” and “OR” operators.

The NOT and OR operators are among the most common operators you will use in the IF formula. These operators allow you to create equations that will say “IF a workbook cell is NOT equal to X OR Y, then perform XYZ action.”

Let’s suppose you are needing to create a spreadsheet and you want a logic formula to adjust the prices of your products up or down according to their sales performance. An example of this would be if your yellow and pink products are not selling and you want to increase the price for the rest of your products while keeping the yellow and pink products the same price. These operators would allow you to say “IF the product is not Yellow or Pink, then increase the price.”

There are many ways to use these operators, and if you combine them with the operators that we reviewed in previous videos, you will find the IF formula to be quite powerful. With some practice, you will find that these operators are able to help you make most any calculator and will reduce your redundant workload.

CW Portal