Ad Free CAclubindia and Daily Updates on your Mobile. Subscribe Now!

how to remove formula in excel sheet-urgent

vandana (student) (225 Points)

09 December 2009

pls. someone tell me that how can I remove formula in an excel sheet............its urgent.......

Reply
Follow
Share

11 Replies

AJAY AGARWAL (Articleship) (102 Points)
Replied 09 December 2009

make a double click on the cell where formula is putted then delete that formula

Upgrade to CAclubindia Pro. Subscribe Now!

vandana (student) (225 Points)
Replied 09 December 2009

thanks........its a long sheet......so any other idea??

AJAY AGARWAL (Articleship) (102 Points)
Replied 09 December 2009

you only have to delete the formula from the cell where the ultimate answer will come not on the whole sheet

CA Varun Arora (CA) (286 Points)
Replied 09 December 2009

Just Copy the cells where u want to remove the formula (shortcut (Ctrl +C)),

then right click on the cells and click on paste special,

then the window comes that has many options, just click on values and press OK

it will remove all formulas and paste the values of the cells as it is.

Thanks,

Varun Arora

1 Like

Ram Avtar Singh (Nagari Sultanpur U.P.Delhi) (14497 Points)
Replied 09 December 2009

select all which you want to remove formula then copy all and click right button in colum A1 and past special select value then ok its remove all formula and your data is safe .

if you want to delet all data then slect all and delet selected row. file will be blank.

1 Like

Upgrade to CAclubindia Pro. Subscribe Now!

AJAY AGARWAL (Articleship) (102 Points)
Replied 09 December 2009

sir ji paste special karne se jis cell ko copy kiye ho uspe jo bhi entry karoge wo bhi copy ho jayega

C.A. M.Com. Suhas A. Nagpure (CA) (1175 Points)
Replied 09 December 2009

Can you share that file with us so will come to know your problem

CA Ayush Agarwal (Kolkata-Pune-Mumbai) (27186 Points)
Replied 09 December 2009

Varun Arora & Rav Avatar Singh are Rite.

vandana (student) (225 Points)
Replied 09 December 2009

thanks VARUN.............n RAM.......it works

CA Adarsh Madrecha (Chartered Accountant) (200 Points)
Replied 14 December 2012

If you have basic knowledge of how macros work, then this is the code for removing all the formulas in the workbook and replacing with the values calulated by the formula. Sub RemoveFormulas() Dim ws As Worksheet For Each ws In Worksheets With ws.UsedRange .Value = .Value End With Next ws End Sub Visit - XBRLforIndianCA.blogspot.com

Upgrade to CAclubindia Pro. Subscribe Now!

CA Adarsh Madrecha (Chartered Accountant) (200 Points)
Replied 14 December 2012

If you have basic knowledge of how macro work in Excel, 
Then this is the code for the macro.


Sub RemoveFormulas()
Dim ws As Worksheet
For Each ws In Worksheets
  With ws.UsedRange
    .Value = .Value
  End With
Next ws
End Sub

Visit - XBRLforIndianCA.blogspot.com