Customizing How Your Tables Look

library(tsg)
library(dplyr)

tsg uses a style object (called a facade) to control how your tables look when exported: fonts, colours, borders, cell sizes, and more. You can:

The same style applies consistently to all output formats (Excel, HTML, PDF, Word).


Using a built-in style

The package ships with thirteen named styles. Pass a name to any writer via the facade argument: a style looks the same in Excel, HTML, PDF and Word.

Style name Description
"default" Clean, neutral; used automatically when no style is specified
"yolo" Bolder colours and heavier formatting
"apa" APA style: horizontal rules only, no fills, Times New Roman
"booktabs" Thick top and bottom rules, a thin rule under the header, nothing else
"striped" Coloured header band and banded body rows
"gridded" Thin lines around every cell and a grey header, as in statistical yearbooks
"minimal" No borders or fills, light grey rules between rows; suits web and dashboards
"financial" Dark blue header, thin grey grid and a bold last row, as in business reports
"nyt" Editorial: bold header over a thick rule, no vertical lines, light banding
"jama" Medical-journal: horizontal rules only, bold sans-serif header and first column
"dark" Light text on a dark background
"pastel" Soft blue header and group titles
"census" Official statistics: grey header, bold group titles, small italic notes
tbl <- person_record |>
  generate_frequency(sex)

write_xlsx(tbl, path = "booktabs.xlsx", facade = "booktabs")
write_html(tbl, path = "booktabs.html", facade = "booktabs")
write_docx(tbl, path = "booktabs.docx", facade = "booktabs")

# The default style is used when you leave out `facade`
write_xlsx(tbl, path = "default.xlsx")

Only "default" differs a little between formats (for example, Word uses a smaller font). Every other style is laid over that format’s default. Settings that only mean something in a spreadsheet, such as row heights and column widths, are ignored outside Excel.

Here is each style in Excel (a screenshot of the sheet write_xlsx() writes) and in HTML (built with as_gt(), which returns the styled gt table for your own Quarto or R Markdown documents):

default

Excel

Excel sheet: default

HTML

Marital status by sex
Marital status Total
Frequency
Percent
Male Female Male Female
Single/never married 1,544 859 685 55.63 44.37
Married 769 387 382 50.33 49.67
Common law/live-in 424 211 213 49.76 50.24
Widowed 138 40 98 28.99 71.01
Separated 43 19 24 44.19 55.81
Total 2,918 1,516 1,402 51.95 48.05
Source: person_record

yolo

Excel

Excel sheet: yolo

HTML

Marital status by sex
Marital status Total
Frequency
Percent
Male Female Male Female
Single/never married 1,544 859 685 55.63 44.37
Married 769 387 382 50.33 49.67
Common law/live-in 424 211 213 49.76 50.24
Widowed 138 40 98 28.99 71.01
Separated 43 19 24 44.19 55.81
Total 2,918 1,516 1,402 51.95 48.05
Source: person_record

apa

Excel

Excel sheet: apa

HTML

Marital status by sex
Marital status Total
Frequency
Percent
Male Female Male Female
Single/never married 1,544 859 685 55.63 44.37
Married 769 387 382 50.33 49.67
Common law/live-in 424 211 213 49.76 50.24
Widowed 138 40 98 28.99 71.01
Separated 43 19 24 44.19 55.81
Total 2,918 1,516 1,402 51.95 48.05
Source: person_record

booktabs

Excel

Excel sheet: booktabs

HTML

Marital status by sex
Marital status Total
Frequency
Percent
Male Female Male Female
Single/never married 1,544 859 685 55.63 44.37
Married 769 387 382 50.33 49.67
Common law/live-in 424 211 213 49.76 50.24
Widowed 138 40 98 28.99 71.01
Separated 43 19 24 44.19 55.81
Total 2,918 1,516 1,402 51.95 48.05
Source: person_record

striped

Excel

Excel sheet: striped

HTML

Marital status by sex
Marital status Total
Frequency
Percent
Male Female Male Female
Single/never married 1,544 859 685 55.63 44.37
Married 769 387 382 50.33 49.67
Common law/live-in 424 211 213 49.76 50.24
Widowed 138 40 98 28.99 71.01
Separated 43 19 24 44.19 55.81
Total 2,918 1,516 1,402 51.95 48.05
Source: person_record

