R is known for its complex modelling capabilities among statisticians and data scientists. However, in corporate environments MS Excel is the primary tool used which needs to be integrated into many data science workflows. Most often, Excel files need to be used as a data source or as a reporting tool. In this tutorial you will learn about the most important R packages to read, create and style Excel files so that you can seamlessly integrate them into end-to-end data science workflows.

R/Excel Introduction

course-r-for-excel-users#01-introduction#01-introduction

course-r-for-excel-users#01-introduction#01-introduction#recipe

Introduction

course-r-for-excel-users#01-introduction#01-introduction#section-excel-pros-and-cons

Excel Pros and Cons

course-r-for-excel-users#01-introduction#01-introduction#section-excel-vs.-r-summary

Excel vs. R Summary

course-r-for-excel-users#01-introduction#01-introduction#quiz-1

Quiz: R Pros vs. Excel

course-r-for-excel-users#01-introduction#01-introduction#quiz-2

Quiz: Excel Cons vs. R

course-r-for-excel-users#01-introduction#01-introduction#section-using-r-as-a-companion-to-excel

Using R as a companion to Excel

course-r-for-excel-users#01-introduction#01-introduction#quiz-3

Quiz: R and Excel Workflows

Overview of ways to read and create Excel files using R including readxl, writexl, openxlsx, RODBC, ...

Read Excel Files using readxl

course-r-for-excel-users#03-read-excel-files-using-readxl

course-r-for-excel-users#03-read-excel-files-using-readxl#01-load-excel-files-as-data-frames

course-r-for-excel-users#03-read-excel-files-using-readxl#01-load-excel-files-as-data-frames#recipe

Load Excel Files as Data Frames

course-r-for-excel-users#03-read-excel-files-using-readxl#01-load-excel-files-as-data-frames#section-introduction

course-r-for-excel-users#03-read-excel-files-using-readxl#01-load-excel-files-as-data-frames#section-the-read_excel-function

The read_excel() Function

course-r-for-excel-users#03-read-excel-files-using-readxl#01-load-excel-files-as-data-frames#exercise-03-01-01

Exercise: Read an Excel File

course-r-for-excel-users#03-read-excel-files-using-readxl#01-load-excel-files-as-data-frames#section-spreadsheet-shape

Spreadsheet Shape

course-r-for-excel-users#03-read-excel-files-using-readxl#01-load-excel-files-as-data-frames#quiz-1

Quiz: Default Settings of read_excel()

course-r-for-excel-users#03-read-excel-files-using-readxl#01-load-excel-files-as-data-frames#section-data-types

Data Types

course-r-for-excel-users#03-read-excel-files-using-readxl#01-load-excel-files-as-data-frames#quiz-2

course-r-for-excel-users#03-read-excel-files-using-readxl#01-load-excel-files-as-data-frames#section-optional-argument-skip

Optional Argument: skip

course-r-for-excel-users#03-read-excel-files-using-readxl#01-load-excel-files-as-data-frames#section-optional-argument-col_names

Optional Argument: col_names

course-r-for-excel-users#03-read-excel-files-using-readxl#01-load-excel-files-as-data-frames#exercise-03-01-04

course-r-for-excel-users#03-read-excel-files-using-readxl#02-handle-excel-files-with-multiple-sheets

course-r-for-excel-users#03-read-excel-files-using-readxl#02-handle-excel-files-with-multiple-sheets#recipe

Handle Excel Files with Multiple Sheets

course-r-for-excel-users#03-read-excel-files-using-readxl#02-handle-excel-files-with-multiple-sheets#section-inspect-the-available-excel-sheets

Inspect the Available Excel Sheets

course-r-for-excel-users#03-read-excel-files-using-readxl#02-handle-excel-files-with-multiple-sheets#exercise-03-02-01

Exercise: Inspect the Available Excel Sheets

course-r-for-excel-users#03-read-excel-files-using-readxl#02-handle-excel-files-with-multiple-sheets#section-read-a-specific-sheet

Read a Specific Sheet

