Pasting values: Basic how-to

Still with the example of the book inventory, imagine you have a very long series of books to record. Now, there are a variety of alternatives you could try, and all of them could be good for one purpose or another. But for this situation the best choice is to use a simple formula, and then paste the results of that formula as values.

First, to convince you of why this technique is the best one for this situation, let’s look at the alternatives. You could, of course, enter the first letter or two of the author, and let it auto-complete the field. But you’d have to do that for every book in the series, and that could get very tedious. You’d also have to do it for the publisher, the series, and probably the binding. Why waste all that effort?

You could copy that information from the first entry and paste it across as many rows as the series lasts. But suppose you’re working with a single author with a lot of different trilogies. In that case you’d have to copy and paste it every three books, which wouldn’t actually save much effort. Or suppose you don’t actually know how many books there are in the series. In that case you could guess, but there’s a good chance you’d either over- or under-estimate.

However, there’s a simple formula that will do a lot of the work for you. You may have already thought of it while reading. Simply make each cell that is likely to have repeating information equal to the one above it, and copy that formula down as far as you feel like. For example, the formula in cell b2 would be “=b1”. That way when you enter the author’s name in row 1, all the remaining authors change to match it. If the book in row 2 is by a different author there’s been no harm done, but if it’s the same author you don’t have to type anything at all. You can skip the author column and go on to whatever’s next. In a long series where the author, publisher, series, binding, and rating are all the same, it may be that the only thing you’d need to record is the title! Then when you’re ready for a new author/series simply enter the new information over the formulas for the appropriate row, and all the following rows will change to match.

However, while this makes data entry very easy it has one significant drawback: sorting. Suppose you entered a Lois McMaster Bujold book before you entered one by Douglas Adams. If you tried to sort it by author, Adams would move before Bujold, and suddenly the cell that was previously referred to might not be what it used to be! This is especially true if you sort by more than one column… say by author and series so that all of the books in a series are together, and all of the series by the same author are together.

How do you get around that? That’s easy. Once you’re done entering the date, just copy it all and paste it as values. In other words, you’re telling the spreadsheet to paste the result of the formula, not the formula itself. That’s not an ordinary paste, though, so here’s how you do it.

No matter what program you’re using, you need to start by selecting everything you want to copy & paste. Then copy it, and don’t de-select or move your cursor.

In Excel 97:

  1. Click on the “Edit” menu, or type “alt-e”.
  2. Click on “Paste Special” or type “s”.
  3. In the menu that pops up, there are a lot of options. For now we only care about one. Click on the radio button next to “Value”, or type “v”
  4. Click on the “OK” button, or hit enter.

In Excel 2007:

  1. In the Home tab, look at the “Clipboard” group. For me it’s the first one on the left.
  2. Below the graphic of the clipboard and the word Paste, there’s a downward-pointing arrow. Click on it.
  3. Click on “Paste Values”

Note: The keyboard shortcut for Excel 97 (Alt-E-S-V-enter) still works in Excel 2007. If they ever change it I’ll be sunk because I swear I sometimes type that shortcut in my sleep.

In Google Spreadsheet:

  1. Click “Edit”.
  2. Click “Paste values only”.

In OpenOffice Calc:

  1. Click “Edit”
  2. Click “Paste Special…”
  3. In the window that pops up, make sure there is not a checkmark next to “Paste all”.
  4. Make sure there is not a checkmark next to “Formulas”
  5. Make sure there is a checkmark next to “Text”.
  6. Make sure there is a checkmark next to “Numbers”.

In Microsoft Works Spreadsheet:

  1. Click “Edit” or type “Alt-E.
  2. Click “Paste Special…” or type “S”.
  3. In the window that pops up, make sure that the radio button next to “Insert the value into the cell” is selected.
  4. Click “OK”