Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

My spreadsheet has rows with cells that contain either numerical values or formulas that use these values (they access only values from the same row). Each row corresponds to an event, when an event of this type happens there should be a new row. The formulas never change.

Now I insert a new row, copy the last one and paste it over the new one. It clones the formatting (which is good), the formulas with updated references (which is good) and the data (which is very bad). Then I type the new data over. It seems very error-prone.

What is the right way to do it? It has to be an idiom.

share|improve this question
up vote 0 down vote accepted


  • I’m not 100% sure that I understand your question.
  • I don’t have access to LibreOffice Calc; only Excel.

… but here’s my 2¢ worth:

  • Create a pristine copy of your event data row.  Fill in the formulas where they belong.  Leave the cells that are destined to hold variable values blank, or enter 0, or enter –1, or enter =1/0, so they will show up as errors –– whatever you prefer, to make it obvious that they need to have valid data entered into them.  This can be Row 1 –– maybe Row 1 of a separate (otherwise unused) sheet.  Then, whenever you need a new row, go to the pristine row, copy it, then go back to where you were working, and paste it.  This has the obvious disadvantage of requiring two or three more mouse actions each time you add a row.  (Optionally, you can protect this row, so you cannot modify it.)  Or,
  • Write a macro to move the cursor down one row and fill in the new row with the functions and null/invalid values, as described above.  Or,
  • Protect (lock) the columns of cells that contain the formulas.  Then write a macro to copy the current row, move the cursor down one row, unlock the sheet, paste the row, and reprotect the sheet.  You should then be able to Tab between the variable value cells.
share|improve this answer
Options 1 and 3 are exactly what I want to avoid. A macro would work, but I hoped there should be a simpler method. – XXYXXY Jan 3 '13 at 7:07

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .