Tag: 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.