NUMBERS...How to copy & paste formulas only

[larrycruzen]

I'd like to replicate an entire table, with formulas but not the data, on another sheet of the spreadsheet. "copy & paste" doesn't offer a "formulas only" option like Excel does. If I "copy & paste" the table, then delete the data, formulas are also deleted.

Is there a way?

Also, I've tried to copy a long nested "IF" statement from the formula line of a cell and paste it into the formula line of a cell on another sheet. Double tapping in the formula line doesn't bring up a paste option.

Is there a way to do this?

Larry Cruzen

 

[twerppoet]

You are right. As far as I can tell there is no way to replicate an entire table (or selection of cells) with just the formulas. When you select Paste Formulas, you get both formulas and data. When you choose Paste Values, formula results are converted to values before pasting.

I've run into this in the past. My solution is to set up a sheet with nothing but tables default values/formulas and name it Templates. I then copy paste new tables and sheets from there.

It requires more planning and work to begin with, but results in a neat, quick workflow afterwards; and gives you a default to go back to incase of mistakes.

On the (many) occasions that I forget to set this up in advance, I make a copy of the sheet or table, then delete the values column by column (or row by row). Not by using the Delete or Cut commands (those delete formatting as well as the value) ,but by double tapping the cell to enter the editor, tapping the backspace key once, then tapping the next cell, then the backspace key, . . . (do not tap Done between entries)

If you tap the backspace key before you enter any other information in the editor, it defaults to clearing the entire cell (while leaving the formatting intact); which makes this slightly less cumbersome than it sounds. Still planning ahead is so much nicer.

---------

I have no trouble pasting formulas, or sections of formulas, into the formula editor. Double tapping can be tricky, since the default here is to select the entire formula. It's usually easier to tap-hold until the magnification bubble appears, slide the cursor to the insertion point you want, then choose paste from the pop-up menu.

 

[larrycruzen]

Thanks, twerppoet, you have helped me out again....by making me think.

The reason I couldn't paste a copied formula into a cell on another sheet was that the target cell was still formatted for numbers, not formulas.....duh.

I agree that foresight is great in setting up a spreadsheet, but I usually just dive in and see where it takes me. In the current case it grew into a monster with some complex (for me) formulas and a couple of nice scatter plots with text showing data limits. I was so impressed that I decided to do the same analysis for each upcoming month.....hence the desire to replicate the charts and table with formulas only.

It finally occurred to me to duplicate the sheet (not copy & paste) and then select and delete all of the input data only. All formulas include an "IF" statement to print a null string if there is no input data.

Mission accomplished.

Larry Cruzen

 

[twerppoet]

No problem.

I have the diving in syndrom as well, hence my description of how to clean up a sheet later (after things are tweaked) to create a template to make copies from.

However, I always tell my self to plan ahead next time, which is why I tell it to everyone else. If it's good advice for me, it's got to be good advice for them, right?

 

[larrycruzen]

YEP. PLAN Ahead.