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.

Step 1: Watch my Free 40-Minute Webinar

Learning data science on your own is hard. I know because IT TOOK ME 5-YEARS to feel confident.

AND, I don’t want it to take that long for you.

So, I put together a FREE 40-minute webinar (a masterclass) that provides a roadmap for what worked for me.

Literally 5-years of learning, consolidated into 40-minutes. It’s jammed packed with value. I wish I saw this when I was starting… It would have made a huge difference.

Step 2: Take action

For my action-takers, if you are ready to become a Business Scientist, then read on.

If you need take your skills to the next level and DON’T want to wait 5-years to learn data science for business, AND you want a career you love that earns you $100,000+ salary (plus bonuses), AND you’d like someone to help you do this in UNDER 6-MONTHS or less….

Then I can help with that too.

Surprise!

There’s a link in the FREE 40-minute webinar for a special price (because you are special!) and taking that action will kickstart your journey with me in your corner.

Get ready. The ride is wild. And the destination is AMAZING!

Join My FREE 40-Minute R Webinar (become a 6-figure data scientist)


👇 Top R-Tips Tutorials you might like:

  1. mmtable2: ggplot2 for tables
  2. ggdist: Make a Raincloud Plot to Visualize Distribution in ggplot2
  3. ggside: Plot linear regression with marginal distributions
  4. DataEditR: Interactive Data Editing in R
  5. openxlsx: How to Automate Excel in R
  6. officer: How to Automate PowerPoint in R
  7. DataExplorer: Fast EDA in R
  8. esquisse: Interactive ggplot2 builder
  9. gghalves: Half-plots with ggplot2
  10. rmarkdown: How to Automate PDF Reporting
  11. patchwork: How to combine multiple ggplots
  12. Geospatial Map Visualizations in R

Want these tips every week? Join R-Tips Weekly.