# Use R with Excel: Importing and Exporting Data

October 16, 2018
By

(This article was first published on R Programming – DataScience+, and kindly contributed to R-bloggers)

Categories

Tags

In this article, you learn how to connect R with Excel by importing and exporting data between the two programs.

### Excel: pros and cons

Excel is still very popular among companies and organizations. One of the main advantages in a spreadsheet is ease of providing the user with a rapid overview of a dataset, using visualizations and tables. On the other hand, given the incredible growth of data, the spreadsheet really lacks efficient and agile solutions for data management and data cleaning.

### Solution: Use R with Excel

Fortunately, it is possible for the data scientist/analyst to empower the advantages of both Excel and R. This can be done with some value adding and efficient packages for R. What Excel lack in data management and data cleaning – R is an excellent and efficient solution for these tasks. Furthermore, it is also possible to create the analytics in R and export the result into Excel for reporting. Basically, you have two great solutions (1): You can import Excel datasets into R for data management, data cleaning and analytics (2): You can export analytical results and clean data from R into Excel for further analytics or presentation.

The below sections presents these solutions with coding examples in R. First we look at how you import Excel into R:

### Import Excel spread sheet in R

```# Read R library
# Import xlsx
```

Now let us use the above import coding with an Excel sheet. We use this demand data:

### Import Excel spread sheet in R with a dataset

```# Read R library
# Import xlsx dataset
demand <- read_excel("~/R work/DataScience+/Make R speak Excel/demand.xls")

The above coding gives us the following table

``` head(demand)
# A tibble: 6 x 7
p1    p2    p3     y    q1    q2    q3
1 129.   76.6  59.6  92.8 128.  140.  129.
2 156.  186.  124.   69.4 133.   23.2  37.7
3 111.  100.   36.6 103.   98.3  59.4 295.
4  55.1 240.   34.8 145.  374    55.7 386.
5 156   196.  134.   80.2  50.3 116.   44.8
6  92.2 150.  129.  110   194   126.   66.6
```

### Export Excel spread sheet in R

Now it is time to Export Excel sheet from R into Excel:

### Export spread sheet from R to Excel

```# Read R library
library(xlsx)
# Export xlsx sheet from R to Excel
write.xlsx(MyExcelSheet, "/ExcelSheet.xlsx") # write sheet
```

Let us use the above export coding with the demand excel sheet. First we do some data management in R:

### Export spread sheet from R to Excel with a dataset

```# Read R library
library(xlsx)
# Import xlsx dataset
demand <- read_excel("~/R work/DataScience+/Make R speak Excel/demand.xls")
# Data management in R
demand\$psum <- demand\$p1 + demand\$p2 + demand\$p3
# Export sheet to Excel
write.xlsx(MyExcelSheet, "/ExcelSheet.xlsx") # write sheet
```

The above coding gives us the following table

```head(demand)
# A tibble: 6 x 8
p1    p2    p3     y    q1    q2    q3  psum
1 129.   76.6  59.6  92.8 128.  140.  129.   265.
2 156.  186.  124.   69.4 133.   23.2  37.7  465.
3 111.  100.   36.6 103.   98.3  59.4 295.   247.
4  55.1 240.   34.8 145.  374    55.7 386.   330.
5 156   196.  134.   80.2  50.3 116.   44.8  486.
6  92.2 150.  129.  110   194   126.   66.6  371.
```

Happy coding!

### References

Related Post

R-bloggers.com offers daily e-mail updates about R news and tutorials on topics such as: Data science, Big Data, R jobs, visualization (ggplot2, Boxplots, maps, animation), programming (RStudio, Sweave, LaTeX, SQL, Eclipse, git, hadoop, Web Scraping) statistics (regression, PCA, time series, trading) and more...