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.

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 cialis 2.5mg 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.

cialis 100 mg class=”entry” align=”justify”>If you want to display both text and date data in one cell, you’ll need to include an extra formatting step. See how to use the TEXT function to get the desired results.

Excel does not normally let you combine text and dates together in the same cell. For example, suppose you have entered the formulas shown in Figure A.

Figure A

Figure B shows the results.

Figure B

While the value in A2 displayed correctly, the value in A1 did not. This is because Excel displays the actual date data entered into the cell until it’s formatted. Thus, to show the data in A1 as a true date, we need add the TEXT function to change the format to text. When we replace the formula in B1 with

="Today is"&" "&TEXT(A1,"MM/DD/YYYY")

we get the results shown in Figure C.

Figure C

With a handful of well-constructed formulas, you can keep on top of the latest information in your Excel worksheets.

By adding a few formulas to your worksheet, you can keep your employees up to date on the latest sales figures. For example, suppose you use the worksheet shown in Figure A to keep track of daily sales for the month.

Figure A

To cheap generic cialis show the Total Sales-To-Date, enter the labels and formulas shown in Figure B.

Figure B

The formula in E2 searches the data cells in the column for a match to today’s date, calculated by the Today()function in E1. When Excel finds a cell whose value matches the date in E1, it returns the address of the cell to the right of the match. The SUM function uses the INDIRECT function to specify E2 as last cell in the range to be totaled.

You can perform different calculations by replacing the SUM with another function. For example, to find the average sales for the month up to today, replace SUM in the formula in E3 with AVERAGE.

Here's a super quick copy trick for MS Excel!

Have you ever needed an exact copy of a cell's contents?

And I do mean exact. Even cell references won't be changed in your formulas.

Well, here's the trick: simply select the cell directly below the item to be copied daily cialis and use the Ctrl + ' (Ctrl + apostrophe) key combination.

Yep, that's all there is to it! The cell contents are duplicated and can now be moved to whatever location you choose.

And those cell references in your formula? Well, unlike the results from the usual copy/paste, this method leaves them exactly as they were.

Now, that's what I call a true copy!

Do you enter a lot of special data into MS Excel, such as zip codes, phone numbers or social security numbers?

Ever find yourself wishing the parenthesis and/or dashes would just fill themselves into the correct locations? I mean, wouldn't it be faster if you could just key 5551234567 in for a phone number and have (555) 123-4567 appear in the cell after you hit the Enter key?

Yeah, I like that idea too. So, let's see what Excel has to offer!

First, you'll need to highlight the cells you're going to work with and then navigate to the Format Cell dialogue window.

For those of you using older versions of Excel, you'll go to the Format menu, Cells choice.

In Excel 2007, you can get there from the Home tab on the ribbon under the Number section. Click on the little down arrow in the bottom right hand corner or choose "More Number Formats" from the bottom of the Number Format drop down list.