Excel Tip: Excluding Diagonals from Conditional Formatting

Download the exercise file here.  This afternoon I created a correlation matrix which I then conditionally formatted to give me a heatmap-like take on the data: Going to Data | Conditional Format | Color Scales, I decided that green would be good and red bad (Logical, right? Thanks, Excel.). Notice a problem? The 1’s across the […]

Converting RTF Files to TXT in R

Recently I downloaded a large number of RTF files. Wanting to analyze this text data in R, I first needed to strip RTF encoding. In this video I use the striprtf library to loop through a folder of files, convert them to .TXT, and save those .TXT files to another folder. The code is pasted […]

Assign Cell Color Fills to Custom Keyboard Shortcuts in Excel

When exploring a workbook, I like to color-code cells so that I remember what I need to do with that information. Red cells indicate I need to take action on something, while green means all systems go. Yellow means either neutral, or that I haven’t decided yet! That’s my system, anyway. And maybe you have your […]

R for Excel Users: The XLView() Function

It’s time for another post about R. I’ll admit, these days most of my work is done in R. But, I still love using Excel, and I really do enjoy teaching and blogging about it. Fortunately, these two platforms work great together… they collide, in a good way. And XLView() is a great example. Subscribe […]

Get Real-Time Cryptocurrency Prices from Yahoo! Finance to Excel

I’ve been a casual student and advocate of cryptocurrencies since ca. 2014, but recent news has attracted me (along with everyone else and their Uber driver) to them more seriously. Subscribe to my newsletter for your free ebook, “The Beginner’s Guide to Getting Hired with Excel.” This afternoon, I wanted to find a way to […]