how to add an additional formatting formula to an existing formula

Posted on 2013-09-26
Medium Priority
Last Modified: 2013-09-26
How can I add a formatting formula round,2 to an existing formula?  I have a 2000 line spreadsheet that I need to add rounding to an existing calculation.  I don't want to have to edit each line, and using the special formatting (0.00) doesn't allow me to pass the formatting when it is transferred via an import process.
Question by:kgittinger
LVL 50

Expert Comment

by:barry houdini
ID: 39525941
Normally you can copy the formula down the column, so assuming your first formula on row 2 just alter that to


[omitting the = at the start of existing_formula]

and then you can copy down the column - if you have continuous data in an adjacent column you can just double-click the "fill-handle" and it will populate automatically all the way down. The "fill-handle" is the black "+" you see if you put the cursor on the bottom right of the cell.

Another way to fill the whole range is like this:

Change first formula and select that cell
In box above A1 type the whole range that the formula needs to populate (including the first cell), e.g. B2:B2000 and press ENTER and that whole range gets selected
Use CTRL+D to "fill down" the formula into that range

regards, barry

Author Comment

ID: 39525971
Unfortunately the formulas are not consistent from line to line, so I cannot drag down the formulas.  They are calculating off of other spreadsheets.  Right now what I am doing is hitting F2 entering round( in the beginning, and ,2) at that end.  Tedious...
LVL 15

Accepted Solution

ChloesDad earned 2000 total points
ID: 39525972
Another way would be to use a small macro

By using this method it will not matter what the original formulae was

Sub Macro1()
' Macro1 Macro


Dim Column As String

Column = "D"
For Row = 1 To 5
    Range(Column & Format$(Row, "0")).Formula = "=ROUND(" & Mid$(Range(Column & Format$(Row, "0")).Formula, 2) & ",2)"
Next Row
End Sub

Open in new window

This has the advantage that its just a case of running the macro when you want to apply the new forumlae

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question