Back

Excel Tips 

What's the Current Date or Time?
Creating Chart
Applying Colours to maximum and/or minimum values
Sorting by Columns
Top Secret:Do Not Print
Quick Math Without Formulas
Creating a time sheet
Shade Alternate Rows
Add a Drop-Down List
Zap the Numbers, Keep the Formulas
Protect and Hide Your Formulas
Spot Duplicate Entries
Skip From Cell to Cell
Hide Those %&@# Formula Errors
Ignoring blank cells when performing calculations
Add a picuture of logo to header/footer

 

What's the Current Date or Time?
You may be familiar with Excel's NOW function, which plugs the current date and time into your spreadsheet. Just enter =NOW() into any cell, and Excel displays the date and time formatted according to the regional options you've set for Windows. If you just want to see the date, use the TODAY function, =TODAY(), instead.

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 change--hold down Ctrl while you press the semicolon ( ;) key. To insert the current time (without a date), press Ctrl-Shift-Semicolon.

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.

Top


Creating Chart
To create and customize a chart at the same time, select the data you want to present in the chart, and then click Chart Wizard on the Standard toolbar.To create a chart instantly, select the data you want to present in the chart, and then press F11 or ALT+F1. Changing the chart type of your chart is easy. Right-click the chart, and then click Chart Type.When you change the chart type, retain custom formatting by clearing the Default Formatting check box on the Standard Types tab. You can change the chart type for just one data series by selecting it, and then clicking Chart Type on the Chart menu. Make sure the Apply To Selection check box is selected.

Top


Applying Colours to maximum and/or minimum values
Select a cell in the region, and press Ctrl+Shift+* (in Excel 2003, press this or Ctrl+A) to select the Current Region. From the Format menu, select Conditional Formatting. In Condition 1, select Formula Is, and type =MAX($F:$F) =$F1.4. Click Format, select the Font tab, select a color, and then click OK. In Condition 2, select Formula Is, and type =MIN($F:$F) =$F1.6. Repeat using format tab and select a different color than you selected for Condition 1, and then click OK.

Note:
Be sure to distinguish between absolute reference and relative reference when entering the formulas.
Screenshot // Applying Colors to Maximum/Minimum Values in a List 

Applying Colors to Maximum/Minimum Values in a List

Top

Sorting by Columns
From the Data menu, select Sort, and then Options.2. Select the Sort left to right option button and click OK. In the Sort by option of the Sort dialog box, select the row number by which the columns will be sorted and click OK. 

Sorting by Columns
 

Top

Top Secret:Do Not Print
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 Ctrl while clicking to select non-adjacent rows). Then right-click one of the highlighted border row numbers and click Hide. Use the same procedure to hide columns (but right-click a highlighted column letter to choose Hide).

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 Unhide.

Top

Quick Math Without Formulas
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:

Enter 1.05 into any blank cell. This is the "multiplier" that will increase the prices by 5 percent.

Select the cell you used in Step 1, and choose Edit, Copy or press Ctrl-C.

Select the range of values to be changed, and choose Edit, Paste Special to display the Paste Special dialog box.

Choose the Multiply option and click OK.

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.

Top

