Ask Amy

Question:  I have a worksheet I use as a template with a cell that brings in the current date every time it is opened. However, once I fill out the template and save it as separate worksheet, I don’t want the date to change. Is there a simple way to keep that date from changing?


Answer:  Here is a tip that will let you quickly change a function (or formula) to its value.  For example, cell A1 contains the function =TODAY(). This function returns the current date every time the worksheet is opened. For example if the worksheet is opened on Jan 3, 2011, it displays the date 1/3/2011 in that cell. If that worksheet is saved and then reopened on Jan 10, that cell displays 1/10/2011. If you do not want the cell to display a new date the next time it is opened follow these steps.

1. Select the cell that contains the function (or  formula).
2. Press the F2 function key.
3. Press the F9 function key.
4. Press the ENTER key.

The =TODAY function is replaced by the date and the cell will not change again.

These same steps can be used to replace a formula with the results of that formula.

For example, you have a cell that contains the formula =sum(A1:10) but displays the number 987 which is the result of the formula. You want to remove the formula from that cell and replace it with the result of the formula. Follow the steps above. The formula is replaced with the result of the formula.

Excel Tip: These keystroke combinations work in all versions of Excel.

• To enter the current date into a cell, press the CTRL+; (semicolon) keystroke combination. This date does not update.
• To enter the current time into a cell, press the CTRL+: (colon) keystroke combination. This time does not update.