{"id":1463,"date":"2020-12-12T18:19:01","date_gmt":"2020-12-12T18:19:01","guid":{"rendered":"https:\/\/www.pyxll.com\/blog\/?p=1463"},"modified":"2026-02-20T10:02:25","modified_gmt":"2026-02-20T10:02:25","slug":"python-jupyter-notebooks-in-excel","status":"publish","type":"post","link":"https:\/\/www.pyxll.com\/blog\/python-jupyter-notebooks-in-excel\/","title":{"rendered":"Python Jupyter Notebooks in Excel"},"content":{"rendered":"\n<p>It used to be an &#8220;either\/or&#8221; choice between Excel and <a rel=\"noreferrer noopener\" href=\"https:\/\/jupyter.org\/\" target=\"_blank\">Python Jupyter Notebooks<\/a>. With the introduction of the <a rel=\"noreferrer noopener\" href=\"https:\/\/pypi.org\/project\/pyxll-jupyter\/\" target=\"_blank\">PyXLL-Jupyter<\/a> package now you can use both together, side by side.<\/p>\n\n\n\n<p>In this post I&#8217;ll show you how to set up Jupyter Notebooks running <em>inside <\/em>Excel. Share data between the two and even call Python functions written in your Jupyter notebook from your Excel workbook!<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<span class=\"embed-youtube\" style=\"text-align:center; display: block;\"><iframe loading=\"lazy\" class=\"youtube-player\" width=\"640\" height=\"360\" src=\"https:\/\/www.youtube.com\/embed\/Od-1cEac9U4?version=3&#038;rel=1&#038;showsearch=0&#038;showinfo=1&#038;iv_load_policy=1&#038;fs=1&#038;hl=en-US&#038;autohide=2&#038;wmode=transparent\" allowfullscreen=\"true\" style=\"border:0;\" sandbox=\"allow-scripts allow-same-origin allow-popups allow-presentation allow-popups-to-escape-sandbox\"><\/iframe><\/span>\n<\/div><\/figure>\n\n\n\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_2 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-69ed492f3de1b\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-69ed492f3de1b\"  aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.pyxll.com\/blog\/python-jupyter-notebooks-in-excel\/#Getting_Started\" >Getting Started<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.pyxll.com\/blog\/python-jupyter-notebooks-in-excel\/#How_is_this_useful\" >How is this useful?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/www.pyxll.com\/blog\/python-jupyter-notebooks-in-excel\/#Getting_data_from_Excel_into_Python\" >Getting data from Excel into Python<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/www.pyxll.com\/blog\/python-jupyter-notebooks-in-excel\/#Moving_data_in_Python_back_to_Excel\" >Moving data in Python back to Excel<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/www.pyxll.com\/blog\/python-jupyter-notebooks-in-excel\/#Use_Python_plots_matplotlibplotly_etc_in_Excel\" >Use Python plots (matplotlib\/plotly etc) in Excel<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/www.pyxll.com\/blog\/python-jupyter-notebooks-in-excel\/#Call_Python_functions_from_Excel\" >Call Python functions from Excel<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/www.pyxll.com\/blog\/python-jupyter-notebooks-in-excel\/#Script_Excel_with_Python_instead_of_VBA\" >Script Excel with Python instead of VBA<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/www.pyxll.com\/blog\/python-jupyter-notebooks-in-excel\/#Summary\" >Summary<\/a><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\" id=\"h-getting-started\"><span class=\"ez-toc-section\" id=\"Getting_Started\"><\/span>Getting Started<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>First off, to run Python code in Excel you need <a href=\"https:\/\/www.pyxll.com\" target=\"_blank\" rel=\"noreferrer noopener\">the PyXLL add-in<\/a>. The PyXLL add-in is what lets us integrate Python into Excel and use Python instead of VBA. To install the PyXLL Excel add-in pip install pyxll and then use the PyXLL command line tool to install the Excel add-in:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\n&gt; pip install pyxll\n&gt; pyxll install\n<\/pre><\/div>\n\n\n<p>If you&#8217;re new to PyXLL then take a look at the <a rel=\"noreferrer noopener\" href=\"https:\/\/www.pyxll.com\/docs\/userguide\/installation\/firsttime.html\" target=\"_blank\">online documentation for first time users<\/a> to help get you started.<\/p>\n\n\n\n<p>Once you have the PyXLL Excel add-in installed the next step is to install the pyxll-jupyter package. This package provides the glue between PyXLL and Jupyter so that we can use our Jupyter notebooks inside of Excel.<\/p>\n\n\n\n<p>The pyxll-jupyter package is installed using pip:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\n&gt; pip install pyxll-jupyter\n<\/pre><\/div>\n\n\n<p>Once both the PyXLL Excel add-in and the PyXLL-Jupyter package are installed start Excel and you will see a new &#8220;Jupyter&#8221; button in the PyXLL tab.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-2.png\"><img decoding=\"async\" width=\"981\" height=\"482\" data-attachment-id=\"1472\" data-permalink=\"https:\/\/www.pyxll.com\/blog\/python-jupyter-notebooks-in-excel\/image-2-3\/\" data-orig-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-2.png\" data-orig-size=\"981,482\" data-comments-opened=\"0\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"image-2\" data-image-description=\"\" data-image-caption=\"\" data-large-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-2.png\" src=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-2.png\" alt=\"Button to open Jupyter in Excel\" class=\"wp-image-1472\" srcset=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-2.png 981w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-2-300x147.png 300w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-2-768x377.png 768w\" sizes=\"(max-width: 981px) 100vw, 981px\" \/><\/a><\/figure>\n\n\n\n<p>Clicking this button opens the Jupyter notebook in a side panel in your Excel workbook. This panel is part of the Excel interface and can be un-docked or docked in a different location by dragging it.<\/p>\n\n\n\n<p>In the Jupyter panel you can select an existing notebook or create a new one. To create a new notebook select the &#8220;New&#8221; button followed by &#8220;Python 3&#8221;.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-3.png\"><img decoding=\"async\" width=\"1024\" height=\"659\" data-attachment-id=\"1474\" data-permalink=\"https:\/\/www.pyxll.com\/blog\/python-jupyter-notebooks-in-excel\/image-3-2\/\" data-orig-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-3.png\" data-orig-size=\"1588,1022\" data-comments-opened=\"0\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"image-3\" data-image-description=\"\" data-image-caption=\"\" data-large-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-3-1024x659.png\" src=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-3-1024x659.png\" alt=\"\" class=\"wp-image-1474\" srcset=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-3-1024x659.png 1024w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-3-300x193.png 300w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-3-768x494.png 768w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-3-1536x989.png 1536w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-3.png 1588w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-4.png\"><img decoding=\"async\" width=\"1024\" height=\"874\" data-attachment-id=\"1476\" data-permalink=\"https:\/\/www.pyxll.com\/blog\/python-jupyter-notebooks-in-excel\/image-4-2\/\" data-orig-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-4.png\" data-orig-size=\"1597,1363\" data-comments-opened=\"0\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"image-4\" data-image-description=\"\" data-image-caption=\"\" data-large-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-4-1024x874.png\" src=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-4-1024x874.png\" alt=\"\" class=\"wp-image-1476\" srcset=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-4-1024x874.png 1024w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-4-300x256.png 300w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-4-768x655.png 768w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-4-1536x1311.png 1536w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-4.png 1597w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><\/figure>\n\n\n\n<p>Starting with version 0.2.0 of the pyxll-jupyter package you can open the Jupyter notebook in a browser as well as in an Excel task pane. If you are working across multiple screens this can make things easier! To upgrade to the latest version run <code>pip install --upgrade pyxll-jupyter<\/code>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-how-is-this-useful\"><span class=\"ez-toc-section\" id=\"How_is_this_useful\"><\/span>How is this useful?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Now you have a complete Jupyter notebook running inside of Excel! But what is this good for? How is this better than running a notebook outside of Excel?<\/p>\n\n\n\n<p>Well, now you can use Excel for working with your data <em>and<\/em> use Python to work on the <em>same data<\/em>. Use Excel as an interactive playground for organizing and visualizing your data, seamlessly switching to Python for more sophisticated tools.<\/p>\n\n\n\n<p>Use a Jupyter notebook as a scratch-pad for trying out Python code. Write Excel functions entirely in Python in a Jupyter notebook and test them out in real-time. Once you&#8217;ve developed a useful re-usable function add it to your PyXLL Python project. That way you can use the same function every time you use Excel.<\/p>\n\n\n\n<p>In the rest of this post I&#8217;ll show you how to:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Share data between Excel and Python using your Jupyter notebook<\/li>\n\n\n\n<li>Write Excel worksheet functions (UDFs) in your notebook<\/li>\n\n\n\n<li>Script Excel with Python instead of VBA<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-getting-data-from-excel-into-python\"><span class=\"ez-toc-section\" id=\"Getting_data_from_Excel_into_Python\"><\/span>Getting data from Excel into Python<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Because PyXLL runs Python in the same process as Excel, accessing Excel data in Python and calling between Python and Excel is fast.<\/p>\n\n\n\n<p>To make things as easy as possible, the pyxll-jupyter package comes with some IPython &#8220;magic&#8221; functions for you to use in your Jupyter notebooks.<\/p>\n\n\n\n<p><strong>%xl_get<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-6.png\"><img decoding=\"async\" width=\"1024\" height=\"876\" data-attachment-id=\"1484\" data-permalink=\"https:\/\/www.pyxll.com\/blog\/python-jupyter-notebooks-in-excel\/image-6-3\/\" data-orig-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-6.png\" data-orig-size=\"1588,1358\" data-comments-opened=\"0\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"image-6\" data-image-description=\"\" data-image-caption=\"\" data-large-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-6-1024x876.png\" src=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-6-1024x876.png\" alt=\"\" class=\"wp-image-1484\" srcset=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-6-1024x876.png 1024w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-6-300x257.png 300w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-6-768x657.png 768w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-6-1536x1314.png 1536w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-6.png 1588w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><\/figure>\n\n\n\n<p>Use the magic function &#8220;%xl_get&#8221; to get the current Excel selection in Python. Have a table of data in Excel? Select the top left corner (or the whole range) and type &#8220;%xl_get&#8221; in your Jupyter notebook and voila! the Excel table is now a pandas DataFrame.<\/p>\n\n\n\n<p class=\"has-background\" style=\"background-color:#ffe9ab\">The %xl_get magic function requires the &#8220;pywin32&#8221; package. You can install this by running:<br><br><strong>pip install &#8220;pywin32>=301&#8221;<\/strong><br><br>If you get a <em>&#8220;DLL Load Failed&#8221;<\/em> error then it may be caused by a bug in version 300 of the pywin32 package. Use the above command to install a later version that includes the fix for this.<\/p>\n\n\n\n<p>The %xl_get magic function as several options:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>-c<\/code> or <code>--cell<\/code>. Pass the address of the cell(s) to get the value of, eg <code>%xl_get --cell A1:D5<\/code>.<\/li>\n\n\n\n<li><code>-t<\/code> or <code>--type<\/code>. Specify a data type to use when getting the value, eg <code>%xl_get --type numpy_array<\/code>.<\/li>\n\n\n\n<li><code>-x<\/code> or <code>--no-auto-resize<\/code>. Only get the data for the selected or given range. Don&#8217;t expand to include the surrounding range of data.<\/li>\n<\/ul>\n\n\n\n<p>PyXLL has other ways of interacting with Excel to read data into Python. The &#8220;%xl_get&#8221; magic function is just a shortcut to make things easier! As the Jupyter notebook is running in Excel, all other methods (eg using the <a rel=\"noreferrer noopener\" href=\"https:\/\/www.pyxll.com\/docs\/api\/classes.html#xlcell\" target=\"_blank\">XLCell<\/a> class, <a rel=\"noreferrer noopener\" href=\"https:\/\/www.pyxll.com\/docs\/userguide\/vba.html\" target=\"_blank\">Excel&#8217;s COM API<\/a> or even <a rel=\"noreferrer noopener\" href=\"https:\/\/www.pyxll.com\/docs\/api\/utils.html#xl-app\" target=\"_blank\">xlwings<\/a>) are still available.<\/p>\n\n\n\n<p><strong>TIP<\/strong>: You can assign a variable to the result of a magic function! For example, try &#8220;df = %xl_get&#8221;.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-moving-data-in-python-back-to-excel\"><span class=\"ez-toc-section\" id=\"Moving_data_in_Python_back_to_Excel\"><\/span>Moving data in Python back to Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Transfering data the other way around, from Python to Excel, works just as well. Whether you&#8217;ve used Python to load a dataset and want to transfer it to your Excel workbook, or if you&#8217;ve manipulated a data set from Excel and want the results back in Excel, copying data to Excel from Python is easy.<\/p>\n\n\n\n<p><strong>%xl_set<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-7.png\"><img decoding=\"async\" width=\"1024\" height=\"872\" data-attachment-id=\"1486\" data-permalink=\"https:\/\/www.pyxll.com\/blog\/python-jupyter-notebooks-in-excel\/image-7-3\/\" data-orig-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-7.png\" data-orig-size=\"1589,1353\" data-comments-opened=\"0\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"image-7\" data-image-description=\"\" data-image-caption=\"\" data-large-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-7-1024x872.png\" src=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-7-1024x872.png\" alt=\"\" class=\"wp-image-1486\" srcset=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-7-1024x872.png 1024w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-7-300x255.png 300w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-7-768x654.png 768w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-7-1536x1308.png 1536w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-7.png 1589w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><\/figure>\n\n\n\n<p>The magic function &#8220;%xl_set&#8221; takes a Python object and writes it to Excel. Have a dataframe &#8220;df&#8221; that you want in Excel? No problem, just use &#8220;%xl_set df&#8221; and it will be written to the current selection in Excel.<\/p>\n\n\n\n<p class=\"has-background\" style=\"background-color:#ffe9ab\">The %xl_set magic function requires the &#8220;pywin32&#8221; package. You can install this by running:<br><br><strong>pip install &#8220;pywin32==228&#8221;<\/strong><br><br>If you get a <em>&#8220;DLL Load Failed&#8221;<\/em> error then it may be caused by a bug in version 300 of the pywin32 package. Use the above command to install the previous version (228). This bug has been fixed in pywin32, but not yet released.<\/p>\n\n\n\n<p>Like %xl_get, %xl_set has a range of options to control its behaviour. You can even use <a href=\"https:\/\/www.pyxll.com\/docs\/api\/formatting.html\" target=\"_blank\" rel=\"noreferrer noopener\">PyXLL&#8217;s cell formatting feature<\/a> to automatically apply formatting at the same time as writing results to Excel.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>-c<\/code> or <code>--cell<\/code>. Address of cell(s) to write the value to, eg <code>%xl_set VALUE --cell A1<\/code>.<\/li>\n\n\n\n<li><code>-t<\/code> or <code>--type<\/code>. Datatype specifier to use when writing the value to Excel, eg <code>%xl_set VALUE --type dataframe&lt;index=False><\/code>.<\/li>\n\n\n\n<li><code>-f<\/code> or <code>--formatter<\/code>. PyXLL cell formatter object, eg <code>%xl_set VALUE --formatter DataFrameFormatter()<\/code>. See <a href=\"https:\/\/www.pyxll.com\/docs\/api\/formatting.html\" target=\"_blank\" rel=\"noreferrer noopener\">cell formatting<\/a>.<\/li>\n\n\n\n<li><code>-x<\/code> or <code>--no-auto-resize<\/code>. Don&#8217;t auto-resize the range to fit the data. Only write values to the current selection or specified range.<\/li>\n<\/ul>\n\n\n\n<p>As with %xl_get, %xl_set is meerly a shortcut and all the other ways of writing back to Excel that you might have used with PyXLL will still work in a Jupyter notebook.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-use-python-plots-matplotlib-plotly-etc-in-excel\"><span class=\"ez-toc-section\" id=\"Use_Python_plots_matplotlibplotly_etc_in_Excel\"><\/span>Use Python plots (matplotlib\/plotly etc) in Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>One of the great things about working with data is the powerful plotting packages available. Being able to plot a pandas DataFrame with a simple &#8220;df.plot()&#8221; is awesome!<\/p>\n\n\n\n<p>PyXLL has <a href=\"https:\/\/www.pyxll.com\/docs\/userguide\/plotting\/index.html\" target=\"_blank\" rel=\"noreferrer noopener\">integration with all of the main plotting libraries<\/a> so you can make the most of these in Excel too. This includes <a href=\"https:\/\/matplotlib.org\/\" target=\"_blank\" rel=\"noreferrer noopener\">matplotlib<\/a> (used by <a rel=\"noreferrer noopener\" href=\"https:\/\/pandas.pydata.org\/\" target=\"_blank\">pandas<\/a>), <a rel=\"noreferrer noopener\" href=\"https:\/\/plotly.com\/python\/\" target=\"_blank\">plotly<\/a>, <a rel=\"noreferrer noopener\" href=\"https:\/\/bokeh.org\/\" target=\"_blank\">bokeh<\/a> and <a rel=\"noreferrer noopener\" href=\"https:\/\/altair-viz.github.io\/\" target=\"_blank\">altair<\/a>.<\/p>\n\n\n\n<p><strong>%xl_plot<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-8.png\"><img decoding=\"async\" width=\"1024\" height=\"871\" data-attachment-id=\"1491\" data-permalink=\"https:\/\/www.pyxll.com\/blog\/python-jupyter-notebooks-in-excel\/image-8-3\/\" data-orig-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-8.png\" data-orig-size=\"1591,1353\" data-comments-opened=\"0\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"image-8\" data-image-description=\"\" data-image-caption=\"\" data-large-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-8-1024x871.png\" src=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-8-1024x871.png\" alt=\"\" class=\"wp-image-1491\" srcset=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-8-1024x871.png 1024w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-8-300x255.png 300w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-8-768x653.png 768w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-8-1536x1306.png 1536w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-8.png 1591w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><\/figure>\n\n\n\n<p>Use &#8220;%xl_plot&#8221; to draw any Python chart in Excel. Pass it any figure object from one of the supported plotting libraries, or use the last pyplot figure. Using pandas plot works great, eg. <code>%xl_plot df.plot(kind='scatter')<\/code>.<\/p>\n\n\n\n<p>The %xl_plot magic function has some options to control how it works:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>-n<\/code> or <code>--name<\/code>. Name of the picture object in Excel. If using a name of a picture that already exists that picture will be replaced.<\/li>\n\n\n\n<li><code>-c<\/code> or <code>--cell<\/code>. Cell address to use as the location for the new picture. If the picture already exists this has no effect.<\/li>\n\n\n\n<li><code>-w<\/code> or <code>--width<\/code>. Width of the picture in Excel in points. This has no effect if updating an existing picture.<\/li>\n\n\n\n<li><code>-h<\/code> or <code>--height<\/code>. Height of the picture in Excel in points. This has no effect if updating an existing picture.<\/li>\n<\/ul>\n\n\n\n<p>%xl_plot is a shortcut for the <a href=\"https:\/\/www.pyxll.com\/docs\/api\/plotting.html#pyxll.plot\" target=\"_blank\" rel=\"noreferrer noopener\">pyxll.plot<\/a> function.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-call-python-functions-from-excel\"><span class=\"ez-toc-section\" id=\"Call_Python_functions_from_Excel\"><\/span>Call Python functions from Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Rather than constantly moving data between Excel and Jupyter and then running some Python code, you can call Python function directly from the Excel workbook!<\/p>\n\n\n\n<p>One of the main use-cases for PyXLL is writing custom Excel worksheet functions (or &#8220;UDFs&#8221;) in Python. This is used for building models in Excel built from Python functions, which can of course themselves use other Python toolkits like pandas and scipy.<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<span class=\"embed-youtube\" style=\"text-align:center; display: block;\"><iframe loading=\"lazy\" class=\"youtube-player\" width=\"640\" height=\"360\" src=\"https:\/\/www.youtube.com\/embed\/vyDd_3r93cU?version=3&#038;rel=1&#038;showsearch=0&#038;showinfo=1&#038;iv_load_policy=1&#038;fs=1&#038;hl=en-US&#038;autohide=2&#038;wmode=transparent\" allowfullscreen=\"true\" style=\"border:0;\" sandbox=\"allow-scripts allow-same-origin allow-popups allow-presentation allow-popups-to-escape-sandbox\"><\/iframe><\/span>\n<\/div><\/figure>\n\n\n\n<p>You can write Excel worksheet functions in your Jupyter notebook too. This is a really great way of trying out ideas without leaving Excel to go to a Python IDE.<\/p>\n\n\n\n<p>Try it out for yourself. Write a simple function and then add the &#8220;pyxll.xl_func&#8221; decorator to your function:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: python; title: ; notranslate\" title=\"\">\nfrom pyxll import xl_func\n\n@xl_func\ndef test_func(a, b, c):\n    # This function can be called from Excel!\n    return (a * b) + c\n<\/pre><\/div>\n\n\n<p>After you&#8217;ve entered the code and run the cell in Jupyter that Python function will immediately be available to call from the Excel workbook.<\/p>\n\n\n\n<p>It&#8217;s not just for simple functions. <a rel=\"noreferrer noopener\" href=\"https:\/\/www.pyxll.com\/docs\/userguide\/pandas.html\" target=\"_blank\">You can pass whole ranges of data to your function as pandas DataFrames<\/a> and return any Python type, including numpy arrays and DataFrames! You can tell PyXLL what types to expect by giving the @xl_func decorator <a rel=\"noreferrer noopener\" href=\"https:\/\/www.pyxll.com\/docs\/userguide\/udfs\/argtypes.html\" target=\"_blank\">a signature string<\/a>.<\/p>\n\n\n\n<p>For example, try out the following:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: python; title: ; notranslate\" title=\"\">\nfrom pyxll import xl_func\n\n# The &quot;signature&quot; tells PyXLL how to convert the arguments\n# and returned value.\n@xl_func(&quot;dataframe df: dataframe&lt;index=True&gt;&quot;, auto_resize=True)\ndef df_describe(df):\n    # &#039;df&#039; is a pandas DataFrame built from the range passed\n    # to this function.\n    desc = df.describe()\n\n    # &#039;desc&#039; is a new DataFrame, which PyXLL will convert to\n    # a range of values when returning to Excel.\n    return desc\n<\/pre><\/div>\n\n\n<p>Now you can write complex Python functions to do data transformation and analysis, but orchestrate how those functions are called or sequenced in Excel. Changing the inputs results in the functions be called and the calculated outputs update in real-time, just as you would expect!<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-script-excel-with-python-instead-of-vba\"><span class=\"ez-toc-section\" id=\"Script_Excel_with_Python_instead_of_VBA\"><\/span>Script Excel with Python instead of VBA<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Did you know that everything you can do in VBA can also be done in Python? The Excel Object Model is what you use when writing VBA, but the same API is available in Python as well.<\/p>\n\n\n\n<p>See <a href=\"https:\/\/www.pyxll.com\/docs\/userguide\/vba.html\" target=\"_blank\" rel=\"noreferrer noopener\">Python as a VBA Replacement<\/a> from the PyXLL documentation for details of how this is possible.<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<span class=\"embed-youtube\" style=\"text-align:center; display: block;\"><iframe loading=\"lazy\" class=\"youtube-player\" width=\"640\" height=\"360\" src=\"https:\/\/www.youtube.com\/embed\/ubqsRcCUcB4?version=3&#038;rel=1&#038;showsearch=0&#038;showinfo=1&#038;iv_load_policy=1&#038;fs=1&#038;hl=en-US&#038;autohide=2&#038;wmode=transparent\" allowfullscreen=\"true\" style=\"border:0;\" sandbox=\"allow-scripts allow-same-origin allow-popups allow-presentation allow-popups-to-escape-sandbox\"><\/iframe><\/span>\n<\/div><\/figure>\n\n\n\n<p>In a Jupyter notebook running in Excel the entire Excel Object Model is available and so you can script Excel in exactly the same way you might do in the Excel VBA Editor.<\/p>\n\n\n\n<p>Because PyXLL runs Python <em>inside<\/em> the Excel process there is no performance penalty for calling into Excel from Python. It is also possible to call into Excel from an external Python process, but this is generally much slower. Having a Jupyter notebook running in Excel makes everything more convenient too!<\/p>\n\n\n\n<p>Use PyXLL&#8217;s <a rel=\"noreferrer noopener\" href=\"https:\/\/www.pyxll.com\/docs\/api\/utils.html#pyxll.xl_app\" target=\"_blank\">xl_app<\/a> function to get the &#8220;Excel.Application&#8221; object, which is equivalent to the Application object in VBA. Try something like getting the current selection and changing the cell interior color. A great way of figuring out how to do something with the Excel Object Model is to record a VBA Macro and then translate that macro into Python! The PyXLL docs page <a rel=\"noreferrer noopener\" href=\"https:\/\/www.pyxll.com\/docs\/userguide\/vba.html\" target=\"_blank\">Python as a VBA Replacement<\/a> has some tips on how to do that.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-9.png\"><img decoding=\"async\" width=\"1024\" height=\"871\" data-attachment-id=\"1499\" data-permalink=\"https:\/\/www.pyxll.com\/blog\/python-jupyter-notebooks-in-excel\/image-9-2\/\" data-orig-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-9.png\" data-orig-size=\"1585,1348\" data-comments-opened=\"0\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"image-9\" data-image-description=\"\" data-image-caption=\"\" data-large-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-9-1024x871.png\" src=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-9-1024x871.png\" alt=\"\" class=\"wp-image-1499\" srcset=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-9-1024x871.png 1024w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-9-300x255.png 300w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-9-768x653.png 768w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-9-1536x1306.png 1536w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-9.png 1585w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-summary\"><span class=\"ez-toc-section\" id=\"Summary\"><\/span>Summary<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Python makes a poweful alternative to VBA. With PyXLL you can write fully featured Excel add-ins entirely in Python. Excel is an amazing tool for interactive computation. Adding Python and Jupyter takes Excel to a whole new level.<\/p>\n\n\n\n<p>Code written in Jupyter notebooks can easily be refactored into <a rel=\"noreferrer noopener\" href=\"https:\/\/www.pyxll.com\/docs\/userguide\/distribution.html#setuptools-entry-points\" target=\"_blank\">standalone Python packages<\/a> to create Excel tool-kits to power intutitive workbooks and dashboards. Any Excel user will be able to take advantage of Python tools written using PyXLL without needing any knowledge of Python.<\/p>\n\n\n\n<p>If you want to find out more then<a rel=\"noreferrer noopener\" href=\"https:\/\/www.pyxll.com\/download.html\" data-type=\"URL\" data-id=\"https:\/\/www.pyxll.com\/download.html\" target=\"_blank\"> download and try PyXLL for yourself<\/a> or <a rel=\"noreferrer noopener\" href=\"https:\/\/www.pyxll.com\/contact.html\" target=\"_blank\">contact us<\/a>. We&#8217;ll be happy to answer any questions you might have and find out how we can help!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>It used to be an &#8220;either\/or&#8221; choice between Excel and Python Jupyter Notebooks. With the introduction of the PyXLL-Jupyter package now you can use both together, side by side.<\/p>\n","protected":false},"author":1,"featured_media":1508,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[649,12,2,504,645,272],"tags":[653,652,650,7,651],"class_list":["post-1463","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-jupyter","category-python","category-pyxll","category-user-interface","category-vba","category-visualization","tag-altair","tag-bokeh","tag-jupyter","tag-matplotlib","tag-plotly"],"acf":[],"jetpack_featured_media_url":"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/image-11.png","jetpack_shortlink":"https:\/\/wp.me\/p7l3LP-nB","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/posts\/1463","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/comments?post=1463"}],"version-history":[{"count":1,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/posts\/1463\/revisions"}],"predecessor-version":[{"id":2068,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/posts\/1463\/revisions\/2068"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/media\/1508"}],"wp:attachment":[{"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/media?parent=1463"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/categories?post=1463"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/tags?post=1463"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}