Skip to content

Formatting via SQL

Since the SQLite engine is running in-process with Excel, it can interact with Excel objects. The primary use case for this is to modify formatting.

Formatting rows

As mentioned before, each table has a hidden __address column which we can use to selectively apply formatting to rows.

Let's set the background color for movies that grossed more than $40M:

--first clear any previous formatting 
select ClearBackgroundColor('movies');

--apply new formatting
select
    *, SetBackgroundColor(__address, 'Orange')
from
     movies
where
    gross > 400000000
order by
    __row asc

Formatting rows example

In the example, we're using the ClearBackgroundColor function to clean any previously applied formatting, after which we apply formatting to the desired rows via the SetBackgroundColor function.

It might seem strange to use a SELECT statement to modify row formatting, but it's fairly easy to get used to, and there doesn't seem to be a more elegant way of doing it via SQL.

Formatting cells

We can use the SetBackgroundColor function to format cells as well. Here's an example of coloring cells based on their values:

select
    *,
    (case
        when value > 100000 
            then SetBackgroundColor(Address, 'blue')
        else 
            SetBackgroundColor(Address, 'red')
    end) color
from
    cells()
where 
    type = 'Double'

Format cells

Here I'm formatting cells that contain a number in different colors, depending on their value.

Comments