#Excel Super Links #12 – shared by David Hager

And, the Super Links just keep on coming!

Automation AddIns As Functions Libraries For Excel And VBA


VBA4Play Part 1: Movement and Collision Detection


Denormalizing Tables in Power Query


My First Custom Ribbon Using Excel-DNA And Visual Studio


The DAX ENDSWITHX Function Equivalent


#Excel: Using Conditional Formatting to Highlight Cells Containing User-Defined Functions by David Hager

Conditional fomatting (CF) in Excel is a powerful tool for highlighting cells that meet certain criteria based on a formula. However, to my knowledge a worksheet formula has never been used with CF to highlight cells containing user-defined functions (UDF). Presented here is a method to accomplish this.

In order to lookup whether a formula is a UDF, a lookup table of all Excel worksheet functions is required. I found the list for this at this Microsoft site.


The list is in the range A2:A472 in the example workbook. It was defined as xlFunctionList. I tried to just name an array of function names, but it exceeded the number of characters allowed.

So, with E2 as the active cell, I created the following defined name formula:


The formula =MID(FORMULATEXT(E2),2,FIND(“(“,FORMULATEXT(E2))-2) locates the first occurrence of the left parens. This value in used by the MID function to return a string with the function name. Then, the MATCH function looks up whether that string is in the function list. If it is not there the ISERROR function will return True. Thus, when applied as conditional formatting to cells E2:F2, E2 has a yellow highlight, indicating that a UDF function is in that cell. F2, containing a native function, is not highlighted. Note, though, that this technique only works if the function is at the beginning of the formula. Perhaps a followup to this article will deal with that issue.


You can download the example file here:


#Excel Super Links #11 – shared by David Hager

I am happy to see that y’all are finding these links useful.

In Cell Charting


Calculate Start and End Dates in Power Query


3 Helpful Power BI Features You Might Not be Using


Formula Forensics 042: Reverse Text – A Formulaic Solution Using TEXTJOIN


Excel Irregular Banding on a Filtered List



#Excel Super Links #10 – shared by David Hager

Here is the 10th issue of ESL. One of the links in each Excel Super Links will be to articles on my web site from now on.

How to Solve an Equation in Excel


Excel Matrix Multiplication – Replacing MMULT with Power Query


Parent-Child Hierarchies in DAX


User-defined Excel Chart Tooltips


BINGO! An #Excel Game – Random Numbers W/O Repetition Using Worksheet Formulas



#Excel Short and Sweep Tip #3 (Freeze Values) by David Hager

Highlight a worksheet range that contains formulas or a mixture of formulas and values. Grab the edge of the range with right-click and pull away, then immediately place back in the same range location. The right-click menu will appear. Click the option labelled “Copy here as Values only”. Every formula in the range will be converted to its underlying value.


#Excel Super Links #9 – shared by David Hager

Issue #9 is here! Starting wth issue #10, I will start presenting some of my blog posts.

Excel Magic Trick 1403: DAX Formulas: Explicit or Implicit Measures? Why Explicit is Better


Solving Business Problems – Power Query Does That


The Art & Science of Creating An Excel Dashboard (With Examples & Templates)


Prevent Users Ctrl Break (ing) Your VBA Code During Execution


Sorting a List Alphabetically (Without Filters) with Excel Formulas