How to Automate Excel with R

Written by Matt Dancho


Your company lives off them… Excel files. Why not automate them & save some time? Here’s an Excel File you’re going to make in this tutorial from R. Let me show you how to automate Excel with R in under 5-minutes, using openxlsx and tidyquant.

Excel Spreadsheet

Automating Excel from R

R-Tips Weekly

This article is part of R-Tips Weekly, a weekly video tutorial that shows you step-by-step how to do common R coding tasks. Pretty cool, right?

Here are the links to get set up. 👇

Video Tutorial

I have a companion video tutorial that shows even more cool automation stuff (plus mistakes to avoid).

Automate Excel with R Tutorial

Here’s an Excel File you’re going to make in this tutorial from R. 👇

Excel Spreadsheet


Step 1: Collect, Prepare Data & Plots

First, collect your data using tidyquant, an awesome package for importing & working with Financial Data.

Import Data

Get the code.


Pivot Table Data

The previous code makes this Pivot Table (stock returns by year and symbol).

Import Data

Time Series Stock Performance Plots

Here’s the stock performance plot from the previous code.

Plot Data


Step 2: Automate R to Excel

Now for the magic - Let’s add the Pivot Table & Stock Plot to Excel! 🔥

  1. Initialize a workbook
  2. Create a worksheet
  3. Add the stock plot
  4. Add the Pivot Table
  5. Save the workbook
  6. Open the workbook (programmatically)

Create Excel Workbook with R

Get the code.

Excel Output (.xlsx)

You’ve just automated creation of an Excel Workbook with R.

Excel Workbook

💡 Conclusions

You learned how to use the openxlsx library to create automated reports using R and Excel together. Great work! But, there’s a lot more to becoming a Business Scientist.

If you’d like to become a Business Scientist (and have an awesome career, improve your quality of life, enjoy your job, and all the fun that comes along), then I can help with that.

My Struggles with Learning Data Science

It took me a long time to learn how to apply data science to business. And I made a lot of mistakes as I fumbled through learning R.

I specifically had a tough time navigating the ever-increasing landscape of tools and packages, trying to pick between R and Python, and getting lost along the way.

If you feel like this, you’re not alone.

In fact, that’s the driving reason that I created Business Science and Business Science University (You can read about my personal journey here).

What I found out is that:

  1. Data Science does not have to be difficult, it just has to be taught from a business perspective
  2. Anyone can learn data science fast provided they are motivated.

How I can help

If you are interested in learning R and the ecosystem of tools at a deeper level, then I have a streamlined program that will get you past your struggles and improve your career in the process.

It’s my 5-Course R-Track System. It’s an integrated system containing 5 courses that work together on a learning path. Through 8 projects, you learn everything you need to help your organization: from data science foundations, to advanced machine learning, to web applications and deployment.

The result is that you break through previous struggles, learning from my experience & our community of 2653 data scientists that are ready to help you succeed.

Ready to take the next step? Then let’s get started.

Join My 5-Course R-Track Program
(Become A 6-Figure Data Scientist)