Convert Excel calculations to literal values
Thursday, October 16, 2008 01:40 PM
Copying Excel data to a different location can send your calculations into a tailspin. Avoid problems by using Paste Special to copy values rather than formulas.
Microsoft Excel
Convert Excel calculations to literal values
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 sheet 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 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 worksheet under a new name. Just go to File | Save As and add "_work" to the end of the "real" name. Select the entire sheet and then copy it. Without moving the cursor, go to Edit | Paste Special. Now, select the Values option, as shown in Figure A, 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.
Figure A
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.
Microsoft Access
Give your Word documents a professional look by adding symbols
Do your users often need to stop and search the Web for data? Make it easier for them by adding a Web browser control to their forms. For example, say your users need to input the latest stock prices of the companies where your clients have placed investments.
Follow these steps:
- Open the form in Design View.
- Click the More Controls button in the Controls toolbox. Scroll down to select Microsoft Web Browser. (In Word 2007, click the Active X button in the Controls group under the Design tab of Forms Design Tools. Scroll down to select Microsoft Web Browser and then click OK.)
- Click and drag in your form to create the Web browser control.
- Click the Text Box Control tool and then click and drag in your form to create the control.
- Click in the text box control label and type Web Address: (Figure A).
- Right-click the text box control and select Properties.
- Click in the After Update property box and then click the Build button (Figure B).
Figure B
- Type the following code at the prompt:
Private Sub Text17_AfterUpdate() Dim varAddress As String varAddress = Me.Text17 Me!WebBrowser6.Navigate varAddress End Sub
- Press Alt + Q.
Now when a user enters a Web address, the Web page displays in the Web browser control window, as shown in Figure C.
Figure C
Microsoft Word
Give your Word documents a professional look by adding symbols
Just a few quick touches with symbols can spruce up your documents in minutes. For example, say you've just formatted the newsletter shown in Figure A.
Figure A
You'd like to add something at the end of Article One that tells the reader the article ends there; you would also like to add something to indicate that Article Two continues on the next page.
Follow these steps:
- Click at the end of Article One.
- Go to Insert | Symbol. (In Word 2007, click the Insert tab and then click Symbol in the Symbols group and select More Symbols.)
- Click the Font box drop-down arrow and select Symbol.
- Click the symbol in the last row, as shown in Figure B, and then click the Insert button.
Figure B
- Click Close.
- Select the symbol you just inserted and change the Font color to dark green.
- Click at the end of the last column in the document.
- Go to Insert | Symbol. (In Word 2007, click the Insert tab and then click Symbol in the Symbols group and select More Symbols.)
- Click the Font box drop-down arrow and select WingDings.
- Click the symbol shown in Figure C and then click the Insert button.
Figure C
- Click Close.
- Select the inserted symbol and change the font size to 18 and the Font color to dark green (Figure D).
Figure D
Note: When you need to use the symbols again, you can find them listed under the most recently used symbols in the Insert Symbol dialog box. In Word 2007, just click Symbol on the Insert tab.
There are currently no comments for this post.