One of the most common mistakes users make in preparing a report or sorting data is including redundant entries. For example, if a worksheet keeps a running tally of purchases made by Customer 1, there's no need to report cells other than the ones keeping the composite figures. A built-in filter in Excel will take care of this problem.

Follow these steps:

1.       Choose Data | Filter | Advanced Filter.

2.       Drag across the worksheet to select the lists containing duplicate entries.

3.       Select Unique Records Only.

4.       Click OK.

Excel will now hide any duplicate records in the selected range.

Converting Julian dates

Here's a little piece of post-Y2K calendar trivia for you. Since the 1950s, astronomers and geophysicists have used Julian dates to track time in continuous, absolute terms, without bothersome seasons, leap years, or man-made conventions. Julian dates associate a date with the number of days elapsed since January 1 of the same year. (For example, Jan. 2, 2000, has the value of 2.)

Excel doesn't have a built-in option to convert dates to Julian time, but you can use this shortcut to convert today's date to the Julian format:

Just type this equation into any cell in your worksheet:

=VALUE(RIGHT(YEAR(TODAY()),2)&TEXT(TODAY()-DATE(YEAR(TODAY())-1,12,31),"000"))

Now, you'll need to format this value to display five digits.

1.       Right-click in the cell that contains the value and select Format Cells.

2.       On the Number tab, choose Custom from the Category list box.

3.       In the Type text box, enter 00000.

4.       Click OK.

Comment an article   Name   E-mail    Title Available characters: 4000  Notify me of follow-up comments Enter what you see:

No comment posted