Year: 2005

Rule #2: Never type in data in Excel.

You rarely spend time creating voluminous data. Usually, you’re just processing it (copying, transforming, whatever).

Sometimes data is on a web page — typically tables. To copy such data, open the page in Internet Explorer and paste it in Excel. You won’t like the formatting. So copy the cells you just pasted, go to a different sheet, and Edit-Paste Special just the values (Alt-E-S-V-Enter).

Sometimes data is on a text file. You can open text files directly in Excel. Each line becomes a row. You can split lines into columns if there is a “delimiter” between any two cells. Just load a text file, select all the rows, and play with the Data – Text to Columns menu (Alt-D-E).

Sometimes, data is on a PDF file. Usually, such data is in a table. If you have Adobe Reader, tough luck. Just select and copy the table, paste it into Notepad, manually format it (painful), copy again from Notepad and paste in Excel. If you have Adobe Acrobat, it’s slightly better. You can use the “Select Column” tool to select and copy entire columns of the table in one shot.

Sometimes, data is on paper. Scanner often come with an optical character recognition (OCR) software. If not, Microsoft Office 2003 comes with a Microsoft Office Document Imaging tool has OCR. Just scan the image, open it in the Microsoft Document Imaging tool, go to the Tools – Recognize Text Using OCR… menu, and pray.

After all this importing, the data is never “clean”. Errors due to unintended delimiters, extraneous blank lines, etc are fairly frequent. I’ll talk about how to manage this when discussing Rule #3: Automate the task

I spend a lot of time building models on Excel. I have 4 rules that help me get things done fast.

1. Never use the mouse. The keyboard is much faster.
2. Never type in data. You can always import it.
3. Avoid manual labour. Use Excel to automate the task.
4. Make your data visually obvious.

Let’s look at Rule #1: Never use the mouse.

Using the keyboard can be 10 times faster than the mouse. It takes time to move one hand from the keyboard to the mouse, locate the item you want to click at, move the mouse there, adjust it finely so it’s pointing at the exact spot, and then click it. For example, to insert text without formatting, I’d just go Alt-E, S, enter. It takes half a second. It took me 5 seconds with a mouse. (I timed 10 continuous attempts in both cases.)

A factor of 10 speed advantage like that is good for two reasons: it saves you time, and it doesn’t distract you from what you’re doing (provided the keyboard shortcuts have become a habit.)

For newbies: To use menus using keyboard shortcuts, first go to Start – Settings – Control Panel – Display – Appearance tab – Effects button – Hide underlined letters for keyboard navigation until I press the Alt key. Make sure it is turned off. To use a menu, let’s say “Insert – Row”. look for the underlined letter on the menu bar (the “I” on “Insert”), press Alt and the underlined letter (Alt-I in this case), and look for the underlined letter on the next menu item (“R” on the “Rows” in this case) and press that letter. So, Alt-I-R is the shortcut to insert a row. Now, just practice Alt-I-R, Alt-I-R, Alt-I-R repeatedly.

Shortcuts next to the menu are quicker, where they exist. For example, the Ctrl-C next to the Edit-Copy menu.

Apart from the arrow keys, Ctrl-S, Ctrl-X, Ctrl-C, Ctrl-V, the keys I use frequently are:

• F2: edit the current cell
• F4: repeat the last action (very useful)
• Ctrl-Z, Ctrl-Y: Undo, Redo
• Ctrl-1: Format cells
• Ctrl-Shift-down arrow: Select all filled cells below selection (also works with other arrow keys)
• Ctrl-PgUp/PgDn: Shift between tabs
• Ctrl-Home/End: Go to top-left, or bottom-right of the sheet (or cell, if you’re editing a cell)
• Ctrl-`: Show formulae
• Shift-Space, Ctrl-Space: Select row, select column
• Alt-Enter: To create a new line while you’re typing in a cell
• Alt-E-I-S-Enter: Select a set of cells and fill a continuous series of numbers in it
• Alt-E-S-Enter: Paste unformatted text
• Alt-D-G-G: Group a set of rows (use Alt-D-G-U for ungroup)
• Alt-D-G-S: Show a collapsed group (use Alt-D-G-H to collapse a group)
• Alt-O-D: Conditional formatting
• Alt-F8: Macros