How to Make Publication-Quality Excel Pivot Tables with R

[This article was first published on, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

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 make Excel pivot tables with R.

(Click image to play tutorial)

Excel Pivot Tables with R Tutorial

The biggest thing I missed when I transititioned from Excel to R was… PIVOT TABLES!

Seriously – Pivot Tables are so useful. You can summarize and reshape (aka Pivot) data so easily with them in Excel. Why not in R???

Here’s the Publication-Quality Pivot Table that we are going to make today. ????

Pivot Table

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

Get Data

Stock Table

Next, make a pivot_table() from the stock data. The resulting data summarized using a percentage change from 1st to last in each pivot cell.

Percent Change by Year

Pivot Table

And with the GT Package, we can make the pivoted data into a PUBLICATION-QUALITY Pivot Table with conditional formatting applied to highlight years with positive stock returns. (Table code partially shown. Get the GitHub code to reproduce the table.)

Pivot Charts

And, now you have a publication-quality stock performance report! ????

Stock Returns

You’ve just made a publication-quality Pivot Table in R.

Boom! ????????????

Learn something? Awesome!

But you need to learn a TON to become an R Samurai Master.


  1. Sign Up to Get the R-Tips Weekly (You’ll get email notifications of NEW R-Tips as they are released):

  2. Set Up the GitHub Repo:

  3. Check out the setup video ( 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. =)

To leave a comment for the author, please follow the link and comment on their blog: offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

Never miss an update!
Subscribe to R-bloggers to receive
e-mails with the latest R posts.
(You will not see this message again.)

Click here to close (This popup will not appear again)