v1.0 - beta

CakeXL User Guide

Welcome to CakeXL, a powerful Microsoft Excel add-in that enables technical and non-technical users to connect to databases, import files, run SQL queries, and build reports, dashboards, and charts directly within Excel using advanced data connectivity and automation features.

Version 1.0 April 2026

▼ What is CakeXL?

CakeXL is a Microsoft Excel add-in that bridges external data sources with Excel calculations and reporting capabilities. It allows users to connect to databases and external data sources, run SQL queries directly inside Excel, and use CakeXL user-defined functions alongside native Excel functions.

Users can also load and analyse files such as CSV, TXT, and XLSX to create reports, charts, dashboards, and data models within the familiar Excel environment. CakeXL is designed to make advanced data analysis accessible to both technical and non-technical users without requiring deep SQL knowledge.

Use CakeXL to work with data from databases or files directly within Excel while leveraging Excel’s existing functionality and flexibility.

▶ Getting Started

Before installing CakeXL, you need to register at cakecodex.com and you will receive a confirmation email from support@cakecodex.com with your download links.

  1. Download the add-in Two formats are available in your welcome email:
    • MSI installer — run the installer and follow the on-screen prompts.
    • ZIP archive — extract the contents to a folder of your choice.
  2. Add the add-in to Excel
    1. Open Excel and go to File → Options → Add-ins.
    2. At the bottom, set the Manage dropdown to Excel Add-ins and click Go…
    3. In the dialog, click Browse and navigate to your install or extracted folder.
    4. Select cakexl.addin64.xll and click OK.
    5. The CakeXL tab will appear in the Excel ribbon automatically.

🔑 License

Once the add-in is installed, activate your license before using CakeXL. You will need the license key included in your welcome email from support@cakecodex.com.

  1. Open the License window Click Activate License in the CakeXL ribbon tab.
  2. The window shows your Device Token.Then click Activate License.
  3. Confirm activation A success message confirms your license is active and CakeXL is ready to use. If activation fails, contact support@cakecodex.com with your Device Token.

■ Ribbon Buttons

The CakeXL ribbon tab contains two groups of buttons:

Info Group

  • Get CPU CountShows the number of logical CPU cores on your machine.
  • Activate Licenselicense activation

Data Group

  • CatalogOpens the Database Manager to register and manage databases.
  • SQL IDEOpens the SQL query editor.

AI

  • AI SettingsConfigure the AI model

📄 Database Manager (Catalog)

Click Catalog in the ribbon to open the Database Manager.

Left Panel — Database List

Lists all registered databases. Each entry shows:

  • Database name
  • Engine badge (DuckDB or MySQL)
  • Number of tables
  • File size

Right Panel — Overview Tab

Selecting a database shows:

  • File location
  • Created date
  • Status
  • File size
  • Table count
  • Last modified date

Right Panel — Tables Tab

Lists all tables in the selected database with their name, type, row count and column count. Each table has three actions:

  • Preview Browse data with filtering, searching and sorting
  • Delete Remove the table from the database
  • Export Download the table as a .csv file

Buttons

✎ SQL IDE

Click SQL IDE in the ribbon to open the query editor. The window stays open while you work in Excel. it is modeless.

Toolbar

Editor

Type your SQL in the editor panel. Keywords, strings, and comments are colour-highlighted. Line numbers are shown on the left. Press F5 or click Run to execute.

Results

Results appear in the grid below the editor. Large result sets are paginated at 1,000 rows per page — use Prev / Next to navigate. The status bar shows row count and page info.

Adding Results to Excel

  1. Select a cell in your Excel sheet first.
  2. Run your query in the SQL IDE.
  3. Click Add to Excel
  4. A =CX.Execute.SQL.QUERY(...) formula is inserted and results spill automatically.

ƒ Excel Functions (CX.*)

All CakeXL functions use the CX. prefix. Results recalculate when you press F9 or Excel recalculates.

