Using R for Excel Analysts
A guide to automating the repetitive parts of Excel and Google Sheets work with R — data management and analysis, visualization, and programmatic reporting that regenerates a PDF, HTML, or Word report in one click whenever the underlying data changes. Sharing reports through OneDrive and Google Drive is covered in depth, and a dedicated chapter introduces GitHub for version-controlling and collaborating on R scripts.
Preliminaries, ten chapters, two appendices, and back matters
R scripts & datasets by chapter
Every script and dataset used in the book's worked examples, organized by the chapter that introduces it.
Introduction
The labor-force walkthrough that opens the book — a Word report, an R Markdown source, the R scripts that built it, and the Zarthan and employee sample datasets used throughout.
Setting Up the R Environment
The employee compensation dataset (Excel and CSV) and the Zarthan sales dataset, in both formats.
Exploratory Data Analysis with Tidyverse
Small employee and state-level datasets used to demonstrate dataset manipulation with the tidyverse.
Data Visualization
Scripts for bar, line, pie, and stacked charts, plus two datasets used across the chapter's examples: cracker sales by location, and the CO124 international-statistics dataset.
Programmatic Reporting
The labor-force report rebuilt as a reproducible pipeline, U.S. population and housing-unit datasets from the Census Bureau, a script for connecting to OneDrive via Microsoft365, and the multi-sheet workbooks used for report automation.
Connecting R to Excel with the openxlsx Package
Scripts for writing data and summary statistics to Excel worksheets, plus the source workbooks and outputs they produce.
Connecting R to Excel with the xlsx Package
A progressive series of workbooks showing worksheets being added, summarized, and removed, alongside the R scripts that manipulate them.