Convert Excel sheets with formula to text

[victorpw]

Hope someone could provide guidance.

I often need to convert Excel files with multiple sheets/tabs with data with formula to plain text; whilst retaining the format.

For example, the 1st file Convert Excel file with formula to text.xlsx contains the original Excel file with 2 sheets (containing formulas)

I need to quickly convert both sheets (Jan/Feb) to text whilst retaining the format (end result = 2nd file Convert Excel file with formula to text (converted).xlsx

Is there a quick way to convert? - some of my files contain 10 sheets.

Thanks.

Victor

 

[Vivek D]

If your main objective is to hide the formulas then you could try one of the options given in the link below
http://www.exceltrick.com/how_to/how-to-hide-formulas-in-excel/

If you want to eliminate the formulas and just have values then I guess you would have to go the VBA route. Are you ok with that or were you looking for a non-macro/VBA solution?

 

[victorpw]

Hi Vivek, many thanks for reply. I actually want to have just values. From your reply, appears VBA is the only solution. I know only basic VBA, thus if you have a basic ready made solution, this will be much appreciated.

 

[Vivek D]

There's actually a manual option and a VBA option

Manual Option
1. Select all sheets (Select 1st, Press Shift and select Last)
2. Ctrl + A (Select All cells)
3. Ctrl + C (Copy all cells)
4. Alt + E + S + V (Paste as Values)

This should convert all formulas to values in all sheets.

VBA Option (Same approach as above but using VBA code)

Sub ConvertFormulaToValues()
    Worksheets.Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    ActiveSheet.Select
    Application.CutCopyMode = False
End Sub

 

[victorpw]

Hi Vivek

Many thanks for your great help. Both manual & macro option works perfectly.

Really appreciate your help!

Regards
Victor