When you first install Excel, it asks you for your name so it can personalize the registration for your program. It also writes your name into the user area. This information is used in various places by Excel, such as in the workbook properties area. If you need to change your name, follow these steps:

1. Choose Options from the Tools menu. You will see the Options dialog box.
2. Make sure the General tab is selected. 
3. Change the information in the User Name box, as desired.
4. Click on OK.

One frustration common to many Excel users is dealing with the way Excel handles cutting and copying to the Clipboard. It is particularly frustrating that you can select a range and copy it by pressing Ctrl+C, and then the copy “disappears” if you take any other action besides pasting right away. It would be great if copying and cutting worked just like it does in other applications, where the information is maintained in the Clipboard until it is explicitly replaced by something else.

Unfortunately, there is no way to do this in Excel. There is a bit of a work-around, however, that Microsoft added in Excel 2000. Beginning in this version they implemented the Clipboard toolbar, as is available in other Office applications. Display the toolbar, and the last couple of things you cut or copied are visible in a palette of Clipboard entries.

If you need even more functionality, however, you may want to look into a Clipboard extender. This classification of program goes way beyond what the Clipboard can do, allowing you to save and name cuts and copies and to use those named items days, weeks, or months later–even after a reboot. There are many different Clipboard extenders available on the market. One example is SmartBoard 2000, a shareware Clipboard extender from Oakley Data Systems. You can find out more information at http://www.sb2k.com/.

Different people enter data in different ways. When you enter information into a cell, Excel tries to figure out what type of information you are entering. If you enter a number such as 08242008, Excel assumes you are entering a numeric value, and treats it accordingly. What if the number you enter actually is a date without any separators? Can Excel understand what you are entering? 

Unfortunately Excel cannot. Why? Because you have given it no indication that this should be a date. (Excel keys on separators, not on numeric values.) If you or your data entry people cannot change their input habits so that separators are also entered, then you will need some sort of a workaround to convert the entered information to an actual date value.

Your first thought might be that you could use a custom format to display the information. Consider the following custom format:

##"/"##"/"####

This format would display the number 08152008 as 8/15/2008. The only problem is that it only changes the display of the number—if you want to use the date as a real Excel date, you cannot do so because you haven’t converted the value into something that Excel recognizes as a date.

If the values input were very consistent in their format, and if they were input as text instead of as numeric values, then there is an easy way you can convert them to dates. By very consistent, I mean that the input always used two digits for the month, two for the day, and four for the year. In addition, the cells containing the values must be formatted as text. In this instance, you can follow these steps:

1. Select the column of dates.
2. Make sure there is nothing in the column just to the right of the dates.
3. Choose Text to Columns from the Data menu. (In Excel 2007, select Text to Columns from the Data tab of the ribbon.) Excel displays the Convert Text to Columns Wizard. 
4. Select the Fixed Width option, then click on Next.
5. Click on Next again.
6. In the Column Data Format area, choose Date.
7. Select the range in the Destination box, then in the worksheet click the cell just to the right of the first value you selected in step 1.
8. Click on Finish.

If all went well, Excel should have parsed the text values as dates, and you can delete the original column. If this did not work, then it means that either the original values were not formatted as text, or eight digits were not used to enter all the dates.

Another possible solution is to use a formula to convert the entered values into actual dates. The following is one such formula:

=DATE(RIGHT(A1,4),LEFT(A1,IF(LEN(A1) = 8,2,1)),LEFT(RIGHT(A1,6),2))

This formula assumes that the entered date (the one without separators) is in cell A1. The formula will work with either seven- or eight-digit dates.

If you prefer custom functions, you can create one in VBA that examines the data being passed, converts it to a date/time format, and then returns the result. The following function is very versatile in this regard; it will work with both American and European date formats:

Function DateTime(dblDateTime As Double, _
  Optional bAmerican As Boolean = True)
'Converts Date and time "number" without
'delimiters into an excel serialdate (which
'can then be formatted with the Excel
'date/time formats)

'If optional argument is TRUE (or missing),
'function assumes value is of form:
'   [m]mddyyyy.hhmm  (leading "0" not required)

'If optional argument is FALSE, function
'assumes value is of form:
'   [d]dmmyyyy.hhmm  (leading "0" not required)

    Dim iYear As Integer
    Dim iMonth As Integer
    Dim iDay As Integer
    Dim iHour As Integer
    Dim iMin As Integer

    iYear = Int((dblDateTime / 10000 - _
      Int(dblDateTime / 10000)) * 10000)
    iDay = Int((dblDateTime / 1000000 - _
      Int(dblDateTime / 1000000)) * 100)
    iMonth = Int((dblDateTime / 1000000))
    iHour = Int((dblDateTime - Int(dblDateTime)) * 100)
    iMin = Int((dblDateTime * 100 - _
      Int(dblDateTime * 100)) * 100 + 0.5)

    If bAmerican Then
        DateTime = DateSerial(iYear, iMonth, iDay)
    Else
        DateTime = DateSerial(iYear, iDay, iMonth)
    End If

    DateTime = DateTime + (iHour + iMin / 60) / 24
