# Working with Cell Notation

XlsxWriter supports two forms of notation to designate the position of cells: Row-column notation and A1 notation.

Row-column notation uses a zero based index for both row and column while A1 notation uses the standard Excel alphanumeric sequence of column letter and 1-based row. For example:

```(0, 0)      # Row-column notation.
('A1')      # The same cell in A1 notation.

(6, 2)      # Row-column notation.
('C7')      # The same cell in A1 notation.
```

Row-column notation is useful if you are referring to cells programmatically:

```for row in range(0, 5):
worksheet.write(row, 0, 'Hello')
```

A1 notation is useful for setting up a worksheet manually and for working with formulas:

```worksheet.write('H1', 200)
worksheet.write('H2', '=H1+1')
```

In general when using the XlsxWriter module you can use A1 notation anywhere you can use row-column notation. This also applies to methods that take a range of cells:

```worksheet.merge_range(2, 1, 3, 3, 'Merged Cells', merge_format)
worksheet.merge_range('B3:D4',    'Merged Cells', merge_format)
```

XlsxWriter supports Excel’s worksheet limits of 1,048,576 rows by 16,384 columns.

Note

• Ranges in A1 notation must be in uppercase, like in Excel.
• In Excel it is also possible to use R1C1 notation. This is not supported by XlsxWriter.

## Row and Column Ranges

In Excel you can specify row or column ranges such as `1:1` for all of the first row or `A:A` for all of the first column. In XlsxWriter these can be set by specifying the full cell range for the row or column:

```worksheet.print_area('A1:XFD1')      # Same as 1:1
worksheet.print_area('A1:A1048576')  # Same as A:A
```

This is actually how Excel stores ranges such as `1:1` and `A:A` internally.

These ranges can also be specified using row-column notation, as explained above:

```worksheet.print_area(0, 0,       0, 16383)  # Same as 1:1
worksheet.print_area(0, 0, 1048575,     0)  # Same as A:A
```

To select the entire worksheet range you can specify `A1:XFD1048576`.

## Relative and Absolute cell references

When dealing with Excel cell references it is important to distinguish between relative and absolute cell references in Excel.

Relative cell references change when they are copied while Absolute references maintain fixed row and/or column references. In Excel absolute references are prefixed by the dollar symbol as shown below:

```'A1'    # Column and row are relative.
'\$A1'   # Column is absolute and row is relative.
'A\$1'   # Column is relative and row is absolute.
'\$A\$1'  # Column and row are absolute.
```

See the Microsoft Office documentation for more information on relative and absolute references.

Some functions such as `conditional_format()` may require absolute references, depending on the range being specified.

## Defined Names and Named Ranges

It is also possible to define and use “Defined names/Named ranges” in workbooks and worksheets, see `define_name()`:

```workbook.define_name('Exchange_rate', '=0.96')
worksheet.write('B3', '=B2*Exchange_rate')
```

## Cell Utility Functions

The `XlsxWriter` `utility` module contains several helper functions for dealing with A1 notation as shown below. These functions can be imported as follows:

```from xlsxwriter.utility import xl_rowcol_to_cell

cell = xl_rowcol_to_cell(1, 2)  # C2
```

### xl_rowcol_to_cell()

`xl_rowcol_to_cell`(row, col[, row_abs, col_abs])

Convert a zero indexed row and column cell reference to a A1 style string.

Parameters: row (int) – The cell row. col (int) – The cell column. row_abs (bool) – Optional flag to make the row absolute. col_abs (bool) – Optional flag to make the column absolute. A1 style string.

The `xl_rowcol_to_cell()` function converts a zero indexed row and column cell values to an `A1` style string:

```cell = xl_rowcol_to_cell(0, 0)   # A1
cell = xl_rowcol_to_cell(0, 1)   # B1
cell = xl_rowcol_to_cell(1, 0)   # A2
```

The optional parameters `row_abs` and `col_abs` can be used to indicate that the row or column is absolute:

```str = xl_rowcol_to_cell(0, 0, col_abs=True)                # \$A1
str = xl_rowcol_to_cell(0, 0, row_abs=True)                # A\$1
str = xl_rowcol_to_cell(0, 0, row_abs=True, col_abs=True)  # \$A\$1
```

### xl_cell_to_rowcol()

`xl_cell_to_rowcol`(cell_str)

Convert a cell reference in A1 notation to a zero indexed row and column.

Parameters: cell_str (string) – A1 style string, absolute or relative. Tuple of ints for (row, col)

The `xl_cell_to_rowcol()` function converts an Excel cell reference in `A1` notation to a zero based row and column. The function will also handle Excel’s absolute, `\$`, cell notation:

```(row, col) = xl_cell_to_rowcol('A1')    # (0, 0)
(row, col) = xl_cell_to_rowcol('B1')    # (0, 1)
(row, col) = xl_cell_to_rowcol('C2')    # (1, 2)
(row, col) = xl_cell_to_rowcol('\$C2')   # (1, 2)
(row, col) = xl_cell_to_rowcol('C\$2')   # (1, 2)
(row, col) = xl_cell_to_rowcol('\$C\$2')  # (1, 2)
```

### xl_col_to_name()

`xl_col_to_name`(col[, col_abs])

Convert a zero indexed column cell reference to a string.

Parameters: col (int) – The cell column. col_abs (bool) – Optional flag to make the column absolute. Column style string.

The `xl_col_to_name()` converts a zero based column reference to a string:

```column = xl_col_to_name(0)    # A
column = xl_col_to_name(1)    # B
column = xl_col_to_name(702)  # AAA
```

The optional parameter `col_abs` can be used to indicate if the column is absolute:

```column = xl_col_to_name(0, False)  # A
column = xl_col_to_name(0, True)   # \$A
column = xl_col_to_name(1, True)   # \$B
```

### xl_range()

`xl_range`(first_row, first_col, last_row, last_col)

Converts zero indexed row and column cell references to a A1:B1 range string.

Parameters: first_row (int) – The first cell row. first_col (int) – The first cell column. last_row (int) – The last cell row. last_col (int) – The last cell column. A1:B1 style range string.

The `xl_range()` function converts zero based row and column cell references to an `A1:B1` style range string:

```cell_range = xl_range(0, 0, 9, 0)  # A1:A10
cell_range = xl_range(1, 2, 8, 2)  # C2:C9
cell_range = xl_range(0, 0, 3, 4)  # A1:E4
cell_range = xl_range(0, 0, 0, 0)  # A1
```

### xl_range_abs()

`xl_range_abs`(first_row, first_col, last_row, last_col)

Converts zero indexed row and column cell references to a \$A\$1:\$B\$1 absolute range string.

Parameters: first_row (int) – The first cell row. first_col (int) – The first cell column. last_row (int) – The last cell row. last_col (int) – The last cell column. \$A\$1:\$B\$1 style range string.

The `xl_range_abs()` function converts zero based row and column cell references to an absolute `\$A\$1:\$B\$1` style range string:

```cell_range = xl_range_abs(0, 0, 9, 0)  # \$A\$1:\$A\$10
cell_range = xl_range_abs(1, 2, 8, 2)  # \$C\$2:\$C\$9
cell_range = xl_range_abs(0, 0, 3, 4)  # \$A\$1:\$E\$4
cell_range = xl_range_abs(0, 0, 0, 0)  # \$A\$1
```