When I started my blogs on Excel, I launched straight in without any mention of some of the simple features that you may or may not know. As I continue this series, I thought perhaps I should cover some of these features. I have covered some of them implicitly to date, but it may help to be explicit before proceeding further.
Often you want to create a sequential list, for instance the numbers from 1 to 100 to identify rows in a table. In Excel you could enter the number 1 in cell B2, and then enter the formula =B2+1 in cell B3, and then copy cell B3 down to cells B4, and so on. Cell B4 will contain the formula =B3+1 , B5 will contain the formula =B4+1 , and so on. Note the transformation of the row number each time pointing at the cell directly above it — we will get back to this in a moment.
This technique certainly has its place, but Excel has an “Autofill” feature, which will speed things up if you don’t need the automatic update that would happen if you insert a row, for instance. Let’s see how this works. First enter the number 1 in cell B2 and 2 in B3. Block B2 and B3, and you will notice a small black rectangle at the lower right corner of the block outline. Click and drag the rectangle downward, and you will see a ghost number appear indicating what the autofill would enter in the cell that you have reached. This is shown in Figure 1.
On releasing the mouse button all the cells to that point will be filled in. But Autofill is pretty smart. It will do preprogrammed things like dates, but will also made a valiant attempt at many other sequences you throw at it. When the autofill is complete you will see a symbol appear at the lower right of the block (Figure 2). If you click on it you can influence the kind of autofill you want. Updating the selection will immediately update the recently filled cells.
You can also pre-define your own lists, like all the resistor values. However there are two caveats. First, the list has to be in text format. Second, the resulting autofill list is installed into Excel itself and not the worksheet, so distributing the list to others needs a bit of forethought. To create a list, enter the data in Excel in a single column or row. You enter the number as text by preceding it with the apostrophe ('). (Formatting the column as text will not work for this purpose.)
Excel will place a small green triangle in the left corner of each cell as a warning that it is a numerical data formatted as text. To enter the list, click on the ribbon tab File followed by Options | Advanced and then scroll down to the General heading and click on the Edit Custom Lists button. Block the list and click on the Import button. Transporting to another machine would require you to place the whole list in a worksheet and transport the worksheet and then import the list all over again.
As they say on TV, “But wait! There’s more!” This tiny rectangle is really something. If you enter a formula in one cell and then drag the rectangle, the formula will follow along. “And even more…” Enter the formula alongside the column you want to act upon and double click on the rectangle. Like magic, the column is filled exactly to the last populated row.
We saw earlier how Excel allowed for relative addressing changes when copying from one cell to another. This is an extremely powerful tool and is probably the primary reason that complex worksheets can be constructed with ease. Sometimes, however, you don’t want the transposition, and this is achieved by the use of the “$” symbol within the text reference, a technique that dates all the way back to Visicalc.
Preceding the column letter with a “$” means that the column will remain constant; preceding the row number with a “$” means that the row remains constant; and preceding both refers to a fixed cell. For instance, when referring to cell C55, $C55 means that any copy (and that includes block copies) will always refer to column C no matter where you paste the copy. Similarly C$55 means that any copy (and that includes block copies) will always refer to row 55 no matter where you paste the copy. $C$55 will always refer to cell C55 no matter where you paste the copy. Another way to set absolute addressing is to use named cells, which we will get to in the next part of this blog.
When you paste a copied cell or cells you will see a dropdown symbol at the bottom right of the pasted data, as you can see in Figure 3. Clicking on it will allow you to change the format of the copied data as you can see in the popup menu. For instance you may have calculated a bunch of results based on some formulae, and all you want to do is copy the results as numerical data. The Values (V) (3rd row 1st column) option will simply update the pasted data from formulae to data. Other actions are left for the reader to investigate “as an exercise.” If you choose to ignore this action the copy will be with the complete formatting and formula setup as the source cell(s).
There is still more stuff to cover. Excel is so intuitive that many of the more powerful aspects of seemingly trivial features are overlooked. I hope that this has proved interesting so far and that you have learned something. See you in Part 2.