Here’s What to Do References such as D1 and B4 are called Relative References. As you copy aformula containing relative references, the cell being referenced in the formulachanges.Sometimes, you want a cell reference to always point to a certain cell. In thiscase, you always want to use the growth rate in cell D1. To do this, change thereference style to an absolute reference by placing a dollar sign before thecolumn letter and row number.

In this case, you need to change the formula in C4 to =B4*$D$1. Now, as you copy the formula, the reference to B4 is allowed to change butevery cell continues to point to the growth rate in D1. You can enter newgrowth rates in D1 to play what-if games.

Note that there are times where you want to freeze the row number but allowthe column letter to move. In this case, a reference such as =C$1 will achievethat result. This is called a Mixed Reference. Other times, you will want a formula to always refer to a value in column A,but it is OK to have the row number change. In this case, a reference such as=$A4 will work. This is also a mixed reference.

Excel Details To simplify the entry of dollar signs in references, you can use the F4 key.When you are typing a formula, hit the F4 key immediately after typing a cellreference. The first time that you hit F4, the reference changes from =D1 to=$D$1. Hit F4 again to toggle to =D$1. Hit F4 again to toggle to =$D1. Hit F4once more to toggle back to a relative reference of =D1.

Highlighting Outliers Here’s the Situation You have a large dataset. You want to find the transactions which are out ofthe ordinary. Perhaps you want to find the largest or smallest transactions, ortransactions above average. Here’s What to Do Use the new Conditional Formatting options in Excel 2007. The opportunities for highlighting outliers with conditional formatting havedramatically improved in Excel 2007. The combination of easy conditionalformatting with the Sort by Color option is fantastic. Most of the chapters in this book focus on illustrating how to do something inExcel 97-2003 and on how this is different in Excel 2007. However, the first90% of this chapter focuses on the improved features in Excel 2007. The finalexample covers conditional formatting in Excel 2003.