Relation for Excel

by Matthias Bürcher @ 2016-2022 Version 3.0

Freeware

This macro provides functions to create simple relational algebra.
Unlike other Excel solutions, this one is purely functional and does not use macros or external DLL.
The functions all return relations. Relations are stored as text in one cell with double colon (::) as field separator and space+newline as row separator. The first line are the column names.

Relation for Excel 3.0 adds SQL syntax. You can now make SQL queries in Excel using tables in any range in a worksheet, including joins between tables.

Download

relation-for-excel-300.xlsm.zip

The zip file contains:

Version 3.0 is only distributed as exampel worksheet (xlsm), not as add-in (xlam). To use it, remove the example tabs or copy the code, which is in one single self contained module in VBA, into your spreadsheet. You can create an add-in from it, if you like.

This presentation will give you a good introduction to relational algebra and how to use it with these functions
relation-for-excel-300-presentation.pdf

Usage

Version 3.0 adds the following functions:

Some valid SQL queries are:

Note that:

Operators: + - * / > < >= <= = <> LIKE IN
Aggregators: AVG COUNT MAX MEDIAN MIN STDEV SUM
Numerical functions: ABS COS EXP INT LN LOG MOD POW ROUND SGN SIN SQRT TAN
Text functions: LEFT LEN LOWER MID REPLACE RIGHT TRIM UPPER

This was a summer project. For the programming challenges see Parsing SQL for Excel

For the documentation of the other functions see Relation for Excel 2.3

Old presentation relation-for-excel-200-presentation.pdf

Github: https://github.com/bellenuit/relation-for-excel

History

21.8.2022 Relation for Excel 3.0

20.8.2020 Relation for Excel 2.3

4.4.2017 Relation for Excel 2.0

15.8.2016 Relation for Excel 1.1.2

15.6.2016 Relation for Excel 1.1.1

10.5.2016 Relation for Excel 1.1

20.4. Relation for Excel 1.0