OAK's DeApply Names command is used to completely or partially eliminate the use of names in formulas by replacing them with the actual cell coordinates that they reference. It is the inverse of OAK’s Apply Names command.
Why would you want to use it?
Opinion is divided between those who like the use of names in Excel formulas and those who don't. OAK's Deapply names allows enthusiasts for names to use them when building models, and then to strip them out from any copy sent to recipients who have an aversion to the things.
Model auditors who use code inspection often wish to satisfy themselves that every name in a worksheet refers to the area it is intended to refer to. OAK can help in this process by building a database of the names that need checking. If there are hundreds of names defined, checking them one at a time can be a burdensome process. One way of obviating the step altogether is to have OAK simply remove the names from the spreadsheet formulas.
One drawback of using names in spreadsheets is that it is awkward to change your mind about what names to use late in the development process. For example, if you have used Sales throughout a large model, and realize that you would prefer to have used Turnover, you have to alter every instance of the word Sales in every formula the mentions it. Excel 2007 has addressed this issue, and OAK provides the ReDefine Name and ReCreate Names commands for earlier versions of Excel. But a further option is to Deapply the existing names, getting the formulas back to coordinate notions, and then to create new names and apply them in the normal way.
How to use it
With the workbook containing the names-based formulas open:
| • | Select the OAK4 | Names | DeApply Names command. This will open a dialog box similar to the one shown below. |
| • | The dialog box shows you all the names within the workbook and allows you to select all or part of these to be removed from the formulas. |
| • | Once you have selected the names you want removed, you have the option of specifying which areas of the workbook the DeApply command should be run over. The three options are: a selected region within the active worksheet, the entire worksheet or the whole workbook. Note that in order for the selected region option to be available, the selection must be made before selecting the DeApply name command. Once you are satisfied with the names you want to remove and where you want these changes to be effected, click the DeApply button. |
Tip: Particularly if you are removing all the formulas from a spreadsheet that uses a lot of them, OAK will change many or most of the formulas in your model. It is only sensible to make a copy of your workbook before using the Names | Deapply names command. Compare the key results between the versions to make absolutely sure that the alterations haven't altered the effect of the spreadsheet.
How it works
To deapply names, OAK is actually understanding the formulas algebraically, identifying all uses of names, extracting and substituting the definitions of the names, and inferring which cells are involved in the calculation after row and column matching has taken effect. In practical terms, the function is implemented as an in-place reconstruction of the original formula.
What you could do if you didn't have OAK
So far as Operis is aware, there is no capability equivalent to OAK's Deapply Names command in any competing Excel add-in.
There is a way to remove names from a formula manually.
| 1 | Highlight a cell that contains a formula using one or more names |
| 2 | Double click on one of the names in the formula. |
| 3 | Press F5. This is the short cut for Go To; it will bring up a dialog box in which the coordinate equivalent of the name is highlighted |
| 4 | Press Ctrl+C to copy the coordinates on to the clipboard |
| 5 | Press Escape to dismiss the Go To dialog. You will return to the formula that contains the unwanted names, still being edited. |
| 6 | Press Ctrl+V to paste the coordinates into the edit box. It will overtype the unwanted name. |
| 7 | Repeat as required for any other names in the formula. |
| 8 | Press Enter to accept the now name-free formula |
This is too manual a process to perform on any scale; does only part of the job because it doesn't address issues of column and row matching; and does nothing useful with names that are themselves defined in terms of other names.
Wrinkles
The DeApply names command does not work when the worksheet or workbook under modification is protected. You must first manually unprotect the worksheet/ workbook before running the command.
DeApply names can be instructed to attend to a selected region within the active worksheet; the entire worksheet or the whole workbook. In the first of these cases, it obeys the One Cell Rule.
While the DeApply command removes the names from the formulas, it does not delete them from the selection/ worksheet/ workbook. As a result, using of Excel, or OAK's Apply names command should exactly reverse the effect of Deapplying the names, putting the names back as they were in the first place. If the names are no longer wanted, they can be removed manually.
If there are many names in a worksheet, and OAK is asked to Deapply many of them, it can take a long time. The action is making a considerable change to the spreadsheet, potentially rewriting nearly all of the formulas. If you are certain that a worksheet is left to right consistent (that is, all formulas after the first on each row are copies of that first one) then it may be quicker to deapply names in the first column of formulas and then copy the result into subsequent columns.
The technology used to Deapply names is new and ambitious. While it has been tested carefully, we would certainly recommend that a version of the spreadsheet is kept as it was before attempting the Deapply, and retained until one is satisfied that the process has worked as intended and the spreadsheet continues to give the same results.