Ask Your Question

How to add/edit a formula to the entire column of existing data

asked 2017-03-08 17:59:46 +0200

adam1969in gravatar image

I have a column of numbers in decimal format. How can I apply ROUND function to the entire column of existing numbers, say a formula like


edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2017-03-08 18:18:35 +0200

Lupp gravatar image

updated 2017-03-08 18:27:21 +0200

If this was not specifically about rounding at all, only the 'Fill' tool is of interest here. It was recently moved from 'Edit' to 'Sheet'.

The other aspects:
If the values in the column are calculated, the appropriate way to do it is exactly as you posted, except that it might be clearer to write: =ROUND(theOriginalExpression;0). You have to use the 'Fill' tool then to update the remaining cells of the column.

If the values are entered manually you have the option to simply enter them already rounded - unrounded values los then - or to use a helper column, say at the right, where you apply the formula posted in the question, or probably better the variant =IF(ISNUMBER(theCellValue);ROUND(theCellValue;0);""). If the data are imported, the helper column should be the only recommendable way.

There is one remaining dilemma. If you actually have in use very many cells of the column (>10000 e.g), the formulae filled into the adjacent column will increase the filesize and make the sheet less efficient.

If this gets very urgent, the only way I can think of is a bit of user programming. If applicable, ask again.

(There is also the option 'Precision as shown' in this context. I would, however, dissuade from using it, because there are unclear and probably not stable implications.)

edit flag offensive delete link more


Thank you for a detailed reply sir. Actually the data is imported from a text file. I can't go on on adding the formula to each cell. So, I thought there would be an easier way to add a formula to the entire column. Creating a helper column and then copy paste with the new values is also tedious job as the file is a big one.

adam1969in gravatar imageadam1969in ( 2017-03-08 18:40:23 +0200 )edit

It is very simple to get a formula work in a huge range of cells. It is the 'Fill' tool that is made to do so. Virtually no formula is applied to "a complete column" in any spreadsheet. This would be 2^20 cells , much more than a million. Working with data at that scale is the job special programs or, of database applications.
Reading your statements I feel not quite sure that you regard the fact that a cell already containing data cannot take a formula working on tthe data at the same time.

Lupp gravatar imageLupp ( 2017-03-08 21:48:25 +0200 )edit

I am still wondering what you actually have and what you actually want, precisely.

Lupp gravatar imageLupp ( 2017-03-08 21:49:16 +0200 )edit

I thought I could add a formula to the already existing values.
But the "Decrease Decimals" button helps me out by decreasing the decimals. Thanks for your valuable time @Lupp

adam1969in gravatar imageadam1969in ( 2017-03-09 03:19:24 +0200 )edit

@adam1969: Thank you for drawing my attention to that tool I never had used before.
Please note that the tool doesn't do any actual rounding. Its label 'Delete Decimal Place' may be misleading with this respect. It only changes the 'Numbers' format setting for the cells concerned. The unrounded values remains content (or formula result) of the cells.

Lupp gravatar imageLupp ( 2017-03-09 12:43:04 +0200 )edit

Yes sir, I know that it won't round the number actally. But is good enough to get a clear view of the numbers as I need. Unfortunately there is no easy way to add a formula to cells already containing values.

adam1969in gravatar imageadam1969in ( 2017-03-09 18:25:07 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-03-08 17:59:46 +0200

Seen: 875 times

Last updated: Mar 08 '17