Following on from last week’s post on creating some fake data we will expand on the functionality with a few setting changes.

The image below is where were up to in the first post. The example file can be downloaded via the button at the bottom of the post.

As well as names you can create fake addresses – see image below.

You may notice that these are US addresses.

If you want a list of fake Australian addresses, you can tweak the initialise cell and add the command below.

fake = Faker('en_AU')

The fake data will then be based on Australia – see image below.

Note these aren’t real addresses they are totally made up.

Wrap text

If you use wrap text on the address column it will split to a first line and last line – see image below.

Stop the data changing

As you may have noticed, each change to a Python cell changes the names/addresses. At some point you will want to keep the fake data and stop it changing. To do that you can add an extra line to the initialisation cell A1.

Faker.seed(42)

Entering an integer between the parentheses stops the data changing. Changing or removing the integer will change the results. See image below. These names/addresses won’t change.

Spill range

These results have created spill ranges. We can treat these just like normal Excel spill ranges.

To extract the first name from column A, we can use the following formula.

=TEXTBEFORE(A4#,” “)

See image below.

Python Editor

You may have noticed the Python Editor on the right of screen – image below.

The colour coding in the Python Editor can help you write code. It also shows error messages – see below.

We needed to use Faker (the Python package) so it needs to be use a capital F. Python is case sensitive.

This has been a brief introduction to Python in Excel. Hopefully it gives you a taste of what Python can offer Excel users.

You can download the file at the button below.