So far we've created formulas with relative cell references this means that this all references are relative to the location of the formula. Now were creative formula it uses and absolute cell reference. We’ll add a column next to the total column where we’ll calculate the percentage of total sales for each ticket price. To do that we’ll divide this ticket price total by the grand total.
We’ll create a formula to calculate this and the copy the formula to the rest of the ticket price rows. We do want the relative reference to this cell to change as the formula is copied down. However, we don’t want the reference to the grand total cell to change, we want it to remain unchanged or absolute. So for this part of the formula we’ll use an absolute reference. We’ll start with the column title percent of sales.
To start the formula we type an equal sign, we select the ticket price total. This cell reference will be relative, then we type a forward slash for division, now we select the grand total cell the cell we want to divide it by. We want the address of this cell to be absolute. The symbol that tells the cells that a cell reference is absolute is the dollar sign, to put dollar signs in a formula we use the F4 function key. We make sure the blinking insertion point is at the cell address we want to make absolute and we press the F4 key. Dollar signs appear before the column letter and row number of the grand total cell. Now when we copy the formula this column letter and row number wont change, the formula is complete so we press enter. The result shows the full of price percentage as a decimal then we drag the fill handle down to copy the formula to the rest of the percent of sales cells.
We’ll format this as percentages in the next topic. Now lets look at our formulas in the formula bar, this is our original percent of sales formula, this is the first cell we copied it too. The reference to the ticket price total cell has changed but the reference to the grand total cell remains the same. And the same is true for the last percent of sales formula. Our absolute references work just the way we intended. You can also use labels and range names and absolute references if you want. And now here is a quick look at another excel feature that like labels and range names helps you know what formulas refer too. Its called range finder, when we edit a formula the range finder feature displays each cell or range reference in a different color. The corresponding cell or range has some border of the same color and range finder not only helps us understand formulas it helps us change them.
We can refer to a different range by dragging the range border and we can change the size of the range by dragging the handle in the lower right. Since this was just a demonstration we’ll cancel our editing by pressing by the ESC key. You’ve seen a lot of the features of using excel formulas but the feature that really makes then useful is their automatic recalculation. When worksheet data changes the formula results change automatically that means you don’t have to retype the whole worksheet every time you change a number.
To demonstrate, watch what happens when we revise one of the ticket sales figures. We select the cell containing the July full price sales figure and we type the revised sales information. When we press enter the formulas that use that information automatically recalculate. We've just enter the new number as an example, we really do want to keep the previous sales figure. To bring back the old figure we’ll undo our last change with the undo feature. We open the edit menu, the undo feature shows what it will undo, we click on it the change is undone and the formula results are updated accordingly.
The edit menu also includes a redo feature, to redo a change that we undid. And the standard tool bar has buttons for undo and redo. This buttons each have a drop down list that lets us undo or redo more than the last change. As you work with excel formulas you'll probably find time when absolute cell references are useful and if you prefer you can type the dollar signs in your cell. You can even use just one of the two dollar signs to make just the well reference or just the common reference absolute, absolutely, absolutely.
Transcription by:
Scribe4you Transcription Services