Excel Now Has Python - And It's Awesome!

Two weeks back Microsoft announced the official Python integration for Excel

This combination of Python and Excel will allow data analysts to leverage Python for data analysis within the familiar Excel environment.

Here is everything you need to know to get started!

Firstly you can access the beta by joining the Insider Program

But in a month once it is released to the public, you can use this as a guide

Accessing Python

To access Python, click a cell, go to Formulas in the ribbon, and select Insert Python.

You can also use the =PY function in a cell to access Python.

You will know it is ready to go when both the cell and formula bars display the green PY icon.

Diagnostics Panel

The classic "Hello world" script causes "None" to display instead of your intended string.

The "Diagnostics" panel is where your message will be shown.

Python with Cells and Formula Bar

To interact between Excel and Python, the custom function xl() is used which can accept Excel objects such as tables, names, ranges, and more!

For example, if you wanted to reference cell B2, you would use xl(“B2“), and to reference the range B2 to C5, you would use xl(“B2:C5“).

Additionally, the formula bar can be used to edit code in a coding-like style, with the shortcut Ctrl + Shift + U to expand the formula bar.

Python and Excel Output types

You can decide for yourself whether or not you want an output to be a Python object or an Excel value.

If you are planning to reuse an output for a future Python calculation, you should return it as a Python object, while if you plan to run Excel analytics on the output, you should return it as an Excel value.

Calculations

Python code in a cell will run top to bottom as usual, but Python in the Excel worksheet will calculate row by row, this means it will start with Row 1, then move to Row 2, Row 3, etc.

Errors

Python will return errors such as #PYTHON!, #BUSY!, and #CONNECT! to Python cells.

Regardless of where you are with data science, learning how Python works with Excel will give you an unfair advantage and stay ahead of everyone else.

Remember to use it Judiciously

Unlike conventional coding, your aim isn't development.

But leveraging Python's might to enhance Excel's data prowess.

Avoid writing longer code listings.

Always return a value as a result of that cell.

This short video will let you know all the libraries you can access

Tweet of the week

This week’s tweet of the week was one of the questions asked in my Data Science Interview

It’s a commonly asked question as well!

If you have read till here, I have something special for you.

This week I launched my first e-book “Data Science Resume Checklist + Template (Bonus)” and close to 400 people have got it and found it useful!

It’s going to be absolutely FREE for just 24 hrs after which the price is going to $50. Dont miss the last chance to get it!

Data Science Resume Checklist + Template (Bonus)