<< A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

–A–

–B–

–C–

Carriage returns

1. In "Replace With:" text box type Alt+0010 (On the keyboard pressing Alt key type 0010 on the keypad). You won't see nothing but the replace will be done.

2.  Sub ClearCRLF()

For Each Cell In Selection
    temp = Cell.Text
For i = 1 To Len(temp)
    a = Mid(temp, i, 1)
        If a = vbCrLf Or a = vbCr Or a = vbLf Then
            temp = Left(temp, i - 1) & Right(temp, Len(temp) - i - 1)
        End If
    Next
    Cell.Value = temp
Next
Sub

cell reference —

which cells are referenced by this cell: ctl [

which cells reference this cell: ctl ]

column heading as letters instead of numbers - Click Tools, then options, then select the General tab.  Unselect the R1C1 Reference Style

crosstab query - see pivot table

–D–

date format, custom - let's say you want 2013-03-28.  Go into format cells, Select "Custom" instead of "Date".  Type yyy-mm-dd

dates off by 4 years when pasted from one spreadsheet to another — see 1900 Date System vs. the 1904 Date System (Tools, Options, Calculations tab, workbook options section, “1904 Date System” checked for one spreadsheet, not checked for another)

–E–

Elapsed days - if you have 2 cells with dates in them, simply subtract cell1 from cell2, format as general

emails, converting email addresses to mailto: hyperlinks>

Public Sub ConvertToMailLinks()
     Const sPATTERN As String = "?*@?*.?*"
     Dim vResult As Variant
     Dim rCell As Range
     Dim rCheck As Range

     If TypeName(Selection) = "Range" Then _
         If Selection.Count > 1 Then _
             Set rCheck = Selection
     If rCheck Is Nothing Then
         vResult = MsgBox( _
                     Prompt:="Search the entire worksheet?", _
                     Buttons:=vbYesNo, _
                     Title:="Convert to MailTo: Links")
     If vResult = vbYes Then
             Set rCheck = ActiveSheet.Cells
         Else
             Set rCheck = ActiveCell
         End If
     End If
     On Error Resume Next
     Set rCheck = rCheck.SpecialCells(xlCellTypeConstants, xlTextValues)
     On Error GoTo 0
     If Not rCheck Is Nothing Then
         For Each rCell In rCheck
             If rCell.Value Like sPATTERN Then _
                 ActiveSheet.Hyperlinks.Add _
                             anchor:=rCell, _
                             Address:="mailto:" & rCell.Value, _
                             TextToDisplay:=rCell.Value
         Next rCell
     End If
End Sub

external references, find - see links, find external references

–F–

"File" menu gone from menu bar — see menu bar does not have the "File" menu

–G–

–H–

–I–

–J–

–K–

–L–

leading zeros — see Zeros, leading: make sure they stay there

links, find external references in a workbook

There is no automatic way to find external references (also called links) that are used in a destination workbook. However, external references use brackets [ ] to enclose the source workbook name when the workbook is closed, for example =SUM([Budget.xls]Annual!C10:C25), so you can use those characters to locate external references.

To find all external references in a destination workbook, you need to look in cells, names, objects (such as a text box or shape), chart titles, and chart data series.

Find external references that are used in cells

  1. Close all source workbooks, and then open the destination workbook.
  2. On the Home tab, in the Editing group, click Find & Select, and then click Find.

