Getting rid of formulas?

[johnF]

Hello,

Is it possible to "finalize" excel worksheets so that cells containin
formulas will place the correct value to the cell and remove th
formula?

For Example I have a formula =SUM(A1:A2) in cell A3 and A1 value is
and A2 value is 2, I want cell A3 to have a value of 3 instead of th
formula.

Thanks

 

[Frank Kabel]

Hi
try the following:
- copy the cells
- goto 'Edit - Paste Special' and insert them as 'Values'

 

[johnF]

That works, thanks.

But is there a another way to do it than the copy-paste method? Or i
it possible to write a marco for a such task

 

[Norman Harker]

Hi JohnF!

You could use a macro but have you considered:

Edit > Goto > Special
Select "Formulas"
Copy
Edit > Paste Special > Values > OK

Also, there is a Paste Special Values button under Edit in the
Customizing menu and if you bring that up to a toolbar, you can move
pretty quickly.

Finally, you can select an entire sheet using Ctrl + A


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Dave Hawley]

Hi John

Try this macro for making life easier

Sub NoFormulas()
Selection=Selection.Value
End Sub

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****

 

[Dave Hawley]

I forgot to say that you can get some other ways here:
http://www.ozgrid.com/Excel/convert-excel-formulas-values.htm

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****

 

[Norman Harker]

Hi John!

There is another way that has ancillary uses.

If you download Ron de Bruin's SendMail Addin you'll find that for all
options (whole workbook, single sheets, selected sheets and selection)
there is an option to send as values only. You can of course email to
yourself.

So you can use this Addin for a flexible method of switching from
formulas to values and creating a new workbook whilst retaining the
original workbook with formulas.

Ron de Bruin's SendMail Addin is obtained from:

http://www.rondebruin.nl/sendmail-addin.htm

Whilst there you might as well see:

http://www.rondebruin.nl/Google.htm

Which gives you Ron's Google Search tool as well. Both have user
guides.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[johnF]

Thanks for replys:-

 

[Domenic]

Hello,

Is it possible to "finalize" excel worksheets so that cells containing
formulas will place the correct value to the cell and remove the
formula?

For Example I have a formula =SUM(A1:A2) in cell A3 and A1 value is 1
and A2 value is 2, I want cell A3 to have a value of 3 instead of the
formula.

Thanks.

Hi John,

I'm not sure if this is what you're looking for, but you can select the cell
or cells that you want the value for, copy, paste special, click on values,
and then click OK. That will replace the formula(s) with the value(s).

Hope this helps!