Tip: Using Microsoft Word's spike to rearrange text

Microsoft has long produced software with a belt-and-braces approach, offering a choice of ways to perform a particular task.

For example, in Word, you’ll find a smorgasbord of methods for cutting, copying and pasting text. There’s the usual cut, copy and paste via keystroke, menu or toolbar. There’s also the Office Clipboard, which is like copy-and-paste on steroids. Then there’s the often-overlooked spike, which sits halfway between the other two methods in terms of power.

The spike lets you quickly reorganize snippets of text and graphics. You grab the snippets from different locations in your document, place them one by one on the spike, then use the Insert From Spike command to paste them as a block into your document.

The spike didn’t qualify for inclusion in Word’s menus or toolbars, but you can easily access it using the keyboard: Ctrl+F3 cuts the selected text and places it on the spike; Ctrl+Shift+F3 copies the entire contents of the spike into the document at the current location.

So if you’re working on a masterpiece such as this:

Oil, that is. Black gold. Texas tea.
A poor mountaineer, barely kept his family fed,
When up through the ground came a bubblin’ crude.
Then one day he was shootin’ at some food
Come listen to a story ’bout a man named Jed

and decide a little rearranging would improve its readability, select the fifth line and press Ctrl+F3; then line 2 and press Ctrl+F3; line 4, Ctrl+F3; line 3, Ctrl+F3; line 1, Ctrl+F3; and finally press Ctrl+Shift+F3 to paste the newly arranged result:

Come listen to a story ’bout a man named Jed
A poor mountaineer, barely kept his family fed,
Then one day he was shootin’ at some food
When up through the ground came a bubblin’ crude.
Oil, that is. Black gold. Texas tea.

Of course, the spike really comes into its own when you’re writing something a little more weighty and need to rearrange entire paragraphs or sections to create the best flow.

Spike’s idiosyncracies

There are several things worth noting about the spike.

• First, the spike uses a separate area of memory from the Windows and Office clipboards. So you can press Ctrl+C to copy an item to the clipboard, then subsequently press Ctrl+F3 to cut an item to the spike, and the contents of the clipboard will remain untouched. When you press Ctrl+V the contents of the clipboard will be pasted into the document; when you press Ctrl+Shift+F3 the contents of the spike will be inserted.
• While the Windows Clipboard can hold a single item at a time and the Office Clipboard can hold 24, the spike has a voracious appetite and is happy to accommodate thousands of items.
• When you insert the contents of the spike into the document using the Insert From Spike command (Ctrl+Shift+F3), the spike is emptied. This contrasts with the behavior of the clipboard, where the contents remain available for pasting and re-pasting until you replace the contents by cutting or copying another item. If you’d prefer to keep the contents of the spike intact while pasting it into your document, it helps to know that when you cut the first item to the spike, in the background Word creates an AutoText entry called ‘spike’; the entry is deleted after you press Ctrl+Shift+F3. So if, instead of pressing Ctrl+Shift+F3 to insert the spike, you instead type spike and press F3, Word performs the usual AutoText actions instead: it copies the contents of the ‘spike’ AutoText entry without deleting it.
• The spike is shared by any documents you have open. You can spike an item from the first document, switch to the second and spike three items there, switch back to the first document and insert all four items.
• Things get a little tricky when you try to add the contents of a table to the spike. If you select the table and then press Ctrl+F3, the only thing that gets spiked is the contents of the table’s first cell. This is because Word stops ‘spiking’ when it hits the first paragraph mark, and, because it treats the end-of-cell marker as a paragraph mark, it copies that first cell’s contents then stops. To get around this, make sure you select the entire table plus the paragraph mark following the table. It may help to click the Show/Hide button on the toolbar so you can quickly spot the paragraph mark.

Avoiding blank lines

You may find it useful to switch off Smart Paragraph Selection in order to avoid creating blank lines between each of the items on the spike.

With Smart Paragraph Selection enabled, it’s impossible to select a paragraph without grabbing that final paragraph mark as well. If you spike the series of lines from our previous example with Smart Paragraph Selection enabled, you’ll end up with this:

Come listen to a story ’bout a man named Jed

A poor mountaineer, barely kept his family fed,

Then one day he was shootin’ at some food

When up through the ground came a bubblin’ crude.

Oil, that is. Black gold. Texas tea.

