Courtesy of Dennis Taylor at lynda.com.

I need an intervention, to stop me from watching “fun with Excel” videos in the evening, when normal people are watching murder/sex and crime TV shows.

• Have a column of items which are the same, and need to have some modification of contents? Say, a department ID number, and you have 100 people listed in that department. You would like to add hyphens to it. So, 123456 becomes 123-456. Select the items to be changed. F2 to edit the active cell. Make your change. Now, instead of pressing ENTER to save your change, press CTRL+ENTER. Your change will be propagated down the entire selection.

Caveat: remember that this change is propagating the entire contents of the changed cell, so all the selected items are being overwritten by the contents of the active cell.

• Have some cells in which you have made some calculations, but for which you only need the values? Indeed, in many cases a thoughtless copy/paste gives unexpected results, because you get the formula instead of the value. If the formulas are no longer needed, here’s how to get rid of them. Select the cells. Hover the mouse cursor over an edge of the selection, so that the four-headed “move” arrow displays. Right click and drag the selection to another location, and then without releasing the mouse button drag the selection back to its original place. Release the mouse button. A context menu appears, one item of which is Paste values only. Select that item, and the values overwrite the formulas.
• Have some codes or product identifiers that have leading zeroes? Then you know how Excel thoughtfully strips leading zeroes. To pad numbers with leading zeroes to a specific length, use the TEXT formula. To pad to six digits, =TEXT(A2,”000000″) (six zeroes) will turn number 12345 in cell A2 into text string 012345.

Pro Tip: to repeat a formula down the column, hover the mouse cursor over the lower right corner of the cell, until the cursor changes to the cross, and double click. The cell will be repeated as far down as the data in the adjacent left column.