This page will walk you through the basics to get up and running with BERT. If you haven’t already done so, you can download an installer from our downloads page.
After you have installed, start Excel. You’ll see the Addins tab on the Excel ribbon menu (toolbar). Click the Addins tab and you’ll see a button for BERT:
Click the BERT Console button to open the console. The console includes a code editor and an integrated R shell. The shell is a standard R environment. It’s also where you can see errors or warnings (or anything you print) from your spreadsheet functions.
From the shell you can interact directly with Excel and your spreadsheet. For a quick example, open the “excelscripting.R” file in the console editor. This should already be open (we open it by default when you install), but if not, it’s in your Documents
directory, under BERT2/examples
.
Right click the code in the editor and select “Execute entire buffer”. This code will add Fischer’s iris data set to your spreadsheet. For more on interacting with Excel, see the page on the Excel scripting interface.
Next up is writing Excel functions. Switch back to the Excel window (you can close the console; closing it won’t erase or remove anything).
When BERT exports functions from R to Excel, it adds the prefix R.
(Rdot). This is done so there’s no conflict with internal Excel functions. For example, the startup file includes a function TestAdd
which just adds numbers together. When BERT loads this function into Excel, it’s called R.TestAdd
(RdotTestAdd).
You can use that function like any other spreadsheet function; just type =R.TestAdd(1,2,3)
into a spreadsheet cell. This function uses a threedots (…) argument, so it takes a variable number of arguments.
All the R functions in Excel are also listed in the Excel function wizard. Click the f(x)
button on the toolbar or press Shift+F3
. In the category box, select Exported R Functions.
Let’s add a new function to Excel! Open the console (click the BERT Console button on the Addins tab).
BERT loads functions from files in the startup folder. By default, the startup folder is Documents\BERT2\functions
. You can add new files to this folder, but for now just edit the sample file functions.R
. That file should already be open in the console editor, but if not you can open it using File > Open from the menu.
We’re going to write a function to get the Cholesky decomposition of a matrix. If you’re not familiar, it’s not that important; for this example, the how of writing a function is more important than the what. Suffice to say that this is difficult to do in Excel or VBA, but it’s already built in to R. So all we have to do is write some code to expose the internal method.
We’ll call the method Cholesky
. Add this code to the functions.R
file:
Cholesky < function(mat){
chol(mat);
}
Then save the file using Control+S or File > Save from the menu. BERT will automatically reload the file when you save it. You’ll see a note in the shell. If you made a mistake, you may see an error instead. In that case, doublecheck the function code and resave the file.
Here we’re calling chol
, R’s built in method for the Cholesky decomposition. The input is a matrix, and the output is a matrix. Because our new function is called Cholesky
, BERT will install it in Excel as R.Cholesky
.
The Cholesky function works on matrices that are symmetric and positivedefinite.
 >>  Decomposition  >> 

Fill in a 3x3 range in Excel with the data in the aboveleft table. Then select another 3x3 range for the result. With the range selected, enter the function =R.Cholesky()
and select your first matrix as the input. Then press Ctrl+Shift+Enter
to enter the function.
You have to press Ctrl+Shift+Enter
to enter the function as an Array function, because it returns a matrix. If you just press Enter
, it will only enter the function in one cell and you’ll see just one value. In that case, just reselect the range, enter the function, and press Ctrl+Shift+Enter
.
That’s all you need to do to get your R functions working in Excel. If you want to get a little more fancy, you can add some function documentation and use custom function categories.
This is all done with R attributes. We use two specific attributes: category and description.
The category attribute is just a string; this will be the category used in the Excel insert function dialog, instead of “Exported R Functions”. For example, we can change the category for the Cholesky function by adding the following code to functions.R
:
attr( Cholesky, "category" ) < "Linear Algebra";
If you switch back to Excel and click insert function (Shift+F3), you’ll see this function in the new category:
You can add function documentation with the description attribute. This attribute is a list. The first entry in the list is the description of the function itself. After that, use named arguments matching the parameter names to describe each parameter. For the Cholesky function, we want to add a general description, and then describe the mat parameter:
attr( Cholesky, "description" ) < list( "Cholesky Decomposition",
mat="Input matrix (must be positivedefinite)" );
Now when you use that function, the Excel function dialog will show the descriptions:
Note that Excel adds periods after everything, we can’t change that.
To learn more, see some example functions; read about talking to Excel from R; and check the rest of the documentation links at the top of the page.