If you switch Smart Paragraph Selection off, you can select a paragraph minus the paragraph mark by dragging carefully to the text end; you can still grab the paragraph mark if you want to by dragging past the “end” of your paragraph to include the mark. You can also grab a paragraph complete with its paragraph mark by placing the cursor in the left-hand margin and dragging down.

To turn Smart Paragraph Selection off, in Word 2007 click the Office Button -> Word Options -> Advanced and deselect Use Smart Paragraph Selection. In Word 2003, click Tools -> Word Options -> Edit tab and deselect Use Smart Paragraph Selection.

Shading every other row in Excel

I sometimes wonder whether Microsoft spends so much on its programming budget it has little left over to spend on designers. This thought often crosses my mind when working with Office sample files, templates and styles.

Take, for example, the auto-formats supplied with pre-2007 versions of Excel. Who designed these things? Not only are they ugly, they also don’t address basic formatting needs such as coloring every other row in order to make a table more readable. Yes, there’s one auto-format option which gives you this – sort of – but it comes with unattractive headings and footer formatting and it’s inflexible.

The solution? Do the job yourself, using conditional formatting. Conditional formatting lets you apply formatting based on the value contained in a cell or based on the result of a formula.

Here’s how you can use it to apply shading to alternate rows:

1. Select the range of cells you wish to format.
2. Click Format -> Conditional Formatting.
3. In the drop-down box, select Formula Is.
4. In the box type:

=MOD(ROW(),2)=0

5. Click the Format button and then click the Patterns tab.
6. Select a color for the shaded rows and click OK twice.

There you have it: a table with banded colors. If you find you don’t like your chosen color, simply select the range once more, open the Conditional Formatting box and choose another color.

The real beauty of this method is that even if you delete a row in your table, the banding will be maintained automatically.

Formatting in Excel 2007

If you’re using Excel 2007, you have a whole range of new formatting tools at your disposal, including the alternate row shading offered through the Format As Table options. I’ll be writing more about these options in the future.

Nevertheless, you can still roll your own conditional formats if you choose: from the Home tab, click Conditional Formatting -> New Rule -> Use A Formula To Determine Which Cells To Format and then use the same formula described in Step 4 above to create banding.

Conditional formatting explained

If you’re not familiar with conditional formatting, a little explanation is in order.

Conditional formatting is applied to a cell when the condition you specify is true. The condition may relate to the cell’s contents. For example, you could set all cells containing negative values to be displayed in a red font. Alternatively, you can use a logical expression or formula which evaluates as TRUE or FALSE, as we did in the example above. In Excel, a 0 value equates to FALSE, while any number greater than 0 equates to TRUE.

The MOD() and ROW() functions

The formula we used contains two functions, ROW(reference) and MOD(number,divisor). The ROW function returns the number of the row contained in reference or, if the reference is omitted (as in our example) it returns the number of the row containing the function. So, if you place the formula =ROW() in cell A9, the value returned is 9.

The MOD function returns the remainder of the number divided by the divisor. So, the formula =MOD(7,6) returns a value of 1, while =MOD(6,7) returns 6.

Evaluating Logical Expressions

Now take a look at the formula we used to create shading on every other row:

=MOD(ROW(),2)=0

In this case, ROW() provides the number while 2 is the divisor in our MOD() function. In English, we divide the current row number by 2. The remainder will either be 0 for even numbered rows or 1 for odd-numbered rows.

We then compare the result to 0. For even numbered rows, we end up with the expression 0=0, which is TRUE. Because the condition evaluates to TRUE, we apply the selected formatting. For odd-numbered rows, the result is 1=0, which is clearly FALSE, and thus the formatting is not applied and the row remains the default color.

Astute readers may be wondering, “Why do we need the =0 on the end of this expression, when we could simply evaluate =MOD(ROW(),2)?” It’s true, we could leave off the =0 and we’d still end up with an expression with alternating values of 0 and 1 and the same bands of color (although the shaded/non-shaded rows would be reversed). But by adding the =0 we make it very easy to extend this expression to coloring every third or fourth or fifth row, and so on. For example:

=MOD(ROW(),3)=0

will color every third row. This is easy to check by evaluating the expression for rows 1 through 6:

=MOD(1,3) = 1 1 = 0 is FALSE (not shaded)
=MOD(2,3) = 2 2 = 0 is FALSE (not shaded)
=MOD(3,3) = 0 0 = 0 is TRUE (shaded)
=MOD(4,3) = 1 1 = 0 is FALSE (not shaded)
=MOD(5,3) = 2 2 = 0 is FALSE (not shaded)
=MOD(6,3) = 0 0 = 0 is TRUE (shaded)

And so on.

So what happens if you leave the =0 off the expression? Instead of shading every nth row you’d color n-1 rows then leave the next row unshaded, something that might come in handy, too. In the case above, you’d shade rows 1 and 2, leave row 3 untouched, shade rows 4 and 5, and leave row 6 untouched.

Column and checkerboard shading

You can use a similar technique to create alternating shading on columns. Instead of using the ROW() function, you use the COLUMN() function:

=MOD(COLUMN(),2)=0

Note that the COLUMN() function returns the column number, with column A=1, column B=2 and so on.

Anyone for checkerboard shading? Try:

=MOD(ROW(),2) =MOD(COLUMN(),2)

You’ll end up with a checkerboard shading pattern.

Understanding the logic

If you’re content with this result and don’t need to know the nuts and bolts, class dismissed. If, on the other hand, you’d like to understand how we end up with a checkerboard pattern from this expression, stick with me. Unless you have some programming or a philosophy degree under your belt, trying to get your head around logical expressions can be a little confusing at first.

Note that to produce the checkerboard pattern, we’ve used two formulas one after the other; we’ve also dispensed with the final =0 we used in the earlier formulas.

Why is this so?

Well, instead of comparing the result of an expression to 0, we compare the result of the first expression to the result of the second expression, and determine whether they are equivalent. So, Excel evaluates the first formula and determines whether it is TRUE or FALSE. It then evaluates the second formula and determines whether it is TRUE or FALSE. Finally, it equates the two results and determines whether the end result is TRUE or FALSE.

In other words, you end up with four possible intermediate results:

1. Both expressions are TRUE.
2. The first is TRUE; the second is FALSE.
3. Both expressions are FALSE.
4. The first is FALSE, the second is TRUE.

Now, take the final step and evaluate these four intermediate results. The first and third evaluate to TRUE (that is, TRUE = TRUE is clearly TRUE, just as FALSE = FALSE is clearly TRUE); the second and fourth evaluate to FALSE (TRUE = FALSE is FALSE; FALSE = TRUE is FALSE).

So, if you apply the conditional expression =MOD(ROW(),2) =MOD(COLUMN(),2) to cell A1 (where both the row and column number are equal to 1) you get:

=MOD(1,2) =MOD(1,2)
1 = 1 which is TRUE

For cell A2:

=MOD(2,2) =MOD(1,2)
0 = 1 which is FALSE

For cell A3:

=MOD(3,2) =MOD(1,2)
1 = 1 which is TRUE

For cell A4:

=MOD(4,2) =MOD(1,2)
0 = 1 which is FALSE

For cell B1:

=MOD(1,2) =MOD(2,2)
1 = 0 which is FALSE

For B2:

=MOD(2,2) =MOD(2,2)
0 = 0 which is TRUE

For B3:

=MOD(3,2) =MOD(2,2)
1 = 0 which is FALSE

For B4:

=MOD(4,2) =MOD(2,2)
0 = 0 which is TRUE

And so on, with the alternating TRUE/FALSE/TRUE/FALSE in one column and FALSE/TRUE/FALSE/TRUE in the next building up the checkerboard shading.

Tip: Understanding Excel references - A quick guide

When you refer to a cell in an Excel formula, you can use any of three different ways of referring to that cell, known as relative, absolute and mixed references.

Relative cell references are the most commonly used. A relative cell reference in a formula is based on the position of the formula’s cell relative to the cell to which it refers. That means if you move the formula cell, or copy it elsewhere, the reference changes.

You denote a relative reference simply by using the cell’s column letter followed by its row number: A1. A simple formula that uses relative cell references to add the numbers in cells B1 through B9 is:

=SUM(B1:B9)

If you place this formula in cell B10 and then copy it across from B10 to C10, Excel makes the sensible assumption that you want to total the values in the same relative positions in column C – that is, cells C1 to C9 – and so it automatically adjusts the formula to read:

=SUM(C1:C9)

