The referenced cells in columns C and D will update relative to where you copy the formula. If, as in the case of the concession data, the column to the left is fully populated, you can double-click the bottom-right corner of the formula to have Excel fill the whole column (see Figure 1-4).
Try this double-click action for yourself, because I'll be using it all over the place in this book, and if you get the hang of it now, you'll save yourself a whole lot of heartache. Now, what if you don't want the cells in the formula to change relative to the target when they're dragged or copied? Whatever you don't want changed, just add a $ in front of it.
For example, if you changed the formula in E2 to:
=C$2*D$2
Then when you copy the formula down, nothing changes. The formula continues to reference row 2.
If you copy the formula to the right, however, C would become D, D would become E, and so on. If you don't want that behavior, you need to put a $ in front of the column references as well. This is called an absolute reference as opposed to a e relative reference.
Formatting Cells
Excel offers static and dynamic options for formatting values. Take a look at column E, the Actual Profit column you just created.
Select column E by clicking on the gray E column label. Then right-click the selection and choose Format Cells. From within the Format Cells menu, you can tell Excel the type of number to be found in column E. In this case you want it to be Currency.
And you can set the number of decimal places. Leave it at two decimals, as shown in Figure 1-5. Also available in Format Cells are options for changing font colors, text alignment, fi ll colors, borders, and so on.
But here's a conundrum. What if you want to format only the cells that have a certain value or range of values in them? And what if you want that formatting to change with the values?
That's called conditional formatting, and this book makes liberal use of it.
Cancel out of the Format Cells menu and navigate to the Home tab. In the Styles section (Mac calls it Format), you'll find the Conditional Formatting button (see Figure 1-6). Click the button to drop down a menu of options. The conditional formatting most used in this text is Color Scales. Pick a scale for column E and note how each cell in the column is colored based on its high or low value.
To remove conditional formatting, use the Clear Rules options under the Conditional Formatting menu.