Function Description
Connection & Setup
=CX.CREATE.DATABASE.CONNECTION("duckdb","MyDB",0) Connects to an existing database or creates a new one. db_type: "duckdb" or "mysql". num_threads: 0 = all threads. For MySQL, also pass connection_name — the saved connection from Manage Connections.
Example (MySQL): =CX.CREATE.DATABASE.CONNECTION("mysql","SalesDB",0,"MyServer")
=CX.DELETE.TABLE(dbRef,"tableName") Deletes a table from a database.
=CX.RUNSQL.FROM.FILE(dbRef,"C:\path\q.sql") Runs a SQL script file against a database. Returns 1 on success.
Data Import
=CX.LOAD.CSV(dbRef,"tableName","C:\file.csv") Loads a CSV file into a database table.
=CX.LOAD.TXT(dbRef,"tableName","C:\file.txt") Loads a TXT file into a database table.
=CX.LOAD.XLSX(dbRef,"tableName","C:\file.xlsx",["sheetName"]) Loads a sheet from an XLSX file into a database table. sheetName is optional — defaults to the first sheet if omitted.
=CX.LOAD.XLSX.RANGE(dbRef,"tableName","rangeAddress") Loads an Excel range into a database table. Flexible range formats supported:
"A1:C10" — active sheet
"Sheet2!A1:C10" — named sheet
"[Book1.xlsx]Sheet2!A1:C10" — open workbook
"[C:\path\file.xlsx]Sheet2!A1:C10" — file path
SQL Querying
=CX.EXECUTE.SQL.QUERY(dbRef,"SELECT ...","") Executes SQL and spills results. Row 1 = headers, Row 2+ = data. Third arg: output table name — leave "" to spill to Excel, or provide a name to store results as a table in the database. Note: results spilled to Excel are capped at 1,000,000 rows; use the output table option for larger result sets.
Explore & Inspect
=CX.VIEW.DATABASE.TABLES(dbRef) Returns a spilled list of all table names in the database. Returns "No tables or error" if the database is empty or the reference is invalid.
=CX.VIEW.DATABASE.TABLE.HEADER.MATRIX(dbRef,tableNames) Returns a matrix of column headers for one or more tables. tableNames accepts a range or array (e.g. {"Orders","Products"}). Each row in the output represents one table; each column represents a header position.
=CX.TABLE.COUNT.ROWS(dbRef,"tableName") Returns the number of rows in a table.
Query Builder
=CX.TOPN(dbRef,"table",N) Returns the first N rows of a table including a header row. N must be greater than 0.
=CX.SELECT(dbRef,"table",columns,limit) Returns only the specified columns from a table. columns: inline array {"col1","col2"} or a range reference. limit: max rows returned (default 10,000).
=CX.DISTINCT(dbRef,"table",columns,limit) Returns distinct combinations of the specified columns. columns: inline array or range. limit: max rows returned (default 10,000).
=CX.SEARCH.TABLE(dbRef,"table","searchValue","col1|col2",limit) Searches for a value across one or more columns and returns matching rows with a header. Search is case-insensitive and partial-match. search_columns: pipe-separated column names, or * to search all text columns. limit: default 10,000.
=CX.FILTER.TABLE(dbRef,"table","col=val|col2>val2","col1 ASC|col2 DESC",limit) Filters a table using pipe-separated conditions with optional sorting. filter_spec operators: = != < > <= >= LIKE NOT LIKE. sort_spec: optional pipe-separated col ASC or col DESC. limit: default 10,000.
=CX.SORT.TABLE(dbRef,"table","col1 ASC|col2 DESC",limit) Sorts a table by one or more columns and returns all rows with a header. sort_spec: pipe-separated pairs e.g. "Sales DESC|Region ASC". limit: default 10,000.
Aggregation & Lookup
=CX.LOOKUP(dbRef,"table","value","lookup_col","return_col") Looks up a value in a table column and returns the corresponding value from a different column.
=CX.SUMIF(dbRef,"table","sum_col","criteria","criteria_col") Sums values in sum_col where criteria_col matches criteria.
=CX.COUNTIF(dbRef,"table","criteria_col","criteria") Counts rows where criteria_col matches criteria.
=CX.AVERAGEIF(dbRef,"table","criteria_col","criteria","avg_col") Averages values in avg_col where criteria_col matches criteria.
=CX.MIN(dbRef,"table","min_col",["criteria_col"],["criteria"]) Returns the minimum value in min_col. Optional filter: supply criteria_col and criteria.
=CX.MAX(dbRef,"table","max_col",["criteria_col"],["criteria"]) Returns the maximum value in max_col. Optional filter: supply criteria_col and criteria.
=CX.PERCENTILE(dbRef,"table","col",percentile,["criteria_col"],["criteria"]) Returns a percentile of col (0 = min, 0.5 = median, 1 = max). Optional filter arguments.
=CX.SUMIFS(dbRef,"table","sum_col","col1","crit1","col2","crit2",...) Sums sum_col matching multiple criteria pairs. Supports = > >= < <= <> operators and * ? wildcards. Mirrors Excel SUMIFS.
=CX.COUNTIFS(dbRef,"table","col1","crit1","col2","crit2",...) Counts rows matching multiple criteria pairs. Supports operators and wildcards. Mirrors Excel COUNTIFS.
=CX.AVERAGEIFS(dbRef,"table","avg_col","col1","crit1","col2","crit2",...) Averages avg_col matching multiple criteria pairs. Supports operators and wildcards. Mirrors Excel AVERAGEIFS.
Data Transformation
=CX.TRANSFORM.WIDE.TO.LONG(dbRef,"table",row_names_original,row_names_transformed,split_keys,"value_col") Pivots a wide table to long (tidy) format. row_names_original: identifier columns to keep. row_names_transformed: output names for those columns (must match in count). split_keys: wide columns to unpivot — their names become row values. value_col: name for the new column holding the unpivoted values. All array args accept a range or array.
=CX.CONVERT.DATATYPE(range)

