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)

debit – see also credit, if

=IF(A1<0, A1, "")

default location for Excel macros – see Personal Macro Workbook (sometimes called the Personal.xlsb file), macros

–E–

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

elapsed minutes - if you have 2 cells with times in them such that A1 has the earlier time and B1 has the later time, =(B2-B1)*24*60

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

floor function – a way to cut off the decimal portion of a number

=FLOOR(3.141596,1) returns 3

see also mod function for remainder when dividing integers, split months into years and months

focus, return to file from VBA

Application.SendKeys "%{F11}"

formatting, only copy formatting without copying content – see copy formatting only

–G–

–H–

–I–

if – see also credit, debit

=IF(A1>0, A1, "")

image, import data from

–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.

3. Click Options.
4. In the Find what box, enter [.
5. In the Within box, click Workbook.
6. In the Look in box, click Formulas.
7. Click Find All.
8. 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.

9. 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 – see Personal Macro Workbook (sometimes called the “Personal.xlsb” file), vba, vba, open in Visual Studio Code

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.

minutes between times – see elapsed minutes

mod function - Returns the remainder after a number (dividend) is divided by another number (divisor)

example: =MOD(10,3) returns 1

see also related/complementary FLOOR function, split months into years and months

months elapsed

=12*YEARFRAC(A1,A2)

=DATEDIF(start_date, end_date, "m")

–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

• Insert a temporary blank column B.
• In cell B2, enter this formula:

=TEXT(A2,"0")

• Fill the formula in B2 down to B3:B100.
• You need to change the formulas to values in order to have them become text. Highlight cells B2:B100.
• Use Ctrl+C to copy, then click Edit > Paste Special > Values > OK.
• The entries in column B will now be text versions of the numbers in column A.
• Copy column B back into column A.
• Delete the temporary column B.

–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 Info.
3. Select the Protect Workbook box and choose Encrypt with Password.
4. Enter a password in the Password box, and then select OK.
5. Confirm the password in the Reenter Password box, and then select OK.

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

PDF, convert to Excel

PDFtoExcel

Personal Macro Workbook (sometimes called the “personal.xls” file) where macros normally reside – see also macros, vba, vba, open in Visual Studio Code

Normally located at: C:\Users\\AppData\Roaming\Microsoft\Excel\XLSTART

Once you create this, it always comes up whenever you open Excel. That is, 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. This automatic opening feature can become annoying after a while, so you can hide by View → Hide

To unhide again, so you can go to View → Unhide. Look for a workbook named PERSONAL.XLSB or similar. If you find it, that’s your Personal Macro Workbook.

Press Alt + F11 to open the VBA editor. In the Project Explorer, you’ll see a project named VBAProject (PERSONAL.XLSB). Expand it to access the modules and code associated with your Personal Macro Workbook.

Once you have “personal.xls” unhidden, it will pop up alongside any file you open thereafter. To run code against the file you intended to open, begin with focus on that file (not the PERSONAL.XLSB) and open vba, move focus to the macro you want and run (F5 or click the green sideways arror)

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.

pick list - data tab → Data Tools group → Data Validation icon

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

remainder - see mod function, split months into years and months

–S–

split alternative to text to columns – - examples below assume text with multiple spaces and we're splitting on spaces but there are extra, extraneious spaces and we need to get stuff only at the end, etc.

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))

or an alternate (from here) that puts the last chunk of text in column C:

C1 =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))

and which has the added bonus of being able to get all the stuff up to that last space into column B:

B1 =LEFT(A1,LEN(A1)-LEN(C1)-1)

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)))

split months into years and months

–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 file

1. Open the Excel workbook containing the hidden file.
2. Click on the “View” tab in the ribbon.
3. Click on the “Unhide” option.
4. select file to unhide.

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–

vba – see Personal Macro Workbook (sometimes called the Personal.xlsb file), macros

Press Alt + F11 to open the VBA editor.

vba, open in Visual Studio Code – see Personal Macro Workbook (sometimes called the Personal.xlsb file), macros, vba

1. Export the VBA Code:

   • Open Excel and access the Personal Macro Workbook (PERSONAL.XLSB) using the VBA editor (Alt + F11).
   • In the Project Explorer, find the module or code you want to view.
   • Right-click on the module and choose Export File. Save it to a location on your computer.

2. Open in VS Code

   • Open VS Code.
   • Use the File → Open Folder option to open the folder where you saved the exported VBA file.

3. Install Extensions (Optional):

   • To enhance your VBA development experience, consider installing the “VBA” extension for VS Code.
   • Search for “VBA” in the Extensions view (Ctrl + Shift + X) and install the one provided by “Dieter Otter”. I couldn’t find one from Dieter, so I tried VBA Pro from Notis Data Analytics. It said to activate, Activated by onLanguage:vba event: 5ms

VBA, return focus to file that called – see focus, return to file from VBA

–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 2^nd column, copy, and then “paste special” into the 3^rd 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