10 obscure Excel tricks that can expedite common chores
10: Convert calculations to literal values
This jewel, like Tip 9, is also found under Edit | Paste Special. Experienced users may squawk that this tip doesn't qualify as obscure. However, I decided to include it because, in teaching both beginning and advanced Excel classes to thousands of adult learners over the years, I've met too many people who never even heard of it--which is a crying shame.
Here's how it works. Suppose you have a worksheet with columns and rows chock full of calculations, running the gamut from Sum functions to If tests to vertical and horizontal lookups. The calculations are correct and your data is pristine. You save the worksheet.
Now you need to use a subset of that worksheet in another worksheet. If all you're going to do is print the subset of columns or rows, you can simply hide those rows and columns, print what you need, and unhide the columns and rows later to restore the sheet to its normal state.
But if you're going to e-mail a copy of the spreadsheet to a coworker or a third party, you may not feel comfortable simply hiding certain rows and columns. You may want to delete them instead. The problem is, of course, if you simply start deleting rows and columns, you're going to get error messages in the cells that depend on the cells you deleted.
The solution? First and foremost, save a copy of your pristine spreadsheet under a new name. Just go to File | Save As and add "_work" to the end of the "real" name. Use the Select All tip (1) to select the entire sheet and then copy it. Without moving the cursor, go to Edit | Paste Special. Now, select the Values option, as shown above,and click OK. When you do, Excel will replace all the formulas with the values they're currently calculating and displaying. At that point, you can delete columns or rows and move cells around without generating a single error message.
Note: If you use the Paste Special | Values option and the data you're pasting contains calculated dates or numbers formatted as currency, the date calculations will be pasted as the Julian date value, and the currency will lose its dollar signs and commas. To preserve that kind of formatting when you convert calculations to literals, simply choose the Values And Number Formats option (instead of Values).
Here's how it works. Suppose you have a worksheet with columns and rows chock full of calculations, running the gamut from Sum functions to If tests to vertical and horizontal lookups. The calculations are correct and your data is pristine. You save the worksheet.
Now you need to use a subset of that worksheet in another worksheet. If all you're going to do is print the subset of columns or rows, you can simply hide those rows and columns, print what you need, and unhide the columns and rows later to restore the sheet to its normal state.
But if you're going to e-mail a copy of the spreadsheet to a coworker or a third party, you may not feel comfortable simply hiding certain rows and columns. You may want to delete them instead. The problem is, of course, if you simply start deleting rows and columns, you're going to get error messages in the cells that depend on the cells you deleted.
The solution? First and foremost, save a copy of your pristine spreadsheet under a new name. Just go to File | Save As and add "_work" to the end of the "real" name. Use the Select All tip (1) to select the entire sheet and then copy it. Without moving the cursor, go to Edit | Paste Special. Now, select the Values option, as shown above,and click OK. When you do, Excel will replace all the formulas with the values they're currently calculating and displaying. At that point, you can delete columns or rows and move cells around without generating a single error message.
Note: If you use the Paste Special | Values option and the data you're pasting contains calculated dates or numbers formatted as currency, the date calculations will be pasted as the Julian date value, and the currency will lose its dollar signs and commas. To preserve that kind of formatting when you convert calculations to literals, simply choose the Values And Number Formats option (instead of Values).
Print/View all Posts Comments on this gallery
10 obscure Excel tricks that can expedite common chores
maryb@... | 05/15/07
|
Wow!!! Love Love LOVE it!
meryllogue@... | 05/15/07
|
