Highlight Every Cell Containing a Formula

Checking for data accuracy is tricky in Excel.  In this blog, I have been presenting some of my tips.  This is a great one for more visually-oriented users. We have a simple inventory model below.  Even in this simple model, there are many formulas, dependent cells, and inputs.  How is the shrink calculated?  Did someone hard-code the […]

No more clumsy file paths! Email “Live” Hyperlinks from Office

Here’s a shortcut I use that impresses people at the office. When collaborating on an Office document, people often either send an attachment of the file or send a “dead” (as in, un-linked) filepath. Subscribe for more Excel tips and tricks. The first isn’t too helpful because that attachment will be a local file, not […]

Excel, Big Data, and Google Trends: What’s Going On?

Trends is a lesser-known Google tool that allows you to follow search interest of a particular topic.  Here’s an unscientific but interesting trend I noticed tonight. As an analyst, I live in Microsoft Excel, and to a lesser extent, Access.  I also hear “big data” a lot.  To me, Excel is the Model T to […]

Build your own Heatmap in Excel

Conditional formatting is a great tool in Excel.  It sounds fancy, but if you’ve ever color-coded anything before, you know how conditional formatting works.  Given the rules you provide Excel, it will format things accordingly. Many conditional formatting features have been added to recent editions of Excel.  My favorite is the Color Scale.  This feature allows you to look […]

Ctrl + ~ : An X-ray of your spreadsheet

This is one of my favorite Excel keyboard functions, used in frequency only after Ctrl + S, but not many know about it. Excel is great for easy data analysis, but it is hard to check for errors. Remember the scandals concerning spreadsheet errors in Thomas Piketty’s book? If Nobel Prize-winning economists have trouble making sure their formulas […]