Microsoft Office Tutorials and References
In Depth Information
Working with formulas
where they live. Paste List is really the only useful feature in the Paste Name dialog box,
given the superior methods of using names described in the previous section.
Figure 12-18 Click Paste List in the Paste Name dialog box to create a list of names and
references starting at the active cell.
When Excel pastes the list of names, it overwrites any existing data without asking for
permission first. If you inadvertently overwrite data, press Ctrl+Z to undo it.
Replacing references with names
You can replace cell references with their corresponding names all at once by using the
Apply Names command, which you access by clicking the arrow next to the Define Name
button on the Formulas tab on the ribbon. When you do so, Excel locates all cell and range
references for which you have defined names and replaces them with the appropriate
name. If you select a single cell before you click the Apply Names command, Excel applies
names throughout the active worksheet; if you select a range of cells first, Excel applies
names to only the selected cells.
Figure 12-19 shows the Apply Names dialog box, which lists all the cell and range names
you defined. Select each name you want to apply, and then click OK.
Excel ordinarily does not apply the column or row name if either is superfluous. For
example, Figure 12-19 shows a worksheet after we applied names using the default options in
the Apply Names dialog box. Cell D17 contained the formula =D5. Because D17 is in the
same column as the referenced cell, only the row name was needed, thanks to implicit
intersection, resulting in the formula =Region_2. In the figure, cell I17 is selected, and the
formula bar shows it contains the formula =Region_2 Qtr_3, which before names were