I know that it's possible to import Excel data into Mathematica using the Import command, as we can see in this post. But sometimes I need just to do some fast calculation and it would be very nice if I could achieve it using clipboard, using something as simple as Ctrl+C/Ctrl+V. How could I perform that?

  • $\begingroup$ This is also quite convenient: stackoverflow.com/a/8816197/695132 On OS X I had to paste to a plain text editor first to strip formatting, but if my memory serves me well, it works well (without needing such steps) on Windows $\endgroup$
    – Szabolcs
    Nov 15, 2012 at 16:08
  • $\begingroup$ Nice link @Szabolcs. It's nice because makes the excel macro unnecessary, so I can remove this part. But you know how can we make this "clean step" using Mathematica? $\endgroup$
    – Murta
    Nov 15, 2012 at 20:23
  • $\begingroup$ My Mathematica is on Mac and my Excel on Windows (in the same machine, using Parallels). $\endgroup$
    – Murta
    Nov 15, 2012 at 20:27
  • $\begingroup$ Sorry I just saw that in your answer. I wouldn't be familiar with that combination, but could you try it in Windows only? I'm curious if it works there without the cleaning. I think it should. The Windows clipboard can store data in multiple formats simultaneously. $\endgroup$
    – Szabolcs
    Nov 15, 2012 at 20:28
  • $\begingroup$ Yes! In both case they work (Mathematica Windows and Mac), but you have to make the "plain text" intermediate step. I think that if you solve it for Windows (remove this step using Mathematica), you solve it for mac too. $\endgroup$
    – Murta
    Nov 15, 2012 at 20:32

3 Answers 3


You can easily implement it in two steps.

  1. You create an Excel macro in your personal.xlsb that you can use to execute some keybord shortcut to copy your selected data. I use CTRL+SHIFT+C.
  2. Second you can create a Mathematica function to import this clipboard data (optional, but very usefull)

More information on how to handle your personal.xlsb here

How to implement it!

Below we have the Excel macro to copy to clipboard the selected Excel range using Mathematica format:

