<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/use-r-with-excel-importing-and-exporting-data/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://datascienceplus.com/use-r-with-excel-importing-and-exporting-of-data/"> R Programming – DataScience+</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<ul></ul>
<p><strong>
<p>Categories</p>
<p></p></strong>
<ol>
<li><a rel="nofollow" href="https://datascienceplus.com/category/data-management/" title="Permanent Link toData Management" target="_blank">Data Management</a></li>
</ol>
<p><strong>
<p>Tags</p>
<p></p></strong>
<ol>
<li><a rel="nofollow" href="https://datascienceplus.com/tag/databases/" title="Permanent Link toDatabases" target="_blank">Databases</a></li>
<li><a rel="nofollow" href="https://datascienceplus.com/tag/import-data/" title="Permanent Link toImport Data" target="_blank">Import Data</a></li>
<li><a rel="nofollow" href="https://datascienceplus.com/tag/rstats/" title="Permanent Link toR Programming" target="_blank">R Programming</a></li>
<li><a rel="nofollow" href="https://datascienceplus.com/tag/tips-tricks/" title="Permanent Link toTips &amp; Tricks" target="_blank">Tips &amp; Tricks</a></li>
</ol>
<p>In this article, you learn how to connect R with Excel by importing and exporting data between the two programs. </p>
<h3>Excel: pros and cons</h3>
<p>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. </p>
<h3>Solution: Use R with Excel</h3>
<p>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. </p>
<p>The below sections presents these solutions with coding examples in R. First we look at how you import Excel into R:</p>
<h3>Import Excel spread sheet in R</h3>
<pre>
# Read R library
library(readxl)
# Import xlsx
MyExcelSheet &lt;- read.xlsx(file=&quot;/ExcelSheet.xlsx&quot;,sep = &quot;,&quot;, header=TRUE)  # read sheet
</pre>
<p>Now let us use the above import coding with an Excel sheet. We use this <a rel="nofollow" href="http://www.principlesofeconometrics.com/excel.htm" target="_blank">demand</a> data:</p>
<h3>Import Excel spread sheet in R with a dataset</h3>
<pre>
# Read R library
library(readxl)
# Import xlsx dataset
demand &lt;- read_excel(&quot;~/R work/DataScience+/Make R speak Excel/demand.xls&quot;)
head(demand)</pre>
<p>The above coding gives us the following table</p>
<pre>
 head(demand)
<em># 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
</em></pre>
<h3>Export Excel spread sheet in R</h3>
<p>Now it is time to Export Excel sheet from R into Excel:</p>
<h3>Export spread sheet from R to Excel</h3>
<pre>
# Read R library
library(readxl)
library(xlsx)
# Export xlsx sheet from R to Excel
write.xlsx(MyExcelSheet, &quot;/ExcelSheet.xlsx&quot;) # write sheet
</pre>
<p>Let us use the above export coding with the <a rel="nofollow" href="http://www.principlesofeconometrics.com/excel.htm" target="_blank">demand</a> excel sheet. First we do some data management in R:</p>
<h3>Export spread sheet from R to Excel with a dataset</h3>
<pre>
# Read R library
library(readxl)
library(xlsx)
# Import xlsx dataset
demand &lt;- read_excel(&quot;~/R work/DataScience+/Make R speak Excel/demand.xls&quot;)
# Data management in R
demand$psum &lt;- demand$p1 + demand$p2 + demand$p3
head(demand)
# Export sheet to Excel
write.xlsx(MyExcelSheet, &quot;/ExcelSheet.xlsx&quot;) # write sheet
</pre>
<p>The above coding gives us the following table</p>
<pre>
head(demand)
<em># 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.
</em></pre>
<p>Happy coding!</p>
<h3>References</h3>
<ol>
<li><a rel="nofollow" href="https://cran.r-project.org/web/packages/readxl/readxl.pdf" target="_blank">Using readxl in R – CRAN.R-project.org</a></li>
<li><a rel="nofollow" href="https://cran.r-project.org/web/packages/xlsx/xlsx.pdf" target="_blank">Using xlsx in R – CRAN.R-project.org</a></li>
</ol>
<p><strong>
<p>Related Post</p>
<p></p></strong>
<ol>
<li><a rel="nofollow" href="https://datascienceplus.com/combining-data-in-r-the-skill-of-merging-joining-and-stacking/" title="Permanent Link toCombining data in R: the skill of merging, joining, and stacking" target="_blank">Combining data in R: the skill of merging, joining, and stacking</a></li>
<li><a rel="nofollow" href="https://datascienceplus.com/efficient-data-management-and-sql-data-selection-in-r/" title="Permanent Link toEfficient data management and SQL data selection in R" target="_blank">Efficient data management and SQL data selection in R</a></li>
<li><a rel="nofollow" href="https://datascienceplus.com/essential-data-cleaning-for-ad-hoc-tasks-in-r/" title="Permanent Link toEssential data cleaning for ad-hoc tasks in R" target="_blank">Essential data cleaning for ad-hoc tasks in R</a></li>
<li><a rel="nofollow" href="https://datascienceplus.com/proteomics-data-analysis-2-3-data-filtering-and-missing-value-imputation/" title="Permanent Link toProteomics Data Analysis (2/3): Data Filtering and Missing Value Imputation" target="_blank">Proteomics Data Analysis (2/3): Data Filtering and Missing Value Imputation</a></li>
<li><a rel="nofollow" href="https://datascienceplus.com/clean-your-data-in-seconds-with-this-r-function/" title="Permanent Link toClean Your Data in Seconds with This R Function" target="_blank">Clean Your Data in Seconds with This R Function</a></li>
</ol>
	<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="https://datascienceplus.com/use-r-with-excel-importing-and-exporting-of-data/"> R Programming – DataScience+</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a rel="nofollow" href="https://www.r-project.org/" title="The R Project for Statistical Computing">R</a> news and <a rel="nofollow" href="https://www.r-bloggers.com/search/tutorial" title="R tutorials">tutorials</a> on topics such as: <a rel="nofollow" href="https://www.r-bloggers.com/search/data%20science" title="Data science">Data science</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data" title="Big Data">Big Data, <a rel="nofollow" href="https://www.r-users.com/" title="R jobs">R jobs</a>, visualization (<a rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2" title="ggplot and ggplot2 tutorials">ggplot2</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/boxplot" title="Boxplots using lattice and ggplot2 tutorials">Boxplots</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/map" title="Maps and gis">maps</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/animation" title="Animation in R">animation</a>), programming (<a rel="nofollow" href="https://www.r-bloggers.com/search/RStudio" title="RStudio IDE for R">RStudio</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/sweave" title="Sweave and literate programming">Sweave</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX" title="LaTeX in R">LaTeX</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/SQL" title="SQL and databases">SQL</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/eclipse" title="Eclipse IDE for R">Eclipse</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/git" title="git and github, Version Control System">git</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/hadoop" title="Large data in R using Hadoop">hadoop</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping" title="Web Scraping of google, facebook, yahoo, twitter and more using R">Web Scraping</a>) statistics (<a rel="nofollow" href="https://www.r-bloggers.com/search/regression" title="Regressions and ANOVA analysis tutorials">regression</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/PCA" title="principal component analysis tutorial">PCA</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/time+series" title="Time series">time series</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/trading" title="finance trading">trading</a>) and more...
<img width="1" alt="" src="http://feeds.feedburner.com/~r/RBloggers/~4/zyaGG9yK7-8" height="1"></a></div>