Exporting from R to Excel with Conditional Formatting

It’s often the case that I want to share preliminary results with colleagues and have them provide feedback. This is pretty easy to do as a pdf using RStudio and a bit of LaTeX or markdown. And it’s now easy to turn my notes and results into a webpage via RStudio and tools like RPubs.
But sometimes I need others to comb through pages of results like factor loadings, and these output formats don’t cut it. In these cases, I export to Excel using the xlsx package so everyone can sort, make notes, highlight cells, etc.
This works really well, but I had a case recently where I needed a team of people to review 20+ sheets of factor loadings and comment on possible structures and interpretations. It’s common to start with some cutoff for factor loadings (x), often abs(x)>=0.30, so conditional formatting in Excel is a nice way to visualize patterns.

cf

This is no problem to do with a few sheets once or twice, but it gets repetitive if you have a lot of sheets or want to do the exercise a few times when you regenerate files with new assumptions. Here’s a brief example that shows how to dump loadings from exploratory factor analyses into different sheets of an Excel file and then highlight the cells yellow if abs(x)>=0.30.

Tags: , , ,