=CX.CONVERT.DATATYPE(,dbRef,"table",["output_table"])		 Two modes:
Excel range mode — pass a range (e.g. A1:Z100): converts text values to native Excel types (numbers, dates, booleans). Wrap query results with this to get proper Excel types.

DuckDB table mode — pass db_ref + table_name (omit the first arg): auto-detects VARCHAR columns where >50% of values are numeric, casts them to DOUBLE, and stores the result as a TEMP table. Optional output_table name — defaults to tableName_typed. Returns the output table name on success.
System & Info
=CX.GET.DB.TABLE.COUNT(dbRef) Returns the number of tables in a database.
=CX.GET.DB.FILE.SIZE(dbRef) Returns the database file size in bytes.
=CX.SHOW.XLLPATH() Returns the add-in file path.

↻ Typical Workflow

  1. Open Excel The CakeXL tab appears in the ribbon automatically.
  2. Create a Database Click Catalog → + New Database Enter a name (e.g. SalesDB) and click Create.
  3. Use the formula reference =CX.CREATE.DATABASE.CONNECTION(...) formula is written into a cell and returns a reference string (e.g. "SalesDB").
  4. Query with that reference Use the cell (e.g. A1) in other formulas:
    =CX.EXECUTE.SQL.QUERY(A1, "SELECT * FROM orders", "")
  5. Open the SQL IDE Click SQL IDESelect your database from the dropdown, type a query, and press F5 to run it.
  6. Add results to Excel Click a cell in your sheet, then click Add to ExcelThe query is inserted as a live formula that spills results automatically.
  7. Refresh at any time Press F9 to refresh all CX.* results.

💡 Tips

🔑
Activate license right after installing
📄
The SQL IDE is a modeless window. You can leave it open and switch freely between it and your Excel workbook.
▶
All CX.* formulas recalculate when Excel recalculates. Press F9 to refresh everything at once.
📊
=CX.EXECUTE.SQL.QUERY always puts column headers in row 1 and data starts from row 2.
🔄
Wrap query results with =CX.CONVERT.DATATYPE() to convert text numbers and dates into proper Excel values:
=CX.CONVERT.DATATYPE(CX.EXECUTE.SQL.QUERY(A1,"SELECT ...",""))
⚠
Removing a database from the Catalog does not delete the underlying database file or its data.
🔗
MySQL databases require a connection string set up via Manage Connections before they can be registered.