Using R for Excel Analysts
by
Kilem L. Gwet, PhD
Return to the Book Collection
In this book, you will learn how to use R to automate tedious and repetitive
tasks in Excel and Google Sheets. In addition to using R to leverage Excel’s capability in data management
and analysis, you will discover all the power of R in data visualization and programmatic reporting. You
will see how R can automate the entire workflow from raw data to the final report in PDF, HTML or Word
formats. This will allow you to update your analysis report with new data in one click.
The use of OneDrive and Google Drive as a way of sharing your reports with others is extensively discussed. As a bonus, I added
a chapter on the important issue of version control with GitHub. Here is where you will learn how to manage
the different versions of your R scripts and share them with others if needed, in a collaborative work
environment. These are some of the essential skills you must develop to be an effective analyst in the
new world of data science.
Using R for Excel Analysts
by Kilem L. Gwet, PhD
R Scripts and Datasets for Download
Chapter 1: Introduction
Chapter 2: Setting Up the R Environment
- employee.xls: Employee
compensation data by gender, age and marital status in Excel.
- employee.csv: Employee
compensation data by gender, age and marital status in CSV (Comma
delimited) format.
Chapter 3: R Datasets
Chapter 4: Manipulating Datasets
- employee_demo.csv:
Demographic data of 7 employess (Employee Id, Gender, Age and Marital status).
- employee_name.csv: Names
of 7 employees along with their State of residency, Employment Status,
and Employee Id
- gdp_by_state.csv: 2020 US
Quarterly GDP Data (in millions of dollars) and 2019 Population Data by State.
Chapter 6: Creating Data Analysis Reports
- chap6datasets.xlsx:
Excel file containing 5 worksheets named "mtcars," "iris," "IrrData,"
"IccData," and "CacData."
- US Population
Statistics.xlsx:
Annual Estimates of the Resident Population for the United States,
Regions, States, District of Columbia, and Puerto Rico: April 1, 2020 to
July 1, 2021
- NST-EST2021-HU.xlsx:
Annual Estimates of Housing Units for the United States, Regions,
States, and the District of Columbia: April 1, 2020 to July 1, 2021
- scr@6OneDrive.r: Script
file showing how to use the Microsoft365 package to interact with the
OneDrive repository
Chapter7: Connecting R to Excel with the Openxlsx Package
- chap7data.xlsx:
Excel file containing 5 worksheets named "mtcars," "iris," "IrrData,"
"IccData," and "CacData."
- chap7Output1.xlsx: Excel
workbook with the summary data frame summary.df written to the IrrData
worksheet.
- scr@7quant1.r: R Script
that illustrates the writeData() and saveWorkbook() functions.
- scr@7quant2.r: Script for
creating the summaryStats1 and summaryStats2 work sheets of Figures 7.5
and 7.6.
- summaryFile.xlsx:
Excel file containing 7 worksheets named "mtcars," "iris," "IrrData,"
"IccData," "CacData," "summaryStats1" ,"summaryStats2"
Chapter 8: Creating Data Analysis Reports
- chap8data.xlsx:
Excel file containing 5 worksheets named "mtcars," "iris," "IrrData,"
"IccData," and "CacData." It is password-protected with the
password Microsoft365.
- chap8data1.xlsx:
Password-protected 2-worsheet Excel file containing the 2 worksheets
"summaryStats1" and "summaryStats2" (Password: Microsoft365).
- chap8delete.xlsx:
Password-protected 1-worsheet Excel file containing the "summaryStats1"
worksheet after deleting "summaryStats2" (Password: Microsoft365).
- chap8data1b.xlsx:
Excel workbook containing 3 worksheets named "Input Data",
"summaryStats1", "summaryStats2"
- scr@8xlsx1.r: R Script for reading
the chap6datasets.xlsx Excel workbook, analyzing its data, and writing
summary statistics to specific worksheets.
- scr@8xlsx2.r: R Script
for adding a caption to a data table.
- scr@8xlsx3-cells.r:
R Script for writing the formatted table of Figure 8.7 to an Excel
worksheet.
-
scr@8xlsx-formatting.r: R Script that creates Table 8.8 using the
irrData worksheet data of the Excel workbook chap6datasets.xlsx
- scr8@plot2xls2.r:
R Script to create a scatterplot and add it to an Excel worksheet.
Chapter 9: Creating Data Analysis Reports
Chapter 10: Using GitHub to Manage Versions
- chap6datasets1a.xlsx:
Excel workbook containing 2 worksheets named "Input Data," and "summaryStats1".
- chap6datasets1b.xlsx:
Excel workbook containing 3 worksheets named "Input Data," "summaryStats1",
and "summaryStats2".
- scr@6xlsx1a0.r: First
modified version of the scr@6xlsx1.r script file aimed at creating the
the chap6datasets1a.xlsx Excel workbook.
- scr@6xlsx1a.r: Second
modified version of the scr@6xlsx1.r script file aimed at creating
the the chap6datasets1b.xlsx Excel workbook.