R for Excel Users


This book is no longer available for sale.

R for Excel Users

An Introduction to R for Excel Analysts

About the Book

While Excel might continue to be your bread-and-butter, knowing R and applying it at the right times can make you a more productive and effective analyst. This book is for beginners, and the goal is to get you started. R is known to have a steep learning curve, but I really think it has three separate curves: (1) data management, (2) statistics and (3) visualization.

The focus of this book will be on data management: how to import, manipulate, transform and summarize data through the use of functions. Once you understand and are able to work with data structures, applying analytical techniques is a relative breeze.

For example, running a linear regression is a simple command that looks like this:

lm(y ~ x1 + x2 + ... xn, data = dataset_name)

That's easy, and if you understand regression, then interpreting the output will also be easy.  But getting your data set in the right format, combining it with other data sets, manipulating columns, filtering rows, etc -- that is the hurdle I will help you overcome.

About the Author

John Taveras
John L Taveras

I have over ten years of business analysis and strategy experience in a number of industries.  I started using Excel during a college internship and it was love at first sight. The structure and logic made sense immediately, and within months I was building advanced tools and teaching colleagues how to get more out of Excel.  Fast-forward a few years, trying R for the first time was not love at first sight.  It was different from anything I had done before, and it seemed like all the learning materials were written for statisticians and coders, which made the transition more difficult.  Thankfully I stuck with it, and today R helps me be more productive and has opened doors to more interesting analyses that I otherwise would not have been able to tackle.

I love to teach, and have helped many colleagues get started with R.  This book, and my website at www.rforexcelusers.com, is my attempt at getting more business analysts started with R.

When I am not analyzing data or writing, I enjoy spending time with my wife and son.  I have a BA in Math & Economics from Wesleyan University, an MBA from MIT Sloan School of Management and work in advertising analytics at Google.  

I would love to hear from you!  Contact me at john@rforexcelusers.com if you have questions, comments, ideas or feedback.

Table of Contents

    • Preface
    • About me
  • Part 1 - Introduction & Set Up
    • 1. Getting Set Up
      • 1.1 Getting mentally ready to write code
      • 1.2 Downloading the software
      • 1.3 Navigating the software
      • 1.4 Using other Software
      • 1.5 Libraries
      • 1.6 Console tips
      • 1.7 Getting help
    • 2. Programming Basics
      • 2.1 Assigning variables
      • 2.2 Testing conditions
      • 2.3 Data structures
      • 2.4 Special values
      • 2.5 Commenting your code
      • 2.6 Control Structures
    • 3. Quick Start - Analysis Examples
      • 3.1 Example 1: General data analysis
      • 3.2 Example 2: Using SQL in R
      • 3.3 Example 3: Multiple regression model
      • 3.4 Summary
  • Part 2 - Building Blocks: Cells and Formulas
    • 4. Cells are Vectors
      • 4.1 Individual cells
      • 4.2 Cell ranges
      • 4.3 They are all vectors
      • 4.4 Why vectors matter
      • 4.5 Working with vectors
      • 4.6 How vector operations work
      • 4.7 Summary
    • 5. Formulas are Functions
      • 5.1 Inputs
      • 5.2 Outputs
      • 5.3 Getting help on functions
      • 5.4 Some base functions
      • 5.5 Create your own functions
      • 5.6 Summary
  • Part 3 - Data Frames
    • 6. Import and Create Data Sets
      • 6.1 Importing from .csv and .txt files
      • 6.2 Importing from .xlsx files
      • 6.3 Creating a data frame
      • 6.4 Deleting a data frame
      • 6.5 Summary
    • 7. Inspect Your Data
      • 7.1 Sneak a peek with brackets
      • 7.2 Sneak another peek with head() and tail()
      • 7.3 Data structure - str()
      • 7.4 Column names - names()
      • 7.5 Summary stats - summary()
      • 7.6 Inspecting columns - unique(), table() and others
      • 7.7 Visually understand your data
      • 7.8 Other quick inspection functions
      • 7.9 Summary
    • 8. Working with Columns
      • 8.1 Refer to columns
      • 8.2 Create columns
      • 8.3 Reformat columns
      • 8.4 Rename columns
      • 8.5 Remove columns
      • 8.6 Unbound columns
      • 8.7 Summary
    • 9. Working with Rows
      • 9.1 Filtering with brackets
      • 9.2 Filtering with subset()
      • 9.3 Filtering uniques with unique()
      • 9.4 Sorting with order()
    • 10. Manipulating Rows and Columns with dplyr
      • 10.1 sqldf
      • 10.2 dplyr - manipulation verbs
      • 10.3 Application: by-group processing with dplyr
      • 10.4 Summary
  • Part 4 - Shape your Dataset
    • 11. Combine Data Tables
      • 11.1 merge() - vlookup on steroids
      • 11.2 merge() options
      • 11.3 Merging with sqldf()
      • 11.4 Merging with dplyr
      • 11.5 Summary
    • 12. PivotTables - Summarize and Transpose your Data
      • 12.1 Making a simple PivotTable
      • 12.2 Reshaping the table
      • 12.3 Summary
  • Part 5 - Advanced Topics
    • 13. Working with lists
      • 13.1 What is a list?
      • 13.2 Two common uses
      • 13.3 How to get things from lists
      • 13.4 Creating and modifying lists
      • 13.5 Basic list functions
      • 13.6 Summary
    • 14. Programming: Loops and Control Flow
      • 14.1 while() loop
      • 14.2 for() loop
      • 14.3 Breaking out of or skipping loops
      • 14.4 if()
      • 14.5 Summary
    • 15. Writing your own functions
      • 15.1 Simple function - Custom table summary
      • 15.2 More advanced function
      • 15.3 Summary
    • 16. Apply Family of Functions
      • 16.1 lapply(), sapply() and mapply()
      • 16.2 apply()
      • 16.3 Summary
    • 17. Text / String Extraction
      • 17.1 Using substr() to mimic LEFT() and RIGHT()
      • 17.2 Using strsplit() and apply functions
      • 17.3 Regular Expression Text Extraction
      • 17.4 Summary
    • Next Steps

The Leanpub 45-day 100% Happiness Guarantee

Within 45 days of purchase you can get a 100% refund on any Leanpub purchase, in two clicks.

See full terms

Do Well. Do Good.

Authors have earned$10,462,200writing, publishing and selling on Leanpub, earning 80% royalties while saving up to 25 million pounds of CO2 and up to 46,000 trees.

Learn more about writing on Leanpub

Free Updates. DRM Free.

If you buy a Leanpub book, you get free updates for as long as the author updates the book! Many authors use Leanpub to publish their books in-progress, while they are writing them. All readers get free updates, regardless of when they bought the book or how much they paid (including free).

Most Leanpub books are available in PDF (for computers), EPUB (for phones and tablets) and MOBI (for Kindle). The formats that a book includes are shown at the top right corner of this page.

Finally, Leanpub books don't have any DRM copy-protection nonsense, so you can easily read them on any supported device.

Learn more about Leanpub's ebook formats and where to read them

Write and Publish on Leanpub

You can use Leanpub to easily write, publish and sell in-progress and completed ebooks and online courses!

Leanpub is a powerful platform for serious authors, combining a simple, elegant writing and publishing workflow with a store focused on selling in-progress ebooks.

Leanpub is a magical typewriter for authors: just write in plain text, and to publish your ebook, just click a button. (Or, if you are producing your ebook your own way, you can even upload your own PDF, EPUB and/or MOBI files and then publish with one click!) It really is that easy.

Learn more about writing on Leanpub