How to Automate Excel with R
Written by Matt Dancho on October 7, 2020
This article is part of a R-Tips Weekly, a weekly video tutorial that shows you step-by-step how to do common R coding tasks.
Let’s learn how to automate Excel with R, using openxlsx
and tidyquant
.

Automate Excel with R Tutorial
Using R to automate Excel is an awesome skill for automating your work (and life). Your company lives off Excel files. Why not automate them & save some time?
Here’s an Excel File you’re going to make in this tutorial from R. 👇
First, collect your data using tidyquant
, an awesome package for importing & working with Financial Data.
The previous code makes this Pivot Table (stock returns by year and symbol).
…And this stock chart over time.
Now for the magic - Let’s add the Pivot Table & Stock Plot to Excel! 🔥
- Initialize a workbook
- Create a worksheet
- Add the stock plot
- Add the Pivot Table
- Save the workbook
- Open the workbook (programmatically)
You’ve just automated creation of an Excel Workbook with R.
It’s magic! 💥 💥 💥
SETUP R-TIPS WEEKLY PROJECT
-
Sign Up to Get the R-Tips Weekly (You’ll get email notifications of NEW R-Tips as they are released): https://learn.business-science.io/r-tips-newsletter
-
Set Up the GitHub Repo: https://github.com/business-science/free_r_tips
-
Check out the setup video (https://youtu.be/F7aYV0RPyD0). Or, Hit Pull in the Git Menu to get the R-Tips Code
Once you take these actions, you’ll be set up to receive R-Tips with Code every week. =)
👇 Top R-Tips Tutorials you might like:
- mmtable2: ggplot2 for tables
- ggdist: Make a Raincloud Plot to Visualize Distribution in ggplot2
- ggside: Plot linear regression with marginal distributions
- DataEditR: Interactive Data Editing in R
- openxlsx: How to Automate Excel in R
- officer: How to Automate PowerPoint in R
- DataExplorer: Fast EDA in R
- esquisse: Interactive ggplot2 builder
- gghalves: Half-plots with ggplot2
- rmarkdown: How to Automate PDF Reporting
- patchwork: How to combine multiple ggplots
- Geospatial Map Visualizations in R
Want these tips every week? Join R-Tips Weekly.