Creating a time sheet
Creating a timesheet which calculates the number of hours worked each day and a sum of the total hours worked. Create 3 columns containing the following data: Date, Time In, Time Out. In the 4th column (Number of Hours), use the following formula: =(C2-B2+(C2

To sum the total number of hours worked, use the SUM function as shown in the following formula:
=SUM(D2:D7)  

Creating a Timesheet
 

Top

Shade Alternate Rows
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:

Highlight the range of cells or rows or columns that you want to format.

Choose Format, Conditional Formatting to display the Conditional Formatting dialog box.

Select Formula Is from the first drop-down list box, and enter =MOD(ROW(),2)=0 in the second box.

Click the Format button to bring up the Format Cells dialog box.

Select the Patterns tab and specify a color for the shaded rows. You'll probably want to choose a light color, so that the default black text will still be legible. Or, you can go all out and change the text color as well (do this in the Font tab of the Format Cells dialog box). For example, you might select a dark blue background, accompanied by a mellow yellow text color.

Click OK twice to return to your worksheet.

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 Format, Conditional Formatting, and click the Delete button in the Conditional Formatting dialog box.

Top


Ignoring blank cells while performing calculations
When columns contain numbers as well as empty cells and you want to be able to multiply each number in one column with the matching number in another without getting false results or errors - Use IF, AND and ISNUMBER functions in the following formula: =IF(AND(ISNUMBER(A2), ISNUMBER(B2)),A2*B2," ")


 

Top

Add a Drop-Down List
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.

Enter the items for your drop-down list into a list on the worksheet, one item per cell. In this example, I'll assume that the month names start in cell E1 and extend down to E12, but they can be in any out-of-the-way location on the worksheet. In Excel terminology, a rectangular group of cells (such as E1 to E12) is called a range.

Select the cell that will contain the drop-down list. If you'd like more than one cell to display the same list, just select them all now rather than setting them up one at a time. (Click and drag to select a range; hold down Ctrl while you click to select non-adjacent cells.)

Choose Data, Validation to display the Data Validation dialog box.

Click the Settings tab.

In the Allow field, select List.

In the Source field, specify the range that contains the list items. In this example, the items are in cells E1 to E12, so type =E1:E12 into the field.

Click OK.


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.

Top

Zap the Numbers, Keep the Formulas
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:

Select Edit, Go To (or press Ctrl-G or F5) to bring up the Go To dialog box.

Click the Special button to show the Go To Special dialog box.

Choose the Constants option, then clear all of the check boxes except Numbers (if some of the input cells in the spreadsheet accept text, leave the check mark next to Text).

Click OK, and Excel will select all of the non-formula cells that contain a value.

Press the Delete key, and those numbers are history--but the formulas remain intact.

Top

Protect and Hide Your Formulas
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:

Select Edit, Go To (or press Ctrl-G or F5) to bring up the Go To dialog box.

Click the Special button to show the Go To Special dialog box.

Choose the Formulas option, and make sure the four check boxes are all checked.

Click OK, and Excel selects all cells that contain a formula.

Choose Format, Cells, and click the Protection tab.

Place a check mark next to the Hidden option, and make sure that there is a check mark next to the Locked option. Click OK.

Here's how to unlock the non-formula cells:

Repeat Steps 1 and 2 above to show the Go To Special dialog box.

Choose the Constants option, and make sure the four check boxes are all checked.

Click OK, and Excel selects all non-empty cells that don't contain a formula.

Choose Format, Cells, and click the Protection tab.

Remove the check mark next to the Locked option. Click OK.

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 protected. Choose Tools, Protection, Protect Sheet to protect the worksheet. You can enter a password if you like.

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.

Top

Spot Duplicate Entries
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.

Select the range (in this case, A1:B18).

Choose Format, Conditional Formatting to display the Conditional Formatting dialog box.

Select Formula Is from the first drop-down list box, and enter =COUNTIF($A$1:$B$18,A1)>1 in the second box.

Click the Format button to bring up the Format Cells dialog box.

Select the Patterns tab, and specify a background color.

Click OK twice to return to your worksheet.

If the range contains any duplicate entries, they will be flagged with the background color you chose in Step 5.


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).

Top

Skip From Cell to Cell
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:

Select all of the input cells on your worksheet. (To select non-adjacent cells, hold down Ctrl while you select the cells.)

Select Format Cells to display the Format Cells dialog box.

Click the Protection tab and remove the check mark from the Locked check box.

Click OK.

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 Tools, Protection, Protect Sheet. If you're using Excel 2002, you'll be able to specify some additional options. Specifically, you can remove the check mark from the Select locked cells option.

When the sheet is protected, you'll find pressing Tab moves the heavy-bordered active cell indicator to the next unlocked cell. Be aware that this does not prevent the user from selecting unlocked cells using the cursor keys--unless you're using Excel 2002 and you removed the check mark from 'Select locked cells' as described above.

Top

Hide Those %&@# Formula Errors
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.

Top


Adding a picture or logo to header/footer
To add a picture (such as a company logo) to the header/ footer in Excel 97 and Excel 2000:

1. Select cell A1.
2. From the Insert menu, select Picture, and then select From File.
3. Select the picture you want, and click Insert.
4. Adjust the picture to the height and width of the row.
5. From the File menu, select Page Setup.
6. Select the Sheet tab.
7. Select Rows to repeat at top.
8. Select row 1, and then click OK.

To add a picture (such as a company logo) to the header/footer in Excel 2002 and Excel 2003:

1. From the File menu, select Page Setup.
2. Select the Header/Footer tab.
3. Select Custom Footer.
4. Select Left section.
5. Click the Picture icon (the second icon from the right).
6. In the Insert Picture dialog box, search for and select the logo or picture you want to add.
7. To format the picture, click the Format Picture icon (the first icon from the right).
8. Click OK.

Top