How do I cut and paste in Excel?
December 19, 2007 11:18 AM   Subscribe

How do I format the cell in Excel so that when I cut and paste a series of data derived from a formula onto a new sheet, it pastes the numbers, and not the formula?

I have a bunch of data. I use the data to write a formula in Excel to derive a new set of numbers. Then, I want to paste the new set of numbers to a new worksheet, either a different page, or a different Excel document. But when I do this, I get that annoying "#REF" error code.

The data might look like this:
Day 1 score ; Day 2 score ; Average score
Person 1 ; 3 ; 1 ; 2
Person 2 ; 0 ; 6 ; 3

The "average score" is done through Excel formula, and I want to copy and paste just the average score to a new sheet/file, not the "day 1 score" and "day 2 score"

What I do to get around the error code is to save the worksheet as a .dat or .txt file so that only the numbers get saved, then copy and paste the numbers to the new file/page. This is a rather cumbersome way of going about doing it, so there has got to be a better way, no?
posted by jujube to Computers & Internet (7 answers total)
 
Best answer: Copy data, click in new xls, right click, "paste special" --> values only
posted by stupidsexyFlanders at 11:19 AM on December 19, 2007


Best answer: Keyboard shortcut: ctrl+C, then alt+e, s, v, enter.
posted by milkrate at 11:36 AM on December 19, 2007


Best answer: Or, if you want menus, it's under Edit/Paste Special (and then click on values only in the dialogue box)
posted by leahwrenn at 11:42 AM on December 19, 2007


Response by poster: Thanks!!!!
posted by jujube at 11:45 AM on December 19, 2007


You can also link your new sheet back to the old one, or make the new sheet a new tab on the old one. As long as the old sheet (if it's a separate file) stays in the same directory on your hard drive, you can pick up that calculated value. Like this:

In your example, the calculated scores are in C2 and C3.

Open up a new sheet (separate tab or new sheet). Place cursor in cell where you want average score to appear. Hit =, then go to your old sheet, place cursor in C2, hit return. This creates a formula on the new sheet linking back to the old one. If values on the old sheet change, when you re-open the new sheet it will automatically be updated so you don't have to cut/paste-special. This will work even if you insert more columns for more days and the average moves to the right.
posted by beagle at 11:51 AM on December 19, 2007


If you do this a lot, you might want to make a macro+shortcut key to do it in 1 step.
posted by signal at 12:14 PM on December 19, 2007


Or, if you do this a lot, you can put a 'paste values' button on your button bar.
posted by still_wears_a_hat at 2:16 PM on December 19, 2007


« Older Blind Date Books?   |   New paralegal job = a million papercuts! How do I... Newer »
This thread is closed to new comments.