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.
▼ 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.
Download and install the add-in
Run the MSI installer and follow the on-screen prompts. Once complete, open Excel and the CakeXL tab will appear in the ribbon automatically.
🔑 License
Once the add-in is installed, activate your license by clicking Activate License ribbon button and follow the steps. You will get 30 day free access.
■ Ribbon Buttons
The CakeXL ribbon tab contains two groups of buttons:
Info Group
- VersionShows the installed version
- Remaining daysShows the number of days remaining on your current license.
- Get CPU CountShows the number of logical CPU cores on your machine.
- Activate LicenseOpens the license activation dialog.
- Upload logsFor troubleshooting issues to the supporting team
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:
- Database Path
- Created date
- 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
- + New Database Register a new DuckDB or MySQL database.
- Manage Connections Set up MySQL connection strings.
- Remove
Remove a database from the catalog.
Note: This does not delete your underlying data file.
- Link to Cell Write a
=CX.CREATE.DATABASE.CONNECTION(...)formula into a chosen Excel cell. - Load CSV Import a CSV file into a table.
- Load TXT Import a TXT file into a table.
- Load XLSX Import a XLSX file into a table.
- Load CSV (Datatype) Import a CSV file into a table by defining datatypes.
- Load TXT (Datatype) Import a TXT file into a table by defining datatypes.
- Load XLSX (Datatype) Import a XLSX file into a table by defining datatypes.
✎ 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
- Database Dropdown to select which database to query.
- Save / Ctrl + S Save current sql script in the editor
- Import .sql Import .sql files
- Add to Excel Insert the current query as a live formula into the selected Excel cell. Disabled until a query returns results.
- Run / F5 Execute the SQL query.
- Ask AI Open section to send English sentence and get the results into the editor
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
- Select a cell in your Excel sheet first.
- Run your query in the SQL IDE.
- Click Add to Excel.
- 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.SUM(dbRef,"table","sum_col",[overlook_error]) |
Sums all values in sum_col. Optional overlook_error (default TRUE): FALSE blocks text columns instead of converting them. |
=CX.AVERAGE(dbRef,"table","avg_col",[overlook_error]) |
Returns the average (arithmetic mean) of all numeric values in avg_col. Optional overlook_error (default TRUE): FALSE blocks text columns. |
=CX.SUMIF(dbRef,"table","sum_col","criteria","criteria_col",[overlook_error]) |
Sums values in sum_col where criteria_col matches criteria. Supports = > >= < <= <> operators and * ? wildcards. Optional overlook_error (default TRUE): FALSE blocks text columns. |
=CX.COUNTIF(dbRef,"table","criteria_col","criteria") |
Counts rows where criteria_col matches criteria. Supports operators and wildcards. |
=CX.AVERAGEIF(dbRef,"table","criteria_col","criteria","avg_col",[overlook_error]) |
Averages values in avg_col where criteria_col matches criteria. Supports operators and wildcards. Optional overlook_error (default TRUE): FALSE blocks text columns. |
=CX.MIN(dbRef,"table","min_col",["criteria_col"],["criteria"],[overlook_error]) |
Returns the minimum value in min_col. Optional filter: supply criteria_col and criteria. Optional overlook_error (default TRUE): FALSE blocks text columns. |
=CX.MAX(dbRef,"table","max_col",["criteria_col"],["criteria"],[overlook_error]) |
Returns the maximum value in max_col. Optional filter: supply criteria_col and criteria. Optional overlook_error (default TRUE): FALSE blocks text columns. |
=CX.PERCENTILE(dbRef,"table","col",percentile,["criteria_col"],["criteria"],[overlook_error]) |
Returns a percentile of col (0 = min, 0.5 = median, 1 = max), matching Excel PERCENTILE.INC. Works on both DuckDB and MySQL. Optional filter and overlook_error 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. An optional trailing TRUE/FALSE sets overlook_error (default TRUE; FALSE blocks text columns). |
=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. An optional trailing TRUE/FALSE sets overlook_error (default TRUE; FALSE blocks text columns). |
| Data Transformation | |
=CX.CONVERT.DATATYPE(range)=CX.CONVERT.DATATYPE(,dbRef,"table",["output_table"]) |
Two modes: Excel range mode — pass a range (e.g. A1:Z100) or a single value: converts text values to native Excel types (numbers, dates, booleans). Wrap query results with this to get proper Excel types.Database table mode — pass db_ref + table_name (omit the first arg): works on both DuckDB and MySQL. Auto-detects text columns where >50% of values are numeric and casts them to a numeric type. The result is stored as a new table that is dropped and recreated on each run. Optional output_table name — defaults to tableName_typed and must differ from the source table. 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
-
Open Excel The CakeXL tab appears in the ribbon automatically.
-
Create a Database Click Catalog → + New Database Enter a name (e.g. SalesDB) and click Create.
-
Use the formula reference
=CX.CREATE.DATABASE.CONNECTION(...)formula is written into a cell and returns a reference string (e.g."SalesDB"). -
Query with that reference Use the cell (e.g. A1) in other formulas:
=CX.EXECUTE.SQL.QUERY(A1, "SELECT * FROM orders", "") -
Open the SQL IDE Click SQL IDESelect your database from the dropdown, type a query, and press F5 to run it.
-
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.
-
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.