█ Date: December 2nd, 2009 █ Author: Jody Gilbert █
Like a lot of business software users, I’m a generalist. On any given day, my job may require a little number crunching and word processing, and maybe some page layout, slide show creation, or report building. For the most part, the various Office apps make it easy to meet these needs. But certain tasks just don’t come up often enough for me to keep them in my head. Excel is a good example. I use it every day, but I seldom need to do more than enter data or a simple formula. So when a job requires something a little more sophisticated, I waste a certain amount of time trying to remember how I got it to work before. Then I waste even more time trying to extract a useful answer from online help.
Frustrating, unproductive, and a little embarrassing.
So I made a list of a few Excel techniques I occasionally need but inevitably forget. This is bunny stuff, and not for you power users out there. But if you’re a casual Excel user, maybe this list will help you cut to the chase. Note: This cheat sheet is also available as a PDF download. Link: http://downloads.techrepublic.com.com/abstract.aspx?docid=1200511
1: Toggle the display of formulas
When you need to see what’s going on under the hood of a worksheet, you may want to turn on Excel’s formula display. There’s a convoluted way to do this via Excel options (and Excel 2007 offers the Show Formulas button in the Formula Auditing group of the Formulas tab - if you want to remember that). But you can toggle the display on the fly just by pressing [Ctrl] ~. If you select a cell whose formula you want to troubleshoot before turning on the display, Excel will also show you the dependent cells for the formula.
2: Convert a formula to its results
Sometimes, you may need to replace a formula with its results - either to preserve a static value or to optimize your sheet by reducing calculations. There’s a pretty simple trick for this, but a word of warning: Be sure you really want to wipe out a formula before you do it. (There could be undesirable consequences.) In fact, a good practice is to create a backup copy of the workbook as a safety net in case things go awry.
To convert a formula, click in its cell and press [F2] to enable in-cell editing. Next, press [F9] to calculate the formula and display its results. Then, hit [Enter], and your formula will be replaced by the value it produced.
You can also copy the formula and use Paste Special | Values to paste the results someplace else, leaving the formula intact in its original location.
3: Create a copy of an existing worksheet
Excel offers an efficient way to copy a worksheet, either within the current book or into a different one - handy when you need to start a new sheet that includes some or all of the data and/or formatting of an existing sheet. It works like this:
· Right-click on the sheet tab of the sheet you want to copy.
· Choose Move Or Copy.
· Select the Create A Copy check box in the bottom-left corner of the Move Or Copy dialog box.
· Choose a different workbook, if desired, from the To Book drop-down list. (That other workbook must be open to show up in the list.) You can also select New Workbook.
· In the Before Sheet list box, specify where you want the copied sheet to go within the specified workbook.
· Click OK.
4: Start a new line within a cell
This may seem beyond simplistic - until the day you can’t remember how to do it. If you need to create a multiple-line entry in a cell, you can’t just press [Enter] to insert a line break, since that will propel you into the next cell. Instead, you have to press [Alt][Enter].
5: Unhide hidden rows or columns
From time to time, someone will send me a worksheet with hidden rows or columns. I usually don’t need to see the data, so of course I forget how to unhide it on the rare occasions when I do need to see it. It’s easy, though: Highlight the row above and the row below the hidden row(s) - or the column to the left and to the right of the hidden column(s). Then, you can reveal the data in various ways:
· Press [Shift][Ctrl]0 (that’s a zero).
· Right-click the selection and choose Unhide.
· Choose Column (or Row) from the Format menu and then select Unhide. In Excel 2007, go to the Cells group on the Home tab, click Format, choose Hide & Unhide, and select Unhide Rows or Unhide Columns.
6: Enter a fraction in a cell
Say you type 1/4 in a cell, wanting to enter the fraction one-fourth. Ordinarily, Excel will turn the value into a date - 4-Jan. To prevent that, just preface your entry with a zero and a space: 0 1/4. Excel will leave your fraction alone. Without the zero, you’ll see 1/4/2009 (or whatever year you happen to be in) in the Formula bar. With the zero, you’ll see 0.25.
7: Simultaneously copy data into noncontiguous cells
To copy data from one cell into adjacent cells, you just drag the cell’s fill handle across the cells where you want the copied data to appear. But sometimes, you’ll need to copy data into cells that are scattered around the worksheet. The most efficient way to handle that task is to copy the desired data, hold down [Ctrl], and select all the other cells where you want to paste the data. Then, press [Ctrl]V and Excel will insert the copied data into each of the selected cells.
8: Simultaneously enter data into noncontiguous cells
Similar to the previous trick, you can save time when you need to enter the same data into cells that aren’t next to each other. Start by holding down the Ctrl key and selecting all the cells into which you want to enter data. Then, type your data and press [Ctrl][Enter]. Excel will insert the data into all of the cells in the noncontiguous selection.
9: Enter text in the same location in multiple worksheets
This may not come up all that often, but it’s a cool trick when you need it. Let’s say that you’re entering month names as column headers at the top of a sheet - and you want them to appear on your other sheets as well. Click in the cell where you’ll be entering January. Then hold down [Ctrl] and click on the sheet tabs of the other sheets where you want the month names to appear. This will group the sheets so that what you do now affects all of them.
Go ahead and type January. Then (another cool trick coming…), drag the cell’s fill handle to the right across the next 11 cells. Excel recognizes that January is the first item in a built-in series, so it will insert the rest of the month names for you.
To complete the process, right-click on one of the selected sheet tabs and choose Ungroup Sheets from the shortcut menu. If you check those sheets, you’ll see your month names have been entered in all of them.
10: Transpose data from a row to a column, or vice-versa
Once in a while, I’ll set up a worksheet using one structure that seems to make sense, only to realize it would make a whole lot more sense if the rows were columns and the columns were rows. And apparently I’m not alone in this befuddlement, because Excel provides a Transpose option to facilitate the necessary flip-flopping of data:
· Select the range of cells you want to transpose and click Copy or press [Ctrl]C.
· Click in a new location (not overlapping your selection).
· Go to Edit | Paste Special and select the Transpose check box. In Excel 2007, click Paste in the Clipboard group of the Home tab and select Transpose.
· You can then delete your original, wrong-structured data.
Posts
