Many packages exist for working with Python and Excel, but
xlwings may be the most powerful all-around due to its many reading and writing features. This versatility, however, comes at the cost of being relatively more difficult to set up.
For example, one of the most useful features of
xlwings is the ability to write Python user-defined functions (UDFs) right from Excel. To do so, however, requires enabling the
xlwings add-in. Here are the steps for PC:
Install Python & Excel
I suggest a desktop version of Excel for 365 and the Anaconda distribution of Python (Download for free here. Follow all suggested prompts.).
xlwings add-in for Excel
This can be done by launching Anaconda Prompt. Type
xlwings addin install into the command line, then hit Enter. If successful, you should see a message like the following (version numbers may vary):
Open Excel & confirm access
Once you’ve installed the add-in, open Excel. Do you see a new tab in the ribbon called xlwings? Success! You now have one more tool for making the most of Excel with Python.
If you do not see this tab on your ribbon, make sure you’ve fully restarted Excel. Check the
xlwings documentation for other potential blockers.
Making Excel fly
It’s already amazing how much Excel is capable. Add Python to the mix and you’ll be an unstoppable analyze. Be sure to check the blog for other posts on combining these tools.