• Copying data from Excel and import into R
• Importing Excel files into R using readxl package
• Importing Excel files using xlsx package

1. Xl Spreadsheet Viewer

The current default file extension for an Excel file is XLSX. Prior to Excel 2007, the default file extension was XLS. The main difference between the two is that XLSX is an XML-based open file format and XLS is a proprietary Microsoft format. Collaborate for free with an online version of Microsoft Excel. Save spreadsheets in OneDrive. Share them with others and work together at the same time.

Previously, we described the essentials of R programming and some best practices for preparing your data. We also provided quick start guides for reading and writing txt and csv files using R base functions as well as using a most modern R package named readr, which is faster (X10) than R base functions.

In this article, you’ll learn how to read data from Excelxls or xlsx file formats into R. This can be done either by:

• copying data from Excel
• using readxl package
• or using xlsx package

1. Launch RStudio as described here: Running RStudio and setting up your working directory

2. Prepare your data as described here: Best practices for preparing your data

On Windows system

1. Open the Excel file containing your data: select and copy the data (ctrl + c)

2. Type the R code below to import the copied data from the clipboard into R and store the data in a data frame (my_data):

On Mac OSX system

1. Select and copy the data (Cmd + c)

2. Use the function pipe(pbpaste) to import the data you’ve copied (with Cmd + c):

The readxl package, developed by Hadley Wickham, can be used to easily import Excel files (xls|xlsx) into R without any external dependencies.

Using readxl package

The readxl package comes with the function read_excel() to read xls and xlsx files

1. Read both xls and xlsx files

The above R code, assumes that the file “my_file.xls” and “my_file.xlsx” is in your current working directory. To know your current working directory, type the function getwd() in R console.

• It’s also possible to choose a file interactively using the function file.choose(), which I recommend if you’re a beginner in R programming:

If you use the R code above in RStudio, you will be asked to choose a file.

2. Specify sheet with a number or name

3. Case of missing values: NA (not available). If NAs are represented by something (example: “—”) other than blank cells, set the na argument:

The xlsx package, a java-based solution, is one of the powerful R packages to read, write and formatExcel files.

Using xlsx package

There are two main functions in xlsx package for reading both xls and xlsx Excel files: read.xlsx() and read.xlsx2() [faster on big files compared to read.xlsx function].

The simplified formats are:

• file: file path
• sheetIndex: the index of the sheet to be read
• header: a logical value. If TRUE, the first row is used as column names.

Example of usage:

Read more

Read more about for reading, writing and formatting Excel files:

• Read Excel files using readxl package: read_excel(file.choose(), sheet = 1)

• Read Excel files using xlsx package: read.xlsx(file.choose(), sheetIndex = 1)

• Previous chapters
• Next chapters

This analysis has been performed using R (ver. 3.2.3).

Recommended for You!

Recommended for you

This section contains best data science and self-development resources to help you on your path.

Coursera - Online Courses and Specialization

Data science

• Course: Machine Learning: Master the Fundamentals by Standford
• Specialization: Data Science by Johns Hopkins University
• Specialization: Python for Everybody by University of Michigan
• Courses: Build Skills for a Top Job in any Industry by Coursera
• Specialization: Master Machine Learning Fundamentals by University of Washington
• Specialization: Statistics with R by Duke University
• Specialization: Software Development in R by Johns Hopkins University
• Specialization: Genomic Data Science by Johns Hopkins University

Popular Courses Launched in 2020

• Google IT Automation with Python by Google
• AI for Medicine by deeplearning.ai
• Epidemiology in Public Health Practice by Johns Hopkins University
• AWS Fundamentals by Amazon Web Services

Trending Courses

• The Science of Well-Being by Yale University
• Google IT Support Professional by Google
• Python for Everybody by University of Michigan
• IBM Data Science Professional Certificate by IBM
• Business Foundations by University of Pennsylvania
• Introduction to Psychology by Yale University
• Excel Skills for Business by Macquarie University
• Psychological First Aid by Johns Hopkins University
• Graphic Design by Cal Arts

Books - Data Science

Our Books

• Practical Guide to Cluster Analysis in R by A. Kassambara (Datanovia)
• Practical Guide To Principal Component Methods in R by A. Kassambara (Datanovia)
• Machine Learning Essentials: Practical Guide in R by A. Kassambara (Datanovia)
• R Graphics Essentials for Great Data Visualization by A. Kassambara (Datanovia)
• GGPlot2 Essentials for Great Data Visualization in R by A. Kassambara (Datanovia)
• Network Analysis and Visualization in R by A. Kassambara (Datanovia)
• Practical Statistics in R for Comparing Groups: Numerical Variables by A. Kassambara (Datanovia)
• Inter-Rater Reliability Essentials: Practical Guide in R by A. Kassambara (Datanovia)

Others

• R for Data Science: Import, Tidy, Transform, Visualize, and Model Data by Hadley Wickham & Garrett Grolemund
• Hands-On Machine Learning with Scikit-Learn, Keras, and TensorFlow: Concepts, Tools, and Techniques to Build Intelligent Systems by Aurelien Géron
• Practical Statistics for Data Scientists: 50 Essential Concepts by Peter Bruce & Andrew Bruce
• Hands-On Programming with R: Write Your Own Functions And Simulations by Garrett Grolemund & Hadley Wickham
• An Introduction to Statistical Learning: with Applications in R by Gareth James et al.
• Deep Learning with R by François Chollet & J.J. Allaire
• Deep Learning with Python by François Chollet

Xl Spreadsheet Viewer