EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Excel Functions User Defined Function in Excel
 

User Defined Function in Excel

Madhuri Thakur
Article byMadhuri Thakur

Updated August 10, 2023

User Defined Function in Excel

 

 

Excel User Defined Function (Table of Contents)
  • Introduction to Excel User Defined Functions
  • Examples to Create User Defined Function

Introduction to Excel User Defined Function

Excel has reached a source of inbuilt functions to make your life easy when analyzing data. However, that is not the end. Sometimes, you might come up with a situation where all the formulae provided must be more suitable for your task. In such cases, you can create your function in Excel with the help of VBA; such functions are called User Defined Functions (UDFs). UDFs can be used within the worksheet the same way you use the normal Excel functions such as SUM, AVERAGE, etc. These functions can be created per the user’s requirement through VBA and ease the user’s tasks in Excel. In this article, we will discuss the User Defined Function in Excel.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Any UDF has a basic rule; you need to define it under a FUNCTION procedure instead of a subroutine procedure (sub-procedure we generally use). Using the FUNCTION procedure, we can create UDFs and use them inside Excel as the general Excel functions such as SUM, AVERAGE, VLOOKUP, etc.

This article will teach how to create UDFs per the user’s requirement and use them in Excel to ease the tasks.

Examples to Create User Defined Function

Let’s understand how to create the User Defined Functions in Excel with some examples.

You can download this User Defined Function Excel Template here – User Defined Function Excel Template

Example #1 – User Defined Function to Find the Area of Triangle

Students from the mathematical stream may always have a problem where they need to find a triangle area. Though it has a very easy formula to work around, students will be pleased if they have some function that can compute the area of a triangle. Follow the steps below to create a function that can compute the area of a triangle.

Step 1: Insert a new module under Visual Basic Editor (VBE). Open VBE > click on Insert tab > select Module.

Insert Module

Step 2: Define a new FUNCTION procedure with the name as TriangleArea. This name will be used to find this function in Excel.

User defined triangle area

Since it is a FUNCTION procedure, we must set the arguments to calculate the triangle area while defining the function.

Step 3: After TriangleArea, mention height and base as two arguments to this function in the bracket. These are the mandatory arguments. The user needs to provide values for height and base to calculate a triangle’s area. The FUNCTION procedure is complete (you can see the End Function clause), and we can write pieces/lines of code within this procedure.

User defined excel - height & base

Step 4: Now, we will write a code that will return the area for a triangle when we provide the height and base values within this procedure. Since the area of the triangle is formulated as we will formulate it under the code and assign it to TriangleArea.

Triangle Area excel 1

Suppose we have different height and base values, and we need to capture the area of a triangle for those values using the function we just have created. This is it; the function is created and can be used. Save the code, and we will see how it works for the different values of height and base.

Triangle Area excel

Step 5: In cell C2, start typing the formula =TriangleArea(A2, B2) to compute the area of a triangle and provide height and base as an argument to the same formula.

Triangle Area excel 2

Step 6: Close the parentheses to complete the formula and press Enter key to see the area of a triangle with height 12 and base 7.5.

use defined function excel 1

Drag the formula to get the area for the second and third values of height and base.

second and third values

Example #2 – User Defined Function to Square any Number

Suppose we want the squared value of any number (integer or float). No such function gives you the squared value for any number. Let’s create one that can do the task for you in Excel.

Step 1: In the same module, start defining a new FUNCTION procedure for squaring a number with the name SquareNum.

Sqaure num excel 1

Step 2: We need to add an argument for the function. Use num as an argument to the function within parentheses. As soon as you provide an argument within the function, you will be able to see the End Function statement, which means the arguments we have provided are accepted,

Square Number excel

Step 3: We can add the code/lines to get the square of numbers within the function. As num is the argument to the SquareNum function, we have two options to get the desired output. One way is to multiply num by itself (num * num ex. 2 * 2), or another way is to use the power operator “^” over the num function (num^2 ex. 3^2). I will go with the other way as it makes the code more versatile.

Square Number excel 1

Step 4: Save the code by clicking on the Save button under VBE so that we can use the function named SquareNum under the Excel sheet.

Save Excel User defined

Suppose we have a set of different numbers, as shown in the image below, and we need to find the square values for those. We can use the SquareNum function defined just above to get the result.

User defined Function excel 2

Step 5: In cell B2, start typing the Square, and you can see the function we just have defined for squaring a number. Double click on it to select the function.
User defined Function excel 3

Step 6: Provide A2 as an argument to this function so that it will square the number present in cell A2.

User defined Function excel 4

Step 7: Close the parentheses to complete the formula and press Enter key to see the square of number 11 (number in cell A2).

SquareNum excel

Step 8: Drag and Paste the formula across the remaining cells to find the square roots of the numbers provided.

SquareNum excel 1

This article emphasized creating User Defined Functions in Excel, which can reduce the man’s work. Let’s wrap things up with some points to be remembered.

Things to Remember About User Defined Functions in Excel

  • UDF, through VBA, helps create custom formulas that can reduce the time we need to do any task and simplify the tasks.
  • The UDF is limited to the sheet under which it is created. However, you can copy and paste the code to other worksheets you want./li>

Recommended Articles

This is a guide to User Defined Functions in Excel. Here we discuss how to create a User Defined Function in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

  1. Excel Function for Range
  2. Logical Functions in Excel
  3. Database Function in Excel?
  4. Time Function in Excel

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download User Defined Function Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download User Defined Function Excel Template

EDUCBA

डाउनलोड User Defined Function Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW