Python in Excel is new. Since it is the year of the snake let’s slither into Python in Excel.

I have started to learn Python programming in the hope that I can implement even more solutions in Excel using Python script.

This is Part One and we will finish this project in Part Two.   

Random Data

In Excel there is no easy way to generate random data for testing or training. Python has a package called Faker that allows you to generate random data.

I thought this would be a good introduction to Python in Excel.

You need the subscription version of Excel to use Python in Excel. Check the Formulas ribbon tab to see if you have the Python section – see below.

If you do have it, (woohoo!) you can follow along.

If you don’t, you can read along to see what’s coming.

In cell A1 of a blank sheet type =py and press Tab. This creates a Python cell – see image below.

In cell A1 you can now type Python commands in the Formula Bar.

Where is Python?

Python is hosted in Azure (Microsoft’s cloud database) in the cloud and so it requires an internet connection to work.

The Python code is kept in the cells within the sheet. When you accept or update a Python cell it communicates with the cloud and runs the code in the cloud and returns to result to the Python cells.

Python cells can take longer than normal cells to calculate because of this cloud interaction.

Comments

The # symbol is used to precede any comments in the Python code. Comments are not Python code. Comments do nothing as far as the code is concerned. They describe the code and are useful for documentation.

Ctrl + Enter

You must press Ctrl + Enter to accept the final Python code. Most Python code is multi-line. Pressing Enter will insert a line break between lines of code in the cell.

In the Python cell type.

---

#generate fake data
from faker import Faker
fake = Faker()
"Initialise Faker"

---

Finish typing the code with Ctrl + Enter.

When typing the above code make sure you get the case (upper/lower) correct as Python is case sensitive.

Code explanation

from faker import Faker

This line of code imports the Faker package gives you access to its functionality.

fake = Faker()

This line captures Faker as the variable named fake. That means in all the other Python cells in this sheet we can use the variable fake to access the Faker package and functionality. Any variable created in cell A1 can be access throughout the sheet.

"Initialise Faker"

This line displays a message in cell A1. In doesn’t do anything, it is just a text description. Sometimes Python cells won’t display anything, or they may display a word. By ending the code with text in quotation marks you can describe what the cell does. Again, this helps documentation.

In cell A2 type =PY and press Tab. Type the following and press Ctrl + Enter.

---

#random name
fake.name()

---

Code explanation

fake.name()

This code displays a fake name. Note the name will change each time the Python cell is re-calculated.

Multiple Names

To display multiple names, we can use some Python looping code.

In cell A4 type the following and press Ctrl + Enter.

---

#multiple fake names
fake_names = [fake.name() for _ in range(20)]

---

This code creates a loop that creates 20 names. The square brackets define a list. The code inside the square brackets is a loop that creates 20 separate names.

This code won’t display the names yet – see image below. It only returns the word list.

To see all the names, you need to click the icon to the left of the PY cell in the Formula Bar.

Choose Excel Value and you will see the random names – see image below.

Again, these names will all change whenever a Python cell is calculated.

In the next post we will see how to stop the random names changing and how to create some other random data and then use the data in Excel.