11.2 Working with Excel files

Excel files are a common data format in business and research. This section covers importing Excel data into R using the readxl package, which offers functions to read various Excel file types and customize the import process.

We'll also explore exporting data from R to Excel using the openxlsx package. This includes creating workbooks, adding worksheets, and applying formatting to make your Excel outputs professional and easy to read.

Reading Excel Files

Using readxl Package for Importing Data

Top images from around the web for Using readxl Package for Importing Data

• 

  Genomics R Data Frames and Factors · Data Science in Omics Introduction View original

  Is this image relevant?

• 

  tidyverse —— readxl包 - 天善智能：专注于商业智能BI和数据分析、大数据领域的垂直社区平台 View original

  Is this image relevant?

• 

  Importare i dati in R [Ricerca Sociale con R] View original

  Is this image relevant?

• 

  Genomics R Data Frames and Factors · Data Science in Omics Introduction View original

  Is this image relevant?

• 

  tidyverse —— readxl包 - 天善智能：专注于商业智能BI和数据分析、大数据领域的垂直社区平台 View original

  Is this image relevant?

1 of 3

Top images from around the web for Using readxl Package for Importing Data

• 

  Genomics R Data Frames and Factors · Data Science in Omics Introduction View original

  Is this image relevant?

• 

  tidyverse —— readxl包 - 天善智能：专注于商业智能BI和数据分析、大数据领域的垂直社区平台 View original

  Is this image relevant?

• 

  Importare i dati in R [Ricerca Sociale con R] View original

  Is this image relevant?

• 

  Genomics R Data Frames and Factors · Data Science in Omics Introduction View original

  Is this image relevant?

• 

  tidyverse —— readxl包 - 天善智能：专注于商业智能BI和数据分析、大数据领域的垂直社区平台 View original

  Is this image relevant?

1 of 3

• readxl

  package provides functions to read Excel files directly into R
• Install

  readxl

  package using

  install.packages("readxl")

  command
• Load the package with

  library(readxl)

  before using its functions

• [read_excel](https://www.fiveableKeyTerm:read_excel)()

  function serves as the primary tool for importing Excel data
• Syntax:

  read_excel("path/to/file.[xlsx](https://www.fiveableKeyTerm:xlsx)")

  reads the entire first sheet
• Supports both .xls and .xlsx file formats
• Automatically detects the data types of columns, reducing manual type conversion

Customizing Excel File Import

• Import specific sheets by adding the

  sheet

  argument (sheet name or number)

• read_excel("file.xlsx", sheet = "Sales Data")

  imports the "Sales Data" sheet
• Use

  [range](https://www.fiveableKeyTerm:Range)

  argument to import specific cell ranges

• read_excel("file.xlsx", range = "B2:D50")

  imports cells B2 through D50
• Combine

  sheet

  and

  range

  for precise data selection

• [col_names](https://www.fiveableKeyTerm:col_names)

  argument controls whether first row is treated as column names
• Set

  col_names = FALSE

  to import all rows as data, useful for headerless files

Managing Missing Values and Data Types

• na

  argument in

  read_excel()

  specifies values to be treated as missing

• read_excel("file.xlsx", na = c("N/A", "Missing"))

  converts "N/A" and "Missing" to NA
• Use

  col_types

  argument to manually specify column data types

• col_types = c("text", "numeric", "date")

  sets types for first three columns

• guess_max

  argument determines how many rows to use for type guessing
• Increase

  guess_max

  for more accurate type detection in large datasets

• trim_ws

  argument removes leading and trailing whitespace from cell values

Writing Excel Files

Creating and Saving Excel Workbooks

• openxlsx

  package provides tools for creating and writing Excel files
• Install with

  install.packages("openxlsx")

  and load using

  library(openxlsx)

• [write.xlsx](https://www.fiveableKeyTerm:write.xlsx)()

  function quickly exports data frames to Excel files
• Syntax:

  write.xlsx(data, file = "output.xlsx")

  saves data to "output.xlsx"
• Creates a new Workbook object to represent the Excel file

• createWorkbook()

  function initializes a new Workbook object
• Add worksheets to the Workbook using

  addWorksheet()

  function

• writeData()

  function writes data to specific worksheets within the Workbook

• saveWorkbook()

  saves the Workbook object as an Excel file on disk

Customizing Excel Output

• Worksheet object represents individual sheets within a Workbook
• Customize worksheet names, tab colors, and visibility

• addWorksheet(wb, "Sheet1", tabColor = "red")

  adds a red-tabbed worksheet
• Control column widths using

  setColWidths()

  function
• Adjust row heights with

  setRowHeights()

  function
• Apply cell styles (font, color, borders) using

  addStyle()

  and

  style()

  functions
• Merge cells with

  mergeCells()

  function for creating headers or grouping data
• Add images to worksheets using

  insertImage()

  function

Advanced Excel Formatting and Features

• Create formulas in cells using

  writeFormula()

  function
• Add conditional formatting rules with

  conditionalFormatting()

  function
• Generate charts and graphs within Excel using

  insertPlot()

  function
• Protect worksheets or entire workbooks with passwords using

  protectWorksheet()

  and

  protectWorkbook()

• Add data validation rules to cells with

  dataValidation()

  function
• Create dropdown lists in cells using

  createNamedRegion()

  and

  dataValidation()

• Freeze panes for better navigation of large datasets with

  freezePane()

  function

Excel Data Manipulation

Understanding Excel Data Types

• Text data type stores alphanumeric characters and symbols
• Numeric data type includes integers and decimal numbers
• Date and time data types represent calendar dates and clock times
• Boolean data type represents TRUE/FALSE or YES/NO values
• Formula data type contains Excel formulas starting with "="
• Error data type indicates calculation errors (DIV/0!, #N/A, #VALUE!)
• R functions like

  is.character()

  ,

  is.numeric()

  ,

  is.logical()

  help identify data types
• Use

  class()

  function in R to determine the imported data type from Excel

Mastering Cell Referencing Techniques

• A1 notation uses column letters and row numbers (A1, B2, C3)
• R1C1 notation uses row and column numbers (R1C1, R2C2, R3C3)
• Relative references adjust when copied (A1 becomes B1 when copied right)
• Absolute references maintain fixed cell locations using $symbol ($A$1)
• Mixed references combine relative and absolute components ($A1 or A$1)
• Range references select multiple cells (A1:C10 selects a rectangular area)
• Named ranges assign custom names to cell ranges for easier referencing
• 3D references span multiple worksheets (Sheet1:Sheet3!A1 references A1 across 3 sheets)