End Function

This macro function assumes that the data being passed to it is a numeric value, as would normally happen when inputting dates without separators. (Refer back to the logic on this at the beginning of the tip.)

As you can tell, there are a number of workarounds, but none of them is as simple as just entering separators when entering the dates. If training yourself or your data input people to do this is difficult, you might consider setting up some data validation rules for the input cells. These rules can check to make sure that you are entering information using a specific format (such as a date with separators), and stop you if you are not.

Dates are a special case in Excel. If you enter information that can be translated as a date (by any stretch of the imagination), then Excel treats it as a date. It converts your data into a serial number that is internally used to represent dates and times. For instance, any of the following entries will be translated to a date by Excel:

• 6/11
• 6-11/06
• 11 Jun
• June 11, 2006

If you enter the first example, Excel will convert it to a date and display that date as best it can determine. If you leave off the year in your entry, Excel assumes you mean the current year. Regardless of how you type a date, it is converted to a special serial number by Excel, and stored internally in that format. How you see the date on your screen is a consequence of how the cell is formatted. Even though Excel stores dates in a standard format internally, they can be displayed using any number of different formats.

Excel is quite configurable in how information appears on your screen. At some time you may want to change the appearance of the worksheet tabs at the bottom of your workbook. Unfortunately, Excel allows very little customization of the way worksheet tabs are presented.

If you want to change the font used in a worksheet tab, you need to change the fonts used by Windows (not Excel) to displays information. You can right-click on your desktop, choose Properties, and then click on the Appearance tab. Changes you make on this tab will affect all programs running on your system, not just Excel.

If you want to change the color used to displays a worksheet tab, you are completely out of luck if you are using Excel 97 or Excel 2000. If you are using Excel 2002 or later, you can changing the formatting for a tab by following these steps:

1. Right-click on the worksheet tab you want to change. Excel displays a Context menu.
2. Choose Tab Color from the Context menu. Excel displays the Format Tab Color dialog box. (In Excel 2007 you don’t see a dialog box; you see a palette of available colors.)
3. Select a color to use for the tab.
4. Click on OK.

It is interesting that if you set the colors for worksheet tabs and then open the worksheet in Excel 2000, the tabs don’t show in color—they show in gray. Excel 2000 won’t get rid of the colors, however. They will still be there when you later open the workbook in a version of Excel that supports the colors.

Some people like to put two spaces at the end of a sentence; other people prefer one. In fact, at times it seems to be the new hot-topic-of-the-day among writers.

The purpose of this tip is not to say which spacing preference is correct. The purpose is to signify how you can adjust your typing for the spacing you prefer. The Help Wanted question that resulted in this tip indicated that WordPerfect would enforce either two-space or single-space end-of-sentence spacing based on a configuration setting. Word provides no such setting, but there are things you can do.

First of all, if you are using Word 97 or a later version of Word you can configure the grammar checker to flag any end-of-sentence spacing that doesn’t match your preferences. Simply follow these steps:

1. Choose Options from the Tools menu. Word displays the Options dialog box.
2. Make sure the Spelling & Grammar tab is displayed.
3. Click on the Settings button. Word displays the Grammar Settings dialog box. 
4. Use the Spaces Between Sentences drop-down list to indicate how many spaces you prefer between your sentences.
5. Click on OK to close the Grammar Settings dialog box.
6. Click on OK to close the Options dialog box.

Now the grammar checker will flag any sentences that don’t conform to your preference with a green wavy underline. When you then right-click on the flagging, you can choose to correct the spacing for that occurrence.

If you want to do mass replacements of your end-of-sentence spacing, the best thing to do is to use the search and replace features of Word. The techniques to do this have been covered in other issues. For those of you who want to automate the process of using search and replace, the following VBA macro is quite handy. It will replace any number of spaces at the end of a sentence with two spaces:

Sub TwoSpaces()
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = "([.\?\!]) {1,}"
        .Replacement.Text = "\1  "
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchWildcards = True
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
End Sub

If you want to alter the macro so that it ensures all your sentences have only a single space at the end, you can simply change the .Replacement.Text line so there is only one space in the replacement string. Another thing to note about this macro is that it corrects any sentences ending in a period, question mark, or exclamation point; it will not catch and correct any sentences that end in a quote mark.

Finally, just in case you are curious, you cannot use AutoCorrect to change end-of-sentence spacing. Why? Because AutoCorrect uses the space character as a signal to trigger checking what was just typed. Thus, AutoCorrect entries cannot utilize spaces.

If after inserting breaks you discover that you need to move the break to a new location, you can follow these steps:

1. Make sure you are in Normal view. (Draft view in Word 2007.) This process is much easier if you are working in either Normal view or Draft view.
2. Select the break, just as you would select any other text.
3. Press Ctrl+X. The break is cut from your document.
4. Position the insertion point where you want to insert the break.
5. Press Ctrl+V. The break is inserted in your document.