gridded

Excel

Excel sheet: gridded

HTML

Marital status by sex
Marital status Total
Frequency
Percent
Male Female Male Female
Single/never married 1,544 859 685 55.63 44.37
Married 769 387 382 50.33 49.67
Common law/live-in 424 211 213 49.76 50.24
Widowed 138 40 98 28.99 71.01
Separated 43 19 24 44.19 55.81
Total 2,918 1,516 1,402 51.95 48.05
Source: person_record

minimal

Excel

Excel sheet: minimal

HTML

Marital status by sex
Marital status Total
Frequency
Percent
Male Female Male Female
Single/never married 1,544 859 685 55.63 44.37
Married 769 387 382 50.33 49.67
Common law/live-in 424 211 213 49.76 50.24
Widowed 138 40 98 28.99 71.01
Separated 43 19 24 44.19 55.81
Total 2,918 1,516 1,402 51.95 48.05
Source: person_record

financial

Excel

Excel sheet: financial

HTML

Marital status by sex
Marital status Total
Frequency
Percent
Male Female Male Female
Single/never married 1,544 859 685 55.63 44.37
Married 769 387 382 50.33 49.67
Common law/live-in 424 211 213 49.76 50.24
Widowed 138 40 98 28.99 71.01
Separated 43 19 24 44.19 55.81
Total 2,918 1,516 1,402 51.95 48.05
Source: person_record

nyt

Excel

Excel sheet: nyt

HTML

Marital status by sex
Marital status Total
Frequency
Percent
Male Female Male Female
Single/never married 1,544 859 685 55.63 44.37
Married 769 387 382 50.33 49.67
Common law/live-in 424 211 213 49.76 50.24
Widowed 138 40 98 28.99 71.01
Separated 43 19 24 44.19 55.81
Total 2,918 1,516 1,402 51.95 48.05
Source: person_record

jama

Excel

Excel sheet: jama

HTML

Marital status by sex
Marital status Total
Frequency
Percent
Male Female Male Female
Single/never married 1,544 859 685 55.63 44.37
Married 769 387 382 50.33 49.67
Common law/live-in 424 211 213 49.76 50.24
Widowed 138 40 98 28.99 71.01
Separated 43 19 24 44.19 55.81
Total 2,918 1,516 1,402 51.95 48.05
Source: person_record

dark

Excel

Excel sheet: dark

HTML

Marital status by sex
Marital status Total
Frequency
Percent
Male Female Male Female
Single/never married 1,544 859 685 55.63 44.37
Married 769 387 382 50.33 49.67
Common law/live-in 424 211 213 49.76 50.24
Widowed 138 40 98 28.99 71.01
Separated 43 19 24 44.19 55.81
Total 2,918 1,516 1,402 51.95 48.05
Source: person_record

pastel

Excel

Excel sheet: pastel

HTML

Marital status by sex
Marital status Total
Frequency
Percent
Male Female Male Female
Single/never married 1,544 859 685 55.63 44.37
Married 769 387 382 50.33 49.67
Common law/live-in 424 211 213 49.76 50.24
Widowed 138 40 98 28.99 71.01
Separated 43 19 24 44.19 55.81
Total 2,918 1,516 1,402 51.95 48.05
Source: person_record

census

Excel

Excel sheet: census

HTML

Marital status by sex
Marital status Total
Frequency
Percent
Male Female Male Female
Single/never married 1,544 859 685 55.63 44.37
Married 769 387 382 50.33 49.67
Common law/live-in 424 211 213 49.76 50.24
Widowed 138 40 98 28.99 71.01
Separated 43 19 24 44.19 55.81
Total 2,918 1,516 1,402 51.95 48.05
Source: person_record

Tweaking a few settings with add_facade()

add_facade() lets you override individual style settings without replacing the whole style. Call it in your pipeline before write_*().

Style settings use a two-part name: which part of the table . what to change. For example:

person_record |>
  generate_frequency(sex) |>
  add_facade(
    table.offsetRow = 2,
    table.offsetCol = 1
  )
#> # A tibble: 3 × 3
#>   category   frequency percent
#>   <int+lbl>      <int>   <dbl>
#> 1 1 [Male]        1516    52.0
#> 2 2 [Female]      1402    48.0
#> 3 0 [Total]       2918   100

