Microsoft Office Tutorials and References
In Depth Information
Using the TRANSPOSE Function to Formulaically Turn Data
Tip
It is difficult to select a cell that contains a HYPERLINK function.
If you click the cell, Excel attempts to follow the hyperlink. Instead,
click the cell and hold the mouse button until the pointer changes
from a hand to a plus.
Figure 12.41 shows a list of web pages in column A. Column B contains the
titles of those web pages. To quickly build a table of hyperlinks, you use
=HYPERLINK(A2,B2) in cell C2 and copy the formula down the column. Un-
fortunately, you must keep columns A and B intact for the hyperlink to keep
working. You can hide those columns, but there is no Paste Special option to
convert the formula to values that will keep the hyperlink.
Figure 12.41.
Figure 12.41. The formulas in column C enable you to create hundreds of hy-
The formulas in column C enable you to create hundreds of hy-
perlinks in seconds.
perlinks in seconds.
To keep only the hyperlinks, copy column C and paste to a blank Word doc-
ument. Open a new workbook. Copy from Word and paste back to the new Excel
document.
Alternatively, use ="#HYPERLINK("""&A2&""""&",
"&""""&B2&""""&")" in C2. Copy down and paste Special Values. Use
Find and Replace to change # to =.
Using the
Using the TRANSPOSE
Function to Formulaically Turn Data
With many people using Excel in a company, there are bound to be different
usage styles from person to person. Some people build their worksheets ho-
TRANSPOSE Function to Formulaically Turn Data
Search JabSto ::




Custom Search