Skip to content

Working with individual cells

Aside from working with tables, you can also work with cells using the cells() table-valued function.

The following query returns a list of cells in the current selection:

select * from cells()

We can also return a list of cells in a specified range, like so:

select * from cells('Sheet1!B5:D9')

Here's what the returned data looks like: Cells query

For each cell in the selection, one row is returned in the results. Each cell is described with the following attributes: address, row, column, column letter, value, type, formula.

Updating cell values

The primary reason to use the cells() function is to make changes to an arbitrary selection of cells. To update the value of a cell, we can use the SetCellValue() function and the Address column.

For example, let's reverse the text in the selected cells:

SELECT
    *, SetCellValue(Address, reverse(Value))
FROM
    cells()

Update cell values example

Using a select statement to do an update might seem strange, but its fairly easy to get used to. In a future version, it's also likely that direct UPDATE statements will be supported.

Tip

You can select multiple areas in Excel by holding down the Ctrl key while selecting them in Excel. To navigate between selected areas you can use the Ctrl+Alt+Left and Ctrl+Alt+Right shortcuts.

Comments