Common tweaks

person_record |>
  generate_crosstab(marital_status, sex) |>
  add_facade(
    # Move the table down 2 rows and right 1 column (Excel only)
    table.offsetRow = 2,
    table.offsetCol = 1,
    # Round numbers to 1 decimal place
    table.decimalPrecision = 1,
    # Make the last row (usually Total) bold
    table.lastRowBold = TRUE,
    # Header background colour and white text
    header.fgFill = "#003366",
    header.fontColour = "#FFFFFF",
    header.textDecoration = "bold",
    # Data cell font size
    body.fontSize = 11
  ) |>
  write_xlsx(path = "styled-table.xlsx")
Excel sheet: tweaks

Good to know: A few settings only mean something in a spreadsheet: row heights (height), column widths (width, table.widthOffset), table.offsetRow, table.offsetCol, table.gridLines, table.tabColour, table.locked, table.hidden, indent and wrapText. HTML, Word and PDF ignore them, so you can attach them once and export to any format without changing anything.

Borders

Borders take three settings per part: border (which sides: "top", "bottom", "left", "right"), borderColour and borderStyle. Border styles use the Excel names in every format; HTML, PDF and Word draw the nearest line:

borderStyle HTML / PDF Word
"thin", "hair" 1px solid 0.75pt solid
"medium" 2px solid 1.5pt solid
"thick" 3px solid 2.25pt solid
"dashed", "mediumDashed" 1px / 2px dashed 0.75pt / 1.5pt dashed
"dotted" 1px dotted 0.75pt dotted
"double" 3px double 2.25pt double
"dashDot", "dashDotDot", "slantDashDot" and their medium forms dashed dashed

The parts with borders are body (every data cell), header (every header cell), spanner (the upper header rows of a crosstab), border_header (the rule under the header) and border_outer (the outline around header and body). body.bandFill shades every other data row.

person_record |>
  generate_crosstab(marital_status, sex) |>
  add_facade(
    border_outer.border = c("top", "bottom"),
    border_outer.borderStyle = "medium",
    border_header.border = "bottom",
    border_header.borderStyle = "thin",
    body.bandFill = "#F2F2F2"
  ) |>
  write_html(path = "rules.html")

Applying the same tweaks across formats

Once you attach style overrides with add_facade(), those overrides are carried with the table object and applied to whichever format you export to. You only need to style the table once.

styled_tbl <- person_record |>
  generate_frequency(sex) |>
  add_table_title("Distribution by Sex") |>
  add_source_note("Source: Survey 2024") |>
  add_facade(
    header.fgFill = "#003366",
    header.fontColour = "#FFFFFF",
    header.textDecoration = "bold",
    body.fontSize = 11,
    table.lastRowBold = TRUE
  )

write_xlsx(styled_tbl, path = "table.xlsx")
write_html(styled_tbl, path = "table.html")
write_pdf(styled_tbl, path = "table.pdf")
write_docx(styled_tbl, path = "table.docx")

Making your own style file

For full control, generate a starter YAML file, edit it to your taste, and load it back in.

Step 1: generate the template

generate_template("my-style.yaml", template = "facade")

Step 2: edit the file

Open my-style.yaml in any text editor. It looks like this:

table.fontName: 'Arial'
table.fontSize: 12
table.offsetRow: 0
table.offsetCol: 0
table.decimalPrecision: 2
table.lastRowBold: false
table.width: 14

header.fgFill: ~          # ~ leaves the setting off
header.fontColour: ~
header.textDecoration: ~
header.height: 28

body.fontSize: ~
body.bgFill: ~
body.numFmt: "###0"
body.height: 20

title.fontSize: 13
title.textDecoration: "bold"
title.height: 24

source_note.fontSize: 10
source_note.textDecoration: "italic"

Change any value you like. A setting set to ~ is turned off; a setting you delete from the file comes from the default facade.

Tip: get_tsg_facade() also accepts .json file paths if you prefer JSON over YAML.

Step 3: use your style

Pass the file’s path as the facade of any writer:

tbl <- person_record |>
  generate_frequency(sex)

write_xlsx(tbl, path = "custom-styled.xlsx", facade = "my-style.yaml")
write_docx(tbl, path = "custom-styled.docx", facade = "my-style.yaml")

