How to Import Excel Files into R

In this tutorial, we will show you one of the easiest ways to import Excel (.xlsx and .xls) files into R using the readxl package.  

We will be working with RStudio, a program that makes it easier to work with R.

 

Install readxl

The readxl package isn’t installed on R by default, so you will need to install it if you haven’t done so yet.  Our recommendation is that you install the complete set of tidyverse packages, which includes readxl.

We install the tidyverse packages by typing the following in the RStudio console:

install.packages("tidyverse")

Select the enter key on your keyboard.  You might need to wait for a couple of minutes for the packages to be installed.

Import Your Excel File into R

One of the easiest ways to import Excel (.xlsx and .xls) files into R is outlined below.

In the top right panel of RStudio we ensure that the Environment tab is selected. 

Click Import Dataset and select From Excel…  from the drop-down menu as shown below:

 

This brings up the Import Excel Data window below.  Click the Browse button.

Browse to the Excel file you want to import and double-click on it.  Your Import Excel Data window should now look something like this:

Here we see:

(1) The file and path (or URL) of the Excel file to be imported into R.

(2) The Data Preview of this file.

(3) The name that R will assign to the file.  To change this, place your cursor in the Name field and edit it.

(4) The name of the Excel worksheet to be imported into R.  If the worksheet that you want to import is not the one displayed in Data Preview, you can select the correct one using the Sheet drop-down menu.

(5) Check this box if the first row of your worksheet contains the names of your variables.

(6) The R code for importing the Excel worksheet

(7) The Import button.

Click the Import button.  You should now seem something like the following in RStudio:

Here, we see:

(1) The first records of our imported data set.  You can use the scrollbar to view the rest of your data.

(2) Information about this data set.  tbl_dbf indicates that it has been imported as a type of data frame called a tibble.  It contains 30 observations of three variables.

(3) The R code that we generated when we imported our Excel file.

Reviewing Your Imported Data Set

Once you have imported your Excel file, it’s a good idea to review it in RStudio.  One way to do this is to check what is displayed in the top left window of RStudio as per note (1) in the last screenshot.

To get additional information, you can also display the first part of your data set by typing the following:

head (your.data)

Replace your.data with the name of your data frame in R.  Our data frame is called exam_scores, so we type:

head(exam_scores)

Select the enter key on your keyboard to execute this command.  By default, the head() command displays the first six records of your data frame, so you should see something like this:

You can see that our example data frame includes two data types:

• dbl: Numeric data.
• chr: character data in the form of text strings

Save Your Tibble Data Frame (Optional)

If you want to save your imported data set as a tibble data frame so that you can work with it in a future sessions of R or RStudio, you can do this by typing the following:

saveRDS(Rtibble, "savedtibble.rds")

Replace Rtibble with the name of your tibble data frame in R.   Ours is called exam_scores.

Replace savedtibble.rds with the your file name and the path for the location in which you want to save the file on your computer.  There are two important things to note here:

1. If you don’t specify a file path, your data frame will be saved in your "working directory".
2. File paths in R need to be formatted in a particular way – we need to replace the backwards slashes (\) that we typically see in Windows file paths with forward slashes (/) in R.   We want to save our data frame to the path C:\Users\user\OneDrive\Documents\R\Datasets with the file name exam_scores_tibble.rds so in R, this will be: C:/Users/user/OneDrive/Documents/R/Datasets/exam_scores_tibble.rds.

So, the command that we need to type in RStudio for this example is:

saveRDS(exam_scores, "C:/Users/user/OneDrive/Documents/R/Datasets/exam_scores_tibble.rds")

Select the enter key on your keyboard to save the data frame to the specified location on your computer.

***************

That’s it for this tutorial.  You should now be able to import Excel (.xlsx and .xls) files into R using RStudio and the readxl package.

***************