course-r-for-excel-users#03-read-excel-files-using-readxl#02-handle-excel-files-with-multiple-sheets#exercise-03-02-02

Exercise: Read a Specific Sheet

course-r-for-excel-users#03-read-excel-files-using-readxl#02-handle-excel-files-with-multiple-sheets#section-read-all-sheets-using-sapply

Read All Sheets Using sapply()

course-r-for-excel-users#03-read-excel-files-using-readxl#02-handle-excel-files-with-multiple-sheets#exercise-03-02-03

Exercise: Read All Sheets from an Excel File

course-r-for-excel-users#03-read-excel-files-using-readxl#03-read-a-specific-range-from-an-excel-sheet

course-r-for-excel-users#03-read-excel-files-using-readxl#03-read-a-specific-range-from-an-excel-sheet#recipe

Read a Specific Range from an Excel Sheet

course-r-for-excel-users#03-read-excel-files-using-readxl#03-read-a-specific-range-from-an-excel-sheet#section-define-a-range-selection

Define a Range Selection

course-r-for-excel-users#03-read-excel-files-using-readxl#03-read-a-specific-range-from-an-excel-sheet#exercise-03-03-01

course-r-for-excel-users#03-read-excel-files-using-readxl#03-read-a-specific-range-from-an-excel-sheet#section-use-range-selector-functions

Use Range Selector Functions

course-r-for-excel-users#03-read-excel-files-using-readxl#03-read-a-specific-range-from-an-excel-sheet#exercise-03-03-02

Read Excel files using readxl and learn how to deal with workbooks containing multiple spreadsheets.

Write Excel Files using writexl

course-r-for-excel-users#04-write-excel-files-using-writexl

course-r-for-excel-users#04-write-excel-files-using-writexl#01-save-data-frames-as-excel-files

course-r-for-excel-users#04-write-excel-files-using-writexl#01-save-data-frames-as-excel-files#recipe

Save Data Frames as Excel Files

course-r-for-excel-users#04-write-excel-files-using-writexl#01-save-data-frames-as-excel-files#section-the-write_xlsx-function

The write_xlsx() Function

course-r-for-excel-users#04-write-excel-files-using-writexl#01-save-data-frames-as-excel-files#exercise-04-01-01

Exercise: write_xlsx()

course-r-for-excel-users#04-write-excel-files-using-writexl#01-save-data-frames-as-excel-files#section-optional-argument-col_names

course-r-for-excel-users#04-write-excel-files-using-writexl#01-save-data-frames-as-excel-files#exercise-04-01-02

Exercise: col_names

course-r-for-excel-users#04-write-excel-files-using-writexl#02-write-multiple-data-frames-to-an-excel-file

course-r-for-excel-users#04-write-excel-files-using-writexl#02-write-multiple-data-frames-to-an-excel-file#recipe

Write Multiple Data Frames to an Excel File

course-r-for-excel-users#04-write-excel-files-using-writexl#02-write-multiple-data-frames-to-an-excel-file#section-export-a-workbook-from-a-list-of-data-frames

Export a Workbook from a List of Data Frames

course-r-for-excel-users#04-write-excel-files-using-writexl#02-write-multiple-data-frames-to-an-excel-file#exercise-04-02-01

Exercise: Export a List of Data Frames

Write data to Excel quickly using writexl. Create files with a single sheet or workbooks with multiple sheets.

Create Customized Excel Files using openxlsx

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#01-read-and-write-excel-files

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#01-read-and-write-excel-files#recipe

Read and Write Excel Files

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#01-read-and-write-excel-files#section-the-read.xlsx-function

The read.xlsx() Function

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#01-read-and-write-excel-files#exercise-05-01-01

Exercise: Read Excel Tables

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#01-read-and-write-excel-files#section-the-write.xlsx-function

The write.xlsx() Function

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#01-read-and-write-excel-files#quiz-1

Quiz: The write.xlsx() Function

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#01-read-and-write-excel-files#section-save-data-frames-as-excel-tables

Save Data Frames as Excel Tables

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#01-read-and-write-excel-files#exercise-05-01-03

