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.
4. At the prompt, paste this text:
This will automatically start the installation process.
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:
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.
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:
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:
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:
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.
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:
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:
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:
These are basic examples to get you started. Learning RExcel will give you another tool for the analysis work you do every day.