# Make Excel Faster

## xlwings Tutorial: Make Excel Faster Using Python

![excel-vs-python](https://www.dataquest.io/wp-content/uploads/2019/02/xlwings-tutorial-python-excel-1040x520.jpg)

Excel is very, very pervasive in today’s businesses. At Dataquest, we generally recommend working with code [for quite a few reasons](https://www.dataquest.io/blog/9-reasons-excel-users-should-consider-learning-programming/), and many of our [data science courses](https://www.dataquest.io/data-science-courses) are aimed at teaching efficient coding for data analysis and data science. But no matter how strongly you prefer working with Python, at the end of the day there is sometimes a need to present your findings or share your data using Excel.

But that doesn’t mean can’t still enjoy some of the efficiencies of Python! In fact, using a library called `xlwings`, you can use Python to make working in Excel faster.

In this xlwings tutorial, we’ll walk through how to use Python in Excel to execute and use some common operations like deleting rows based on certain criteria, using Excel functions and formulas, autofilling, creating sheets, charts, etc. In order to follow this post you should be familiar with basic Python concepts (objects, methods, attributes, functions) and Python’s syntax and have an intermediate knowledge of Excel and [VBA](https://en.wikipedia.org/wiki/Visual_Basic_for_Applications).

We will be working with a data set that contains information regarding the draws of an European lottery called [EuroMillions](https://en.wikipedia.org/wiki/EuroMillions). This data set was downloaded from [this](http://lottery.merseyworld.com/cgi-bin/lottery?days=20\&Machine=Z\&Ballset=0\&order=0\&show=1\&year=0\&display=CSV) link and it contains all the EuroMillions draws up to, and including, the 20th of September. The data available at that link should be updated with the latest information up to whenever you’re reading this post, but in case it’s unavailable, [here’s a CSV file](https://www.dataquest.io/wp-content/uploads/2019/09/euromillions.csv) with the data from that link as of September 20.

Euromillions is a multinational lottery available in some European countries, specifically in Andorra, Austria, Belgium, France (including overseas regions and collectivities), Ireland, the Isle of Man, Liechtenstein, Luxembourg, Monaco, Portugal, Spain, Switzerland and the United Kingdom ([source](https://en.wikipedia.org/wiki/EuroMillions#Eligibility)).

As of this writing, the draw consists of five numbers from a pool of 50 numbers (numbered 1 through 50) and two numbers called `lucky stars` from a pool of 12 numbers. In order to win the jackpot, participants must correctly choose all drawn numbers and lucky stars. The largest jackpot ever won was €190 million. (Note, though, that our data set denominates winnings in Pounds, not Euros).

In this tutorial, we’ll use Python and xlwings with Excel to clean up a data set and then generate some graphs to visualize which numbers win the EuroMillions most frequently.

```python
import pandas as pd
import xlwings as xw
df = pd.read_csv('euromillions.csv')
df.sample(5)
```

|      | No. | Day | DD | MMM | YYYY | N1 | N2 | N3 | N4 | N5 | L1 | L2 | Jackpot  | Wins |
| ---- | --- | --- | -- | --- | ---- | -- | -- | -- | -- | -- | -- | -- | -------- | ---- |
| 627  | 514 | Fri | 24 | Aug | 2012 | 6  | 5  | 19 | 37 | 12 | 7  | 3  | 37709047 | 1    |
| 230  | 911 | Tue | 14 | Jun | 2016 | 13 | 50 | 42 | 39 | 34 | 11 | 9  | 11928000 | 0    |
| 998  | 143 | Fri | 3  | Nov | 2006 | 13 | 11 | 44 | 24 | 49 | 9  | 3  | 88344099 | 0    |
| 1071 | 70  | Fri | 10 | Jun | 2005 | 37 | 32 | 47 | 7  | 6  | 7  | 1  | 21734699 | 0    |
| 522  | 619 | Tue | 27 | Aug | 2013 | 7  | 40 | 38 | 43 | 30 | 6  | 2  | 12931500 | 0    |

The first column is the draw number, columns `N1-L2` are the drawn numbers and lucky stars (by the order they were drawn), the `Jackpot` column is the jackpot in Euros and the `Wins` column tell us how many bets hit the jackpot.

### Meet `xlwings`

`xlwings` is a Python library that makes some of the data analysis features of Python available in an Excel instance, including support for `numpy` arrays and `pandas` Series and DataFrames. Like any other Python library, it can be installed using common methods like `pip` or `conda`, but you can [access the documentation for `xlwings` here](https://docs.xlwings.org/en/stable/installation.html) if you need additional details.

Note that you’ll need to have a version of Microsoft Excel installed on the computer you’re using to do this xlwings tutorial.

**xlwings objects**

In `xlwings` there are four main object types which are, in decreasing hierarchical order: `App` (which represents an Excel instance), `Book`, `Sheet` and `Range`. In addition to these we’ll also be dealing with `Chart` and `Shape` objects. You can find useful information regarding these and other objects in the [official documentation](http://docs.xlwings.org/en/stable/api.html#object-model), but we’ll be looking at each of these objects one at a time.

Let’s start by creating a `Book` instance and naming it `wb` (workbook).

```python
wb = xw.Book() # wb = xw.Book(filename) would open an existing file
```

When you run that code, it should look something like this.

![01.-New-book-1](https://www.dataquest.io/wp-content/uploads/2019/09/gif3_1.gif)

Notice that when the code cell is run in the Jupyter Notebook, Excel is launched automatically.

By instantiating a `Book` object, an `App` object that belongs to our book object is created automatically. Here’s how we can check all opened Excel instances.

Note: we’re not going to include gifs for every step of this tutorial, because we don’t want this page to be a hassle to load for people with slow or limited internet connections. However, subsequent code-running steps should look similar to what we can see above: when we run a cell in Juypter, the Excel spreadsheet is updated in accordance with whatever code we’ve run.

```python
print(xw.apps)
```

```python
Apps([<Excel App 9536>])
```

The object `xw.apps` is an iterable. To check what workbooks belong to the unique instances in this iterable we can invoke the `books` method on it like so.

```python
print(xw.apps[0].books)
```

```python
Books([<Book [Book1]>])
```

As expected the only instance of this iterable is the workbook `wb`. We check this fact below.

```python
print(xw.apps[0].books[0] == wb)
```

```
True
```

Similarly, we can check what sheets belong to this workbook:

```python
print(wb.sheets)
```

```python
Sheets([<Sheet [Book1]Sheet1>])
```

We can also refer to sheets by their name:

```python
#creates a worksheet object assigns it to ws
ws = wb.sheets["Sheet1"]
#checks that wb.sheets[0] equals ws
print(ws == wb.sheets[0])
```

```
True
```

`Sheet` objects have a `name` attribute that works as expected. Let us change the name of our only sheet.

```python
ws.name = "EuroMillions"
```

We can move data from certain Python objects (e.g. lists and tuples) into Excel. Let’s move the data in our dataframe into the sheet *EuroMillions*. To do this, we’ll make use of `range` to create a range object that stores the data from our DataFrame in a range of cells in Excel, starting in this case with the cell A1:

```python
#ws.range("A1") is a Range object
ws.range("A1").value = df
```

Here’s how that will look:

![Python-excel-xlwings-make-excel-faster](https://www.dataquest.io/wp-content/uploads/2019/09/xlwings-python-excel-faster.jpg)

As we can see, the index column of `df` was also moved to Excel. Let’s clear the contents of this sheet and copy the data without the index.

```python
ws.clear_contents()
ws.range("A1").options(index=False).value = df
```

It will be useful to be able to tell where our table ends. More specifically, we’ll need the last row that has data in it. To this end, we can use the `end` method and the `row` attribute of `Range` objects.

The `row` method, not surprisingly, returns the `row` of the `Range` object.

The method `end` takes in as an argument a direction (`"up"` (or `1`), `"right"` (or `2`), `"left"` (or `3`, `"down"` (or `4`)) and returns another range object. It mimics the very common action in Excel CTRL+Shift+Arrow.

```
last_row = ws.range(1,1).end('down').row
print("The last row is {row}.".format(row=last_row))
print("The DataFrame df has {rows} rows.".format(rows=df.shape[0]))
```

```python
The last row is 1142.
The DataFrame df has 1141 rows.
```

It checks out!

**The API property**

Not every Excel functionality is available as a native `xlwings` feature. Sometimes we’ll have to find workarounds to do what we want. Fortunately `xlwings` makes this very easy for us. From the [Missing Features](http://docs.xlwings.org/en/stable/missing_features.html) section in the official documentation:

> Workaround: in essence, xlwings is just a smart wrapper around [pywin32](https://github.com/mhammond/pywin32) on Windows and [appscript](http://appscript.sourceforge.net/) on Mac. You can access the underlying objects by calling the api property. The underlying objects will offer you pretty much everything you can do with VBA, using the syntax of pywin32 (which pretty much feels like VBA) and appscript (which doesn’t feel like VBA). But apart from looking ugly, keep in mind that **it makes your code platform specific (!)**. [Excel Visual Basic for Applications](https://docs.microsoft.com/en-us/office/vba/api/overview/excel) is a rich source of explanations for the various existing Excel objects.

Sorting is one such functionality that is missing from `xlwings`. You may have noticed that the records are ordered from the most recent to the oldest draw. In the next few steps we’ll be reversing the order.

The object `ws.range("A2:N{row}".format(row=last_row))` is a `Range` object. Appending the `api` property to it yields a [VBA Range](https://docs.microsoft.com/en-us/office/vba/api/excel.range\(object\)) object which in turn gives access to its VBA features.

We’ll be using the [Sort](https://docs.microsoft.com/en-us/office/vba/api/excel.range.sort) property of this VBA object. In its simplest application, `Sort` takes in two arguments: the column by which we want to order the table (as a VBA Range object), and the order type (whether we want to sort it in ascending or descending order). The second argument’s parameter’s documentation can be seen [here](https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.xlsortorder?redirectedfrom=MSDN\&view=excel-pia). We’ll be sorting in ascending order.

Putting all of that together looks like this:

```python
ws.range(
"A2:N{row}".format(row=last_row)
).api.Sort(Key1=ws.range("A:A").api, Order1=1)
```

```
True
```

Here’s how it’ll look on your screen after running (note that the first column has changed, and it’s now sorted in ascending rather than descending order.

![python-excel-xlwings-make-excel-faster-2](https://www.dataquest.io/wp-content/uploads/2019/09/xlwings-python-excel-faster-2.jpg)

**Analyzing Our Data**

One problem we’ll encounter in trying to analyze this data set is that the dates are scattered across three different columns. We’ll need to compress it into a single column. To do this, we’ll concatenate the columns appropriately in Excel, using Python. We begin by inserting a header in the empty adjacent column.

```python
ws.range("O1").value = "Date"
```

Next, we can insert the Excel formula we want to use as a string. Note: the specifics of what argument separator you should use depends on your machine’s local regional settings. On mine, the argument separator is a comma and that’s what I’ve used in this tutorial, but on yours it might be a semicolon.

```python
ws.range("O2").value = "=C2&D2&RIGHT(E2, 2)"
```

Having inserted the formula in the first cell, it’s second nature in the regular Excel workflow to autofill the rest of the cells through the end of the table. [Autofill](https://docs.microsoft.com/en-us/office/vba/api/excel.range.autofill) is a method of the `VBA Range` object. It takes as parameters the destination cells as a `VBA Range` object and the [type of fill](https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.xlautofilltype?redirectedfrom=MSDN\&view=excel-pia). We’re interested in the default one whose enumeration is `0`.

```python
ws.range("O2").api.AutoFill(
ws.range("O2:O{row}".format(row=last_row)).api,
0
)
```

```
True
```

Here’s roughly how the screen should look after this step; note the new “Date” column on the far right.

![08.-Autofill](https://www.dataquest.io/wp-content/uploads/2019/09/xlqings-image-3.jpg)

We can also use the named form of the type of fill we want. To do this we’ll need to retrieve it from the module `xlwings.constants` which contains named versions of the enumerated arguments of most [VBA properties](https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel?view=excel-pia#enums). Recall that you can always check the available attributes by printing `dir(xlwings.constants)`.

(If you’re not familiar with it, `dir` is a native Python function and can take several kinds of arguments (modules, classes and regular objects (like lists and strings). For example, if you print `dir(some_list)` it will give you all the methods and attributes that you can use with a list.)

What we did above could also be achieved with the following code snippet.

```python
from xlwings.constants import AutoFillType
ws.range("O2").api.AutoFill(
ws.range("O2:O{row}".format(row=last_row)).api,
    AutoFillType.xlFillDefault
)
```

Since we’ll be using this often, we’ll create a function that applies the default fill, given:

* a worksheet
* a string representing a cell in the worksheet
* a last row to fill.

To do this we’ll introduce a new `Range` method called [`get_address`](http://docs.xlwings.org/en/stable/api.html?highlight=get_address). It takes in four Boolean arguments and returns a string that identifies the range with varying levels of detail. Here’s a hopefully enlightening illustration of this method.

```python
for arg4 in (0, 1):
for arg3 in (0,1):
for arg2 in (0,1):
for arg1 in (0,1):
print(ws.range("O2").get_address(arg1, arg2, arg3, arg4))
```

```
O2
O$2
$O2
$O$2
EuroMillions!O2
EuroMillions!O$2
EuroMillions!$O2
EuroMillions!$O$2
[Book1]EuroMillions!O2
[Book1]EuroMillions!O$2
[Book1]EuroMillions!$O2
[Book1]EuroMillions!$O$2
[Book1]EuroMillions!O2
[Book1]EuroMillions!O$2
[Book1]EuroMillions!$O2
[Book1]EuroMillions!$O$2
```

And now we define our function.

```python
def autofill(worksheet, cell, last_row):
   rg_cell = worksheet.range(cell)
   to_fill = "{col}{top_row}:{col}{last_row}".format(
      col=rg_cell.get_address(0,0)[0],
      top_row=rg_cell.row,
      last_row=last_row
   )
   rg_cell.api.Autofill(worksheet.range(to_fill).api, 0)
```

In order to save Excel from making unnecessary computations, we’ll replace the formulas we just inserted on column `O` with hard coded values. Before we do this let us take a moment to think about what kind of Python object is `Range.value` when `Range` is an array.

```python
print(type(ws.range("O2:O{row}".format(row=last_row)).value))
```

```
<class 'list'>
```

It’s a list! Let’s see its first ten elements.

```python
print(ws.range('O2:O{row}'.format(row=last_row)).value[:10])
```

```
['13Feb04', '20Feb04', '27Feb04', '5Mar04', '12Mar04', '19Mar04', '26Mar04', '2Apr04', '9Apr04', '16Apr04']
```

If we insert this list into any range, it will place the values horizontally, which isn’t what we want. In order to place them vertically, we’ll need to use the [`options`](http://docs.xlwings.org/en/stable/api.html?highlight=get_address#xlwings.Range.options) method of `Range` objects with the option `transpose=True` as a parameter, like this:

```python
ws.range('O2').options(transpose=True).value\
= ws.range('O2:O{row}'.format(row=last_row)).value
```

And we can now [delete](https://docs.microsoft.com/en-us/office/vba/api/excel.range.delete) columns `C` through `E`.

```python
ws.range('C:E').api.Delete()
```

```
True
```

EuroMillions format has suffered some mild modifications over the years, the last one happened on September 24, 2016.

From September 24, 2016 the amount of lucky stars changed from a pool of 11 to a pool of 12 numbers. In order to make a meaningful analysis, we’ll only consider draws that occurred after the last modification. The next code snippet finds the last game that happened prior to the modification and names it `to_delete`.

```python
import datetime

for day in ws.range('L2:L{}'.format(last_row)):
# checks if day is not prior to the change of the rules
    if day.value <= datetime.datetime(2016, 9, 24, 0, 0):
        # since day is past the modification date,
        # the row we want is the previous one, hence minus 1
        to_delete = int(day.get_address(0, 0)[1:])-1
        # leave the for cycle
        break
```

And we can now delete every row from the first game to `to_delete`.

```python
ws.range('2:{}'.format(to_delete)).api.Delete()
```

```
True
```

Here’s where we’re at, at this point:

![11.-Delete-rows](https://www.dataquest.io/wp-content/uploads/2019/09/xlwings-image-4.jpg)

Having finished preparing the data, we will now format this table. We’ll begin by setting the [font](https://docs.microsoft.com/en-us/office/vba/api/excel.range.font) of the first row to **bold**.

```python
ws.range('1:1').api.Font.Bold = True
```

We can follow that by formatting the `Jackpot` column in millions. Note that the string format below depends on your machine’s local regional settings. If the format looks odd on your end, try swapping the commas with the dots. More on Excel custom formats [here](https://exceljet.net/custom-number-formats).

```python
ws.range('J:J').number_format = "£##.##0,,' M'"
```

As an auxiliary step for what will follow, we’ll find the letter corresponding to the last column that has data.

```python
last_column = ws.range(1,1).end('right').get_address(0,0)[0]
```

Let’s now add a border to the bottom of the header cells. Similarly to what we have been doing, we’ll use the `api` property. Additionally we are going to need the [`Border`](https://docs.microsoft.com/en-us/office/vba/api/excel.range.borders) property of `Range` objects, the [border orientation enumeration](https://docs.microsoft.com/en-us/office/vba/api/excel.xlbordersindex) and the [style of the border](https://docs.microsoft.com/en-US/dotnet/api/microsoft.office.interop.excel.xllinestyle?view=excel-pia). We’ll be setting a double edged border (line style `-4119`) on the bottom of the header cells only (orientation `9`).

```python
ws.range('A1:{}1'.format(last_column)).api.Borders(9).LineStyle = -4119
```

Let us now [autofit](http://docs.xlwings.org/en/stable/api.html?highlight=get_address#xlwings.Sheet.autofit) by both rows and columns.

```python
ws.autofit()
```

![xlwings-python-excel-faster-5](https://www.dataquest.io/wp-content/uploads/2019/09/xlwings-python-excel-faster-5.jpg)

Oops! This looks a bit squished, let us set the width of all columns to that of column `J` which seems to be the largest. [Here](https://docs.microsoft.com/en-us/office/vba/api/excel.range.columnwidth) is the [`ColumnWidth`](https://docs.microsoft.com/en-us/office/vba/api/excel.range.columnwidth) documentation that we are using just below.

```python
ws.range('A:L').api.ColumnWidth = ws.range('J:J').api.ColumnWidth
```

That should look better. And we’re done with this sheet!

Let us [`add`](http://docs.xlwings.org/en/stable/api.html?highlight=get_address#xlwings.main.Sheets.add) a new blank sheet called `Frequencies` and let us assign it to the Python name `frequencies`.

```python
wb.sheets.add('Frequencies')
frequencies = wb.sheets['Frequencies']
```

We will be populating this sheet with the absolute frequencies of each number and each lucky star in the data set we just organized in the sheet `EuroMillions`.

```python
# add a header for the numbers
frequencies.range('A1').value = 'Number'
# populate the fifty cells immediately below with the numbers 1 through 50
# since we're starting from the second row, we'll want to substract 1 from the row
frequencies.range('A2:A51').value = '=ROW()-1'
```

Below we’ll be inserting a header for the frequencies in cell `B1` and in cell `B2` we will input a formula that will count how many times the value in `A2` occurs in the range `C2:G201`. In other words, we will count how many times `1` occurred in the columns `N1-N5`. After this we will autofill the rest of the cells on column `B` to do the same for their respective rows.

```python
# add a header for the frequencies
frequencies.range('B1').value = 'Frequency'
# insert on B2 the result of a standard Excel formula
frequencies.range('B2').value = '=COUNTIF(Euromillions!$C$2:$G$201,Frequencies!A2)'
autofill(frequencies, 'B2', 51)
```

And we do the same for the lucky stars:

```python
frequencies.range('D1').value = 'Lucky Star'
frequencies.range('E1').value = 'Frequency'
frequencies.range('D2:D13').value = '=ROW()-1'
frequencies.range('E2').value =\
'=COUNTIF(EuroMillions!$H$2:$I$201,Frequencies!D2)'
autofill(frequencies, 'E2', 13)
frequencies.autofit()
```

Here’s how our new sheet should be looking at this point:

![xlwings-python-excel-6](https://www.dataquest.io/wp-content/uploads/2019/09/xlqings-image-6.jpg)

We are nearing our goal. Let’s create a sheet called `Graphs`.

```python
wb.sheets.add('Graphs')
graphs = wb.sheets['Graphs']
```

Now we’ll create a [`Chart`](http://docs.xlwings.org/en/stable/api.html?highlight=get_address#chart) object. This will only produce a blank white box, but don’t worry! We’re going to use that box to chart our data in a moment.

```python
nr_freq = xw.Chart()
```

We can [`name`](http://docs.xlwings.org/en/stable/api.html?highlight=get_address#xlwings.Chart.name) our chart similarly to what we did for sheets. The method [`set_source_data`](http://docs.xlwings.org/en/stable/api.html?highlight=get_address#xlwings.Chart.set_source_data) allows us to define the data source for our chart by passing in a range object.

```python
nr_freq.name = 'Number Frequencies'
nr_freq.set_source_data(frequencies.range('Frequencies!B1:B51'))
```

Excel will try to guess what the x-axis should be, but we can force it to be the numbers we created on `Frequencies` by using the `VBA Chart` method [`FullSeriesCollection`](https://docs.microsoft.com/en-us/office/vba/api/excel.chart.fullseriescollection). We can edit the chart by using the object at index 1 of `nr_freq.api`:

```python
nr_freq.api[1].FullSeriesCollection(1).XValues = '=Frequencies!A2:A51'
```

Excel is very good at guessing what kind of chart the user wants, but just in case it guesses incorrectly, we will force it to be a column chart. The various types of charts are listed [here](https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.xlcharttype?view=excel-pia). Alas, the only documentation bridging these to the possible values of the [`chart_type`](http://docs.xlwings.org/en/stable/api.html?highlight=get_address#xlwings.Chart.chart_type) attribute is the [source code](https://github.com/ZoomerAnalytics/xlwings/blob/master/xlwings/_xlmac.py) itself.

```python
nr_freq.chart_type = 'column_clustered'
```

We will now define the height and the width of the chart. The measure unit will be [points](https://en.wikipedia.org/wiki/Point_\(typography\)).

```python
nr_freq.height = 250
nr_freq.width = 750
```

Here’s what we should be seeing at this point:

![xlwings-chart](https://www.dataquest.io/wp-content/uploads/2019/09/xlqings-image-7.jpg)

The `SetElement` method together with the parameter `2` sets the title above the chart. See other arguments [here](https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.core.msochartelementtype?view=office-pia).

```python
nr_freq.api[1].SetElement(2)  # Place chart title at the top
nr_freq.api[1].ChartTitle.Text = 'Number Frequencies'
```

And we add the final touches. We remove the legend using the [`HasLegend`](https://docs.microsoft.com/en-us/office/vba/api/excel.chart.haslegend) property.

```python
nr_freq.api[1].HasLegend = 0
```

The [`xlCategory`](https://docs.microsoft.com/en-us/office/vba/api/excel.xlaxistype) category passed in as the argument 1 to the `Axes` method, together with the property [TickLabelSpacing](https://docs.microsoft.com/en-us/office/vba/api/Excel.Axis.TickLabelSpacing) set to `1`, ensures that every element of our axis is displayed.

```python
nr_freq.api[1].Axes(1).TickLabelSpacing = 1
```

To finish formatting this chart, we remove the outline by setting the property [`Visible`](https://docs.microsoft.com/en-us/office/vba/api/access.line.isvisible) of the [`Line`](https://docs.microsoft.com/en-us/office/vba/api/access.line) object to `0`.

```python
graphs.shapes.api('Number Frequencies').Line.Visible = 0
```

And here’s what we’ll see:

![](https://www.dataquest.io/wp-content/uploads/2019/09/xlqings-image-8.jpg)

Below we do nearly the same thing for the lucky stars.

```python
ls_freq = xw.Chart()
ls_freq.top = 250
ls_freq.name = 'Lucky Star Frequencies'
ls_freq.set_source_data(frequencies.range('Frequencies!E1:E13'))
ls_freq.api[1].FullSeriesCollection(1).XValues = '=Frequencies!D2:D13'
ls_freq.chart_type = 'column_clustered'
ls_freq.height = 250
ls_freq.width = 750
ls_freq.api[1].SetElement(2)
ls_freq.api[1].ChartTitle.Text = 'Lucky Star Frequencies'
ls_freq.api[1].HasLegend = 0
ls_freq.api[1].Axes(1).TickLabelSpacing = 1
graphs.shapes.api('Lucky Star Frequencies').Line.Visible = 0
```

![29.-Lucky-stars-chart](https://www.dataquest.io/wp-content/uploads/2019/09/xlqings-image-9.jpg)

And to finalize we create a time series graph displaying the evolution of the jackpot.

```python
jackpot = xw.Chart()
jackpot.top = 500
jackpot.name = 'Jackpot'
last_row = ws.range(1,1).end('down').row
jackpot.set_source_data(ws.range('Euromillions!J2:J{}'.format(last_row)))
jackpot.api[1].FullSeriesCollection(1).XValues\
= 'Euromillions!L2:L{}'.format(last_row)
jackpot.chart_type = 'line'
jackpot.height = 250
jackpot.width = 750
jackpot.api[1].SetElement(2)
jackpot.api[1].ChartTitle.Text = 'Jackpot'
jackpot.api[1].HasLegend = 0
graphs.shapes.api('Jackpot').Line.Visible = 0
```

And we fix the vertical axis’ labels’ format by setting the [TickLabels](https://docs.microsoft.com/en-us/office/vba/api/excel.axis.ticklabels) property [`NumberFormat`](https://docs.microsoft.com/en-us/office/vba/api/excel.ticklabels.numberformat) to the desired appearance.

```python
jackpot.api[1].Axes(2).TickLabels.NumberFormat = "£0,,' M'"
```

![30.-Jackpot-chart](https://www.dataquest.io/wp-content/uploads/2019/09/xlqings-image-10.jpg)

And we’re done! Now we save the file and exit our Excel instance.

```python
wb.save('EuroMillions.xlsx')
xw.apps[0].quit()
```

Hopefully, this xlwings tutorial has been helpful!

Some useful resources for learning about `xlwings` are [the official documentation](http://docs.xlwings.org/en/stable/index.html), [this](http://nbviewer.jupyter.org/github/pybokeh/jupyter_notebooks/blob/master/xlwings/Excel_Formatting.ipynb) formatting cheat sheet, the [VBA for Excel documentation](https://docs.microsoft.com/en-us/office/vba/api/overview/excel) and the course [xlwings: Python for Excel](https://training.zoomeranalytics.com/p/xlwings) designed by [Felix Zumstein](https://github.com/fzumstein) himself, the developer of `xlwings`.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://yo-sarawut.gitbook.io/tutorials/articles/pandas/make-excel-faster.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.