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
Automating Excel from R
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. 👇
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. 👇
Step 1: Collect, Prepare Data & Plots
First, collect your data using
tidyquant, an awesome package for importing & working with Financial Data.
Pivot Table Data
The previous code makes this Pivot Table (stock returns by year and symbol).
Time Series Stock Performance Plots
Here’s the stock performance plot from the previous code.
Step 2: Automate R to Excel
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)
Excel Output (.xlsx)
You’ve just automated creation of an Excel Workbook with R.
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.
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!
👇 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.