Private Sub Excel_To_Mathematica()

    'Program by:  Dana DeLouis  (Microsoft Excel MVP)
    'Modified by: Rodrigo Murta (Mathematica Addicted)
    'Changes by Murta:
    '   Usable for "," as decimal separator
    '   Eliminate bug for big and small Numbers
    '   Elminate Transpose line number limitations

      Dim ClipBoard As New DataObject

      Dim Nr As Long    '# of Rows
      Dim Nc As Long    '# of Columns
      Dim r As Long     ' Row Pointer
      Dim C As Long     ' Column Pointer
      Dim T()           'Temporary Storage
      Dim Tc()          'Temporary Storage
      Dim v As Variant  'Holds the data from Worksheet

      Dim s As String
      Dim ButtonClicked As Long
      Const DQ As String = """" 'Double Quotes: 4 of them!
      Dim transp                'Temp Array for Transpose Case

      Application.ScreenUpdating = False

    '// A little error checking first...
      If TypeName(selection) <> "Range" Then
          MsgBox "Select a Range first"
          Exit Sub
      End If

      If selection.Areas.Count > 1 Then
          MsgBox "Select only 1 area.  Macro will Exit"
          Exit Sub
      End If

    '// Load data into an Array
      If selection.Cells.Count = 1 Then
          ReDim v(1 To 1, 1 To 1)
          v(1, 1) = selection
          v = selection
      End If

    '// Get number of Rows & Columns
      Nr = UBound(v, 1)
      Nc = UBound(v, 2)

      If Nc = 1 And Nr > 1 Then
          ButtonClicked = MsgBox("Transform Vectors in Columns?", vbYesNo)
      End If

        '// Put quotes around text
          For r = 1 To Nr
              For C = 1 To Nc
                  If IsNumeric(v(r, C)) Then
                    v(r, C) = Replace(Replace(Format(v(r, C), "@"), ",", "."), "@", "")
                    v(r, C) = Replace(v(r, C), "E", "*10^")
                    v(r, C) = DQ & v(r, C) & DQ
                  End If
              Next C
          Next r

          If ButtonClicked = vbYes Then

              ReDim tempArray(1 To Nr)
              For i = 1 To Nr
                tempArray(i) = v(i, 1)

              v = tempArray

              s = "{" & Join(v, ",") & "}"
              ReDim T(1 To Nr)
              ReDim Tc(1 To Nc)
              For r = 1 To Nr
                    For C = 1 To Nc
                        Tc(C) = v(r, C)

                    T(r) = "{" & Join(Tc(), ",") & "}"
              s = Join(T, ",")
              If Nr > 1 Then s = "{" & s & "}"
          End If

      ClipBoard.SetText s
      Application.ScreenUpdating = True
      'Application.StatusBar = "data copied"
      'Application.StatusBar = False

    End Sub

    Private Function TransposeDim(v As Variant) As Variant
    ' Custom Function to Transpose a 1-based array (v)

        Dim x As Long, y As Long, Xupper As Long, Yupper As Long
        Dim tempArray()

        Xupper = UBound(v, 2)
        Yupper = UBound(v, 1)

        ReDim tempArray(1 To Xupper, 1 To Yupper)

        For x = 1 To Xupper
            For y = 1 To Yupper
                tempArray(x, y) = v(y, x)
            Next y
        Next x

        TransposeDim = tempArray

    End Function

If you want, you can create the Excel shortcut using this command in your ThisWorkbook object of you personal.xlsb file:

Private Sub Workbook_Open()

    Application.OnKey "^+c", "Excel_To_Mathematica" 'ctrl + shift + c

End Sub

Using the above macros, you are ok to past your data into Mathematica using CTRL+V, but some times, you have a big list, and you would like to make some variable to receive this information directly. So I use this Mathematica function my start up pack.

  getClipboardData[]:=  NotebookGet[ClipboardNotebook[]][[1, 1, 1]] // ToExpression

So, instead of CTRL+V my Excel data, I can write:

data = getClipboardData[]

I tested it 1M Excel lines without problems (that is the excel limit in 2010). I use Mathematica on Mac and Excel on Windows (using Parallels).


The code still works with Excel 2013

  • $\begingroup$ Great answer, but the link for "More information on how to handle your personal.xlsb" is broken. $\endgroup$ Aug 8, 2014 at 15:31
  • $\begingroup$ I think the VBA script is not compatible with Excel 2007. The line "Dim ClipBoard As New DataObject" generates this error: "User-defined type not defined". Any ideas? $\endgroup$ Aug 8, 2014 at 16:11
  • $\begingroup$ You can try this. Let me know if it helps. $\endgroup$
    – Murta
    Aug 9, 2014 at 1:14
  • $\begingroup$ Yes it works now. Thank you. I had to add the "Microsoft Forms 2.0 Object Library" but it was not in the list so I had to click "Browse" and select "C:\Windows\System32\FM20.DLL" on my Win7 machine. $\endgroup$ Aug 11, 2014 at 7:38
  • 1
    $\begingroup$ @William I have just done this again on a different Windows 97 PC, and the C:\Windows\System32\FM20.DL file was already there. Maybe the solution is different for Excel 16. I am stuck with Excel 2007. $\endgroup$ Mar 22, 2016 at 19:29

A very simple way to import tables of integers is like this:
add a few columns in Excel containing the separators "{", "," and "}," in between your columns of integers:

enter image description here

then paste in your workbook, and add a leading "{" and a trailing "}" and ... done.
(mind the superfluous comma at the end). Works in version 10.


@Murta's method has served me well for several years now, but this seems to be broken under x64 Office installations in Windows 8+. For me (on Windows 10 x64 and Office 2016 x64), the routine places random unicode characters in the clipboard.

I've scoured the web and found a solution. Place the following code in the same Module as @Murta's routines.

Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As LongPtr)
Private Declare PtrSafe Function GlobalUnlock Lib "kernel32" (ByVal hMem As LongLong) As Long
Private Declare PtrSafe Function GlobalLock Lib "kernel32" (ByVal hMem As LongLong) As LongPtr
Private Declare PtrSafe Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal dwBytes As LongLong) As LongPtr
Private Declare PtrSafe Function GlobalSize Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr
Private Declare PtrSafe Function CloseClipboard Lib "user32" () As Long
Private Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongLong) As Long
Private Declare PtrSafe Function EmptyClipboard Lib "user32" () As Long
Private Declare PtrSafe Function lstrcpy Lib "kernel32" Alias "lstrcpyA" (ByVal lpString1 As Any, ByVal lpString2 As String) As LongPtr
Private Declare PtrSafe Function GetClipboardData Lib "user32" (ByVal wFormat As Long) As LongPtr
Private Declare PtrSafe Function SetClipboardData Lib "user32" (ByVal wFormat As Long, ByVal hMem As LongLong) As LongLong
Private Declare PtrSafe Function EnumClipboardFormats Lib "user32" (ByVal wFormat As Long) As Long
Private Declare PtrSafe Function IsClipboardFormatAvailable Lib "user32" (ByVal wFormat As Long) As Long

Public Enum eCBFormat
    CF_TEXT = 1
    CF_BITMAP = 2
    CF_SYLK = 4
    CF_DIF = 5
    CF_TIFF = 6
    CF_OEMTEXT = 7
    CF_DIB = 8
    CF_PALETTE = 9
    CF_PENDATA = 10
    CF_RIFF = 11
    CF_WAVE = 12
    CF_HDROP = 15
    CF_LOCALE = 16
    CF_MAX = 17
    CF_DSPTEXT = &H81
End Enum
Const GHND = &H42

Public Function ClipBoard_HasFormat(ByVal phWnd As LongLong, ByVal peCBFormat As eCBFormat) As Boolean
    Dim lRet    As Long

  If OpenClipboard(phWnd) > 0 Then
    lRet = EnumClipboardFormats(0)
    If lRet <> 0 Then
        If lRet = peCBFormat Then
          ClipBoard_HasFormat = True
          Exit Do
        End If
        lRet = EnumClipboardFormats(lRet)
      Loop While lRet <> 0
    End If
    'Problem: Cannot open clipboard
  End If
End Function

Public Function ClipBoard_GetTextData(ByVal phWnd As LongLong) As String
  Dim hData       As LongPtr
  Dim lByteLen    As LongPtr
  Dim lPointer    As LongPtr
  Dim lSize       As LongLong
  Dim lRet        As Long
  Dim abData()    As Byte
  Dim sText       As String

  lRet = OpenClipboard(phWnd)
  If lRet > 0 Then
    hData = GetClipboardData(eCBFormat.CF_TEXT)
    If hData <> 0 Then
      lByteLen = GlobalSize(hData)
      lSize = GlobalSize(hData)
      lPointer = GlobalLock(hData)
      If lSize > 0 Then
        ReDim abData(0 To CLng(lSize) - CLng(1)) As Byte
        CopyMemory abData(0), ByVal lPointer, lSize
        GlobalUnlock hData
        sText = StrConv(abData, vbUnicode)
      End If
      'Problem: Cannot open clipboard
    End If
  End If

  ClipBoard_GetTextData = sText
End Function

Public Function ClipBoard_SetData(psData As String) As Boolean
    Dim hGlobalMemory   As LongLong
    Dim lpGlobalMemory  As LongPtr
    Dim hClipMemory     As LongLong
    Dim fOK             As Boolean

  fOK = True

  ' Allocate moveable global memory.
    hGlobalMemory = GlobalAlloc(GHND, LenB(psData) + 1)
  If hGlobalMemory = 0 Then
    Exit Function
  End If
  ' Lock the block to get a far pointer
  ' to this memory.
  lpGlobalMemory = GlobalLock(hGlobalMemory)
  ' Copy the string to this global memory.
  lpGlobalMemory = lstrcpy(lpGlobalMemory, psData)
  ' Unlock the memory.
  If GlobalUnlock(hGlobalMemory) <> 0 Then
    fOK = False
    GoTo OutOfHere2
  End If
  ' Open the Clipboard to copy data to.
  If OpenClipboard(0&) = 0 Then
    fOK = False
    Exit Function
  End If
  ' Clear the Clipboard.
  Call EmptyClipboard
  ' Copy the data to the Clipboard.
  hClipMemory = SetClipboardData(eCBFormat.CF_TEXT, hGlobalMemory)

   Call CloseClipboard
   ClipBoard_SetData = fOK
End Function

Then change Murta's function to include ClipBoard_SetData (s) instead of

ClipBoard.SetText s

References: 1, 2


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.