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 […]

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 […]

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 […]

Making Easy, Flexible Histograms with PivotTables

This post accompanies my segment on MyExcelOnline’s “Best Excel Tips of 2017 Podcast.” The first commandment of data analysis: KNOW THY DATA! It’s always smart to get a feel for your data’s distribution. Histograms offer a great way to visualize the distributions of a variable of interest. Download the exercise file here. A histogram is […]

All White Space, No Fillers: Hiding Gridlines in Excel

I don’t use Excel for everything these days, but I continue to use it for formatting tables. Because, let’s be honest. Word is simply not up to the task when it comes to cleanly-formatted tables. Generally tables in publications do not include gridlines – it’s pixel overload. So to remove my gridlines before saving the […]