hiding huge formula's in an excel sheet

[Guest]

Hello,

One of collegue's had made an excel sheet with a huge formula.

When you open the excel sheet you see the huge formula wich stands in the
content of the sheet

Is the anyway to hide the formula ?

 

[Pete_UK]

You could hide the column so it will not be visible.

You could use Format | Cells | Protection (tab) on the cells
containing the formula and check both Locked and Hidden - if you then
protect the sheet (with or without a password) then you will only see
the result of the formula, not the formula itself.

Hope this helps.

Pete

 

[Roger Govier]

Hi

In addition to what Pete has suggested, if the formulae are not too
long, then you might be able to create named formulae.
Insert>Name>define>
Name myFormula1
Refers to =your_long_formula

If you copy the formula, then you can use Ctrl + V to Paste it into the
Refers to pane.
In your cell then just use =myFormula1

Alternatively, If the problem is only when you access the cell for
editing purposes, and you cannot see the column headings, you could run
the small macro, which will toggle the sheet from filling the screen, to
taking up a smaller area. These are the sizes I find suitable, you can
vary the values to suit yourself.

Sub smallsheet()

If ActiveWindow.WindowState = xlMaximized Then
Windows.Arrange ArrangeStyle:=xlCascade
With ActiveWindow
.Top = 56.5
.Left = -2.75
.Width = 765
.Height = 393
End With

Else
ActiveWindow.WindowState = xlMaximized
End If

End Sub

You can copy the code and paste it into your Visual Basic Editor
(VBE) in a Standard Module located in your file.

To do this,

Alt + F11 (open VBE)
Ctrl + R (open Project Explorer)
Select the file name on the left
Insert > Module
Paste code in Module

To run the macro, Tools>Macro>Macros>Select the macro>Run.

If you want to create a shortcut, then Tools>Macros>Select the
macro>Options>enter a key to use with Ctrl>OK


If you are new to entering macros, then David McRitchie has lots of
useful help on his site at
http://www.mvps.org/dmcritchie/excel/install.htm
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Guest]

Hello,

I want to thank you both for your answers, i will notify my collegue of this
post and hope he can use one of the provided solutions.
Thanks again