O'Reilly logo
live online training icon Live Online training

R-Powered Excel for Business Analytics

Augment and automate your spreadsheets for higher quality data

George Mount

R is an open-source programming language widely used for statistical and data analysis. Project managers, analysts, and other professionals want to make informed decisions using data, but may run into limitations using Excel for advanced, reproducible analysis. By relating the foundations of R to everyday Excel tasks, professionals will be empowered to perform data analysis and exploration in R.

The focus of this course will be data manipulation, focusing on the most common data structures for business analytics: vectors and data frames. We will work in RStudio Desktop with an overview of the popular Tidyverse collection of packages for data analytics.

What you'll learn-and how you can apply it

By the end of this live, hands-on, online course, you’ll understand:

  • R’s powerful universe of open-source packages and tools
  • The R equivalent of common Excel tasks such as PivotTables, sorting and filtering
  • Where R can augment and automate common data preparation and manipulation tasks often done in Excel
  • The role of vectors, objects, and functions in R

And you’ll be able to:

  • Load, view and write Excel files from R
  • Perform common data wrangling tasks such as sorting, filtering, and aggregation, using the dplyr library
  • Navigate the RStudio integrated development environment
  • Identify, install and implement useful packages for your needs

This training course is for you because...

  • You're an analyst responsible for collecting, analyzing, and interpreting data for business insights.
  • You want to learn how to use R with RStudio and its most common packages.
  • You would like to speed up, automate, and validate your reporting and analytics using a free data science tool.

Prerequisites

  • You should be comfortable with basic tasks and functions in Excel, including sorting and filtering, and the use of IF statements. Ideally, you've also used conditional aggregates like SUMIF() and COUNTIF() as well as PivotTables and VLOOKUP().
  • No prior programming knowledge needed. As an Excel user, you already know more programming than you realize!

Recommended preparation:

Recommended follow-up:

About your instructor

  • George works as an independent analyst and data analytics educator with the goal to help clients manage their data so they think more creatively. He serves as a technical expert and lead curriculum developer for Thinkful’s data analytics program and is the instructor of the DataCamp course “Survey and Measure Development in R.” George blogs about data, innovation, and career development at georgejmount.com. He holds a master’s degree in information systems with a certificate of achievement in quantitative methods from Case Western Reserve University.

Schedule

The timeframes are only estimates and may vary according to how the class is progressing

Excel and R for data analytics (15 minutes)

  • Presentation: What is R and when would you use it?
  • Poll:
  • Presentation: Overview of the R Project and the Comprehensive R Archive Network. Understanding the relationship between “base R” and additional packages.
  • Exercise: Check out CRAN taskviews and install one of interest.
  • Q&A

From Excel calculations to R development (35 minutes)

  • Presentation: Introduction to RStudio Desktop.
  • Exercise: Use R to perform basic calculations and comparison operations. Navigate and customize RStudio Desktop (changing the panes arrangement and launching the help files of a function).
  • Q&A
  • Break (5 minutes)

From Excel cells to R vectors (30 minutes)

  • Presentation: Building and inspecting vectors, the building block of R.
  • Presentation: Manipulating vectors -- indexing, subsetting and vector arithmetic.
  • Exercise: Practice building, inspecting and manipulating a vector

From Excel tables to R data frames (30 minutes)

  • Presentation: Importing and inspecting a data frame, the basic structure for tabular data in R and most equivalent to an Excel worksheet
  • Presentation: Manipulating data frames and exporting your results
  • Exercise: Practice reading in, inspecting and manipulating a data frame
  • Q&A
  • Break (5 minutes)

From Excel lookups and PivotTables to dplyr manipulation (30 minutes)

  • Presentation: Exploring the grammar of dplyr, a tidyverse package for data manipulation
  • Sort, filter, rename columns, and summarize a data frame
  • Join two data frames
  • Chain multiple manipulation tasks together with dplyr’s pipe
  • Exercise: Practice building a data cleaning pipeline using dplyr.
  • Q&A

From “That’s hard in Excel” to “That’s easy in R!” (30 minutes)

  • Presentation: Capstone: Build a “reporting pipeline” in R. Take a set of .csv files that need to be appended, transposed and summarized every week. This would be a lot of manual work in “base” Excel but a lot easier in R!
  • Presentation: What next? How to get help in R and resources for your R journey.
  • Q&A