Apple Event: May 7th at 7 am PT
I know how to increase individual cells via formula to increase a number by a certain percentage, but is there a way to increase an entire column (as in Excel) to avoid having to type the formula in each cell?
MacBook Pro 15″, OS X 10.11
Hi ginka,
The only version of Numbers that (if memory serves) 'master formulas' for action on a full column was version 1 (Number '08). The feature (if it really existed) was dropped for Numbers '09, and hasn't (re)appeared since then.
You need a formula in each cell of a column separate from the one currently containing the amounts to be changed, BUT you don't need to "type the formula in each cell."
Example: Column B contains the current values.
Click once on any cell of column B of of any column to the right of column B. (I chose D2)
Then press option-right arrow to add a new column to the right of the cell you chose.
Click on the top data cell in the new column, the press = to open the formula editor.
Enter the formula shown below the table, using the decimal equivalent to the percentage by which you want the amounts in column B to be increased (0.01 for 1%, 0.11 for 11 %, etc.)
Click the green checkmark to confirm the entry and close the editor.
With the cell still selected, and the editor closed, press command-C to Copy the cell (and its formula).
With the cell still selected, move the pointer to the last cell in the column, then Shift-click (press and hold shift, click on the cell, then release the shift key).
With the column selected, press command-V to paste the formula into all cells of the column. The column will show the results.
With the column still selected, press command-C to copy the results (and the underlying formulas)
Then click once on B2 to select it, and
Go to the Edit menu and choose Paste Formula Results.
Finally:
Click on the column reference tab for the new column (here, E)
Click the disclosure arrow ( v ) at the right end of the reference tab, and choose Delete column.
(Or choose Hide Column if you want to keep it available for the next change.)
Regards,
Barry
PS: If you decide to keep the temp column, you might want to delete all copies of the formula except the one in row 2 to reduce the processor load of calculating these results each time there is a change to any cell in the table.
B.
Hi ginka,
The only version of Numbers that (if memory serves) 'master formulas' for action on a full column was version 1 (Number '08). The feature (if it really existed) was dropped for Numbers '09, and hasn't (re)appeared since then.
You need a formula in each cell of a column separate from the one currently containing the amounts to be changed, BUT you don't need to "type the formula in each cell."
Example: Column B contains the current values.
Click once on any cell of column B of of any column to the right of column B. (I chose D2)
Then press option-right arrow to add a new column to the right of the cell you chose.
Click on the top data cell in the new column, the press = to open the formula editor.
Enter the formula shown below the table, using the decimal equivalent to the percentage by which you want the amounts in column B to be increased (0.01 for 1%, 0.11 for 11 %, etc.)
Click the green checkmark to confirm the entry and close the editor.
With the cell still selected, and the editor closed, press command-C to Copy the cell (and its formula).
With the cell still selected, move the pointer to the last cell in the column, then Shift-click (press and hold shift, click on the cell, then release the shift key).
With the column selected, press command-V to paste the formula into all cells of the column. The column will show the results.
With the column still selected, press command-C to copy the results (and the underlying formulas)
Then click once on B2 to select it, and
Go to the Edit menu and choose Paste Formula Results.
Finally:
Click on the column reference tab for the new column (here, E)
Click the disclosure arrow ( v ) at the right end of the reference tab, and choose Delete column.
(Or choose Hide Column if you want to keep it available for the next change.)
Regards,
Barry
PS: If you decide to keep the temp column, you might want to delete all copies of the formula except the one in row 2 to reduce the processor load of calculating these results each time there is a change to any cell in the table.
B.
Numbers: How to increase a column of numbers by a certain percentage
