Welcome back to my blog on the features of Excel that are often overlooked in our hurry to be productive. A while back, we had part 1. Moving around an Excel worksheet can be challenging, especially when it is a large worksheet. The following table details the more common keyboard shortcuts for getting around.
In the top left corner of the worksheet is the goto bar, as shown in Figure 1. When you enter a cell location here, you are taken immediately to that cell.
In the real world, we often use names to reference an object or group of objects, and it is convenient to follow suit in Excel. The goto bar has a double purpose; in addition to taking you to a cell, it will let you name a cell or group of cells. First, select a cell or group of cells (and they need not be contiguous). Then click in the goto bar and enter the name you want to use.
You cannot use a name that also refers to a cell (such as R10 or even RE10). However, Res10 would work for resistor No. 10. As mentioned before, when used in a formula, these names become absolute cell references. The dropdown arrow on the right of the goto box will list the named cells. Clicking on one will take you there.
You can also name other objects. A chart is one example, but you can go a step further. You can name both constants and formulae. Let's say you want to use “pF” as a constant throughout a worksheet to define picoFarads. Click the Formulas tab on the ribbon, and then click on Define Name . A dialogue like the one in Figure 2 will pop up. Enter the text “pF” as the Name and the formula in the Refers to field. Now, if you want to enter the value of 220pF in a cell, enter the formula =220*pF, and the value will be scaled accordingly.
Let's look at naming a formula. Say we want to calculate power dissipation based on current and resistance (I2 R). Start by selecting a cell for current H4 and resistance I4. In this case, we will always calculate the power dissipation and place it one cell to the right of the resistance (J4). As you did with the Define Name sequence above, enter “PowerDissipation” as the Name, and enter the formula =H4^2*I4 in the Refers to field. Now, whenever you enter the formula “=PowerDissipation” into a cell, it will calculate the vale based on the product of the square the value of the cell two columns to its left and multiplied by the cell one column to its left.
Looking at a worksheet, it is impossible to know what cells and areas are named. Access and editing is done by clicking on the Name Manager button shown in Figure 2.
In some worksheets, it is easier to use intermediate calculations. It is possible to hide columns and rows to prevent confusion and partially protect the data. Select the columns (or rows), right click, and select Hide . To reverse the process, do what is intuitive — select the columns (or rows) straddling the hidden columns, right click, and select Unhide .
Sometimes it is convenient to keep one area of the worksheet (like titles of columns) constant on the screen while working elsewhere. You can achieve this using panes, which can be activated through the View tab, as shown in Figure 3. However, it is easier to create the pane by dragging the associated bar (as seen in Figure 3) down or across. Switch from one pane to the other by clicking in the desired pane area. To get rid of the pane, simply drag the bar back to where it was.
Excel will allow your text to overflow a cell into the next cell, provided that next cell is empty. If it isn't, the text will be truncated, or values will be replaced by the string ####. You can expand the column width or height by moving the mouse pointer to the list of rows or columns. Hover over the row or column boundary (where the mouse icon changes to a double-headed arrow), and then drag to the width or height you want. You can automate this to the widest or highest requirement by double clicking, rather than dragging the two-headed icon.
In part 3, I will deal with adding comments, adding and naming worksheets, and cross-worksheet access. I will also touch on protecting and auditing a worksheet.