Use Power Query to Create a Drop-Down List without Duplicates

In this post, we’ll create a drop-down that contains a unique list of choices derived from a column that contains duplicate values. This may sound familiar as we previously accomplished this with a PivotTable. However, the Power Query feature that’s built-in to Excel 2016 makes this process easier. Objective We have a data table that contains RepID, […]

Hyperlinks Article

Back in the old days, when I used workpapers that were made with, you know, paper, I used a red pencil to create cross-references that made it easy to navigate to related documents. Today, I use Excel hyperlinks instead. Check out this CalCPA Technology and Business Resource Guide article where I describe how to use Excel hyperlinks to cross-reference digital […]


I recently had the immense pleasure of being a guest on the My Excel Online Podcast with John Michaloudis. He is a PivotTable master and an Excel friend. If you’d like to listen to the audio interview, I’ve provided the link below. While you are there, be sure to subscribe to John’s podcast, he does an amazing […]

Sort by Color

Sort by color

In this post, I’ll answer a question submitted by reader Chérie about sorting by color. The basic question is this. “I have created a color coded list, where yes=green, no=red, maybe=orange, and other is any other color. How can I sort the list so that all the yes rows are first, then no, then maybe, […]

Number Format Macro

Excel users often spend a bunch of time applying the same format to number cells. Some numbers are stored values and others are calculated with formulas. The task of manually applying the same format to all of the numbers in the worksheet, especially when the numbers aren’t in a continuous range, can feel quite repetitive. […]

Default Workbook

Excel users find themselves making the same customizations over and over again. This post demonstrates how to store customizations such as cell formatting, headers, print preferences, the default PivotTable style, and cell styles, in a template workbook. Plus, we’ll see how the name and location of the template determines when it is used, and, if it […]