Share your tips, on Engage!
Do you have some tips of your own about spreadsheets? If you're a member, I'd love you to share them on AIChE Engage.
Often it's the small steps that add up to either big gains or big failures. That's certainly the case with the common operations of selecting, copying, and moving when using Excel. If you want to save time and find a more dependable path to the answers you seek, then hone these frequently used commands.
Master common operations to ramp up results
All ChEs should know how to move around the spreadsheet, select blocks of cells, copy cells to other locations, and move cells or blocks of cells. Many of these techniques can be accomplished with the mouse, with the keyboard and the mouse in combination, or using the keyboard alone.
First to consider is moving a selected cell to another location on the spreadsheet. To choose an active cell, click on that cell. The active cell location can be moved quickly to an adjacent cell using the arrow keys. A quick way to jump to a cell far away is to type the cell address or name into the Name Box and press Enter. If a cell name has been defined, you can also use the drop-down list in the Name Box and click on the name there. Another way to do this is with the F5 “Go To” shortcut key.
When operating within a column of filled cells, you can jump to the last filled cell in the column by double-clicking the lower boundary of the cell selected at the top (see below). You can also use the same technique to jump across a column of blank cells to a filled cell.
Of course, if a column is short, like the one shown above, you could simply click on the last cell. However, the jump technique is handy for much longer columns (of several hundred cells). As a keyboard alternative, you can use the Ctrl-arrow key combination (Ctrl-↓).
To make block selections rather than jumps, add the Shift key to any of the above techniques. Select an entire block of cells with either the Ctrl-a or Ctrl-* key combination. If you need to select cells or blocks of cells that are not contiguous, you can hold down the Ctrl key and proceed through the selections, as shown below.
Once cells are selected, you can change their format together, rather than using a tool like the Format Painter and moving from cell to cell. Another application of this technique is to select nonadjacent columns of data to create a chart.
When selecting a lengthy column of data using the Ctrl-Shift-↓ key combination, the bottom of the column is in view with the last cell as the active cell. A neat trick to bring the top of the selection back into view is to press the Tab key followed by the Shift-Tab combination. Of course, you can also drag the slider on the side of the spreadsheet window.
The standard technique for copying cells to adjacent locations is to drag the Fill Handle, located in the lower right corner of the selection. To copy one or more cells to a more distant location, you can initiate the copy with the Ctrl-C key combination, select the destination’s upper left-hand corner cell, and press the Enter key. Alternatively, you can drag the selection to the destination with the Ctrl key pressed; a small + sign will appear adjacent to the mouse cursor arrow when you use this method.
Moving cells is similar to copying cells. To move a cell to an adjacent location, rather than drag the Fill Handle as for copying, you can drag the cell boundary using the arrow cursor. You can also drag selected blocks of cells in that manner. A move can also be initiated with a “cut” shortcut command, Ctrl-X, and terminated with the Enter key, as with the copy technique.
More tips and techniques
Join us for the next installemnt of the series when we'll look at how to be smart about managing units in spreadsheets. If you're just joining us, check out the entire series. And if you want a full crash course instead of just helpful tips, you should check out the AIChE Academy's "Spreadsheet Problem-Solving for Chemical Engineers," where these tips come from, and also check out the other Excel courses available through the AIChE Academy at www.aiche.org/academy.
Want more Excel tips for chemical engineers?
If you know you want to delve even deeper than this blog series – or if our Excel tips leave you hungry for more – be sure to check out AIChE’s virtual combo course on spreadsheet problem solving and VBA programming. It’s taught by David E. Clough, the author of this series, and combines two of AIChE’s most popular spreadsheet courses, Spreadsheet Problem-Solving for Chemical Engineers and Excel VBA Programming for Chemical Engineers.
Share your tips, on Engage!
Do you have some tips of your own about spreadsheets? If you're a member, I'd love you to share them on AIChE Engage.
This Excel spreadsheet series is drawn from an article by David Clough that appeared in AIChE's CEP Magazine. You can find the current issue and an extensive archives of back issues at www.aiche.org/cep.