R and Excel go together like macaroni and cheese. There’s no need to choose one over the other, as there are numerous packages and extensions that allow them to work together. Just because you have an xlsx file doesn’t mean you can’t analyze it with R. Likewise, you can write R code to populate and manipulate Excel spreadsheets.

Today you’ll get a grasp of two tools used to manage communication between R and Excel. These are readxl and Bert. The first one is an R package used to read and manipulate Excel files, while the latter is a free extension that brings R functionality to Excel.

Psst – do you think Google Sheets is superior? You can easily connect R to Google Sheets with googlesheets4.

Table of contents:

• Readxl: R Package for Reading Excel Files
• Bert: Connect Excel with R
• Summary of R and Excel

Readxl: R Package for Reading Excel Files

The readxl package is among the easiest ones you can use to communicate between R and Excel. It’s different from the alternative R packages because it has no external dependencies, meaning it’s easy to install and use on all operating systems.

It supports both the .xls and .xslx file formats. So if you’re working on some legacy spreadsheets, there’s no need to convert them to a more modern, XML-based, file format beforehand.

You can install the package directly from CRAN using the install.packages("readxl") command, and then import it as any other library.

It comes bundled up with many example Excel files you can use. To list them all, call the readxl_example() function. We’ll use the datasets.xlsx file in this section. It contains many datasets you’re likely already familiar with – such as Iris and Mtcars. All are available as separate sheets.

To list all sheets of a given Excel file, use the following function call:

Image 1 – Sheets available in an Excel file

As mentioned before, each of the four datasets is stored on an individual sheet. You can read a specific sheet by specifying the sheet parameter:

Image 2 – Mtcars dataset from an example Excel file

But here’s the best part – you can specify the row and column ranges in Excel-like syntax. The range parameter is used to specify, well, the range from which you want to extract data. The value of range = "B1:E5" means you want to extract data from the second to the fifth column, spanning from the row 1 to 5:

Image 3 – Specifying range when reading Excel files

Let’s take the whole thing a step further and omit the sheet parameter – you can specify everything in range in a true Excel manner:

Image 4 – Specifying sheet and range in a single parameter

Long story short, readxl is a truly powerful R package for reading Excel files. It allows you to read both modern and legacy Excel spreadsheets, and manipulate them using an Excel-like syntax. But how can you go the other way around? Let’s explore that next.

Bert: Connect Excel with R

Bert is a free tool for connecting Excel with R. It’s designed to run (user-defined) R functions in spreadsheets. The only thing you have to do is write the function – Bert handles everything else for you.

But here’s a catch: At this time, Bert is available only for Windows. We’re running Windows 11 as an emulation on M1 Macbook (ARM) and experiencing unexpected crashes. These are likely due to the ARM architecture Bert didn’t expect.

Now, Bert isn’t the only option you have. Similar solutions exist, such as RExcel, but we haven’t managed to find a download link due to their website being down. Adding insult to injury, RExcel is also available for Windows only.

To get started with Bert, head over to their download page and download the installed. It goes without saying, but you should have Excel and R installed already.

Once you have both Excel and Bert console running, use the following code to populate cells in the range from A1 to C4 with a constant value:

It feels unnatural at first, but you’ve just used R code to populate an Excel spreadsheet!

From here, you can do much of what you’d normally do in R. For example, the code snippet below plots a histogram of the normal distribution:

The true power of Bert lies in using the custom R function in Excel. To do so, open an R file located under C:\Users\username\Documents\BERT2\functions\functions.R and add your custom functions. For example, the one we added sums two numbers:

Image 7 – Adding custom R functions to Bert

Bert will automatically link R code to Excel – you don’t have to lift a finger. Once you save the file, use the function by writing =R. in any Excel cell:

Summary of R Programming and Excel

And that’s how you can connect Excel and R with Bert. But should you? Some tech-savvy people find Excel obsolete.

Do you think Excel is obsolete? Consider these two alternatives from R and Python.

Either way, reading and manipulating Excel files is a daily task of any R data analyst. The readxl package comes with everything you need to make communication as easy as possible. If you’re an Excel expert and just starting with R, Bert might be a good option to ease the transition period.

What are your thoughts on these R and Excel cross-communication options? Let us know in the comment section below.

The post R and Excel: How to Combine the Best of Both Worlds appeared first on Appsilon | Enterprise R Shiny Dashboards.