Documentation of all projects is important, and Excel provides a method for that by allowing comments to be associated with a cell. When the mouse pointer hovers over the cell, the comment will pop up. You can use this for operator instruction or a self-reminder. Creation of a comment is trivial. Right-click on a cell (or group of cells, although only the first is selected) and select Insert Comment . A yellow box will pop up. Normally it will include your name as the first line of the comment, but it can be deleted easily. You can add text, paste text, format text, and size the box to suit the message.
Clicking away from the box will establish the comment and indicate there is one by means of a small red triangle in the upper right corner of the cell. Once there is a comment, right clicking on the cell will allow you to delete and edit the comment. You can also select the Show/Hide Comments which, when active, will keep the comment for that particular cell continuously visible.
An Excel “workbook” consists of one or more worksheets denoted by the tabs that are visible along the bottom of the window. You can add a new worksheet by clicking on the new worksheet tab (on the extreme right of the tabs). You can also right click on a tab and select Insert… | New Worksheet . Right clicking the tab also provides several other options, which include renaming, and you can use symbols as well as text — I once used π when working with MTBF data. Re-ordering the tabs is merely a case of dragging the tab to where you want it in the tab sequence.
You can access data from other worksheets by prefacing the cell with the sheet name and an exclamation point (exclamation mark if you speak the Queen’s English). For example if we want the value of cell B2 from Sheet2 to appear in cell C6 on Sheet1, the contents of cell C6 will be =Sheet2!B2 .
Working on complex worksheets can lead to problems. Excel provides some tools to help diagnose these problems, and these can be found under the Formulas ribbon tab as seen in Figure 2. These show which cells contribute to a value (precedents) and how a cell affects others (dependents). Just because Microsoft says these are for auditing does not mean that that cannot be used for your own nefarious purposes. Take a look at my design idea, “Use Excel To Develop A Traceability Matrix,” to get an idea of what I mean.
By default, Excel recalculates at every change on the worksheet. On complex calculations this can take some time, and so you can prevent the continuous update and recalculate on command using the Calculation controls you can see in Figure 2.
Even though it is probably common knowledge to all of you, I thought I should mention that it is possible to protect worksheets and areas on the worksheet so that critical data and formulas cannot be changed inadvertently. When a sheet is protected, by default all cells are also protected. You have to unprotect the cells that are allowed to be changed by right clicking the cell (or group of cells) and clicking Format Cells … and select the Protection tab. To activate the protection on a sheet you can right-click a sheet tab (see Figure 1) or go to the Changes section of the Review tab on the ribbon. There are quite a few types of changes that you can protect against, as you can see in Figure 3.
If you create a file that you want to use as a prototype, you can save it as a template by doing just that — File | Save as , and select the Excel template from the drop down menu. When you click on File | New there is an option, My templates , which will open the template as a new file.
So there you have it. I do hope that I managed to impart some new information to you and that presuming this knowledge up till now did not detract from earlier blogs. My next blog on Excel (which may not actually be my next blog) will create an application that builds on a number of the topics that I have covered. It will select a pair of resistors that will provide the minimum of error for a given formula.