Writing Special Cell Types with writexl

The writexl package exports plain data frame values (numbers, strings, booleans, dates) automatically. For anything more specialised — formulas, hyperlinks, or mixed-type columns — the package provides the xl_cell_general family of helpers.

1. Formulas

Use xl_formula() to mark character strings as Excel formulas. Each formula must start with "=". A length-1 formula is automatically recycled to fill the whole column.

df <- data.frame(
  name    = c("UCLA", "Berkeley", "Caltech"),
  founded = c(1919, 1868, 1891)
)
df$age <- xl_formula('=(YEAR(TODAY()) - INDIRECT("B" & ROW()))')

You can also supply a pre-calculated result so that xlsx readers that do not recalculate on open still show a sensible value:

cells <- c(
  xl_cell_general(value = 55.0, formula = "=SUM(B2:B4)"),
  xl_cell_general(value = 10.5, formula = "=AVERAGE(B2:B4)")
)
df2 <- data.frame(label = c("Sum", "Mean"))
df2$result <- cells

When value is numeric the package uses worksheet_write_formula_num() so the stored value is visible even without recalculation. When value is a character string, worksheet_write_formula_str() is used instead.

2. Hyperlinks

Use xl_hyperlink() for clickable URLs. Optionally supply a name vector for the display text shown in the cell:

df3 <- data.frame(
  name = c("UCLA", "Berkeley"),
  url  = xl_hyperlink(
    c("http://www.ucla.edu", "http://www.berkeley.edu"),
    name = "homepage"
  )
)
#> Warning: The `name` argument of xl_hyperlink() is deprecated; use `value`
#> instead, which is what xl_hyperlink_cell() and xl_cell_general() already call
#> it.

For more control — including a tooltip — use xl_cell_general() directly. Supply a character value for the display text and a named-list hyperlink for the URL and tooltip:

df4 <- data.frame(x = 1:2)
df4$link <- c(
  xl_cell_general(
    value     = "UCLA website",
    hyperlink = list(url = "http://www.ucla.edu", tooltip = "Opens UCLA homepage")
  ),
  xl_cell_general(hyperlink = "http://www.berkeley.edu")
)

NA URLs produce blank cells with no hyperlink:

urls <- c("http://www.ucla.edu", NA, "http://www.caltech.edu")
df5 <- data.frame(name = c("UCLA", "???", "Caltech"))
df5$url <- xl_hyperlink(urls)

3. Value-only cells

xl_cell_general() with only a value argument writes a plain cell of whatever R type is provided. This is useful when a column contains a single scalar that should be recycled:

df6 <- data.frame(x = 1:4)
df6$constant <- xl_cell_general(value = 99L)  # integer 99 in every row

Supported value types include numeric, integer, logical, character, Date, and POSIXct.

4. Mixed-type columns

Because each element of an xl_cell_general object represents one cell, you can combine different types in a single column by concatenating individual cells with c():

mixed <- c(
  xl_cell_general(value = 1.5),                  # numeric
  xl_cell_general(value = "see note"),            # string
  xl_cell_general(formula = "=A1+A2"),            # formula
  xl_cell_general(hyperlink = "https://cran.r-project.org")  # hyperlink
)

df7 <- data.frame(row = 1:4)
df7$mixed_col <- mixed

A list passed to value allows different R types per cell in one call:

df8 <- data.frame(x = 1:3)
df8$v <- xl_cell_general(value = list(42L, "hello", TRUE))

5. Formatted empty cells

A cell whose value is NA normally writes nothing at all, because Excel ignores blank cells that carry no formatting. Give one a format, though, and the cell is written as a formatted blank so the styling survives — useful for shading gaps in a table:

df_blank <- data.frame(quarter = c("Q1", "Q2", "Q3"))
df_blank$actual <- xl_cell_general(
  value  = list(101.5, NA, 98.2),
  format = xl_fill(background = "#FFF2CC")
)

The middle cell is empty but still shaded. This applies to xl_cell_general() cells; NA in a plain data frame column is left to the column’s own format, which Excel already applies to empty cells.

6. Array and dynamic array formulas

array and dynamic control how a formula is stored. A dynamic formula is the modern spelling: you write it to one cell and Excel spills the result over as many cells as it needs.

df_dyn <- data.frame(city = c("Perth", "Hobart", "Perth", "Darwin"))
df_dyn$unique_cities <- xl_cell_general(
  formula = c("=UNIQUE(A2:A5)", NA, NA, NA),
  dynamic = TRUE
)

An array formula is the legacy Ctrl-Shift-Enter form, most often used for a single-cell aggregate:

df_arr <- data.frame(qty = c(2, 5, 3), price = c(10, 4, 7))
df_arr$total <- xl_cell_general(
  formula = c("=SUM(A2:A4*B2:B4)", NA, NA),
  array   = TRUE
)

Either may carry a pre-calculated numeric result via value, so the workbook displays a value without Excel recalculating on open. A character result is not possible: Excel stores no cached string result for an array formula, and supplying one is an error rather than a silent downgrade.

The flags are inert on a cell that has no formula, so a single array = TRUE recycles across a column that mixes formula and value cells.

array_range declares the extent of a legacy array formula in the rare case it must be stated. It has to start at the cell holding the formula and extend into cells the sheet does not otherwise write, because the range is padded on write and an overlap would have the padding and your data overwrite each other. Supplying it also forces the workbook out of its memory-efficient row-streaming mode. Prefer a single-cell dynamic formula wherever you can.

7. Rich strings: several fonts in one cell

xl_rich_string() splits a single cell’s text into runs, each drawn in its own font. Pass a bare string for an unformatted run, or xl_rich_run() to give one a font:

df_rich <- data.frame(id = 1L)
df_rich$label <- xl_cell_general(
  value = xl_rich_string(
    "This is ",
    xl_rich_run("bold", xl_font(bold = TRUE)),
    " and this is ",
    xl_rich_run("red italic", xl_font(color = "red", italic = TRUE))
  )
)

A run is drawn with a font only, so only the xl_font() properties apply; supplying a fill, border, alignment or number format on a run warns and is ignored. A format on the cell (xl_cell_general(format =)) still works normally and applies to the whole cell.

Excel needs at least two runs — a string with one format is an ordinary character value, so pass it as one.

8. Backward compatibility

All existing xl_formula() and xl_hyperlink() call sites continue to work unchanged. Both functions now return an xl_cell_general object instead of a plain character vector, but the writing behaviour is identical:

# These calls are identical to writexl < 1.5.5
df9 <- data.frame(
  name    = c("UCLA", "Berkeley"),
  founded = c(1919, 1868),
  website = xl_hyperlink(c("http://www.ucla.edu", "http://www.berkeley.edu"),
                          "homepage")
)
df9$age <- xl_formula('=(YEAR(TODAY()) - INDIRECT("B" & ROW()))')

path <- write_xlsx(df9)
file.exists(path)
#> [1] TRUE