in a previous excel video we showed how it was usually best to specify constants
outside an Excel data table rather than repeating them in each row or column but
for this to work you need to understand the difference between relative and
absolute cell references here's a simple table showing the scores for a group of
people to get the total for each person all you need to do is to use the sum
tool enter score 1 + score 2 then pull the formula down and each score is
calculated see how the cell reference has moved relative to each line that's
because Excel uses a relative reference but if you want each calculation to
refer to a constant in the same cell you need to tell Excel to use an absolute
reference there are two ways to make Excel refer to a constant as an absolute
reference one way is to add the dollar sign before either the row or the column
number or both adding a dollar sign before the row number like this makes
the row a constant that keeps the column relative a dollar sign before the column
letter keeps the column constant but the row relative and a dollar sign before
both keeps both row and column fixed as constants an example might be when
you're applying sale tax to a set of prices here's an extract from a list of
products with their unit price you'll see that there are three different tax
rates suppose the tax rates change imagine having to change every line in a
table with hundreds of entries a long and boring job and a recipe for making a
mistake it's much easier to define those tax rates externally as constants and
create a reference to those external constants in your
table that way you only need to change each tax rate once should there be a
change and here's the completed table
see how by changing a single constant cell all the related calculations are
changed automatically
but you'll never remember which cell reference to add so you'll probably have
to look back at the constants every time you want to add them to the functional
formula to check their proper address a much better way is to give each constant
cell a unique name and to refer to that name rather than the usual row and
column reference that way you don't have to keep checking the references address
here's how it's done at the top left of the navigation bar find the cell name
and select it type a unique reference name something that you'll recognize and
press Enter and it's done now you can refer to that cell by name and you'll
get an absolute reference to it it's so much easier to work with so let's
complete our table using an absolute reference with those names
bingo job done eTop Technology we're here to help
if you like these videos please subscribe to our YouTube channel if you
found this video helpful check out some of our more recent videos and if you're
looking for a new IT partner please visit our website