Exercise: Write Excel Tables

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#02-create-excel-files-from-scratch

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#02-create-excel-files-from-scratch#recipe

Create Excel Files from Scratch

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#02-create-excel-files-from-scratch#section-create-and-save-a-workbook

Create and Save a Workbook

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#02-create-excel-files-from-scratch#exercise-05-02-01

Exercise: Create and Save a Workbook

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#02-create-excel-files-from-scratch#section-add-worksheets-to-a-workbook

Add Worksheets to a Workbook

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#02-create-excel-files-from-scratch#exercise-05-02-02

Exercise: Add Worksheets to a Workbook

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#02-create-excel-files-from-scratch#section-insert-data-into-worksheets

Insert Data into Worksheets

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#02-create-excel-files-from-scratch#exercise-05-02-03

Exercise: Insert Data into Worksheets

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#02-create-excel-files-from-scratch#section-load-excel-files-as-workbooks

Load Excel Files as Workbooks

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#02-create-excel-files-from-scratch#exercise-05-02-05

Exercise: Load Excel Files as Workbooks

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#03-specify-column-formatting

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#03-specify-column-formatting#recipe

Specify Column Formatting

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#03-specify-column-formatting#section-define-column-classes

Define Column Classes

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#03-specify-column-formatting#exercise-05-03-01

Exercise: Define Column Classes

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#03-specify-column-formatting#section-date-formatting-options

Date Formatting Options

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#03-specify-column-formatting#exercise-05-03-02

Exercise: Date Formatting Options

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#03-specify-column-formatting#section-column-formatting-with-openxlsx-workbooks

Column Formatting with openxlsx Workbooks

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#03-specify-column-formatting#exercise-05-03-03

Exercise: Column Formatting with openxlsx Workbooks

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#04-create-custom-styling

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#04-create-custom-styling#recipe

Create Custom Styling

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#04-create-custom-styling#section-define-styling-objects

Define Styling Objects

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#04-create-custom-styling#exercise-05-04-01

Exercise: Styling Objects

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#04-create-custom-styling#section-header-styling

Header Styling

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#04-create-custom-styling#exercise-05-04-02

Exercise: Header Styling

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#04-create-custom-styling#section-cell-specific-styling-of-worksheets

Cell Specific Styling of Worksheets

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#04-create-custom-styling#exercise-05-04-03

Exercise: Cell Styling

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#04-create-custom-styling#section-combining-cell-formatting-and-styling

Combining Cell Formatting and Styling

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#04-create-custom-styling#exercise-05-04-04

Exercise: Style Stacking

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#05-add-images-to-worksheets

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#05-add-images-to-worksheets#recipe

Add Images to Worksheets

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#05-add-images-to-worksheets#section-insert-image-files

Insert Image Files

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#05-add-images-to-worksheets#quiz-1

Quiz: Insert Image Files

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#05-add-images-to-worksheets#exercise-05-05-02

Exercise: Insert Image Files

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#05-add-images-to-worksheets#section-insert-plots

Insert Plots

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#05-add-images-to-worksheets#exercise-05-05-03

Excercise: Insert Plots

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#06-define-excel-formulas

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#06-define-excel-formulas#recipe

Define Excel Formulas

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#06-define-excel-formulas#section-assign-a-formula-to-a-cell

Assign a Formula to a Cell

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#06-define-excel-formulas#exercise-05-06-01

Exercise: Insert Formula

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#06-define-excel-formulas#section-assign-formulas-to-multiple-cells

Assign Formulas to Multiple Cells

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#06-define-excel-formulas#quiz-1

Quiz: Insert Multiple Formulas

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#06-define-excel-formulas#section-assign-the-formula-class-to-a-column

Assign the Formula Class to a Column

course-r-for-excel-users#05-create-customized-excel-files-using-openxlsx#06-define-excel-formulas#exercise-05-06-03

Exercise: Formula Columns

Read and write Excel files using openxlsx. Define the formatting of the columns, and create custom styling for the sheets.

Introduction

Introduction

R for Excel Users

We don't support your browser anymore