Who Knew Excel Could Do That? Ten Top Tips
If you use Excel regularly, you're probably comfortable doing the standard spreadsheet stuff: You enter data, create basic formulas, and apply formatting. Although complete mastery of Excel certainly isn't one of your goals in life, you're always on the lookout for something that can make your job easier. But you simply haven't had the time or motivation to fully explore all that Excel has to offer.
Sound familiar? If so, now's your chance to pick up a few handy tricks that you may not have discovered yet. And if you're new to Excel, these tips will help get you off to a flying start.
The following ten tips will work in Excel versions 97 and later.
You may be familiar with Excel's NOW function, which plugs the current
date and time into your spreadsheet. Just enter
But if you want to use either of these functions as a type of "timestamp" you'll find that they don't do the job, because they're automatically updated whenever your worksheet is calculated or re-opened. The cell that displays today's date will display tomorrow's date when you open the spreadsheet tomorrow.
To insert a fixed date into a cell--a date that you don't want to
These commands are useful if you like to document your work. For example, you might want to keep track of when you added new data to your worksheet.
When it's time to print your Excel worksheet, you may prefer that some information is left unprinted. For example, you may have some confidential information (such as employee salaries) in a column, or your sheet may have some intermediate calculations that the top brass aren't interested in seeing.
To avoid printing specific rows or columns, just hide them before
printing. To hide rows, select them by clicking the row numbers (click and drag
to select a block of rows; hold down
When you're finished printing, you can quickly unhide all rows or
columns by selecting the entire worksheet (click the blank gray box formed by
the intersection of the row and column borders at the top left corner of your
spreadsheet). Then right-click a row or column border and choose
If you want to do math with Excel, your only choice is to write formulas, right? Wrong! Get familiar with Excel's Paste Function dialog box, and learn how to perform a variety of basic math operations without a single formula. Consider this scenario: Your company's product price list stored in a worksheet. Your boss informs you that, effective immediately, all prices must be increased by 5 percent.
You could create some formulas to do the math, and then convert the formulas to values, and then copy and paste the new prices over the old prices. Or, you could use this more efficient method:
Voilà! The values are changed in an instant. You can then delete the cell that contains the 1.05 multiplier.
Note that the Paste Special dialog box lets you carry out other mathematical operations--like add and subtract, for instance--so you can use this technique for a variety of other calculations.
Looking for a way to simulate that nerdy green-banded computer paper on screen? Look no further. Excel's Conditional Formatting feature has many uses, and here's one that's particularly handy. Conditional Formatting makes it simple to apply cell shading (green or otherwise) to every other row in a worksheet range. For a lengthy list, shading alternate rows can improve legibility.
Here's how to do it:
The best part is that the row shading is dynamic: The alternate row
shading persists even if you insert or delete rows within the original range.
By the way, if get tired of this new look and want to get things back to
normal, just select the range, choose
Inserting a drop-down list to a cell is a slick trick that can add a touch of professionalism to your worksheets--and also ensure that erroneous data is not entered into the cell. And best of all: Macros are not required!
Assume that you have an input cell in which the user is supposed to enter a month name: January, February, and so on. Here's how to add a drop-down list to that cell to make data entry a breeze--and save some keystrokes.
After performing these steps, you'll see a drop-down arrow whenever any of the drop-down cells you just defined is "active" (that is, selected and awaiting input). Click the arrow and choose a month from the list. If you try to type something else into the cell, you'll get scolded in the form of a pop-up message.
If you'd like to provide your own wording for invalid entries, use the Error Alert tab in the Data Validation dialog box, and enter your own text in the 'Error message' field.
If your list of items is relatively small, you can bypass Step 1 and enter the list items directly into the Source field in Step 6. Just separate each list item with a comma.
One common type of spreadsheet is a fill-in-the-blanks template. Typically, such a sheet will have a number of input cells, and a number of formula cells. You enter your data in the input cells, and the formula cells kick into action and use those values to display some type of (usually) useful result.
For example, you may have a worksheet set up to calculate a loan amortization table. Input cells would include items such as loan amount, interest rate, loan term, and so on. Formulas in other cells use this information to display the calculated results.
At some point, you may want to clear the slate and delete all the values in the input cells. Rather than scroll around and look for the non-formula cells, you can take advantage of an often-overlooked Excel feature that lets you select cells in a "special" way:
When a cell contains a formula, the formula is visible for all to see. Just activate the cell and glance up at the Formula bar. In some cases, you many want to hide your formulas to give your worksheet a cleaner look--or to keep others from seeing how your calculations are done.
You can hide the formulas by setting the Hide option for the formula cells and protecting the sheet. But before you protect the sheet, you need to "unlock" all non-formula cells (by default, all cells are locked).
Here's how to hide the formula cells:
Here's how to unlock the non-formula cells:
At this point, the formula cells are set to Hide, and the non-formula
cells are set to Unlock. But these settings have no effect unless the sheet is
After performing these steps, you can select any formula cell, and the formula will not be displayed in the Formula bar. Furthermore, the formula cannot be changed. But the cells that contain other information can be changed.
It's important to understand that protecting a worksheet--even if you use a password--is a very weak form of security. Password-cracking utilities are widespread on the Internet.
This tip describes a way to use the Conditional Formatting feature to quickly identify dupes in a range.
Say that you've been saddled with the job of organizing the seating list for the company dinner party. You have the list of assignments in range A1:B18. Obviously, the same person should not be assigned to more than one table. Here's a quick way to determine if the range contains duplicate names.
You can adapt this technique to any other worksheet by changing the ranges used in the COUNTIF function. The first argument should be the full address of the range you selected--and make sure that you use the dollar signs to signify an absolute reference. The second argument should be the address of the upper left cell of your range, expressed as a relative reference (no dollar signs).
You may have a spreadsheet set up with a number of data input cells. Wouldn't it be nice if the user could just press the Tab key to jump to the next input cell?
This type of thing is fairly easy to set up. The key is to unlock the input cells, and then protect the worksheet. Here's how to do it:
Keep in mind that all cells are locked by default. But also remember
that locking or unlocking cells has no effect unless the worksheet is
protected. To protect the worksheet, select
When the sheet is protected, you'll find pressing
If you've dabbled with formulas, you've probably encountered the dreaded formula error. Rather than return a numeric result, the formula cell displays a weird message such as #VALUE! or #DIV/0!.
Most of the time, this means you need to track down the source of the error and fix it. But sometimes a formula error simply means that the data used by the formula is not yet available. For example, say you run a small telemarketing company. You might have a spreadsheet set up to track your daily sales as a percentage of calls made.
The formulas in column D do the calculations that come up with the percentages. For example, cell D4 contains the formula =C4/B4. The formula in D4 was simply copied down the column to handle the other days.
The formula does its job well--as long as there is data to calculate. An empty cell (such as B9) is treated as a zero, and division by zero is not allowed on this planet. As a result, Excel displays an ugly #DIV/0! error message, which makes your entire worksheet look like it was created by a novice.
You can avoid displaying formula errors by re-writing your formula to use an IF and an ISERROR function. For example, =IF(ISERROR(C4/B4),"", C4/B4) displays a blank if the division operation results in an error (cell B4 is empty or contains 0), yet still displays valid results.
Although this formula looks complex, when you break it down, it's not that daunting. In plain English: If you get an error performing the formula, then display an empty string (that is, nothing); otherwise, display the result of the formula.
It's actually easy to adapt this technique to any formula you might have. The original formula serves as the argument for the ISERROR function, and it repeats as the last argument of the IF function, like this: =IF(ISERROR(OriginalFormula),"",OriginalFormula)
If you prefer, you can replace the empty string ("") with other text of your choice--just make sure the text is enclosed in quote marks.
By the way, if you really own a small telemarketing company, please put me on your "do not call" list. Thank you.