An absolute reference refers to a cell in a fixed location. Such references come in handy when you want to refer consistently to the same cell, or range of cells, throughout a worksheet.

For example, if you use a worksheet to estimate a mileage allowance for business travel, you could put the allowance rate per mile in cell D2 and then use an absolute reference to that cell anywhere you use a formula based on the mileage rate. To indicate an absolute reference use dollar signs, thus: $D$2.

Name that cell

You can make life easier for yourself by naming a cell or range of cells. This is particularly handy when you want to refer to a certain cell repeatedly in formulas. When you name a cell, Excel automatically makes it an absolute reference.

 

For example, to name our mileage rate cell in Excel 2007:

1. Right-click the cell and select Name A Range from the pop-up menu.
2. Type a name in the Name box, such as MileageRate and click OK. Although you can’t include spaces in the name, you can use underscores (Mileage_Rate), periods/full stops (Mileage.Rate) or slashes (Mileage\Rate). Excel ignores the case, but it can make your worksheets more readable if you mix lower- and uppercase.

To name the cell in Excel 2003:

1. Click the cell.
2. Click Insert -> Name -> Define.
3. Type in the name, MileageRate, and click OK.

Once you’ve named the cell, you can use its name in any formula, thus:

=E7*MileageRate

As you define the name, notice the value in the Refers To box. You’ll see the full absolute reference consists of the worksheet name followed by an exclamation mark and the absolute cell reference, for example:

=’Travel Expenses’!$D$2

That means you can use the named reference – in our case, MileageRate – in any worksheet in your workbook, not just the current worksheet.

Specifying the scope of a name 

That’s great, but what if you’ve set your workbook up with a separate worksheet for each employee and each of those employees has a different mileage rate? In that case, you won’t want a reference to MileageRate on Mildred’s worksheet grabbing the value from Darren’s worksheet.

In Excel 2003, you deal with this situation by specifying the worksheet when defining a name: in the Name box, first type the current worksheet’s name, followed by an exclamation mark, followed by your chosen cell name. For example:

Darren!MileageRate

Excel 2007, makes this easier by including a Scope box in the New Name dialog: when you define the name, select the current worksheet from the drop-down Scope box to restrict the reference to the current sheet, and Excel will name it appropriately.

Deleting a name 

If you ever need to delete a cell/range name, in Excel 2003:

1. Click Insert -> Name -> Define.
2. In the Define Name dialog, click the name you want to delete then click the Delete button.

In Excel 2007:

1. On the Formulas tab click Name Manager.
2. In the Name Manager dialog, click the name you want to delete and click the Delete button.

Mixed references

Mixed references are a combination of relative and absolute: either the column is relative and the row fixed (absolute), for example D$2, or the column is fixed and the row relative: $D2.

When would you need such a reference? One case is when you create any table where the values are derived by multiplying the x axis by the y axis. A multiplication table is the simplest example of this.

The easiest way to get a feel for mixed references is to give them a try:

1. Place the values 1 through 12 across in cells B2 to M2. This is the x axis of your multiplication table.
2. Place the values 1 through 12 down in cells A3 to A14. This is the y axis of your multiplication table.
3. In the top, leftmost cell of the results grid, cell B3, enter the formula: =B$2*$A3.
4. Copy that formula down and then across to include the entire table.

This formula translates as: multiply the value in row 2, column x by the value in column A, row y. For the first cell referenced in the formula, the row remains constant (row 2, the x axis where you placed the values 1 through 12) while the column changes. For the second cell reference, the column remains constant (column A, the y axis where you placed the values 1 through 12), while the row changes. No matter where you click in the table, you’ll see row 2 and column A referenced in the formula bar, while the other values vary.

 

Changing reference types

If you find you’ve used the wrong type of reference in a formula, Excel offers a shortcut for changing the reference:

1. Click the offending cell.
2. In the formula bar, click the incorrect cell reference and press F4. Each time you press F4, Excel will cycle the reference through relative -> absolute -> mixed (relative/absolute) -> mixed (absolute/relative).
3. Repeat this process for each incorrect reference in the formula.

A memory jogger to help you remember to use the F4 key for changing references is to take a look at your keyboard: notice how the $ symbol, used to denote absolute references, is above the number 4.  Use it as a reminder (a trick I learnt from a Kiwi school teacher).