You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Copy and paste forumulas in Numbers

Why does Apple make it so difficult to copy and paste a formula from one cell to a range of cells?


Could also be I don't know enough about Numbers, but I found this impossible to do.


Basically I erased the original formula from the cell, then selected the range of cells I wanted to copy the formula to and couldn't, then I created the formula over again to enter it in the cell range, which recalculated the starting cell with the original formula that I had to delete to do this as well as the cells in the range. Sounds convoluted but it only took a minute to do.


Posted on Jun 19, 2020 11:24 AM

Reply
Question marked as Top-ranking reply

Posted on Jun 19, 2020 3:52 PM

The usually recommended practice is quite similar to what you describe:


  • Enter the formula into the top cell of the range that is to contain it.
  • Select that cell (one click), then copy.
  • Shift-click on the bottom cell in the range to add it and all cells between it and the top cell to the selection, then paste.


Regards,

Barry

6 replies
Question marked as Top-ranking reply

Jun 19, 2020 3:52 PM in response to ecodoc59

The usually recommended practice is quite similar to what you describe:


  • Enter the formula into the top cell of the range that is to contain it.
  • Select that cell (one click), then copy.
  • Shift-click on the bottom cell in the range to add it and all cells between it and the top cell to the selection, then paste.


Regards,

Barry

Jun 20, 2020 12:14 AM in response to ecodoc59

"The thing is, I could not copy the formula from the bottom cell where the calculation was correct and paste it into the range of cells above it in the column to duplicate the formula in those cells."


Try this:

C10 and D10 contain the same formula, shown below the table.



Procedure 1:

Select (one click) C10. Copy.

Shift-click on C2 to extend the selection to B2:B10. Paste

Note results.



Procedure 2:

Double-click D10 to open the Formula editor.

Press command-A to select the formula in that cell. Copy.

Press shift and click (or double click) D2 to select the whole column. Paste

Note the result.

In Procedure 1, you copied the cell and the formula it contained.

When pasted to the column, Numbers adjusts the formula to so that each copy references the cell in the same position, relative to its cell as the original was relative to the position of its cell.


In procedure 2, you copied the formula from the cell, then pasted that formula (as text, as it was not pasted into the Formula Editor) into each cell in the column.


If your own procedure differed brom both of these, you may have had different results.


Note also: Dragging the yellow dot, as described by SGIII, can also be done in the opposite direction. With the formula replaced, as written, in C10, hover the pointer near the top border of that cell to make the dot appear, grab the dot with the pointer, and drag up to C2. The formula will be filled into the cells and adjusted in each row to reference the column A cell on that row, with the results below:


Whether to choose 'Copy the cell, select the column, paste' or 'Select the cell, drag the fill control up or down)' depends on how tall the column is. Drag is easier for short columns, Copy, select, paste is more efficient for long columns.


Regards,

Barry


EDIT: Saw the notice of SG's reply above coming in, but was writing the same message at the time and didn't get to read his note, hence the 'echo.'


PS: No need to do two (or more copies of a reply. Every participant in a discussion gets a copy of every new post in that discussion. So SG got your most recent reply to him AND your identical reply to me. And so did I.


B.

Jun 19, 2020 10:08 PM in response to SGIII

The thing is, I could not copy the formula from the bottom cell where the calculation was correct and paste it into the range of cells above it in the column to duplicate the formula in those cells. So I had to resort to the procedure I described to enter the formula in all the cells in the column.


Thanks for your help!

Jun 19, 2020 10:09 PM in response to Barry

The thing is, I could not copy the formula from the bottom cell where the calculation was correct and paste it into the range of cells above it in the column to duplicate the formula in those cells. So I had to resort to the procedure I described to enter the formula in all the cells in the column.


Thanks for your help!

Copy and paste forumulas in Numbers

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.