Learn Data Science for Business

How to make Ridiculous Tables in R (from Excel)

Written by Matt Dancho

Did you know that you can make RIDICULOUS tables in R (from Excel)? You can. And in this free R-tip, I share a real case study where I made the working R code for my financial cash flow statement table. I converted it from Excel Spreadsheet into a publication-ready table for an Accounting Report containing financial statements.

Table of Contents

Here’s what you’re learning today:

  • 2-Minute Case Study: How I made a Cash Flow Statement Table in R.
  • New R packages for making Tables: Get a full gt and gtExtras code explanations including how to make Sparklines!
  • Bonus: Free download of all the code shown today to make this table 👇

Bonus: Get the Code for this financial statement table (details below)

SPECIAL ANNOUNCEMENT: AI for Data Scientists Workshop on December 18th

What: GenAI for Data Scientists
When: Wednesday December 18th, 2pm EST
How It Will Help You: Whether you are new to data science or are an expert, Generative AI is changing the game. There’s a ton of hype. But how can Generative AI actually help you become a better data scientist and help you stand out in your career? I’ll show you inside my free Generative AI for Data Scientists workshop.

Price: Does Free sound good?
How To Join: 👉 Register Here

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. 👇

This Tutorial is Available in Video

I have a companion video tutorial that walks you through how to use gt and gtExtras for this analysis. 👇

How to make RIDICULOUS Tables in R (from Excel)

❌ Excel is killing your analysis

Listen, if you are still doing analysis, modeling and reporting in Excel, then did you know:

  1. 90% of Excel spreadsheets contain errors
  2. R can be used to automate the reporting process saving you tons of time every month
  3. Your boss won’t care how you do it, as long as it’s done right the first time

✅ R can make awesome tables

What if you could make a better looking table in R, and virtually guarantee your analysis models were error free?

You can with R! And here’s the same Cash Flow Statement table in R to prove it:

This table can be popped into a PDF or Word document straight from R. And more importantly, the analysis that made the table can be automated from R, virtually eliminating Spreadsheet errors.

Free Gift: Cheat Sheet for my Top 100 R Packages (Special Data Analysis Topics Included)

To help, I’m going to share my secret weapon…
Even I forget which R packages to use from time to time. And this cheat sheet saves me so much time. Instead of googling to filter through 20,000 R packages to find a needle in a haystack. I keep my cheat sheet handy so I know which to use and when to use them. Seriously. This cheat sheet is my bible.

Once you download it, head over to page 3 and you’ll see several R packages I use frequently just for Data Analysis.

Which is important when you want to work in these fields:

  • Machine Learning
  • Time Series
  • Financial Analysis
  • Geospatial Analysis
  • Text Analysis and NLP
  • Shiny Web App Development

Tutorial: How to make Ridiculous Tables in R

This tutorial is awesome. You’ll learn 2 amazing R packages for creating ridiculous tables in R: gt and gtExtras. And through this free R-tip you’ll improve your ability to make professional tables in R. Let’s go.

Step 1: Load the Libraries

Step 2: Get the Excel File

Next, get this Excel file. To do so, join the R-Tips Newsletter.

Step 3: Load the Excel File into R

Next, load the excel file.

Step 4: Data Manipulation

We need to get the raw cash flow data into the right format for the Cash Flow Table. Run this code to get the data prepared:

Code explanation:

The resulting cashflow_wide_tbl data frame contains cleaned and organized data, ready for further analysis or visualization.

Step 5: Make the Table in R

Finally, we are ready to make the table in R. Run this code:

Code Explanation:

The final table looks amazing and is ready to add in to your financial report:

BONUS: Steal My Code for the R Tables (it’s legal)

Want all the code I just showed you? Steal my R-tip library.

Need to advance your business data science skills?

I’ve helped over 6,107 students learn data science for business from an elite business consultant’s perspective.

And I built a training program that gets my students life-changing data science careers.

Whenever you are ready, here’s the system they are taking:

Here’s the system that has gotten aspiring data scientists, career transitioners, and lifelong learners data science jobs and promotions…