How to Format Areas of a Table Using the Formattable R Package

This article describes how to go from a table...

...to an R table using the formattable package with formatted cell ranges.

Requirements

• A table with multiple rows and columns

Method

1. Select your table.

2. Copy the name from Properties > General > General > Name.

3. From the toolbar, select Calculation > Custom Code.

4. Click on the page to place the custom calculation.

5. Go to the Code panel.

6. Reference the formattable R library and define the table using the name from step 2. In this example, we are first adding the row headers of prevalence.table as a new column and then removing these row headers:

library(formattable)
prevalence <- cbind("Indicator Name" = rownames(prevalence.table), as.data.frame(prevalence.table))
rownames(prevalence) <- c()

7. Use the area argument within the formattable function. In this example, we have set all the columns other than Indicator Name as percentages and are using the built-in color_tile function to set the color gradient based on the cell values.

formattable(prevalence, align = c("l",rep("r", NCOL(prevalence) - 1)), 
list(`Indicator Name` = formatter("span", style = ~ style(color = "grey",font.weight = "bold")), 
area(col = 2:9) ~ function(x) percent(x / 100, digits = 0),
area(col = 2:7) ~ color_tile("#B1CBEB", "#3E7DCC")))

8. You can also create your own function for setting the gradient and other formatting. Here, custom_color_tile additionally applies custom padding, border radius, and font color.

custom_color_tile <- function (...) 
{
formatter("span",
style = function(x) style(display = "block", 
padding = "0 4px", 
`color` = "white", 
`border-radius` = "4px", 
`background-color` = csscolor(gradient(as.numeric(x), 
...))))
}

formattable(prevalence, align = c("l",rep("r", NCOL(prevalence) - 1)), list(
`Indicator Name` = formatter("span", style = ~ style(color = "grey",font.weight = "bold")), 
area(col = 2:9) ~ function(x) percent(x / 100, digits = 0),
area(col = 2:7) ~ custom_color_tile("#B1CBEB", "#3E7DCC")))

Next

How to Customize a Table Using the Formattable R Package

How to Add Statistical Significance to a Table Using the Formattable R Package

How to Add Sparklines to a Table Using the Formattable R Package