Jump to content

How to use R inside Microsoft Excel

0
  adfm's Photo
Posted Jan 20 2010 11:25 AM

Excel can be a safe comfort zone for some users, but sometimes you need access to more robust tools like R. Thankfully there's RExcel to bridge the gap. In this excerpt from Joseph Adler's R in a Nutshell you'll learn how to get safely up and running with RExcel.


If you’re familiar with Microsoft Excel, or if you work with a lot of data files in Excel format, you might want to run R directly from inside Excel. The RExcel software lets you do just that (on Microsoft Windows systems). You can find information about this software at http://rcom.univie.ac.at/. This site also includes a single installer that will install R plus all the other software you need to use RExcel.

If you already have R installed, you can install RExcel as a package from CRAN. The following set of commands will download RExcel, configure the RCOM server, install RDCOM, and launch the RExcel installer:

> install.packages("RExcelInstaller", "rcom", "rsproxy")

> # configure rcom

> library(rcom)

> comRegisterRegistry()

> library(RExcelInstaller)

> # excecute the following command in R to start the installer for RDCOM

> installstatconnDCOM()

> # excecute the following command in R to start the installer for REXCEL

> installRExcel()

Follow the prompts within the installer to install RExcel.

After you have installed RExcel, you will be able to access RExcel from a menu item. If you are using Excel 2007, you will need to select the “Add-Ins” ribbon to find this menu as shown in Figure 2.4. To use RExcel, first select the R Start menu item. As a simple test, try doing the following:

  1. Enter a set of numeric values into a column in Excel (for example, B1:B5).

  2. Select the values you entered.

  3. On the RExcel menu, go to the item “Put R Var” > “Array.”

  4. A dialog box will open, asking you to name the object that you are creating in Excel. Enter “v” and press the Enter key. This will create an array (in this case, just a vector) in R with the values that you entered with the name v.

  5. Now, select a blank cell in Excel.

  6. On the RExcel menu, go to the item “Get R Value” > “Array.”

  7. A dialog box will open, prompting you to enter an R expression. As an example, try entering (v - mean(v)) / sd(v). This will rescale the contents of v, changing the mean to 0 and the standard deviation to 1.

  8. Inspect the results that have been returned within Excel.

Figure 2.4. Accessing RExcel in Microsoft Excel 2007

Attached Image

For some more interesting examples of how to use RExcel, take a look at the Demo Worksheets under this menu. You can use Excel functions to evaluate R expressions, use R expressions in macros, and even plot R graphics within Excel.

R in a Nutshell

Learn more about this topic from R in a Nutshell.

R is rapidly becoming the standard for developing statistical software, and R in a Nutshell provides a quick and practical way to learn this increasingly popular open source language and environment. You'll not only learn how to program in R, but also how to find the right user-contributed R packages for statistical modeling, visualization, and bioinformatics.

See what you'll learn


2 Replies

0
  bjbreitling's Photo
Posted Jul 09 2012 10:11 AM

When I run the first command: install.packages("RExcelInstaller", "rcom", "rsproxy")
I get the following error: Warning in install.packages("RExcelInstaller", "rcom", "rsproxy") :
'lib = "rcom"' is not writable
Error in install.packages("RExcelInstaller", "rcom", "rsproxy") :
unable to install packages

What Should I do?

I had to Open Server 01 StatConnector Test under the start menu and click Start R.
0
  Ruchi Yadav's Photo
Posted Apr 05 2013 02:35 AM

Hi,
I am Using Windows 7, 32 bit Machine and RExcel Installer is not installing into My R. The Version which I am using of R is R 2.15.1.
It gives the following Error

install.packages('RExcelInstaller','rcom','rsproxy')
Warning in install.packages("RExcelInstaller", "rcom", "rsproxy") :
'lib = "rcom"' is not writable
Error in install.packages("RExcelInstaller", "rcom", "rsproxy") :
unable to install packages


kindly Please help me out of this. Please suggest some ways to install it. Its Really very Urgent.
Thanks & Regards
Ruchi