Programmatic style overrides

If you need to build style settings dynamically (for example, in a loop or from a config), keep them in a named list and pass it to add_facade() with do.call(), or pass the list as the facade of a writer.

overrides <- list(
  table.offsetRow = 3,
  header.fgFill = "#2E4057",
  header.fontColour = "#FFFFFF",
  body.fontSize = 11
)

do.call(
  add_facade,
  c(list(data = person_record |> generate_frequency(sex)), overrides)
)
#> # A tibble: 3 × 3
#>   category   frequency percent
#>   <int+lbl>      <int>   <dbl>
#> 1 1 [Male]        1516    52.0
#> 2 2 [Female]      1402    48.0
#> 3 0 [Total]       2918   100

Style property reference

The table below lists every setting supported by each section of the table.

Section Properties
table fontName, fontSize, fontColour, bgFill, fgFill, halign, valign, textDecoration, wrapText, indent, border, borderColour, borderStyle, width
title fontName, fontSize, fontColour, bgFill, fgFill, halign, valign, textDecoration, wrapText, indent, border, borderColour, borderStyle, height
subtitle fontName, fontSize, fontColour, bgFill, fgFill, halign, valign, textDecoration, wrapText, indent, border, borderColour, borderStyle, height
header fontName, fontSize, fontColour, bgFill, fgFill, halign, valign, textDecoration, wrapText, indent, border, borderColour, borderStyle, height
spanner fontName, fontSize, fontColour, bgFill, fgFill, halign, valign, textDecoration, wrapText, indent, border, borderColour, borderStyle, height
body fontName, fontSize, fontColour, bgFill, fgFill, halign, valign, textDecoration, wrapText, indent, border, borderColour, borderStyle, numFmt, height
col_first fontName, fontSize, fontColour, bgFill, fgFill, halign, valign, textDecoration, wrapText, indent, border, borderColour, borderStyle, numFmt, height, width
col_last fontName, fontSize, fontColour, bgFill, fgFill, halign, valign, textDecoration, wrapText, indent, border, borderColour, borderStyle, numFmt, height, width
row_group fontName, fontSize, fontColour, bgFill, fgFill, halign, valign, textDecoration, wrapText, indent, border, borderColour, borderStyle, height, width
sub_group fontName, fontSize, fontColour, bgFill, fgFill, halign, valign, textDecoration, wrapText, indent, border, borderColour, borderStyle, height
source_note fontName, fontSize, fontColour, bgFill, fgFill, halign, valign, textDecoration, wrapText, indent, border, borderColour, borderStyle, height
footnotes fontName, fontSize, fontColour, bgFill, fgFill, halign, valign, textDecoration, wrapText, indent, border, borderColour, borderStyle, height

Table-level settings (full list)

Setting What it does Excel HTML Word
table.fontName Default font family ✓ ✓ ✓
table.fontSize Default font size ✓ ✓ ✓
table.fontColour Default font colour ✓ ✓ ✓
table.bgFill Table background colour ✓ ✓ ✓
table.decimalPrecision Decimal places for numbers ✓ ✓ ✓
table.decimalCols Which columns get decimal formatting ✓ ✓ ✓
table.lastRowBold Make the last row bold ✓ ✓ ✓
table.offsetRow Blank rows above the table ✓
table.offsetCol Blank columns to the left ✓
table.gridLines Show worksheet grid lines ✓
table.tabColour Excel worksheet tab colour ✓
table.locked Lock the worksheet ✓
table.hidden Hide the worksheet ✓
table.width Column width ✓
table.widthOffset Extra width added to auto-sized columns ✓

Colour values

fgFill is the fill colour of a cell. bgFill is the background of a fill pattern in Excel, so it shows only behind a pattern; HTML and Word use it when fgFill is not set. All fontColour, bgFill, and fgFill settings accept:

Section names

Section What it styles
table Global table settings
title Title row above the table
subtitle Subtitle row
header Column header row(s)
spanner Grouped header rows in cross-tabulations
body Data rows
col_first First (category) column
col_last Last column
row_group Group header rows
sub_group Sub-group rows
source_note Source note row below the table
footnotes Footnote rows
border_header Border under the header
border_outer Outer border around the table
border_bottom Bottom border row height