Customizing the formatting of built-in standard tables in Q is somewhat limited and you are limited to the table styles available in Office. Sometimes you may need to create very custom formatting changes to a table, this can be done with an Autofit table (use Create > Tables > Enter Data and check Autofit). However, you have even more flexibility in formatting with creating the table in R using the CreateCustomTable function from our flipFormat package.
In our example we will go from a table with two columns differences...
...to a CreateCustomTable R table (similar to a table with Autofit checked) with specific colors and a difference column colored by values:
- A table or you can create your own table/data inside the R Output. We will use the table above in our code below.
- Knowledge of customizations available in CreateCustomTable.
1. Right click on the Report tree and select Add R Output.
2. Paste the below under Properties > R CODE and modify it to your liking:
####Setup the data
#specify the table you'd like to use
mytable = table.Brand.attitude.duplicate
#change it to a data.frame to make it easier to work with
#create difference column
#divide by 100 to get in percentage format
####Create the formatting
#make matrix of cell.colors the same shape as your table default the color to white
#shade the cells green above 0%
cell.colors[mytable[,NCOL(mytable)] > 0,NCOL(mytable)]="green"
#shade cells red below 0%
cell.colors[mytable[,NCOL(mytable)] < 0,NCOL(mytable)]="red"
####Create the table
#create custom table using CreateCustomTable
row.header.fill = "#636363",
row.header.font.color = "white",
row.header.pad = 5,
col.header.fill = "#636363",
col.header.font.color = "white",
corner.fill = "#636363",
corner.font.color = "white",
3. Right click on the R Output and select Calculation Options.
4. Change Exporting > Format > To PowerPoint as Image of R Output. Note that this means you cannot edit the table once it is exported to PowerPoint.
Note, you can reference colors by HTML name, HEX, and rgb, e.g. rgb(255,255,255).