The Find and Replace dialog box is displayed.

  1. Click Options.
  2. In the Find what box, enter [.
  3. In the Within box, click Workbook.
  4. In the Look in box, click Formulas.
  5. Click Find All.
  6. In the list box that is displayed, look in the Formula column for formulas that contain [.

 Tip   Click the Formula column header to sort the column, and to group all of the external references together.

  1. To select the cell with an external reference, select the row in the list box.

Links are also found in names, objects (such as a text box or shape), chart titles, and chart data series.  More info.

–M–

macros — in the “C:\Program Files\Microsoft Office\OFFICE11\XLSTART” folder the macros reside in “personal.xls”

Hide the file in Excel (Window menu, Hide command).

You keep personal.xls hidden unless you want to add macros to it. Whenever you start Excel, personal.xls opens automatically and runs in the background. If you don't hide the file, Excel opens it and allows you to add data to it.

If everything looks OK but the dang thing still doesn’t work from your shortcut key, look under Help|About MS Excel, you'll see a button called:  "Disabled Items...".  Check under there to see if it's marked not to open.  You can enable it there.

menu bar does not have the "File" menu.  Edit, View etc. are all there but not File.  How to get it back?

Tools>Customize. On ToolbarTab, make sure WorksheetMenuBar is highlighted and click Reset. "File". Options should be restored to the toolbar.

months elapsed - =12*YEARFRAC(A1,A2)

–N–

number — convert to text

if you have the “new” version (Excel 2007, 2010) which ends in “.xlsx”, then you can use one of the following 2 methods:

using the Format > Cells > Number > Text command.

Highlight the column of numbers and use the Data > Text to Columns command. In Page 1 of the wizard, choose the appropriate file type (this will probably be Delimited). In Page 2, remove any column dividers that may have shown up to keep the data in one column. In Page 3, click Text under Column data format to indicate that this column is text.

But if you have the “old” version (Excel 2003 and before) which ends in “.xls”, then you must use this somewhat more cumbersome process:

  1. Use the TEXT function

=TEXT(A2,"0")

–O–

Outlook, can't open Excel files from within (an error has occurred sending the command to the program)

In Excel, File → Options → Advanced tab on the pop-up → General area towards the bottom → uncheck "Ignore other applications that use Dynamic Data Exchange (DDE)" checkbox

–P–

parse data you’ve pasted — data, text to columns

Password, change

  1. Open the workbook.
  2. On the File menu, click Save As.
  3. On the Tools menu of the window that pops up, click General Options.
  4. In the Password to open or Password to modify box, double-click the asterisks.
  5. Type the new password (password: A way to restrict access to a workbook, worksheet, or part of a worksheet. Excel passwords can be up to 255 letters, numbers, spaces, and symbols. You must type uppercase and lowercase letters correctly when you set and enter passwords.), and then click OK.
  6. When prompted, retype the new password, and then click OK.
  7. Click Save.
  8. If prompted, click Yes to replace the existing workbook.

pasting dates from one spreadsheet to another results in years being off by 4 years— see dates off by 4 years

pivot table - I usually start by highlighting the cells I want to tabulate - with field headings included up top.  Then, from the menu up top, insert tab, pivot table (extreme left).  Click where you want put the table (I often keep it in the same data sheet).  Then click anywhere in the little template that'll magically appear embedded in your sheet starting with the cell you picked.  A "PivotTable Field List" should pop up to the right as a dockable window.  You should have fields available at top of that window.  Drag whichever field you want to be your row or column labels at the bottom.  Then drag the field that holds your detail values into the Values pane.  Default is count which is usually worthless for me.  Maybe you want sum instead.  Click the "Sum of [field name]" pick list in that pane, you'll see a "Value Field Settings..." toward the bottom of that pick list.  Click that and a pop-up with ability to change from "count" to "sum".

prepend leading zeros to Zip codes — see Zeros, leading: make sure they stay there

–Q–

–R–

reference to/by cell —

which cells are referenced by this cell: ctl [

which cells reference this cell: ctl ]

references, find external - see links, find external references

–S–

split alternative to text to columns

split column at first space delimiter only (from here)

If you are only going to split into 2 cells then you could put this formula in the first cell to return the first part:
  =LEFT(A1,FIND(" ",A1,1)-1)
and this formula in the next cell to return the right-hand remainder
  =RIGHT(A1,LEN(A1)-FIND(" ",A1,1))

Extract the last substring from a cell - This works, even when there's stuff in the middle (like middle names) (from here):

=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,LEN(A1))

If you want everything but the last of several space-separated entries (from here)

  =LEFT(A1,FIND("?",SUBSTITUTE(A1," ","?",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))
which uses a trick of using the "?" as a temporary substitute. Actually, this leaves a trailing space. So use this instead:
  =LEFT(E4,FIND("?",SUBSTITUTE(E4," ","?",LEN(E4)-LEN(SUBSTITUTE(E4," ",""))))-1)
to rid of the final space (which you won't be able to see but which can mess you up if you try to pile these results on top of each other.)

If you want everything but the last two of several space-separated entries:

=LEFT(A1,FIND("?",SUBSTITUTE(A1," ","?",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1)))

–T–

text to columns alternative — see split alternative to text to columns

Truncate - =left(a1,1) will return just the first initial, for instance

–U–

Unhide top row

  1. Press F5. Excel displays the Go To dialog box. (Click here to see a related figure.)
  2. In the Reference field at the bottom of the dialog box, enter the number of the row range that you want to unhide. For instance, if you want to unhide rows 2 through 3, enter 2:3. Likewise, if you want to unhide row 1, enter 1:1.
  3. Click on OK. The rows you specified are now selected, even though you cannot see it on the screen.
  4. Choose Row from the Format menu, then choose Unhide.  In 2007, Home/Format/Hide & Unhide/Unhide Rows

–V–

–W–

Write to a spreadsheet programmatically using VBA

Sub Main()
    Dim oExcel As Excel.Application
    Dim oWB As Excel.Workbook
    Dim oWS As Excel.Worksheet
 
    oExcel = New Excel.Application
    oExcel.Visible = True ' <-- *** Optional ***
 
    Dim oRng1 As Excel.Range
    Dim oRng2 As Excel.Range
 
    oWB = oExcel.Workbooks.Add
    oWS = oWB.Worksheets("Sheet1")
 
    oRng1 = oWS.Range("A1")
    oRng2 = oWS.Range("B2:E5")
 
    oRng1.Value = "Hello World"
    Call oRng1.Copy(Destination:=oRng2)
 
    oWB.SaveAs("C:\Hello World.xls") ' <-- Results saved here.
 
Cleanup:
    oWS = Nothing
    If Not oWB Is Nothing Then oWB.Close()
    oWB = Nothing
    oExcel.Quit()
    oExcel = Nothing
End Sub

–X–

–Y–

–Z–

Zeros, leading: make sure they stay there — Somehow you get in the situation where you’ve stored text info with leading zeros — like 5-digit zip codes — as numbers so you lose all your leading zeros.  Now, of course, you can make them look like they’re there by Format Cells / number / and then either Special/Zip Code or Custom / 00000.  But if you want to do a mail merge with this data that won’t help a bit.  No, assuming you have a whole column (J) of 500 rows of these wrongly stored zip codes, one way to do this is to create two adjacent columns.  In the first column, ="0" &J1.  Drag that formula down to all the cells in that column.  This is pretty simplistic, assuming you have only 4-digit zip codes.  If you have a mixture of 4 & 5 digit zip codes, then you have to do conditional formatting: =IF(LEN(J1) = 4,"0" &J1, J1).  Or, programmatically for rows 1 through 500:

For i = 1 To 500
    If Len(Range("J" & i)) = 4 Then
        Range("J" & i) = "'0" & Range("J" & i)
    End If
Next i

Then highlight the whole 2nd column, copy, and then “paste special” into the 3rd column, taking only values.

Zip codes lose leading zeros — see Zeros, leading: make sure they stay there

–No's–

4 years difference between dates pasted from one spreadsheet to — see dates off by 4 years