Installing and Using RExcel

By Barry Nagle, Managing Partner/Senior Researcher, Evaluation and Action Research Associates  

R has become my primary statistical analysis tool. It is powerful, cost-effective and gives experienced users the ability to perform analyses without having to purchase additional programs. Microsoft® Excel, however, is still a primary data management tool for many researchers, including me. Many basic analyses can be efficiently performed with Excel rather than having to work through the R interface and writing code. There is an answer to having to use multiple programs. An Excel Add-In that combines power of R with Excel’s efficiency is available. Erich Neuwirth developed RExcel which enables users to access R through Excel. 

This Tech Tip will review how to install RExcel and provide a basic analysis example. But first, there are a couple of important technology-related notes. RExcel functions only with 32-bit Excel versions. The steps outlined here incorporated RExcel with Excel 2010 on a computer running Windows 7. 

This developer’s website is one of many that outlines the installation process in an effective way. The site also has direct links to the required downloads.  

Follow the steps below:

1. Install R if it is not already installed. This is a very easy process and is similar to any software installation. It is useful to create an R desktop shortcut to ease the RExcel installation process.  

2. Download and install the Download and install the statconn DCOM server. 

3. Right-click on the R shortcut and select “Run as Administrator.” The graphic shows how this displays in Windows 7.

1tt42014.JPG
4. At the prompt, paste this text:

           install.packages(c("rscproxy","rcom"),repos="http://rcom.univie.ac.at/download",lib=.Library)
           library(rcom)
           comRegisterRegistry() 

This will automatically start the installation process. 

2tt42014.JPG

5. Close R: This is not specified in the directions outlined on the installation site. I have found, however, that this is a good habit to get into when installing any software. Not doing this, though, will not impact your installation process.Close R: This is not specified in the directions outlined on the installation site. I have found, however, that this is a good habit to get into when installing any software. Not doing this, though, will not impact your installation process. 

6. Download Download RExcel 3.2.14: (This is the latest version) 

7. Once again open R by right-clicking and selecting “Run as administrator.”Once again open R by right-clicking and selecting “Run as administrator.” 

8. Install R Commander by pasting this at the prompt:Install R Commander by pasting this at the prompt: 

install.packages(c("RcmdrPlugin.mosaic","ENmisc"),
     lib=.Library, dependencies=TRUE) 

9. Install the book example files using the link provided on the website listed above. This is not necessary but it will give you a lot of samples within Excel that can help you learn how to use RExcel.

There is one additional step you need to take when you use Excel 2010. When you are installing RExcel, you will receive a warning.

In Excel 2010, navigate to File -> TrustCenter ->Trust Center Settings ->Macro Settings and check the box next to "Trust access to the VBA Project object model." Then close Excel. In R, issue the "installRthroughExcel()"command again.

Using RExcel

Now that you have completed the process above, the fun part is using the add-in. After you install the program, you will see a shortcut on your desktop that looks like this:

3tt42014.JPG

When you double-click on the icon, Excel will open. In Excel, click on the Add-Ins tab and you will see RExcel with an arrow that will open Menu Commands. What you see when you click on the arrow is displayed on the left. Also displayed is the Demo Worksheets menu on the right:

4tt42014.JPG

Note that you do not have to click on the desktop shortcut. You can, instead, just open Excel. RExcel will be available on the add-in tab.

There are simple and complex ways to use R within Excel. Knowledgeable Excel users are familiar with entering formulas to complete a calculation or other action. For example, if you want to find the sine of a number, the Excel formula is =sin(cell reference). If you want to use R, the worksheet function is =Rapply(“sin”, cell reference). In this case, both methods return .249389 as the sine of 54032. RExcel, of course, can do many more complex things that are more efficient than Excel formulas.

A more complex example would be linear regression. Here is some sample data that has study hours and final grades:

5tt42014.JPG

On the Excel sheet, highlight the data. Click on the Add-Ins tab and then click on the arrow next to RExcel. Select "Put R Var" and then select "Dataframe." When you do this, a dialog box will display. Enter the name you would like to give your dataframe and click the “with rownames” box if you selected this when you selected the data. Then click OK.

6tt42014.JPG

To prepare to do the analysis, click on the RCommander option on the RExcel menu. Then click on “With Excel Menus.” This is what will display, although you may have to select the dataset:

7tt42014.JPG

After the dataset is active, click on Statistics ​-> Fit Model -> Linear Regression. Doing this will display a box where you can select the Respone variable and the Explanatory variable:

8tt42014.JPG

For this example, Grade is our response variable and Study.Hours is our explanatory variable. After clicking OK, the R Commander will display the R Script while the Output box will display the linear regression model results. The results will display like this:

9tt42014.JPG
These are basic examples to get you started. Learning RExcel will give you another tool for the analysis work you do every day.

 

 

 

 Comments

 
To add a comment, Sign In
Total Comments: 8
 
Denise posted on 4/10/2014 9:52 AM
Wow, this is a great reference source.
Terry posted on 4/10/2014 10:18 AM
My two favorite programs linked together. Since we are starting an intense data analysis project for our system this is going to be invaluable.
Julia posted on 4/10/2014 10:37 AM
This looks like a very useful add-in and simple to use (even though it can do complex things). I am definitely going to try this out! Thanks for sharing.
Jeffrey posted on 4/10/2014 11:28 AM
I look forward to trying this. I had problems installing on my Excel 2013 32bit system. It showed up in the available addins but I couldn't find a menu. I went to the start menu to the new statconn menu line and then Rexcel, there was an 'activate Rexcel2013addin' option. Running that seemed to fix the problem. It is now available in the add-ins menu.
Steve posted on 4/10/2014 12:44 PM
R is becoming an increasingly popular tool for data analysis, and if it can integrate this seamlessly with Excel then perhaps even more offices will convert. This is excellent. Definitely something to explore.
Shabnam posted on 4/10/2014 12:51 PM
Wow, Excel has come a long way. I know you could import SAS data with Excel Add-in and now doing the same with R is great.
Gary posted on 4/10/2014 4:33 PM
Great tip, Barry. I can see this as a good solution for a lot of people who want to do statistics in Excel. It's also a good way to gradually get one's feet wet with R.
Mary Jo posted on 4/11/2014 11:50 PM
I didn't know that R worked with Excel. Great tip!!