How to hide spreadsheet formulae in Excel
Liam regularly writes for the Chartered Institute of Management Accountants (CIMA). This month’s article, recently published on the CIMA website, illustrates different ways of hiding formulae which will help you develop spreadsheets where users can't edit or view the formulae you are using.
Liam’s article, Spreadsheet skills: hiding formulae, is based on a recent query he received from a financial modeller. The person was asking whether it was possible to hide spreadsheet formulae in Excel so that users of the model can neither view nor edit it.
Hiding spreadsheet formulae – what to consider
As with most things in Excel, there is more than one way to achieve this. In this instance, the immediate solution appears to be to hide the Formula Bar in Excel. This option might seem obvious and entirely reasonable at first, but as Liam explains, there are issues with this approach:
-
You won’t just hide the formulae; no content can be viewed from the Formula Bar using this method
-
The formulae can still be viewed and edited by clicking on the cell in question and pressing the F2 (edit) function key
-
The end user can simply switch the Formula Bar back on using the method(s) outlined above.
Hiding Excel spreadsheet formulae – a three part problem
So, what appears to be a seemingly simple query is not as straightforward as we first thought. We are looking at a problem which has three parts to it.
-
How do we apply this to formulae only?
-
How do we make sure it applies to all formulae?
-
How can we prevent other users from undoing want we do and make formulae visible again?
In his article, Spreadsheet skills: hiding formulae, Liam walks us through how to do this including the differences between Excel 2003 and Excel 2007. When you know the steps it’s easy – learn all about it here.
Post new comment