How to Import an Excel File into R: 6 Easy Steps

In this lesson, we will learn how to import an Excel file into R Studio in six easy-to-follow steps that would take just a few minutes to complete.

We will handle both .xls and .xlsx file formats by using direct coding methods alongside R Studio’s intuitive graphical user interface (GUI).

If your dataset is CVS (comma-separated values) file, follow the guide on How To Import a CSV File in R instead.

Prerequisites

Before you start, make sure you have installed the following on your computer:

  1. R: You can download it from the official R website.
  2. R Studio: This can be downloaded from the official R Studio website.
  3. Follow the easy guide to install R and R Studio on Windows, macOS, Linux, and UNIX.

Once done, open up R Studio, and we can begin.

Import an Excel File Into R Using GUI

The easiest way to import an Excel file into R is via RStudio Graphical User Interface. Here are the steps you need to follow:

Step 1: Open R Studio

First, launch R Studio on your computer.

Step 2: Click on ‘Import Dataset’

Go to the Environment pane in R Studio, typically on the upper right-hand side. You will see an Import Dataset dropdown menu – as shown in the image below. Click on it.

Import dataset in R. Source: uedufy.com

Step 3: Choose ‘From Excel…’

In the dropdown menu, select the From Excel… option. This will open a file explorer.

Import an Excel file into R. Source: uedufy.com

Step 4: Select Your File

On the Import Excel Data window, click the Browse button to navigate to your Excel file in the file explorer, select it, and click Open.

Step 5: Configure the Import Options

R Studio will show you a data import window where you can customize how R Studio reads your Excel file. You can set options such as:

  • Which sheets to import
  • Whether the first row contains the column names
  • Whether to convert text to factors
  • The maximum number of rows to read in, among others.

Step 6: Click on ‘Import’

Once you’ve adjusted the settings to your satisfaction, click on the Import button.

Adjust settings for importing Excel dataset in R. Source: uedufy.com

Your data will be imported into R Studio and will appear as a data frame in your environment. You can now work with your Excel data in R.

This GUI approach is a great way for beginners to start working with data in R Studio. However, as you become more advanced, you might find the code-based approach more flexible and powerful, particularly for reproducibility and automation of your data analysis workflows.

Import an Excel File Into R Using Code

As you become more advanced in R, you will likely prefer to import Excel files in R via code. Lets learn how to do that next.

Step 1: Install Necessary Packages

Several packages in R can import Excel files, but for this tutorial, we will use the readxl and writexl packages. These packages can handle both .xls and .xlsx formats.

rCopy codeinstall.packages("readxl")
install.packages("writexl")

The install.packages() function downloads and installs the package from CRAN (the Comprehensive R Archive Network).

Step 2: Load the Required Package

After installing, we must load the readxl package into our R environment. We can do this using the library() function:

rCopy codelibrary(readxl)

Step 3: Set Your Working Directory

In R, it’s important to set the working directory to the location where your Excel file is located. This can be done with the setwd() function.

rCopy codesetwd("/path/to/your/directory")

Replace "/path/to/your/directory" with the actual path to your directory.

Step 4: Import the Excel File

Now, we are ready to import our Excel file. We’ll use the read_excel() function for this. Let’s say our file is named ‘data.xlsx‘.

rCopy codedata <- read_excel("data.xlsx")

In this code, ‘data.xlsx‘ is the name of the Excel file we’re importing, and data is the name of the R data frame where we’re storing the contents of the Excel file.

Step 5: Verify the Data

To make sure your data was imported correctly, you can view the first few rows with the head() function or the entire dataset with the View() function:

rCopy codehead(data)
View(data)

Step 6: Saving the Data

If you want to write the data back to Excel, use the write_xlsx() function from the writexl library.

rCopy codelibrary(writexl)
write_xlsx(data, "data_modified.xlsx")

This will save your data frame ‘data’ to an Excel file named ‘data_modified.xlsx‘.

That’s it! You have now learned how to import an Excel file into R using R Studio, manipulate it, and save it back to an Excel file. Remember that working effectively with R comes with practice.