MS Excel

You can save your worksheet as an HTML file, and Excel will automatically convert any charts to GIF files.

If that seems like overkill, you can write a simple macro that will do the job. Press Alt-F11 to activate the Visual Basic editor. Select your workbook in the Projects window, and choose Insert, Module to insert a new VBA module. Then type the following four-line procedure into the module:

Sub SaveChartAsGIF ()

Fname = ThisWorkbook.Path & "" & ActiveChart.Name & ".gif"

ActiveChart.Export FileName:=Fname, FilterName:="GIF"

End Sub

After the macro is entered, reactivate Excel and click the chart to be saved. Press Alt-F8 to display the Macro dialog box. Select the SaveChartAsGIF macro and click Run. The procedure uses the chart's name as the GIF file name, and the file is stored in the same directory as the workbook.

This simple macro does no error checking, so it will generate an error if a chart is not selected or if the workbook has not been saved.

For whatever reason, Microsoft continues to ignore what must be thousands of requests for this feature. Although Microsoft Word offers this feature, Excel offers no direct way to print a workbook's full path in the header or footer. The only solution is to create a macro. The technique described below works with Excel 97 and later.

In Excel, press Alt-F11 to activate the Visual Basic editor. In the Project window, double-click the project that corresponds to your workbook. The project list will expand to show several objects. Double-click the item labeled Microsoft Excel Objects, and then double-click the object labeled ThisWorkbook. Enter the following three lines of VBA code into the code module for the ThisWorkbook object (usually in the right pane of the window you're seeing at this point).

Private Sub Workbook_BeforePrint(Cancel As Boolean)

ActiveSheet.PageSetup.LeftHeader = ThisWorkbook.FullName

End Sub

After inserting the code, press Alt-Q to return to Excel.

This procedure will be executed before you print or preview your workbook. It simply inserts the workbook's path into the left header position. If you prefer to put the path in a different position, substitute any of the following for LeftHeader: CenterHeader, RightHeader, LeftFooter, CenterFooter, or RightFooter.

Excel stores dates and times as numeric values, so it should be possible to add or subtract one from the other. The problem occurs if you have a workbook containing only times and no dates. As you discovered, subtracting one time from another doesn't always work. Negative time values appear as a series of pound signs, even though you've assigned the [h]:mm format to the cells. By default, Excel uses a date system that begins with January 1, 1900. A negative time value generates a date-time combination that falls before this date, which is invalid.

The solution is to use the 1904 date system. Select Tools, Options. In the Options dialog box, click the Calculation tab and check the 1904 date system option to change the starting date to January 2, 1904. Your negative times will now be displayed correctly.

If you use the 1904 date system, be careful when linking to date cells in other workbooks. If the linked workbook uses the 1900 date system, the dates retrieved by the links will be incorrect.

Yes, and Excel offers several ways to do so. Assume that cell A1 contains a date value. The formula below uses the WEEKDAY function, which returns an integer between 1 and 7 (1 for Sunday, 2 for Monday, and so on).

=WEEKDAY(A1)

If you'd prefer to see words rather than integers, modify the formula as follows:

=CHOOSE(WEEKDAY(A1), "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")

Another approach is to change the number format for the date cell. Activate the cell that contains your date, and then choose Format, Cells. In the Format Cells dialog box, click the Number tab. Choose Custom from the Category list, and type a custom number format string into the box labeled Type. The trick here is to use dddd as part of the format string. For example, a format string of dddd mmmm d, yyyy will display the date and the day of the week, like this: Thursday November 23, 2000.

Excel stores dates as serial numbers. The number 1 represents January 1, 1900, the number 2 represents January 2, 1900, and so on. Formatting these numbers using a date format causes them to appear as actual dates. Therefore, if you have dates stored in two cells, you can simply create a formula that subtracts one from the other to get the number of intervening days. You'll want to make sure that the formula cell is formatted as a number, not a date.

You might also find the DATEDIF function useful. This function, which was not documented prior to Excel 2000, returns the difference between two dates, expressed in years, months, or days. You might use the DATEDIF function to calculate how many months the payment on an invoice is overdue or to determine a person's age when you know their birth date.

Excel's DATEDIF function takes three arguments. Its syntax is:

=DATEDIF(start_date,end_date,units)

In the syntax, start_date is a date or reference to a date, end_date is a date or reference to a date, and units is a one- or two-digit string (in double quotes) specifying the units for the difference between the two dates. Acceptable values for the units argument are shown below.

* y returns the number of full years in the period.

* m returns the number of full months in the period.

* d returns the number of full days in the period.

* md returns the number of full days in excess of the last full month.

* ym returns the number of full months in excess of the last full year.

* yd returns the number of full days in excess of the last full year.

For example, assume cells A1 and B1 contain dates. The formula below returns the number of full years between the dates (useful for calculating a person's age):

=DATEDIF(A1,B1,"y")

The formula below calculates the number of full months between the two dates:

=DATEDIF(A1,B1,"m")

I've never known Excel to be wrong about identifying links, so there's an excellent chance your workbook does contain one or more links--but they are probably not formula links.

* If you have a chart in your workbook, click each data series in the chart and examine the Series formula in the formula bar. If the formula refers to another workbook, you've identified the link. To eliminate it, move the chart's data into the current workbook and recreate your chart.

* If your workbook contains any dialog sheets, select each object in each dialog box and examine the formula bar. If any object contains a reference to another workbook, edit or delete that reference.

If these two approaches don't solve your problem, follow these steps:

1. Select Edit, Links. (In some cases, this command is not available. If you can't select it, skip to step 4.) The Links dialog box will appear.

2. Click the Change Source button and change the link to the active file.

3. Select Insert, Name, Define. Scroll down the list in the Define Name dialog box and examine the "Refers to" box. Delete names that refer to another workbook or that contain an erroneous reference (such as #REF!). This is the most common cause of "phantom links."

4. Save your workbook. When you reopen it, Excel won't ask you to update links.

First, let's clarify the question. We're hunting for a formula that, given the range 100, 99, 98, 100, 98, 100, 98, would return 3. This type of counting requires an array formula. The formula below, for example, counts the number of distinct entries in the range A1:D100.

=SUM(1/COUNTIF(A1:D100, A1:D100))

When you enter this formula, you must press Ctrl-Shift-Enter. Pressing only Enter will give you the wrong result. Excel will place brackets around the formula to remind you that you've created an array formula.

The preceding formula works fine in many cases, but it will return an error if the range contains any blank cells. The formula below (also an array formula, so input it with Ctrl-Shift-Enter) is more complex, but it will handle a range that contains a blank cell.

=SUM(IF(COUNTIF(A1:D100,A1:D100)=0, "", 1/COUNTIF(A1:D100,A1:D100)))

step 1:

Every cell has two key properties: locked and hidden. A locked cell can't be changed, and the contents of a hidden cell don't appear in the formula bar when the cell is selected. By default, every cell is locked and not hidden. But it's important to remember that these attributes have no effect unless the worksheet itself is protected. First, to change the attributes, select the appropriate cell or range and then choose Format, Cells. In the Format Cells dialog box, click the Protection tab and select Locked or Hidden (or both). Unlock cells that accept user input, and lock formula and other cells that should stay unchanged (such as titles). To prevent others from seeing your formulas, lock and hide the formula cells: The results of the formulas will be visible, but the formulas will not.

step 2:

Now, to protect the worksheet, choose Tools, Protection, Protect Sheet to bring up the Protect Sheet dialog box. Make sure the Contents box is checked. You can enter a password to prevent others from unprotecting the sheet. Locked cells in a protected sheet cannot be edited, and other worksheet changes are disabled. For example, no one can insert rows or columns, change column width, or create embedded charts.