Is it possible to create a custom user function in Excel without using VBA?
My problem is I have a long string of standard excel functions strung together to make one very long function. This function is used across 25 different worksheets in my workbook. If I need to make changes to it I would like to do so in only one place and have the changes propagated across all the sheets automatically.

For example, a simple and trivial case would be to add one to the result of SUM(), ie. SUM(mySeries)+1, and call it a new function MYSUM().

I am hesitant to translate the long formula into VBA due to the potential errors that might arise and the added complexity.

  • Nice question, though I doubt you'll find an answer. I think a better course would be to ask for help building the required function in VBA.
    – EliadTech
    Apr 17, 2014 at 20:40

5 Answers 5


Yes, it's possible if you use Excel Named Formulas.

For instance, suppose that you need to calculate the difference between the sums of two consecutive columns (A5:Ax - B5:Bx) in different places in your workbook (x is the final row of the each column):

So you define in A11 a name called diff (any name can be used) as =Sum(A$5:A10)-Sum(B$5:B10), assuming that data start in row 5 until the previous row. It could be any cell, not just A11, but the definition changes in the same way.

Unfortunately, Excel 2010 inserts absolute prefixes ($) and worksheet prefixes, so you need to erase the prefixes, but keeping the exclamation marks and erase most $ characters.

When you move the formulas, most references are relative. So it always calculates the difference between the current column and the following, starting from the row 5 until the line before the total line.

So if you have data between C5 and D100 you put just =Diff in C101 and it calculates Sum(C5:C100) - Sum(D5:D100).

Obviously, you can use local or global names in the named formulas, like you mentioned in your question.

You can read more details in Named Formulas.


I know you said no VBA, but doing as follows does NOT require you actually re-write your formulas, know much about VBA, nor maintain your formulas in VBA. You can write it once and forget about it.

Create a User-Defined Function to extract the formula from a cell as a string:

Function GetFormula(Target As Range) As String
    GetFormula = Target.Formula
End Function

Create another to evaluate a string like a formula:

Function Eval(Ref As String)
    Eval = Evaluate(Ref)
End Function

If you had your master formula in sheet1!a1, then you'd put this in every cell that needs to use it:

  • What's the advantage of using this method over Excel Named Formulas?
    – DakotaD
    Sep 15, 2017 at 14:55
  • This actually defines a function, whereas "Named Formulas" just defines a reference to a result. For example, if I want to create a function that will check if a cell is either blank or just whitespace, I'd want to call it like =IF(BlankOrWhitespace(A5),true,false). For Named Formulas that's not possible because you're referencing the formula contents of a cell and not defining a new function
    – mtalexan
    Dec 15, 2017 at 19:02
  • 1
    There's another problem that arises from usage of VBA. Security. Your workbook would be consindered unsafe because VBA can be abused. Your workbook would be mistaken as having virus.
    – Xwtek
    Mar 22, 2018 at 14:27
  • @Akangkathe reason why I avoid VBA. That is so sad.
    – Pedro77
    Mar 21, 2019 at 12:42

This thread is a bit old, but I stumbled across it looking for something else and thought I'd share a workbook I created a while back trying to accomplish this exact thing: https://www.dropbox.com/s/gf5qrjj5q81tpke/Title_Case_Named_Range_Function.xlsx?dl=1

In short, you CAN create a UDF without VBA (sort of) by hijacking one of Excel's built-in formulas with a 0-length string output and coming up with a method to interpret the inserted data via Named Range string manipulation, but it is beyond inefficient to do so. YOU'VE BEEN WARNED! :)


  1. In the example, I used both =REPT([cell reference or some text between quotes here],0) and =TEXT([cell reference or some text between quotes here],";;;") as the starting point to be able to have some form of user input into a built-in formula that would not produce any visible text result in the cell itself.

  2. Then I created a Named Range that would be the equivalent of your UDF name (TCase is the primary example in the sheet, but there are also several other variations in there). This Named Range actually references a whole host of other hidden names in the workbook to extract the FormulaText from the cell it's used within and then applies some logic to it to produce a final output as a string. It's worth noting that this method could only return text results and not numbers (though it could return a number as text to be converted in a separate cell).

  3. On its own, =TCase produces an error since there is no cell/string reference for it to interpret (in the example sheet, the error message is just a reminder of the format that needs to be followed). But, combining TCase with the expected 0-length string Excel formula allows it to read the input from the formula as a string and apply your logic to it. In the end, the formula/output would look something like below. Note that I inserted the text string directly in the formula below, but you'll see in the example sheet that it can also interpret a single cell reference.

    =TCase&REPT("i want to convert this mIxEd sTRing into A TITLE cAsE string!!!",0)


    I Want to Convert This Mixed String Into a Title Case String!!!

In the end, I would not recommend following this method for anything other than a morbid curiosity to see what limits Excel can be pushed to. VBA is a much simpler and elegant UDF solution, but I at least had fun taking on the challenge.

NOTE: If you want to see how it works in the example file a bit easier, use the Evaluate Formula from the Formulas ribbon and step in/out of the formula in one of the cells where I inserted an example. I hid all of the underlying Named Ranges to clean it all up after I was done, so they don't show up in the Names Manager unless you unhide them all.

  • I didn't even know one could hide names from the unzipped /xl/workbook.xml file! I tested your workbook, but all cells output the err message...
    – hymced
    Jan 14, 2023 at 9:44
  • I understood the trick and was able to reproduce it in a simpler example, but I could make yours work :/
    – hymced
    Jan 14, 2023 at 18:13

There is a "LAMBDA function" feature in Office 365 that allows you to define a function entirely within Excel as a defined name.

No longer a need for macros and VBA. Certainly no need to "hijack" anything.

The documentation says it is available for Excel for Microsoft 365, Excel for Microsoft 365 for Mac, and Excel for the web

Please check this out: https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67


The following is based on the answer by @Kirk, but I wanted to provide an MRE/MCVE of the technique he uses.

enter image description here

Add the following names in the Name Manager:

Name            Refers to
myfn            =INDIRECT(myfn.reference)+1
myfn.debug      =myfn.reference
myfn.formula    =FORMULATEXT(myfn.self)
myfn.reference  =MID(myfn.formula;myfn.textcall+1;LEN(myfn.formula)-(myfn.textcall+1)-2)
myfn.self       =INDIRECT("RC";FALSE)
myfn.textcall   =FIND("(";myfn.formula)

Then call the "function" with a relative ref like this:


or with an absolute reference (or any other type of ref):


Note: to translate to fr-fr, one only need to replace "RC" by "LC"

  • This only works for a reference (a single cell, a range, or even a named range) passed as argument. To pass a string, one has to remove the INDIRECT() in myfn: =myfn.reference&" and some appended text" and call it like this in a cell: =myfn&REPT("some text";0). If you want to pass the return value of another function as argument: =myfn&REPT(REPT("a";3);0) or pass a named constant (with Refers To set to "=1" for example): =myfn&REPT("named_constant";0), the XLM EVALUATE() function will be required instead of INDIRECT() (and your file will need to be an .xlsm one)
    – hymced
    Jan 15, 2023 at 22:17
  • superuser.com/questions/253353/…
    – hymced
    Jan 15, 2023 at 22:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .