Category Archives: Techniques

Tips from 20 Excel Experts

My Excel-friend John Michaloudis does an annual interview of Excel experts from all over the world. The format of his interview is an audio podcast. This year, you can hear directly from 20 Excel experts as they share their best Excel tips and ideas. The tips include shortcuts, functions, and features, and it is a […]

Data Validation with ALL CAPS

In recent back-to-back webinars, two different attendees asked how to check to see if a user entered a text string with all caps. Specifically, they asked how to set up a data validation rule to determine if all letters are capitalized. So, I figured I would write a quick post about the technique. Objective Let’s just take a moment […]

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

Slicers as an Alternative to Conditional Drop Downs

Slicers by Jeff Lenning

Beginning with Excel 2013 for Windows, we can use Slicers to filter table data. In this post, we’ll explore how to use Slicers as a relatively easy alternative to conditional drop-downs. Objective When we want to allow a user to select a choice from a list of items, we often consider using the Data Validation feature to […]

Income Tax Formula

In this post, we’ll examine a couple of ideas for computing income tax in Excel using tax tables. Specifically, we’ll use VLOOKUP with a helper column, we’ll remove the helper column with SUMPRODUCT, and then we’ll use data validation and the INDIRECT function to make it easy to pick the desired tax table, such as single or married […]